FIBPlus. Master-detail. Step 2. Creation of live queries. Use of generators for getting Primary Key values.

Step 2. Creation of live queries. Use of generators for getting Primary Key values.

If you try to make some changes of values in DBGrid1 you will see that at present we have a read-only query. For making our query available for editing (or «alive») we need to write some additional queries, which will be automatically executed by the pFIBDataSet1 when editing data in DBGrid1. You need to set the following properties:

Figure 9.

FIBPlus contains a special design-time editor for editing and generation of modifying queries. You can call “SQL Generator” from the context menu if you press the right mouse button on pFIBDataSet1.

Figure 10.

For generation of modifying queries first you need to set the main table in the list. If in our query we have only one table, it will be chosen in the list automatically. After this press “Get Table Fields” for filling in the “Key Fields” and “Update Fields” lists. In the first list you need to select those fields, which will be inserted into WHERE clause in all modifying queries. In “Update Fields” select the fields you want to edit. All fields from the DEPARTMENT table are selected by default. Now it is necessary to press “Generate SQLs” and you will automatically get all modifying queries, which you can see on the SQLs tab. For example we will get the following query for the InsertSQL property:

INSERT INTO DEPARTMENT(
    DEPT_NO,  DEPARTMENT,  HEAD_DEPT,  MNGR_NO,  BUDGET,  LOCATION,
    PHONE_NO
)
VALUES(
    ?DEPT_NO, ?DEPARTMENT,  ?HEAD_DEPT, ?MNGR_NO, ?BUDGET, ?LOCATION,
    ?PHONE_NO
)

Pay attention on the query for RefreshSQL. It is also a selecting query but it must return only the current record! Now after creation of all queries when a user inserts a new record into DBGrid1, pFIBDataSet1 will automatically set values of ?DEPT_NO, ?DEPARTMENT, etc parameters by values of the fields, which the user has set. Pay attention to the DEPT_NO field. This integer field is a primary key for the table and it must contain unique values. InterBase has a special feature called “generators” for getting such values and it guarantees that they will be unique for the database. The peculiarity of generator use is that we should get a new generator value in the application before execution of the insert query. Why do we need it? The point is that after execution of any modifying query (except for deleting), pFIBDataSet1 automatically executes the query from RefreshSQL, setting current parameter values as a clause. In our case for such setting we need to use a primary key value. If we do not get it in advance but generate it using a trigger, we cannot set the DEPT_NO value in the RefreshSQL query, and so we cannot reread the changed record. Thus if any record fields have been changed in database triggers, we will not see these changes before complete reopening of the query. But if first we get a new generator value, then insert it with other parameters, then we can use this value for the current record refresh and get to know all actual field values without redundant reopening!

TpFIBDataSet allows automatic inserting and getting primary key values by use of the generator. For this you need to set AutoUpdateOptions:

Figure 11.

First set names of the DEPARTMENT table, DEPT_NO key field and DEPT_NO_GEN generator. The WhenGetGenID property can have the following three values:

• wgOnNewRecord – to get a generator value just after buffer preparation for a new record.;
• wgBeforePost – to get a generator value just before sending a new record to the server;
• wgNever – not to use mechanism of generation of key values;

In our example we will set the wgOnNewRecord value for seeing in DBGrid1 those values of the DEPT_NO field, which will be received from the server. Now we can run our application, edit any records and even insert new ones.

Figure 12.

In the picture we see that there was automatically got a value for the DEPT_NO field equal 22. Pay attention that there were also automatically got default values for the BUDGET and PHONE_NO fields.

See the full example code.