Delphi, Firebird, FibPlus. Part 5 – Parametric Datasets

0

In the previous parts we used the TpFIBDataset component to access all records in a database table. Here we will use the TpFIBDataset component to access only the records that satisfy a certain condition.

Let us improve our “Electronic library” database by adding a “PUBLISHER” table. The publishers and books are “one-to-many” related, so we also add a column into the “BOOK” table that references the “PUBLISHER” table. Execute the following script using ISQL:

SET SQL DIALECT 3;
SET NAMES UTF8;
CONNECT 'localhost:D:\ElLib\ElLib.fdb' user 'SYSDBA' password 'masterkey';

CREATE TABLE PUBLISHER (
  PUBLISHER_ID INTEGER NOT NULL,
  PUBLISHER_NAME VARCHAR(40),
  CONSTRAINT PK_PUBLISHER
    PRIMARY KEY (PUBLISHER_ID)
  );

ALTER TABLE BOOK
  ADD BOOK_PUBLISHER INTEGER NOT NULL;

ALTER TABLE BOOK
  ADD CONSTRAINT FK_BOOK_PUBLISHER
  FOREIGN KEY(BOOK_PUBLISHER)
    REFERENCES PUBLISHER(PUBLISHER_ID);

CREATE GENERATOR GEN_PUBLISHER_ID;

SET TERM ^ ;

CREATE TRIGGER BI_PUBLISHER FOR PUBLISHER
ACTIVE BEFORE INSERT POSITION 0
AS
  BEGIN
    IF (NEW.PUBLISHER_ID IS NULL) THEN
      NEW.PUBLISHER_ID = GEN_ID(GEN_PUBLISHER_ID, 1);
  END^

SET TERM ; ^
COMMIT;

You can use the following command file to execute the SQL script in one click:

@echo off
cls
echo Creating publisher table
echo ------------------------
echo.

set ISQLEXE=C:\Firebird\Firebird_2_1\bin\ISQL.exe
set ISQLSCRIPT=publisher.SQL

if exist %ISQLEXE% goto findSQL
  echo %ISQLEXE% not found.
  goto abort

:findSQL
  if exist %ISQLSCRIPT% goto execSQL
  echo %ISQLSCRIPT% not found.
  goto abort

:execSQL
  %ISQLEXE% -i %ISQLSCRIPT%
  goto fin

:abort
  echo 
  echo Execution failed,

:fin

  set ISQLEXE=
  set ISQLSCRIPT=
  echo.
  pause

Drop a TpFIBDataset component to the data module and set its “Name” property to “dsetPublisherBook”:

The dataset is meant to access all books published by a certain publisher. Set “Database” and “AutoCommit” properties and click the ellipsis button on the right of the “SQLs” property to invoke the “SQL Generator” dialog. Select “BOOK” table in the “Tables/Views” list and doubleclick on it to generate selective query:

Now the autogenerated query is not what we need, and we must add “WHERE” condition manually:

Select “Generate Modify SQLs” tab:

and click “Generate SQLs” button:

The 4 modify SQLs are generated correctly and does not require further manual editing. Click “OK” button to close the dialog. Set the autoincrement – related properties in “AutoUpdateOptions”:

Doubleclick on the “dsetPublisherBook” component to invoke the “Fields Editor” dialog. Rightclick in the “Fields Editor” dialog and select “add all fields” from the context menu:

A parametric dataset requires one more step to set its properties compared with a “table” dataset. We must fill the parameter – linked field (“BOOK_PUBLISHER”) prior to insert a new record into the underlying database table “BOOK”. There are 2 ways to do it. The first possibility is to create an “OnNewRecord” event handler:

procedure TDM.dsetPublisherBookNewRecord(DataSet: TDataSet);
var
  DS: TpFIBDataSet absolute Dataset;

begin
  DS.FieldByName('BOOK_PUBLISHER').AsInteger:= DS.ParamByName('PublisherID').AsInteger;
end;

That is the same method as used in BDE TQuery component.

FibPlus provides a better alternative: expand the “AutoUpdateOptions” property of the “dsetPublisherBook” component and click the ellipsis button on the right of “ParamsToFieldsLinks”:

The “Params to Fields Links” dialog dialog appears:

Click the “Scan” button:

As we can see the link “BOOK_PUBLISHER=PUBLISHERID” is detected correctly. Click “OK” button to close the dialog – now the “BOOK_PUBLISHER” field will be autofilled by the “PublisherID” parameter value for every new record.

Delphi, Firebird, FibPlus. Part 4 – Autoincrement Fields

0

There are no native autoincrement fields in Firebird. Instead, the autoincrement fields are implemented using a generator and a trigger database objects. It takes a little more work to create an autoincrement field compared with other database servers with native autoincrement field support, but as an award we have a better control over autoincrement fields on the client side. First and foremost, we can get the value of an autoincrement field before the “INSERT” query is executed. The FibPlus datasets support this feature.

Select the previously created “dsetAuthor” dataset in the data module:


Expand the “AutoUpdateOptions” property of “dsetAuthor” component in the object inspector:


Set the “UpdateTableName” to “AUTHOR”, “KeyFields” to “AUTHOR_ID”, “GeneratorName” to “GEN_AUTHOR_ID” and “WhenGetGenID” to “wgBeforePost”:


Now every time before we insert a new record into the “AUTHOR” database table we get a new autoincremented value of the “GEN_AUTHOR_ID” generator and send the record to server with already filled “AUTHOR_ID” field. Let us have a look at the fragment from the original database generation script from the Part 1:

CREATE GENERATOR GEN_AUTHOR_ID;
..
CREATE TRIGGER BI_AUTHOR FOR AUTHOR
ACTIVE BEFORE INSERT POSITION 0
AS
  BEGIN
    IF (NEW.AUTHOR_ID IS NULL) THEN
      NEW.AUTHOR_ID = GEN_ID(GEN_AUTHOR_ID, 1);
  END^

The “BI_AUTHOR” trigger sees that the key field “AUTHOR_ID” is already filled and does not generate a new autoincremented value on server.

In our case we need to obtain the value of the “AUTHOR_ID” field on the client side before posting a new record for the “Refresh” query to work properly, so I have set the “wgBeforePost” value for the “AutoUpdateOptions.WhenGetGenID” property. Sometimes we may need to obtain a value for an autoincrement field even more earlier, and in that case we should use the “wgOnNewRecord” value for the “AutoUpdateOptions.WhenGetGenID” property.

Now we have set all properties of the “dsetAuthor” dataset we need. Drop a new dataset to the data module, name it “dsetBook” and set its transactional, SQL and autoincremental properties to access the “BOOK” database table.

Delphi, Firebird, FibPlus. Part 3 – Datasets and SQLs

0

In the previous Part 2 we have learned how to set the transactional properties of a FibPlus dataset. Here we will consider how to set the dataset’s “SQLs” property to access database tables.

Prior to access the database table we must set the database (“dbElLib”) component’s connection properties. Double-click the “dbElLib” icon in the data module, the “Connection Properties” dialog appears. Fill “User name”, “Password”, “Charset” and “SQL Dialect” parameters as shown below and click “OK” button:

Drop a TpFIBDataset component to the data module and set its “Name” property to “dsetAuthor”. The dataset is meant to access the “AUTHOR” database table:

Set the dataset’s transactional properties by setting “Database” and “AutoCommit” properties. Click the ellipsis button on the right of “SQLs” properties in the object inspector to show the “SQL Generator” dialog:

Change “No Objects” combobox to “Table/Views” to get the list of available database tables:

Doubleclick on the “AUTHOR” item of the list. You can see the automatically generated “SELECT” query on the left panel:

Switch to “Generate Modify SQLs” tab. You can set some options like “Key Fields” to generate the SQLs here. In our case the default settings are just what we need:

Click “Generate SQLs” button:

We can see four autogenerated SQL queries for the “AUTHOR” table. In our simple case there is no need to edit them. Click “OK” button to close the dialog.

The purpose of the first 3 modify queries is obvious. The purpose of the 4th (“Refresh”) query is to select a single record just after the record has been updated or inserted. The “Refresh” query is required because the record can be changed on server (by triggers) so that without refreshing the actual record data on server may differ from the record data in the dataset’s cache. After refreshing the dataset’s cache is updated and the record’s data on server and in the dataset’s cache are synchronized.

Delphi, Firebird, FibPlus. Part 2 – Datasets and Transactions

2

BDE uses TTable component to access database tables; FibPlus uses TpFIBDataset component for the same purpose. The TpFIBDataset settings are more complicated than TTable settings. Let us consider the transactional settings of TpFIBDataset.
The things that should be taken into account first are TpFIBDataset.Transaction and TpFIBDataset.UpdateTransaction properties. We must assign TpFIBTransaction components to these properties. When we assign a value to the TpFIBDataset.Transaction property, the TpFIBDataset.UpdateTransaction property is automatically assigned the same value. If we don’t change the automatically assigned TpFIBDataset.UpdateTransaction value, the TpFIBDataset works in a single transaction context. If we assign a different TpFIBTransaction component to TpFIBDataset.UpdateTransaction property, we have separate read and write transactions, so that the “SELECT” queries are executed in the context of read transaction while “UPDATE” and “INSERT” queries are executed in the context of write transaction.
Another important property is TpFIBDataset.AutoCommit. If AutoCommit = False (default setting) we should call TpFIBDataset.UpdateTransaction.Commit or TpFIBDataset.UpdateTransaction.CommitRetaining manually to save any changes made in dataset. If we set AutoCommit = True, every successful TpFIBDataset.Post call automatically saves the changes like good old TTable component do. If we are working in a single transaction context the changes are saved by TpFIBDataset.UpdateTransaction.CommitRetaining call, and if we are using two transactions the changes are saved by TpFIBDataset.UpdateTransaction.Commit.
The Firebird professionals recommend to use two separate transactions for read/write operations – a long read transaction and a short write transaction. Usually it is sufficient to assign the same two transactions to all datasets in a database application.

Now let us come from theory to practice. Create a new VCL application (select “File->New->VCL Forms Application” in the Delphi main menu) and add a new data module to it (select “File->New->Other…”, in the “New Items” dialog select “Delphi Files” on the left panel and “Data Module” on the right panel, click “OK” button):

Drop TpFIBDatabase component and two TpFIBTransaction components to the data module. Set the Name property of TpFIBDatabase to dbElLib, the Name property of the first TpFIBTransaction component to trRead (read transaction) and the Name property of the second TpFIBTransaction component to trWrite (write transaction):

Assign the database (d:\ellib\ellib.fdb) we created in Part 1 to the ElLib.DBName property. I have also changed ElLib.LibraryName property from default “gds32.dll” to “C:\Firebird\Firebird_2_1\bin\fbclient.dll” – this step is not nessessary, but logical since gds32.dll is meant for legacy applications. Assign trRead to dbElLib.DefaultTransaction and trWrite to dbElLib.DefaultUpdateTransaction:

Set trRead.TRParams property to

read
nowait
read_committed
rec_version

Set trWrite.TRParams property to

write
nowait
concurrency

Drop a TpFIBDataset component to the same data module. All we have to do to set Transaction and UpdateTransaction properties of the TpFIBDataset component is to assign the TpFIBDatabase component to TpFIBDataset.Database property. One can see in object inspector that Transaction and UpdateTransaction properties are assigned automatically by trRead and trWrite transactions. If we don’t want to commit changes manually we must also set TpFIBDataset.AutoCommit = True:

So we see that after properly setting TpFIBDatabase and two TpFIBTransaction component’s properties we can set TpFIBDataset component’s transaction properties in two mouse clicks – the first click is to set Database property and the second click is to set AutoCommit = True.

Delphi, Firebird, FibPlus. Part 1 – Introduction

5

Once in a while I write desktop database applications. Last time it was about 5 years ago, and it was BDE application. Now BDE is obsolete and I was forced to choose something else for my new database project. After thinking a little I have chosen Firebird as a database server and FibPlus as a programmer’s library to access the database. I was absolutely new to Firebird and FibPlus and I had a lot of questions for the first time. Now I am feeling quite comfortable with these technologies (though I can’t say I am a Firebird professional) so I decided to write a series of articles about creating native database applications in Delphi for Windows using Firebird and FibPlus for the absolute beginner.
The first thing one should do is to download and install the latest stable Firebird version from http://www.firebirdsql.org . For my case it was Firebird 2.1.3. The installation process is easy and straightforward. The two things you should take care of are:
• Do not install Firebird in “Program Files” directory to avoid access rights problems on Vista and above Windows systems. I have chosen “C:\Firebird\Firebird_2_1″ directory

Firebird setup

• Make notice are you installing Firebird server+client or just a client alone. For the latter case uncheck the “Server components” checkbox. For the purpose of this post we need both server and client on a developer system.

Now we are ready to create our first Firebird database. For the illustration purposes I have created a sample “Electronic Library” database with two tables – “BOOK” and “AUTHOR” linked by “many-to-many” relation. Here is the SQL script (ellib.sql) to create the database:

SET SQL DIALECT 3;
SET NAMES UTF8;
CREATE DATABASE 'localhost:D:\ElLib\ElLib.fdb' user 'SYSDBA' password 'masterkey'
DEFAULT CHARACTER SET UTF8;

CREATE TABLE AUTHOR (
  AUTHOR_ID INTEGER NOT NULL,
  AUTHOR_NAME VARCHAR(40),
  CONSTRAINT PK_AUTHOR
    PRIMARY KEY (AUTHOR_ID)
  );

CREATE TABLE BOOK (
  BOOK_ID INTEGER NOT NULL,
  BOOK_NAME VARCHAR(40),
  CONSTRAINT PK_BOOK
    PRIMARY KEY (BOOK_ID)
  );

CREATE TABLE LINK (
  LINK_AUTHOR INTEGER NOT NULL,
  LINK_BOOK INTEGER NOT NULL,
  CONSTRAINT PK_LINK
    PRIMARY KEY (LINK_AUTHOR, LINK_BOOK)
  );

ALTER TABLE LINK
  ADD CONSTRAINT FK_LINK_AUTHOR
  FOREIGN KEY(LINK_AUTHOR)
    REFERENCES AUTHOR(AUTHOR_ID);

ALTER TABLE LINK
  ADD CONSTRAINT FK_LINK_BOOK
  FOREIGN KEY(LINK_BOOK)
    REFERENCES BOOK(BOOK_ID);

CREATE GENERATOR GEN_AUTHOR_ID;
CREATE GENERATOR GEN_BOOK_ID;

SET TERM ^ ;

CREATE TRIGGER BI_AUTHOR FOR AUTHOR
ACTIVE BEFORE INSERT POSITION 0
AS
  BEGIN
    IF (NEW.AUTHOR_ID IS NULL) THEN
      NEW.AUTHOR_ID = GEN_ID(GEN_AUTHOR_ID, 1);
  END^

CREATE TRIGGER BI_BOOK FOR BOOK
ACTIVE BEFORE INSERT POSITION 0
AS
  BEGIN
    IF (NEW.BOOK_ID IS NULL) THEN
      NEW.BOOK_ID = GEN_ID(GEN_BOOK_ID, 1);
  END^

SET TERM ; ^
COMMIT;

The script is obvious for everybody who ever created Firebird or Interbase databases. I have chosen UTF8 character encoding since unicode is a worldwide standard nowadays. Both “AUTHOR” and “BOOK” tables have autoincrement primary keys (AUTHOR_ID and BOOK_ID). The autoincrement fields in Firebird are implemented using generator and trigger database objects. The trigger code allows a client application to fill the autoincrement field or leave it empty so that it would be filled on server. The LINK table implements “many-to-many” relation between authors and books.

Now we must execute the script. There are several third-party GUI tools like IBAccess for the purpose. They are useful for debugging the SQL scripts, but in our case the console Firebird ISQL Tool utility is sufficient. The utility can be found in \bin subdirectory of the Firebird installation directory (“C:\Firebird\Firebird_2_1\bin\isql.exe”):

Every command in ISQL ends with semicolon “;”. A good command for a novice is “help”:

We see that we need “in” command to execute a SQL script:

The absence of any ISQL messages after the command means that the execution was successful and we are ready to work with our newly created database (d:\ellib\ellib.fdb).

The alternative way to execute SQL script with ISQL is to use -i command line parameter. That is useful for executing the scripts from command (.bat) files. I have written a demo ellib.bat file to create the ellib.fdb database:

@echo off
cls
echo Creating ellib.fdb
echo ------------------
echo.

set ISQLEXE=C:\Firebird\Firebird_2_1\bin\ISQL.exe
set ISQLSCRIPT=ellib.SQL

if exist %ISQLEXE% goto findSQL
  echo %ISQLEXE% not found.
  goto abort

:findSQL
  if exist %ISQLSCRIPT% goto execSQL
  echo %ISQLSCRIPT% not found.
  goto abort

:execSQL
  %ISQLEXE% -i %ISQLSCRIPT%
  goto fin

:abort
  echo _
  echo Execution failed

:fin

  set ISQLEXE=
  set ISQLSCRIPT=
  echo.
  pause

The result of successful execution of the command file is shown below:

You should delete the previously created ellib.fdb file to see this screenshot. Otherwise you will see ISQL error message: