Delphi, Firebird, FibPlus. Part 5 – Parametric Datasets

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s