* * *

Author Topic: Live bindings to Firebird table  (Read 459 times)

Wargan

  • New member
  • *
  • Posts: 41
    • 'This way' site
Live bindings to Firebird table
« on: September 09, 2017, 09:06:27 am »
Hello, friends!
I recently started getting to know Firebird 3.0.2.

However, in the tools "out of the box", I did not find things that were extremely important to me, such as binding to table classes from the database.

Here is my example:
I use Virtual String Tree to display data from a database (this is a list of streets). To improve performance, I used records.

Code: Pascal  [Select]
  1. TStreet = record
  2.     Id: integer;
  3.     Name: string;
  4.   end;

However, in the future, everything changed in my work :)

Firebird database has a table called Streets with the following parameters:

Code: MySQL  [Select]

In the future, I still plan to create a separate class using the MVC pattern, like this template:

Code: Pascal  [Select]
  1.   TStreetEx = class
  2.   private
  3.     FId: integer;
  4.     FName: string;
  5.     procedure SetId (AValue: integer);
  6.     procedure SetName (AValue: string);
  7.   public
  8.     property Id: integer read FId write SetId;
  9.     property Name: string read FName write SetName;
  10.   end;

So, can I bind the fields of the Streets table to the fields of my TStreetEx? For them to be loaded automatically (for example, by binding to TSQLQuery), then they were changed through the program interface, and then saved.

p.s. so I understand, to use several TStreetEx I need to create a separate class TStreetsList with the methods BeginUpdate and EndUpdate, and then shove them into the VST...
« Last Edit: September 09, 2017, 09:37:59 am by Wargan »
Lazarus 1.6.4 stable + FPC 3.0.2.
Windows XP SP3, RAM 1 Gb - testing and commissioning programs for old computers.

mangakissa

  • Hero Member
  • *****
  • Posts: 710
Re: Live bindings to Firebird table
« Reply #1 on: September 09, 2017, 09:58:45 am »
Well, something like mORMot or tiOPF shall do the trick. MORMot use SQLite as internal database.
I haven't read MVC pattern book yet, but you have to create a link by yourself.
Filling the class is easy:
Code: Pascal  [Select]
  1. uses fgl;
  2.  
  3. TStreetEx = class
  4.   private
  5.     FId: integer;
  6.     FName: string;
  7.     procedure SetId (AValue: integer);
  8.     procedure SetName (AValue: string);
  9.   public
  10.     property Id: integer read FId write SetId;
  11.     property Name: string read FName write SetName;
  12.   end;
  13.  
  14. TStreetlist = specialize Tobjectlist<TStreetEx>;
  15.  
  16. TStreets = class
  17. private
  18.   fStreetlist : TStreetlist;
  19. public
  20.   constructor create;
  21.   destructor  Destroy; override;
  22.  
  23.   procedure load;
  24. end;
  25.  
  26. constructor TStreets.create;
  27. begin
  28.   fStreetlist := TStreetlist.create;
  29. end;
  30.  
  31. destructor Tstreets.destroy;
  32. begin
  33.   fStreetlist.free;
  34. end;
  35.  
  36. procedure TStreets.load;
  37. var SQLQuery : TSQLQuery;
  38.     FStreetEx: TStreetEx;
  39. begin
  40.   SQLQuery := TSQLQuery.create(nil);
  41.   try
  42.     /* connect your table to SQLQuery */
  43.     while not SQLQuery.eof do
  44.     begin
  45.       FStreetEx      := TStreetEx.create;
  46.       FStreetEx.Id   := SQLQuery.fields[0].AsInteger;  
  47.       FStreetEx.Name := SQLQuery.fields[1].AsString;
  48.       fStreetlist.Add(FStreetEx);
  49.       SQLQuery.next;
  50.     end;
  51.   finally
  52.     SQLQuery.free;
  53.   end;
  54. end;
  55.  
Delphi has his own livebindings and use the same method underwater.

It's not perfect, but it answers your question.
For generics: see http://wiki.freepascal.org/Generics
« Last Edit: September 09, 2017, 02:17:04 pm by mangakissa »
Lazarus 1.64 (32b) / FPC 3.0
Windows 10

Thaddy

  • Hero Member
  • *****
  • Posts: 4446
Re: Live bindings to Firebird table
« Reply #2 on: September 09, 2017, 03:21:09 pm »
Hello, friends!
I recently started getting to know Firebird 3.0.2.
p.s. so I understand, to use several TStreetEx I need to create a separate class TStreetsList with the methods BeginUpdate and EndUpdate, and then shove them into the VST...

You are using the wrong string type in your record.
Code: Pascal  [Select]
  1. TStreet = packed record
  2.     Id: integer;
  3.     Name: Array[0..31] of AnsiChar;
  4.   end;

Maps to
Code: MySQL  [Select]

Note if your database is created in UTF8 it needs to be array[0..127] of AnsiChar, four times the size!
Subsequent conversions are otherwise easy.

Reason is the database stores no string length inside the record, but in the table and field meta data, so you need to use an array of a suitable fixed length char type. (Ansi, UCS2, or UTF32).
UTF8, UTF16 are variable length char types (from 1 to 4 bytes).
Pascal strings always contain string length...The database does not.

Note your record can have e.g. an added ToString function to do that. Advanced records... Does not matter for mapping or storage.


« Last Edit: September 09, 2017, 03:39:53 pm by Thaddy »
"Logically, no number of positive outcomes at the level of experimental testing can confirm a scientific theory, but a single counterexample is logically decisive."

Graeme

  • Hero Member
  • *****
  • Posts: 1394
    • Graeme on the web
Re: Live bindings to Firebird table
« Reply #3 on: September 11, 2017, 11:15:01 am »
Take a look at tiOPF [http://tiopf.sourceforge.net], it does exactly what you need, and has been open source and continuously maintained and  developed since 1999. One the "tiopf_apps" repository, there is also a db mapping tool that will generate fully working tiOPF based units for you - from simple XML mapping information. This will save you tons of boiler-plate coding. tiOPF also include an implementation of MGM (Model-GUI-Mediator) which is very similar to MVC, except that it reused the events of modern GUI toolkits - no need to reinvent the wheel again - what MVC requires.

You are welcome to visit the tiopf.support newsgroup for any additional questions. Simply follow the Support link on the website.
--
fpGUI Toolkit - a cross-platform GUI toolkit using Free Pascal
http://fpgui.sourceforge.net/

 

Recent

Get Lazarus at SourceForge.net. Fast, secure and Free Open Source software downloads Open Hub project report for Lazarus