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.
The Programming Works 