Working With TSQLQuery/fr

│ English (en) │ español (es) │ français (fr) │ 日本語 (ja) │ polski (pl) │ 中文（中国大陆）‎ (zh_CN) │

Portail de la base de données

Références: Information générale Bibliothèques Types de champ Contrôles FAQ Comment faire en SQL ? Travailler avec TSQLQuery Applications de base de données en mémoire Tutoriels/articles pratiques : Vue d'ensemble 0 - Configuration de la base de données 1 - Commençons 2 - Edition 3 - Requêtes 4 - Modules de données Référence de programmation SQLdb Bases de données Advantage - MySQL - MSSQL - Postgres - Interbase - Firebird - Oracle - ODBC - Paradox - SQLite - dBASE - MS Access - Zeos

Général

TSQLQuery est un objet qui peut incarner un ensemble de données provenant d'une base de données (SGDBR qui utilise SQL, tel que FireBird, MS SQL Server, Oracle...). En utilisant un ordre SQL SELECT dans la propriété SQL du TSQLQuery, vous pouvez déterminer quelle donnée récupérer de la base de données vers le DataSet. Quand le DataSet est modifié par le programme (ou l'utilisateur), les modifications peuvent être soumises en retour à la base de données.

Un TSQLQuery peut aussi être utilisé pour modifie directement la donnée : si vous spécifiez l'ordre SQL INSERT, UPDATE, DELETE etc. désiré dans la propriété SQL et appelez la méthode ExecSQL de TSQLQuery, l'objet requête enverra le SQL vers la base de données sans retourner de résultat.

Outre son utilisation dans FPC, Lazarus fournit également un composant: TSQLQuery .

Documentation officielle

Voir Documentation TSQLQuery.

Beaucoup de documentation sensible au contexte est maintenant disponible dans Lazarus. Malheureusement, TSQLQuery n'apparaît pas dans l'index de l'aide de Lazarus 1.0. SI vous placez votre curseur sur les méthodes et propriétés de TSQLQuery, essayer de presser sur F1 pour voir si le code est documenté ; p.ex. cela marchera :

var
Q: TSQLQuery
...
  Q.Open; //<--- place cursor on Open and press F1

Contrôles communément utilisés

Le DataSet retourné par TSQLQuery peut être commodément visualisé avec une instance de TDBGrid, mais ce n'est pas très adapté pour éditer les données dans des champs et cellules individuels. Pour cet usage, vous avez besoin de placer quelques contrôles champs simples sensibles aux données tels que TDBEdit sur votre fiche, et définir la propriété DataSource avec la DataSource à utiliser. La propriété DataField devrait être aussi définie avec un nom de champ du DataSet (p.ex. 'IDENTITY') ou une expression qui retourne une chaîne convenable.

L'ajout d'une barre d'outils TDBNavigator rend la navigation très facile entre les enregistrements, et pour sélectionner l'enregistrement à mettre à jour. Quand un enregistrement est sélectionné par la barre d'outil ou en bougeant la souris à travers la grille de données, la donnée pour les colonne et ligne pertinentes apparaît dans la zone d'édition TDBEdit et si le bouton Edit est cliqué le contenu de la zone d'édition peut être modifiée. Cliquer sur le bouton 'Post' confirme la mise à jour, le clic sur le bouton 'Cancel' annule les mises à jour.

En général, le processus est le suivant :

1. Placer un composant TSQLQuery sur la fiche/le module de données, et définissez les propriétés Database, Transaction et SQL.
2. Placer un composant TDataSource, et définissez la propriété DataSet dans l'instance TSQLQuery.
3. Placer un composant TDBGrid sur la fiche et définissez sa propriété DataSource par l'instance de TDataSource.
4. Eventuellement, placer une instance de TDBNavigator sur la fiche et définissez sa propriété Datasource par l'instance de TDataSource.

Après ceci, la propriété Active peut être mise à 'True', et il devient possible de voir les données récupérées par la requête. (À condition que les composants TSQLConnection et TSQLTransaction soient actifs)

Mise à jour de données

Si vous voulez être en mesure de supprimer ou de modifier des enregistrements, votre table devrait soit :

1. contenir une colonne PRIMARY KEY (clé primaire)
2. ou avoir un ensemble de champs qui détermine de manière unique l'enregistrement. Normalement, ils devraient constituer un index unique. Cela n'est pas obligatoire, mais accélérera beaucoup les requêtes.

S'il n'y a pas de champ de clé primaire ou aucun ensemble champ qui identifie votre enregsitrement, alors une clé primaire devrait être ajoutée. Ceci est fait de préférence quand la structure de la table est conçue, au moment du CREATE, mais peut être ajouté plus tard.

Par exemple, le code exemple suivant dans votre client MySQL ajoutera un index unique à votre table :

alter table testrig 
add column autoid int 
primary key auto_increment;

L'ajout de ce champ ne fera pas de mal et permettra à vos applications de mettre à jour le champ.

Mise à jour en cache

Le composant TSQLQuery met en cache toutes les modifications. Autrement dit, les mises à jour ne sont pas envoyées immédiatement à la base de données, mais sont conservées en mémoire jusqu'à ce que la méthode ApplyUpdates soit appelée. A ce stade, les mises à jour seront transformées en ordre SQL de mise à jour et seront appliquées à la base de données. Si vous n'appelez pas ApplyUpdates, la base de données ne sera pas mise à jour avec les modifications locales.

Champs clé primaire

Lors de la mise à jour des enregistrements, TSQLQuery doit savoir quels sont les champs constituant la clé primaire qui peuvent être utilisés pour mettre à jour l'enregistrement et quels sont les champs qui doivent être mis à jour : à partir de ces informations, il construit un ordre SQL UPDATE, INSERT ou DELETE.

La construction de l'ordre SQL est contrôlée par la propriété UsePrimaryKeyAsKey du composant TSQLQuery et les propriétés ProviderFlags de ses composants TField.

La propriété Providerflags est un ensemble de 3 drapeaux :

pfInkey

Le champ fait partie de la clé primaire.

pfInWhere

Le champ devrait être utilisé dans la clause WHERE des ordres SQL.

pfInUpdate

Ce champ devrait être inclus dans les mises à jour ou insertions.

Par défaut, ProviderFlags ne contient que pfInUpdate.

Si votre table a une clé primaire (comme décrit au dessus) alors vous n'aurez qu'à mettre la propriété UsePrimaryKeyAsKey à True et tout sera fait pour vous. Ceci mettra le drapeau pfInkey pour les champs de clé primaire.

Si la table n'a pas d'index de clé primaire, mais possède des champs qui peuvent identifier de manière unique l'enregistrement, alors vous pouvez inclure l'option pfInKey dans la propriété ProviderFlags de tous les champs qui déterminent uniquement cet enregistrement.

La propriété UpdateMode déterminera alors quels champs seront utilisés dans la clause WHERE :

upWhereKeyOnly

Quand TSQLQuery a besoin de construire la clause WHERE pour une mise à jour, il collectera tous les champs qui ont le drapeau pfInKey dans la propriété ensemble ProviderFlags, et utilisera les valeurs pour construire une clause WHERE qui déterminera l'enregistrement à mettre à jour -- normalement cela est nécessaire uniquement pour un ordre UPDATE ou DELETE.

upWhereChanged

En plus des champs qui ont le drapeau pfInKey dans la propriété ProviderFlags, tous les champs qui ont aussi le drapeau pfInWhere et qui ont été changés seront aussi inclus dans la clause WHERE.

upWhereAll

En plus des champs qui ont le drapeau pfInKey dans la propriété ProviderFlags, tous les champs qui ont aussi le drapeau pfInWhere, seront aussi ajoutés dans la clause WHERE.clause.

Contrôler la mise à jour

Il est possible de spécifier quels champs devront être mis à jour, comme indiqué ci-dessus : seuls les champs qui ont pfInUpdate dans leur propriété ProviderFlags seront inclus dans les ordres SQL INSERT ou UPDATE. Par défaut, pfInUpdate est toujours inclus dans la propriété ProviderFlags.

Personnalisation du SQL dans le TSQLQuery

Normalement TSQLQuery utilisera des ordres SQL génériques basés sur les propriétés discutées ci-dessus. Pourtant, le §SQL générique produit par sqldb peut ne pas être correct dans votre cas. TSQLQuery vous permet de personnaliser les ordres SQL utilisés pour des actions divserses, pour mieux s'accorder avec votre cas de mise à jour. Pour cela, vous devez les propriétés SQL, InsertSQL, UpdateSQL et DeleteSQL.

Toutes ces propriétés sont de type TStringList, une liste de chaînes, qui acceptent du SQL sur plusieurs lignes. Toutes ces propriétés viennent avec un éditeur de propriété dans l'EDI. Dans l'EDI, sélectionnez la propriété et ouvrez l'éditeur en cliquant que le bouton 'points de suspension'. Dans cet éditeur, (TSQLQuery metadata tool), vous pourrez aussi retrouver de l'information sur la table etc.

Dans le code, utilisez par exemple InsertSQL.Text ou InsertSQL.Add() pour définir les lignes des ordres SQL. Un ordre peut s'étendre sur plusieurs lignes et se termine par un point-virgule.

Aussi, les quatre propriétés acceptent des paramètres expliqués plus bas.

SQL - Personnalisation SQL de base

La propriété SQL est normalement utilisée pour aller chercher les données dans la base de données. Le SQL générique est SELECT * FROM fpdev où fpdev est la table définie dans la base de données.

Le DataSet retourné par l'ordre SQL générique sera sous la forme de brouillon. Si vous le montrez dans un DBGrid, l'ordre des enregistrements semblera aléatoire, l'ordre des colonnes ne sera pas celui que vous atttendez, le nom des colonne sera techniquement correct mais pas orienté utilisateur. En utilisant le SQL personnalisé, vous pouvez améliorer ceci.

Pour une table appelée fpdev avec des colonnes id, UserName et InstEmail, vous pouvez faire quelque chose comme ceci :

SELECT id AS 'ID', UserName AS 'User', InstEmail AS 'e-mail' FROM fpdev ORDER BY id;

Le DataSet qui résulte de cette requête utilise les noms de champ donnés dans la requête (ID, User et e-mail), l'ordre des colonnes est donné par la requête et les enregistrements sont triés selon leur id.

InsertSQL, UpdateSQL et DeleteSQL - Utilisaiton basique des paramètres

When you assign a SELECT query to an SQLQuery's SQL property the SQLQuery knows how to get data from the database. However, when using databound controls such as a DBGrid, SQLQuery will also need to be able to insert, update and delete rows from the database based on the user's actions.

In order to speed development, SQLQuery can try and deduce the required SQL statements. If the SQL property exists and the ParseSQL property is true (which it is by default), SQLQuery will try to generate these statements by parsing the SQL property. SQLDB stores these statements in the InsertSQL, UpdateSQL and DeleteSQL properties.

However, sometimes the generated statements will not work (e.g. when inserting in tables with auto-increment/autonumber primary keys) or will be very slow. If needed, you can manually assign the statements.

The statements in the InsertSQL, UpdateSQL and DeleteSQL properties accept parameters that represent fields in the dataset. The following rules apply:

• Parameter names must be exactly the same as the field names used in the dataset. The field names in the dataset may be different from the column names in the table, depending on the used select statement (see above).
• Just as parameters in other SQLDB queries, parameter names must be written preceded by a colon.
• For use in update/delete statements, precede the dataset field name with OLD_ (strictly uppercase, at least in Lazarus v. 1.0) to get the value of the record before it was edited instead of the new value.

If you have a table called fpdev and columns id, UserName and InstEmail, linked to a dataset with fields ID, User and e-mail (see example in select statement), you could write this InsertSQL query:

INSERT INTO fpdev(id, UserName, InstEmail) VALUES(:ID,:User,:e-mail);

This statement will insert the values of ID, User and e-mail from the current record of the dataset into the respective fields of table fpdev.

This example statement is actually more or less what SQLDB itself would autogenerate. 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,:User,:e-mail)
 ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID();

The above statement tries to insert a new record using 0 (zero) for the id column. 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 Firebird, if you emulate autoincrement keys [1] something like this should work:

INSERT INTO fpdev(UserName, InstEmail) VALUES(:User,:e-mail);(

The statement inserts everything except the primary key and lets the Firebird before insert trigger use a generator/sequence to insert an id value for you.

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. As mentioned before, the field values before editing must be written as the field name precede by OLD_ (strictly uppercase, at least in Lazarus v. 1.0). For example, this query:

UPDATE fpdev SET UserName=:User, InstEmail=:e-mail WHERE UserName=:OLD_User;

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

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

See also the official documentation:

• InsertSQL documentation
• UpdateSQL documentation
• DeleteSQL documentation

Paramètres dans TSQLQuery.SQL

In most situations, the SQL property of TSQLQuery will contain the select statement which in most situations doesn't need parameters. However, it can contain them. This allows a very easy and powerful way to filter your records.

Parameters have the following advantages:

• no need to format your data as SQL text, date etc arguments (i.e. no need to remember how to format a date for MySQL, which might differ from the Firebird implementation; no need to escape text data like O'Malley's "SQL Horror"
• possibly increased performance
• protection against SQL injection attacks

The use of parameters may help performance of the database. Most databases support prepared statements, which means that the statement is prepared and cached in the database. A prepared statement can be used more than once and doesn't require parsing and query planning every time it is used, only the parameters are changed each time it is used. In situations where the same statement is used a large number of times (where only the values of the parameters differ), prepared statements can help performance considerably. Additionally, SQL injection attacks can be mitigated by use of parameters.

The InsertSQL, UpdateSQL and DeleteSQL properties have predefined parameters for current and old field values. However, the SQL property does not. You can create your own parameters in the Params property.

Exemple de requête Select

This example shows how to select data using parameters. It also demonstrates using aliases (... AS ...) in SQL.

 sql_temp.sql.text := 'SELECT id AS ''ID'', UserName AS ''User'', InstEmail AS ''e-mail'' FROM fpdev WHERE InstEmail=:emailsearch;'
 ...
 //This will create a parameter called emailsearch.

 //If we want to, we can explicitly set what kind of parameter it is... which might only be necessary if FPC guesses wrong:
 //sql_temp.params.parambyname('emailsearch').datatype:=ftWideString
 
 //We can now fill in the parameter value:
 sql_temp.params.parambyname('emailsearch').asstring := 'mvancanneyt@freepascal.org';
 ...
 //Then use your regular way to retrieve data,
 //optionally change the parameter value & run it again

Exemple de requête Insert

This example shows how to insert a new record into the table using parameters:

 sql_temp.sql.text := 'insert into PRODUCTS (ITEMNR,DESCRIPTION) values (:OURITEMNR,:OURDESCRIPTION)'
 ...
 sql_temp.Params.ParamByName('OURITEMNR').AsString := 'XXXX';
 sql_temp.Params.ParamByName('OURDESCRIPTION').AsString := 'description';
 sql_temp.ExecSQL;
 SQLTransaction1.Commit; //or possibly CommitRetaining, depending on how your application is set up

Another way of doing this is something like:

 tsqlquery1.appendrecord(['XXXX', 'description']) 
 tsqltransaction1.commit; //or commitretaining

Requête avec la fonction Format

Using parameterized queries is the preferred approach, but in some situations the format function can be an alternative. (see warning below). For example, parameters can't be used when you execute statements with the connection's ExecuteDirect procedure (of course, you can just as well use a query to run the SQL statement in question). Then this can come in handy:

procedure InsertRecord
var
  aSQLText: string;
  aSQLCommand: string;
begin
  aSQLText:= 'INSERT INTO products(item_no, description) VALUES(%d, %s)';
  aSQLCommand:= Format(aSQLText, [strtoint(Edit1.Text), Edit2.Text]);
  aConnection.ExecuteDirect(aSQLCommand);
  aTransaction.Commit;
end;

The values of the variables can change and the query values will change with them, just as with parameterized queries.

The parameter %d is used for integers, %s for strings; etc. See the documentation on the Format function for details.

Avertissement: Be aware that you may run into issues with text containing ' and dates using this technique!

Exécution de votre propre SQL et obtention de métadonnée

If you want to just check some SQL statements, troubleshoot, or get metadata (e.g. list of tables) from the database, you can do so from within the IDE. In your program, with your T*Connection, transaction, query object etc set up at design-time, go into the SQL property for the query object, then click the ... button.

You'll see a window with SQL code, and you can run some statements like

SELECT * FROM EMPLOYEE

by pressing the play icon:

You can also get metadata: table names, column names etc (if the sqldb connector supports it but most of them do nowadays):

(See also: Database metadata#Lazarus TSQLQuery metadata tool)

Dépannage

Journalisation

Voir ici : SqlDBHowto/fr#Dépannage : Journalisation par TSQLConnection pour plus de détails.

Performance pauvre

• Make sure your database queries are optimized (use proper indexes etc). Use your database tools (e.g. providing query plans) for this.
• See #Out of memory errors below for possible performance improvements when moving forward through an SQLQuery.

Erreurs de dépassement de mémoire

TSQLQuery is a descendant of BufDataset, a dataset that buffers the data it receives into memory. If you retrieve a lot of records (e.g. when looping through them for an export), your heap memory may become full (with records you already looped through) and you will get out of memory errors.

Although this situation has improved in the FPC development version, a workaround is to tell bufdataset to discard the records you have already read by setting the Unidirectional property to true before opening the query:

MySQLQuery.UniDirectional:=True;

This may also improve performance.

Dataset en lecture seule

This may happen if you specify a query that you know is updatable but FPC doesn't.

Example:

select p.dob, p.surname, p.sex from people p;

The SQL parser in FPC is quite simplistic and when it finds a comma or space in the FROM part it considers multiple tables are involved and sets the dataset to read only. To its defense, aliasing tables is usually not done when only one table is involved. Solution: rewrite the query or specify your own InsertSQL, UpdateSQL and DeleteSQL.