Jump to content


Photo

Database connectivity - 1

database connect datasource

  • Please log in to reply
No replies to this topic

#1 Nico Wouterse

Nico Wouterse
  • Moderators
  • 230 posts
  • LocationAustralia

Posted 17 May 2017 - 03:56 AM

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
 

  • ielite and Igor Savkic like this
Nico Wouterse





Also tagged with one or more of these keywords: database, connect, datasource

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users