Difference between revisions of "Working With TSQLQuery"

From Lazarus wiki
Line 64: Line 64:
 
== Controlling the update ==
 
== 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.
 
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.
 +
 +
== Customizing TSQLQuery for Your Database ==
 +
Normally TSQLQuery will use generic SQL statements based on properties as discussed above. However, the generic SQL created by sqldb may not be correct for your situation. TSQLQuery allows you to customize SQL statements used for the various actions, to work best in your situation with your database. For this purpose you use the properties ''InsertSQL'', ''UpdateSQL'' and ''DeleteSQL''.
 +
 +
All three properties are of type TStringList, a list of strings, that accepts multiple lines of SQL. All three come with a property editor in the IDE. In the IDE, select the property and open de editor by clicking the ellipsis button. In code, use for example <tt>InsertSQL.Add()</tt> to add lines of SQL statements. One statement may span several lines. The list may contain several statements separated by semicolons, or the separator used in your database.
 +
 +
The statements in ''InsertSQL'', ''UpdateSQL'' and ''DeleteSQL'' accept parameters. Field names are used exactly as they are in the table definition and the dataset field names. Field values must be written as the field name preceded by a colon. For example:
 +
INSERT INTO fpdev(id, UserName, InstEmail) VALUES(:id,:UserName,:InstEmail);
 +
This statement will insert the values of <tt>id</tt>, <tt>UserName</tt> and <tt>InstEmail</tt> from the current record of the dataset into the respective fields of table <tt>fpdev</tt>.
 +
 +
The previously shown <tt>INSERT</tt> statement is a generic <tt>INSERT</tt> statement and would be of limited use in TSQLQuery as it will be the same as the SQL generated with slqdb itself. The given statement may result in errors when the <tt>id</tt> field is an auto-increment field in a unique key. Different databases solve this problem in different ways. For example, the following works for MySQL.
 +
INSERT INTO fpdev(id, UserName, InstEmail) VALUES(0,:UserName,:InstEmail)
 +
ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID();
 +
The above statement tries to insert a new record using 0 (zero) for the <tt>id</tt>. If zero is already used as a key, then a duplicate is detected and the <tt>id</tt> is updated to use the last inserted id. Well, actually an id one increment higher than the last one used.
 +
 +
For an <tt>INSERT</tt> statement you may want to use the current field values of the selected record. For <tt>UPDATE</tt> statements, you will want to use the field values as they were before editing in the <tt>WHERE</tt> clause. This is also possible. The field values before editing must be written as the field name precede by ':old_'. For example, the following works for MYSQL:
 +
  UPDATE fpdev SET UserName=:UserName, InstEmail=:InstEmail WHERE UserName=:Old_UserName;
 +
The above statement updates the <tt>UserName</tt> and <tt>InstEmail</tt> fields of all records where <tt>UserName</tt> equals the old <tt>UserName</tt> value.
 +
 +
We leave it as an exercise to the reader to use the current field values and the old field values in DELETE statements. Other possible exercises:
 +
* Update multiple tables with the changed values.
 +
* Maintain referential integrity in a database that doesn't have referential integrity.

Revision as of 21:11, 17 May 2010

English (en) español (es) français (fr) 中文(中国大陆)‎ (zh_CN)

General

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.

In general, the process is as follows:

  1. Drop a TSQLQuery on a form/datamodule, and set the Database, Transaction and SQL properties.
  2. Drop a TDatasource component, and set it's Dataset property to the TSQLQuery instance.
  3. Drop a TDBGrid on the form and set it's Datasource property to the TDatasource instance.
  4. Optionally, drop a TDBNavigator instance on the form, and set it's Datasource property to the TDatasource instance.

After this, the Active property can be set to True, and it should be possible to see the data retrieved by the query. (provided both the TSQLConnection and TSQLTransaction components are active)

Updating data

If you need to be able to DELETE or otherwise modify records, your database table should either

  1. contain one PRIMARY KEY column.
  2. have a set of fields that uniquely determine the record. Normally, they should be part of a unique index. This is not required, but will speed up the queries quite a lot

If there is no primary field, or no fields that uniquely determine your record, then a primary key field should be added. This is done preferably when the table structure is designed, at CREATE time, but can be added at a later time.

For instance the following example code in your MySQL client will add a unique index to your table:

 alter table testrig 
 add column autoid int 
 primary key auto_increment;

Adding this field will not hurt, and will allow your applications to update the field.

Cached Updates

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:

pfInkey
The field is part of the primary key
pfInWhere
The field should be used in the WHERE clause of SQL statements.
pfInUpdate
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:

upWhereKeyOnly
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.
upWhereChanged
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.
upWhereAll
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.

Customizing TSQLQuery for Your Database

Normally TSQLQuery will use generic SQL statements based on properties as discussed above. However, the generic SQL created by sqldb may not be correct for your situation. TSQLQuery allows you to customize SQL statements used for the various actions, to work best in your situation with your database. For this purpose you use the properties InsertSQL, UpdateSQL and DeleteSQL.

All three properties are of type TStringList, a list of strings, that accepts multiple lines of SQL. All three come with a property editor in the IDE. In the IDE, select the property and open de editor by clicking the ellipsis button. In code, use for example InsertSQL.Add() to add lines of SQL statements. One statement may span several lines. The list may contain several statements separated by semicolons, or the separator used in your database.

The statements in InsertSQL, UpdateSQL and DeleteSQL accept parameters. Field names are used exactly as they are in the table definition and the dataset field names. Field values must be written as the field name preceded by a colon. For example:

INSERT INTO fpdev(id, UserName, InstEmail) VALUES(:id,:UserName,:InstEmail);

This statement will insert the values of id, UserName and InstEmail from the current record of the dataset into the respective fields of table fpdev.

The previously shown INSERT statement is a generic INSERT statement and would be of limited use in TSQLQuery as it will be the same as the SQL generated with slqdb itself. The given statement may result in errors when the id field is an auto-increment field in a unique key. Different databases solve this problem in different ways. For example, the following works for MySQL.

INSERT INTO fpdev(id, UserName, InstEmail) VALUES(0,:UserName,:InstEmail)
ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID();

The above statement tries to insert a new record using 0 (zero) for the id. If zero is already used as a key, then a duplicate is detected and the id is updated to use the last inserted id. Well, actually an id one increment higher than the last one used.

For an INSERT statement you may want to use the current field values of the selected record. For UPDATE statements, you will want to use the field values as they were before editing in the WHERE clause. This is also possible. The field values before editing must be written as the field name precede by ':old_'. For example, the following works for MYSQL:

 UPDATE fpdev SET UserName=:UserName, InstEmail=:InstEmail WHERE UserName=:Old_UserName;

The above statement updates the UserName and InstEmail fields of all records where UserName equals the old UserName value.

We leave it as an exercise to the reader to use the current field values and the old field values in DELETE statements. Other possible exercises:

  • Update multiple tables with the changed values.
  • Maintain referential integrity in a database that doesn't have referential integrity.