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 - Utilisation basique des paramètres

Quand vous affectez une requête SELECT dans la propriété SQL du SQLQuery, celui-ci sait comment récupérer les données depuis la base de données. Cependant, en utilisant des contrôles liés aux données tels qu'un DBGrid, SQLQuery devra savoir aussi comment insérer, mettre à jour ou supprimer des lignes dans la base de données selon les actions de l'utilisateur.

En vue d'accélerer le développement, SQLQuery peut essayer de déduire les ordres SQL requis. Si la propriété SQL est définie et que la propriété ParseSQL est True (ce qu'elle est par défaut), SQLQuery tentera de générer ces ordres en analysant la propriété SQL. SQLDB stocke ces ordres dans les propriétés InsertSQL, UpdateSQL et DeleteSQL.

Pourtant, parfois les ordres générés ne marcheront pas (p.ex. en insérant dans une table avec clé primaire auto-incrémentée) ou sera très lent. Au besoin, vous pouvez affecter manuellement ces propriétés.

Les ordres dans les proriétés InsertSQL, UpdateSQL et DeleteSQL acceptent des paramètres qui représentent des champs du DataSet. Les règles suivantes s'appliquent :

• Les noms de paramètres doivent être exactement identiques aux noms de champs utilisés dans le DataSet, les noms de champ dans le DataSet pouvant différer des noms de colonne dans la table, selon l'ordre SQL SELECT (voir ci-dessus).
• Tout comme les paramètres dans d'autres requêtes SQLDB, les noms de paramètres doivent être écrits précédés d'un signe deux-points.
• Pour l'utilisation dans les ordres update/delete, OLD_ (strictement en majuscules, au moins dans Lazarus v. 1.0) précède le nom de champ du DataSet pour obtenir la valeur de l'enregistrement avant modification au lieu de la nouvelle valeur.

Si vous avez une table appelée fpdev et des colonnes id, UserName et InstEmail, liés à un DataSet avec des champs ID, User et e-mail (voir l'exemple dans l'ordre SELECT), vous pourrez écrire cette requête InsertSQL :

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

Cet ordre insérera les valeurs de ID, User et e-mail de l'enregistrement courant du DataSet dans les champs respàectifs de la table fpdev.

Cet exemple d'ordre est actuellement plus ou moins celui que SQLDB auto-généréra lui-même. L'ordre donné peut provoquer des erreurs quand le champ id est un champ auto-incrémenté d'une clé unique. Les différents SGBDR résolvent ce problème de différentes façons. P. ex., le code suivant tourne dans MySQL.

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

L'ordre du dessus tente d'insérer un nouvel enregistrement en utilisant 0 (zéro) pour la colonne id. Si zéro est déjà utilisé comme clé, alors un doublon est détecté et le champ id est mis à jour pour utiliser le dernier id inséré. Eh bien, en fait un id incrémenté de un que le dernier utilisé.

Pour FireBird, si vous émulez les clés auto-incrémentées [1], quelque chose comme ceci devrait fonctionner :

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

Cet ordre insère tout sauf la clé primaire et laisse le trigger FireBird Before Insert utiliser un générateur/une séquence pour insérer la valeur de la colonne id pour vous.

Pour un ordre INSERT vous pouvez vouloir utiliser les valeurs courantes des champs de l'enregistrement sélectionné. Pour les ordres UPDATE, vous voudrez utiliser les valeurs de champ telles qu'elles étaient avant édition dans la clause WHERE. Comme indiqué plus haut, les valeurs de champ avant édition doivent être écrites avec le nom de champ précédé par OLD_ (en majuscules strictement, au moins dans Lazarus v. 1.0). Par exemple, cette requête :

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

mettra à jour les colonnes UserName et InstEmail dans tous les enregistrements où User vaut l'ancienne valeur de User.

Nous laissons en exercice au lecteur l'utilisation des valeurs courantes des champs et des anciennes valeurs dans les ordres DELETE.

Voir aussi la documentation officielle :

• Documentation InsertSQL
• Documentation UpdateSQL
• Documentation DeleteSQL

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.