|
Updating Data with IBDAC Dataset Components |
|
|
|
Updating Data with IBDAC Dataset Components |
|
Queries are often complex so posting result set modifications to the database becomes not a trivial task. IBDAC dataset components which descend from TCustomIBCQuery provide different means for reflecting local changes on the server.
The following components are used to execute SQL statements: TIBCQuery, TIBCStoredProc, TIBCTable.
If application requires result set from a single database table then use TIBCTable to query data. Setting only TableName property you may obtain data, modify it and then post changes back to the database.
TIBCQuery component may return recordsets from different tables and views all in a single query. There is often no reliable way to make automated update of the database having only original SQL statement or particularly only a name of the stored procedure. To solve this problem additional properties are provided: SQLInsert, SQLUpdate and SQLDelete. Set them with SQL statements which will perform corresponding data modifications on behalf of the original statement whenever insert, update or delete operation is called. You may also assign UpdateObject property with the TIBCUpdateSQL class instance which holds all updating SQL statements in one place.
TIBCQuery can generate SQL statements for the SQLInsert, SQLUpdate and SQLDelete properties based on the original SQL statement. To identify rows which have to be processed when modified data is applied to the database KeyFields property must be assigned with the names of key fields so that the records are uniquely identified.
For the more careful customization of data update operations you can use InsertObject, ModifyObject and DeleteObject properties of TIBCUpdateSQL component.
Set the Transaction property of your DataSet component to the transaction component with ReadCommitted/ReadOnly IsolationLevel property, and UpdateTransaction property to the transaction component with ReadCommitted IsolationLevel property for the optimal transaction using performance. Borland recommends to start the read-only transaction and commit it with CommitReataining on InterBase 7.1. Using transactions in such a way minimizes server load.
In Firebird 2.0 and higher you can use RETURNING clause of INSERT statement to get the inserted values. It can be useful for getting back inserted values if they are changed by BEFORE INSERT trigger.To add returning clause to SQLInsert automatically set DMLRefresh property to True.
When you use returning clause of statement in the IBCSQL or IBCQuery component, additional out parameters appear after preparing or executing the statement. They contain returned values and have names like "RET_" + column_name. Column name is a name of the column of returned value.
Example:
INSERT INTO T1 (F1, F2)
VALUES (:F1, :F2)
RETURNING F1, F2 After executing or preparing such statement the following out parameters appear: RET_F1 and RET_F2. They will contain values of F1 and F2 fields.
IBDAC supports working with RDB$DB_KEY field in Firebird 2.0. RDB$DB_KEY is raw record position in database. DB_KEY provides DB_KEY field that is used when it is incuded in the SQL explicitly and KeyFields property is not set. This field is represented with TIBCDbKeyField class. It will be used for building SQLInsert, SQLUpdate and SQLDelete properties. It can speed up your work because DB_KEY is even faster than PK.
Note: By default Db_Key field initialized with Visible = False. You should explicitly create Db_Key field to display it.