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

La plupart du temps, la propriété SQL du TSQLQuery contiendra l'ordre SELECT qui ne demandera pas de paramètre. Pourtant, il peut les contenir. Ceci permet une façon simple et puissante pour filtrer vos enregistrements.

Les paramères ont les avantages suivants :

• Nul besoin de formatter vos données comme texte de requête SQL, les arguments date (i.e. pas besoin de savoir comment formatter une date dans MySQL, qui peut différer de l'implémentation de Firebird ; pas besoin d'échapper la donnée texte comme O'Malley's "SQL Horror".
• Performance potentiellement augmentée (requête préparée).
• Protection contre les injections SQL.

L'utilisation de paramètres peut aider à la performance d'une base de données. La plupart des SGBDR supportent les ordres préparés, ce qui signifie que l'ordre est péparé et mis en cache dans la base de données. Un ordre préparé peut être utilisé plus d'une fois et ne nécessite plus d'analyse ni de détermination du plan à chaque fois qu'il est utilisé, seuls les paramètres sont changés chaque fois qu'il est utilisé. Dans les cas où le même ordre est utilisé un grand nombre de fois (où seules les valeurs de paramètres changent), les ordres préparés peuvent grandement améliorer la performance. En outre, les attaques par injection SQL peuvent être atténués par l'emploi de paramètres.

Les propriétés InsertSQL, UpdateSQL et DeleteSQL ont des paramètres prédéfinis pour les valeurs de champ anciennes et actuelles mais pas dans la propriété SQL (seules les valeurs actuelles sont permises). Vous pouvez créer vos propres paramètres dans la propriété Params.

Exemple de requête Select

Cet exemple montre comment sélectionner des données en utilisant des paramètres. Il montre aussi l'utilisation d'alias de nom de champ (... AS ...) en 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

Cet exemple montre comment insérer un nouvel enregistrement dans la table en employant des paramètres :

 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

Une autre façon de faire est aussi :

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

Requête avec la fonction Format

L'emploi de rerquêtes paramétrées est l'approche à privilégier, mais dans certaines situations, la fonction Format peut être une alternative (voir l'avertissement plus bas). Par exemple, des paramètres ne peuvent pas utilisés quand vous exécutez des ordres avec la procédure ExecuteDirect de la connection (Bien sûr, vous pouvez tout aussi bien utiliser une requête pour exécuter l'instruction SQL en question); Ensuite, cela peut être pratique :

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;

Les valeurs des variables peuvent changer et les valeurs des requêtes vont changer avec elles, tout comme avec les requêtes paramétrées.

Le paramètre %d est utilisé pour les entiers, %s pour les chaînes ; etc. Voir la documentation de la fonction Format pour les détails.

Avertissement: Soyez conscient que vous pouvez rencontrer des problèmes avec du texte contenant ' et les dates en utilisant cette technique !

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

Si voulez juste contrôler certains ordres SQL, dépanner ou obtenir de la métadonnée (p.ex. la liste des tables) de la base de données, vous pouvez faire ainsi dans l'EDI. Dans votre programme, avec votre objet TConnection, Transaction, objet requête etc. en conception, aller dans la propriété SQL de l'objet TQuery et cliquer sur le bouton '...'.

Vous verrez une fenêtre avec le code SQL et vous pouvez exécuter des ordre comme cela :

SELECT * FROM EMPLOYEE

en pressant sur l'icône 'play' :

Vous pouvez aussi obtenir de la métadonnée : noms de table, noms de colonne etc. (si le connecteur SQLDB le support, mais la plupart d'entre eux le font actuellement) :
(Voir aussi : Database metadata#Lazarus Outil de métadonnée de TSQLQuery)

Dépannage

Journalisation

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

Performance pauvre

• Assurez-vous que vos requêtes sont optimisées (utiliser des index adaptés). Utilisez vos outils de base de données (p.ex. ceux fournissant les plans d'exécution des requêtes) pour cela.
• Voir #Erreurs de dépassement de mémoire ci-dessous pour des amélioration de performance possibles quand vous avancerez avec le SQLQuery.

Erreurs de dépassement de mémoire

TSQLQuery est un descendant de TBufDataSet, un DaatSet qui tamponne en mémoire les données qu'il recçoit. Si vous récupérez beaucoup d'enregistrements (p.ex. en les parcourant pour un export), votre mémoire de tas (heap) peut se remplir (avec les enregistrements que vous avez déjà parcouru) et vous aurez des erreurs de mémoire.

Bien que cette situation se soit améliorée dans la version de développement FPC, une solution de contournement est d'indiquer au TBufDataSet de jeter les enregistrements que vous avez déjà lus en définissant la propriété unidirectionnelle sur 'True' avant d'ouvrir la requête:

MySQLQuery.UniDirectional:=True;

Ceci peut améliorer la performance.

Dataset en lecture seule

Ceci peut survenir quand vous spécifier une requête que vous savez modifiable mais que FPC ignore. Exemple:

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

L'analyseur SQL dans FPC est plutôt simpliste et quand il trouve une virgule ou une espace dans la partie FROM, il considère que plusieurs tables entrent en jeu et place le DataSet en lecture seule. Pour sa défense, la définition d'un alias de table est rarement employé pour une table unique.

Solution : réécrivez la requête ou spécifiez votre propre code InsertSQL, UpdateSQL et DeleteSQL.