[Previous][Up][Next] |
SQLDB (more in particular, TSQLQuery) can automatically generate update statements for the data it fetches. To this end, it will scan the SQL statement and determine the main table in the query: this is the first table encountered in the FROM part of the SELECT statement.
For INSERT and UPDATE operations, the SQL statement will update/insert all fields that have pfInUpdate in their ProviderFlags property. Read-only fields will not be added to the SQL statement. Fields that are NULL will not be added to an insert query, which means that the database server will insert whatever is in the DEFAULT clause of the corresponding field definition.
The WHERE clause for update and delete statements consists of all fields with pfInKey in their ProviderFlags property. Depending on the value of the UpdateMode property, additional fields may be added to the WHERE clause:
In order to let SQLDB generate correct statements, it is important to set the ProviderFlags properties correct for all fields.
In many cases, for example when only a single table is queried, and no AS field aliases are used, setting TSQLQuery.UsePrimaryKeyAsKey combined with UpdateMode equal to upWhereKeyOnly is sufficient.
If the automatically generated queries are not correct, it is possible to specify the SQL statements to be used in the UpdateSQL, InsertSQL and DeleteSQL properties. The new field values should be specified using params with the same name as the field. The old field values should be specified using the OLD_ prefix to the field name. The following example demonstrates this:
INSERT INTO MYTABLE (MYFIELD,MYFIELD2) VALUES (:MYFIELD,:MYFIELD2); UPDATE MYTABLE SET MYFIELD=:MYFIELD MYFIELD2=:MYFIELD2 WHERE (MYFIELD=:OLD_MYFIELD); DELETE FROM MYTABLE WHERE (MyField=:OLD_MYFIELD);
|
Using parameters |
|
|
Class to handle SQL commands (with or without result set) |
|
|
Statement to be used when updating an existing row in the database |
|
|
Statement to be used when inserting a new row in the database |
|
|
Statement to be used when updating an existing row in the database |