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

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.

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