Working With TSQLQuery
If you need to be able to DELETE or otherwise modify records, your database table should contain one PRIMARY KEY column that has the property AUTO_INCREMENT. This enables the DELETE method to identify the records marked for deletion in the 'WHERE' clause written back to the database. The primary key field should preferably be designed into the table structure at CREATE time, but can be added using the following example code in your MySQL client:
alter table testrig add column autoid int primary key auto_increment;
The code can, of course, be written on one line but has been broken up for clarity.
The data set returned by TSQlQuery can conveniently be viewed with an instance of TDBGrid, but it is not very suitable for editing the data in the individual fields and cells. For this purpose you need to place some Data-Aware single-field controls such as TDBEdit on your form, and set their DataSource poperty to the data source being used. The DataField property should be set to a named field (eg 'IDENTITY') or to some expression that returns a suitable string.
Addition of a TDBNavigator toolbar makes it very easy to navigate through the records, and to select records for editing. When a record is selected by the toolbar or by moving the mouse through the data grid, the data for the relevant row and column appear in the TDBEdit box and if the 'Edit' button is clicked, the contents in the Edit box can be modified. Clicking on the 'Post' button confirms the change, or clicking on the 'Cancel' button cancels the changes.
The TSQLQuery component caches all updates. That is, the updates are not sent immediately to the database, but are kept in memory till the APPLYUPDATES method is called. At that point, the updates will be transformed to SQL update statements, and will be applied to the database. If you do not call ApplyUpdates, the database will not be updated with the local changes.
Primary key fields
When updating records, TSQLQuery needs to know which fields comprise the primary key that can be used to update the record, and which fields should be updated: based on that information, it constructs an SQL UPDATE, INSERT or DELETE command.
The construction of the SQL statement is controlled by the UsePrimaryKeyAsKey property and the ProviderFlags properties.
The Providerflags property is a set of 3 flags:
- The field is part of the primary key
- The field should be used in the WHERE clause of SQL statements.
- Updates or inserts should include this field.
By default, ProviderFlags consists of pfInUpdate only.
If your table has a primary key (as described above) then you only need to set the UsePrimaryKeyAsKey property to True and everything will be done for you. This will set the pfInKey flag for the primary key fields.
If the table doesn't have a primary key index, but does have some fields that can be used to uniquely identify the record, then you can include the pfInKey option in the ProviderFlags property all the fields that uniquely determine the record.
The UpdateMode property will then determine which fields exactly will be used in the WHERE clause:
- When TSQLQuery needs to construct a WHERE clause for the update, it will collect all fields that have the pfInKey flag in their ProviderFlags property set, and will use the values to construct a WHERE clause which uniquely determines the record to update -- normally this is only needed for an UPDATE or DELETE statement.
- In addition to the fields that have pfInKey in the ProviderFlags property, all fields that have pfInWhere in their ProviderFlags and that have changed, will also be included in the WHERE clause.
- In addition to the fields that have pfInKey in the ProviderFlags property, all fields that have pfInWhere in their ProviderFlags, will also be included in the WHERE clause.
Controlling the update
It is possible to specify which fields should be updated: As mentioned above: Only fields that have pfInUpdate in their ProviderOptions property will be included in the SQL UPDATE or INSERT statements. By default, pfInUpdate is always included in the ProviderOptions property.