Jump to content
Sign in to follow this  
lynkfs@gmail.com

Database connectivity - 1

Recommended Posts

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

 


Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×