Jump to content

Database connectivity - 1


Recommended Posts

  • Moderators
Delphi has a number of database components, which make it pretty easy to connect to external datastores.

TDataBase, TTable, TQuery and TDataSource encapsulate database connections, provide access to tables directly or through queries, and provide the links to visual elements.

 

I think it is feasible to construct something similar (and maybe even better) in Smart

 

This post is an exploration on how this could look, proof of concept basically

Please feel free to comment

 

Slide1.JPG

 

It should be possible to connect to a variety of external databases, like MySQL, Oracle etc, as long as they understand ansi-SQL, and to internal datastores based on SQLLite or TDataSet

 

Server connections should at least cover php/ajax and node/sockets

 

There probably should be a set of DB-aware visual components, like TDBEditBox, TDBMemo, TDBCheckBox etc. which make it possible to display and alter data, and maybe also a set of read-only components like TDBLabel for read-only purposes.

 

At this stage these terms only loosely refer to the Delphi component names, and they certainly will not be a straight copy in any way or form. 

 

 

How would this work

 

a) from server to client

- data will be accessed through sql-queries (TQuery) or implied queries (TTable)

- current data will be stored in TFields, which also store links to visual components on the form

 

b)from client to server

- data can be changed by the user using the specialised DB-aware components, but also programmatically (business logic)

- all changes to TField-data will auto-propagate back to the database - if possible

 

Slide2.JPG

 

 

c) possible further future explorations 

- it should eventually be possible to auto-generate forms based on query results. F.i. a query like 'select firstname, lastname, gender, age from empl' contains enough information to be able to generate a form with the relevant editboxes, labels etc.

- by adding some kind of templating, it should be possible to auto-generate more intricate forms, like forms which can handle master-detail relationships (supplier -> supplier invoices)

- looking at formal mapping of relational and object data (orm), to see if it would be worthwhile to delve into that

 

 

TField

Lets start with this non-visual object.

TFields are in a way the interface between database and form elements, and as such store current values and the links to visual elements.

The key behaviour is that whenever its data changes, an attempt is made to update the underlying database. Data can be changed by the user, or programmatically, and both have the same effect.

 

In the new version of Smart there is a TVariant.watch function built in, which could be used to trigger actions on changes. However this version is in alpha only.

Alternatively an eventlistener can be added which fires when the data changes.

 

The following code implements this behaviour

 



type
  TField = class (TObject)
  private
    FHandle: THandle;
  public
    Constructor Create; virtual;
    Procedure Bind(Element : Variant);
    Procedure ChangeValue(Data: Variant);
  end;
 
  procedure updateDB(value: string);
 
implementation
 
Constructor TField.Create;
begin
  inherited Create;
 
  asm
    function MyField() {};
 
    MyField.prototype.bind = function (element) {
      this.element = document.getElementById(element);
      this.data = this.element.value;
      this.element.addEventListener('change', this, false);
    };
 
    MyField.prototype.handleEvent = function (event) {
      switch (event.type) {
        case "change":
            this.change(this.element.value);
      }
    };
 
    MyField.prototype.change = function (value) {
      if (this.data !== value) {
        this.data = value;
        this.element.value = value;
        @updateDB(value);
      };
    };
 
    @FHandle = new MyField();
 
  end;
 
end;
 
Procedure TField.Bind(Element : Variant);
begin
  FHandle.bind(Element);
end;
 
Procedure TField.ChangeValue(Data: Variant);
begin
  FHandle.change(Data);
end;
 
procedure updateDB(value: String);
begin
  ShowMessage('updating field in DB with value : ' + value);
end;


 

 

and can be used like

 



  MyField.Bind(W3EditBox1.TagId);
 
//bind the TField to some visual element (W3EditBox1)
//when a user types something in this EditBox, and leaves it, the change event fires
//which triggers the change function
//this works for <input>, <select>, and <textarea> based elements
//so EditBox, Memo, CheckBox, RadioBox, ComboBox, ToggleSwitch, datepickers, filepickers etc
 
 
  MyField.ChangeValue('aaaaa');
 
//or change its value programmatically, which also executes the change procedure


 

Demo project code here

The first button binds a TField to an editbox

Changing the value and leaving this field (click somewhere else) triggers an update action

The second button programmatically changes the TField value which also triggers the update action

 


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...