Exécution des commandes (FireDAC)
Remonter à Utilisation des commandes (FireDAC)
Sommaire
Utilisation de TFDConnection
TFDConnection fournit des méthodes ExecSQL. Simples d'emploi, ces méthodes surchargées sont utiles dans les cas suivants :
- Aucun ensemble de résultats n'est renvoyé.
- Une commande SQL n'est exécutée qu'une fois et n'a donc pas besoin d'être stockée dans un état préparé.
- Il n'est pas nécessaire de définir des paramètres avancés.
- Il n'est pas nécessaire de définir des commandes SQL à la conception.
Par exemple, pour exécuter des commandes DDL, utilisez la syntaxe suivante :
FDConnection1.ExecSQL('drop table testtab');
FDConnection1.ExecSQL('create table testtab (id integer, name varchar(10))');
FDConnection1.ExecSQL('insert into testtab values (1, ''FireDAC'')');
Pour exécuter une requête paramétrée, utilisez l'autre méthode surchargée :
FDConnection1.ExecSQL('insert into testtab values (:id, :name)', [1, 'FireDAC']);
De même, TFDConnection fournit des méthodes ExecSQLScalar différentes des méthodes ExecSQL, dans la mesure où elles renvoient une valeur d'ensemble de résultats unique :
sName := FDConnection1.ExecSQLScalar('select name from testtab where id = :id', [1]);
Utilisation de TFDQuery
En général, TFDQuery est défini à la conception et/ou à l'exécution.
Définition de TFDQuery à la conception
Pour définir TFDQuery à la conception, déposez-le sur une fiche. TFDQuery.Connection sera automatiquement défini pour pointer vers un composant TFDConnection sur cette fiche, s'il en existe un. Ensuite, double-cliquez sur TFDQuery pour invoquer l'éditeur de requête FireDAC :
De cet emplacement, vous pouvez spécifier du texte SQL et, en option, indiquer des valeurs de paramètre et de macro et définir des options.
Appuyez sur le bouton Exécuter pour exécuter la requête. Si une commande renvoie un ensemble de résultats, celui-ci s'affichera dans le volet Ensemble d'enregistrements et la structure de l'ensemble de résultats s'affichera dans le volet Structure. Si le SGBD renvoie des messages ou des avertissements pour une commande, ceux-ci s'afficheront dans le volet Messages. Le bouton Ensemble d'enregistrements suivant vous permet de parcourir tous les ensembles de résultats renvoyés par la commande.
Lorsque vous testez des commandes DML, telles que UPDATE/INSERT/DELETE, vous devez activer la case à cocher Restauration automatique pour annuler automatiquement les actions effectuées par une commande.
Appuyez sur OK pour enregistrer les modifications dans le composant TFDQuery.
Utilisation des paramètres
L'utilisation des requêtes paramétrées est l'une des meilleures pratiques du développement d'applications de base de données SQL. Les principaux avantages sont les suivants :
- Vous avez la possibilité de composer une commande SQL unique et de l'utiliser plusieurs fois avec des valeurs de paramètre différentes. Le SGBD construira le plan d'exécution de la commande une seule fois, Cela réduit la charge du moteur du SGBD.
- A la prochaine exécution de la commande, seules les valeurs de paramètre seront transférées au moteur du SGBD, réduisant ainsi la charge du réseau.
- Vous n'avez pas besoin d'écrire des formats de constante SQL sans erreur, par exemple une constante de date dans Microsoft Access SQL.
Pour insérer un marqueur de paramètre dans le texte SQL, utilisez la syntaxe :<name>. Assignez ensuite les valeurs correspondantes à l'aide de la collection Params. Par exemple :
FDQuery1.SQL.Text := 'select * from tab where code = :Code';
FDQuery1.ParamByName('code').AsString := '123';
FDQuery1.Open;
Avant l'exécution de la requête, chaque paramètre Name, DataType et ParamType doit être rempli. Le paramètre Position doit également être défini.
Lorsque vous assignez la propriété SQL et que la propriété ResourceOptions.ParamCreate vaut True, la collection Params est automatiquement remplie.
Vous pouvez également remplir la collection Params par du code. Pour cela, vous devez désactiver la création automatique de paramètres en définissant la propriété ResourceOptions.ParamCreate sur False. Par exemple :
FDQuery1.ResourceOptions.ParamCreate := False;
FDQuery1.SQL.Text := 'select * from tab where code = :Code';
with FDQuery1.Params do begin
Clear;
with Add do begin
Name := 'CODE';
DataType := ftString;
Size := 10;
ParamType := ptInput;
end;
end;
Les paramètres peuvent être liés à la commande SQL par nom ou par position. Pour contrôler les points suivants, utilisez la propriété Params.BindMode :
- pbByName. Les paramètres de la collection Params seront liés par la propriété Name aux marqueurs de paramètre correspondants. Si la commande SQL comporte des marqueurs de paramètre portant les mêmes noms, une seule instance apparaîtra dans la collection Params.
- pbByNumber. Les paramètres de la collection Params seront liés par la propriété Position aux marqueurs du texte de la commande SQL. Si la commande SQL comporte des marqueurs de paramètre portant les mêmes noms, chaque occurrence apparaîtra dans la collection Params. Si la propriété Position n'est pas définie pour les paramètres, elle sera assignée automatiquement lors de la préparation de commande à l'aide des index de paramètres. La propriété Position commence à 1.
La propriété DataType est spécifiée soit explicitement, soit implicitement, par l'assignation d'une valeur de paramètre à la propriété Value ou AsXxxx. Sinon, la propriété FormatOptions.DefaultParamDataType est utilisée.
Lorsque la propriété ParamType n'est pas spécifiée, la propriété ResourceOptions.DefaultParamType est utilisée. Par défaut, tous les paramètres sont des paramètres d'entrée uniquement. Si vous souhaitez utiliser des paramètres de sortie, vous devez spécifier la propriété ParamType de manière explicite.
Les valeurs de paramètre d'entrée doivent être définies avant l'exécution de la requête. Pour cela, choisissez l'une des options suivantes :
- Assignez des valeurs de paramètre de manière explicite :
FDQuery1.ParamByName('code').AsString := 'DA1001';
FDQuery1.Open;
- Utilisez l'une des méthodes ExecSQL ou Open surchargées :
FDQuery1.Open('', ['DA1001']);
Pour définir la valeur du paramètre sur Null, spécifiez le type de données du paramètre, puis appelez la méthode Clear :
with FDQuery1.ParamByName('name') do begin
DataType := ftString;
Clear;
end;
FDQuery1.ExecSQL;
Les valeurs de paramètre de sortie sont accessibles après l'exécution de la requête. Certaines commandes SQL et le SGBD peuvent requérir en premier lieu le traitement de tous les ensembles de résultats de commande, suivi de la réception des valeurs de paramètre de sortie. Pour forcer l'obtention des valeurs de paramètre de sortie, utilisez la méthode TFDAdaptedDataSet.GetResults. Consultez également la rubrique Support unifié de RETURNING concernant les paramètres de sortie dans la phrase RETURNING sous Firebird et Oracle.
Préparation de la requête
Une requête doit être préparée avant d'être exécutée. Pour cela, FireDAC réalise automatiquement les actions suivantes :
- Met la connexion à l'état active ou en ligne.
- Vérifie que tous les paramètres sont définis correctement.
- Prétraite le texte des commandes.
- En option, applique RecsSkip et RecsMax au texte de la commande.
- En option, démarre une transaction pour le SGBD Firebird/InterBase, lorsqu'il n'y a pas de connexion active.
- Transfère le texte de commande final à l'API du SGBD. Le texte final est obtenu à l'aide de la propriété TFDQuery.Text.
Lors de la préparation de la commande, la connexion doit être active et la requête conserve les ressources du serveur. Pour annuler la préparation de la requête et libérer toutes les ressources, utilisez la méthode Unprepare ou Disconnect.
Si vous devez exécuter une commande une seule fois, définissez la propriété ResourceOptions.DirectExecute sur True. Pour certains SGBD (SQL Server, SQL Anywhere), cette action permet d'accélérer l'exécution car elle évite l'opération coûteuse de préparation de commande SQL.
Exécution de la requête
Pour exécuter une requête qui ne renvoie aucun ensemble de résultats, utilisez les méthodes ExecSQL. Si une requête renvoie un ensemble de résultats, l'exception "[FireDAC][Phys][MSAcc]-310. Impossible d'exécuter la commande retournant un ensemble de résultats" est déclenchée.
Pour exécuter une requête, renvoyer et ouvrir un ensemble de résultats, utilisez les méthodes Open. Si une requête ne renvoie aucun ensemble de résultats, l'exception "[FireDAC][Phys][MSAcc]-308. Impossible d'ouvrir / définir la commande, qui ne retourne aucun ensemble de résultats" est déclenchée.
Pour exécuter une requête ad hoc, utilisez la méthode ExecuteOrOpen.
Exception Le type de données du paramètre est modifié
Si le type du paramètre a été modifié après le premier appel de la méthode Prepare/ExecSQL/Open, FireDAC déclenche une exception sur un appel suivant :
[FireDAC][Phys][IB]-338. Parameter [Xxxx] data type is changed.
Query must be reprepared.
Au premier appel de la méthode Prepare/ExecSQL/Open, FireDAC mémorise le type de données du paramètre. Le type de données peut être spécifié soit explicitement en définissant la propriété TFDParam.DataType, soit implicitement en assignant une valeur à l'aide de la propriété TFDParam.AsXxxx ou TFDParam.Value. Comme l'application modifie le type de données du paramètre avant l'appel suivant de la méthode ExecSQL/Open, lorsque ExecSQL/Open est rappelée, l'exception "Le type de données du paramètre [xxxxx] est modifié" est déclenchée.
La propriété TFDParam.Value ne modifie pas le type de données du paramètre lors de l'appel suivant. Elle transtype la valeur assignée dans le type de données en cours. Par conséquent, pour éviter l'exception ci-dessus, utilisez :
- La valeur de la propriété TFDParam.Value.
- La propriété TFDParam.AsXxxx, puisque Xxxx a été utilisé la première fois.
Retour d'informations du SGBD
Utilisez la propriété TFDQuery.RowsAffected pour obtenir le nombre de lignes traitées par la commande (par exemple, le nombre de lignes supprimées par la commande DELETE).
Si la commande renvoie une erreur, une exception est déclenchée. Pour plus d'informations, voir la rubrique Gestion des erreurs. L'exception peut être traitée selon l'une des trois méthodes suivantes :
- En utilisant la construction try/except/end. Par exemple :
try
FDQuery1.ExecSQL;
except
on E: EFDDBEngineException do
; // do something here
end;
- En définissant le gestionnaire d'événement TFDQuery.OnError.
- En définissant le gestionnaire d'événement TFDConnection.OnError. Par exemple :
procedure TForm1.FDConnection1Error(ASender: TObject; const AInitiator: IFDStanObject;
var AException: Exception);
begin
if (AException is EFDDBEngineException) and (EFDDBEngineException(AException).Kind = ekRecordLocked) then
AException.Message := 'Please, try the operation later. At moment, the record is busy';
end;
FDConnection1.OnError := FDConnection1Error;
Pour la commande Array DML, la gestion des erreurs est plus complexe.
En outre, la commande renvoie des avertissements, des conseils et des messages, selon le SGBD utilisé. Pour activer le traitement des messages, définissez ResourceOptions.ServerOutput sur True. Pour traiter les messages, utilisez la propriété TFDConnection.Messages. Par exemple :
var
i: Integer;
begin
FDConnection1.ResourceOptions.ServerOutput := True;
FDQuery1.ExecSQL;
if FDConnection1.Messages <> nil then
for i := 0 to FDConnection1.Messages.ErrorCount - 1 do
Memo1.Lines.Add(FDConnection1.Messages[i].Message);
end;
Certains SGBD, tels SQL Server, renvoient des messages en tant qu'ensemble de résultats supplémentaire. Pour traiter les messages, l'application doit donc traiter plusieurs ensembles de résultats. Voici un exemple plus complexe qui fournit le statut et des messages pour SQL Server. Comme vous pouvez le constater, nous utilisons TFDMemTable pour stocker les ensembles de résultats avec les lignes.
var
i: Integer;
begin
FDConnection1.ResourceOptions.ServerOutput := True;
FDQuery1.FetchOptions.AutoClose := False;
FDQuery1.Open('select * from Region; print ''Hello''');
FDMemTable1.Data := FDQuery1.Data;
Memo1.Lines.Add(Format('%d rows processed', [FDMemTable1.RecordCount]));
FDQuery1.NextRecordSet;
if FDConnection1.Messages <> nil then
for i := 0 to FDConnection1.Messages.ErrorCount - 1 do
Memo1.Lines.Add(FDConnection1.Messages[i].Message);
end;
Exécution de requêtes et transactions
Par défaut, toutes les exécutions de commande SQL sont effectuées en mode Validation automatique. Cela signifie que, s'il n'y a pas de transaction active juste avant l'exécution d'une commande, cette transaction implicite démarre. De la même façon, juste après l'exécution de la commande, la transaction se termine :
- par Commit, si l'exécution a abouti.
- par Rollback, si l'exécution a échoué.
Pour Firebird et InterBase, une transaction implicite démarre juste avant la préparation de la requête. Il est inutile d'encapsuler une exécution de commande unique dans des transactions explicites ; il suffit d'utiliser le mode Validation automatique.
Avancées
La plupart des applications de base de données comportent des utilitaires d'administration dorsaux qui doivent exécuter un script SQL. Les lignes contenues dans ces scripts sont compatibles avec la syntaxe des scripts SQL du SGBD. Pour exécuter les scripts SQL, utilisez le composant TFDScript.
Parfois, une application de base de données exécute des requêtes hétérogènes à l'aide de tables provenant de plusieurs bases de données ou exécute des commandes SQL sur les descendants TDataSet à la place des tables de base de données. Pour exécuter de telles requêtes, utilisez le moteur SQL local et le composant TFDLocalSQL.