Array DML (FireDAC)

De RAD Studio
Aller à : navigation, rechercher

Remonter à Utilisation des commandes (FireDAC)

Informations générales

La technique d'exécution Array DML soumet une seule commande de SGBD accompagnée d'un tableau de paramètres. Chaque paramètre de commande a un tableau de valeurs, et tous les paramètres ont des tableaux de la même longueur. FireDAC demande ensuite au SGBD d'exécuter la commande une fois pour chaque ligne des tableaux. Cette technique réduit le volume de communication entre le SGBD et le client, et permet au SGBD de fluidifier l'exécution de la commande. Le délai d'exécution est ainsi réduit.

L'image suivante illustre ce processus :

FDPhysCmdBatch.png

Dans FireDAC, les formulations "exécution d'une commande groupée" et "exécution Array DML" sont souvent utilisées comme des synonymes. Array DML peut être utilisé pour pratiquement toutes les commandes paramétrées, y compris les appels aux procédures stockées. FireDAC implémente Array DML en utilisant les capacités API natives du SGBD ou émule l'exécution Array DML, si l'API du SGBD ne la prend pas en charge.

Le tableau suivant présente les SGBD et les fonctionnalités Array DML :

SGBD Implémentation de Array DML Mode Array DML Symptômes des limites de Array DML
Advantage Database Emulation aeUpToFirstError
Serveur DataSnap Emulation aeUpToFirstError
IBM DB2 Native aeCollectAllErrors
Informix Native aeCollectAllErrors
InterBase v < XE3 Emulation aeUpToFirstError
InterBase v >= XE3 Native (API de groupes de commandes) aeUpToFirstError
Firebird v < 2.1 Emulation aeUpToFirstError
Firebird v >= 2.1 Native (EXECUTE BLOCK) aeOnErrorUndoAll Erreur "Trop de contextes"
Microsoft SQL Server Native aeCollectAllErrors Erreur "Violation d'accès" possible
Base de données Microsoft Access Emulation aeUpToFirstError
Serveur MySQL Native (INSERT avec plusieurs VALEURS) aeOnErrorUndoAll
Serveur Oracle Native (Array DML OCI) aeUpToFirstError L'application est suspendue. Limite explicite - 65 000 éléments de tableau.
PostgreSQL v < 8.1 Emulation aeUpToFirstError
PostgreSQL v >= 8.1 Native (INSERT / MERGE avec plusieurs VALEURS) aeOnErrorUndoAll
Base de données SQLite v < 3.7.11 Emulation aeUpToFirstError
Base de données SQLite v >= 3.7.11
  • Emulation, lorsque Params.BindMode = pbByName
  • Native (INSERT avec plusieurs VALEURS), lorsque Params.BindMode = pbByNumber
  • aeUpToFirstError
  • aeOnErrorUndoAll
Sybase SQL Anywhere Native aeUpToFirstError
Base de données Teradata Native aeOnErrorUndoAll
Remarque:

Exécution de la commande

Avant l'exécution de Array DML, le code de l'application doit configurer les tableaux de valeurs des paramètres. Commencez par définir la longueur du tableau en assignant une valeur à Params.ArraySize. En affectant une valeur à cette propriété, vous assignez implicitement la longueur de tableau spécifiée à tous les paramètres de la propriété ArraySize. La collection Params ne doit donc pas être vide avant l'assignation d'une valeur à Params.ArraySize. Ensuite, assignez des valeurs aux tableaux de paramètres. La classe TADParam comporte un ensemble de propriétés AsXXXs[AIndex: Integer], similaires aux propriétés AsXXX et aux autres propriétés et méthodes acceptant comme premier paramètre l'index de tableau. Par exemple : 

FDQuery1.SQL.Text := 'insert into MyTab values (:p1, :p2, :p3)';
// here FDQuery1.Params collection is filled by 3 parameters
FDQuery1.Params.ArraySize := 100;
for i := 0 to 100-1 do begin
  FDQuery1.Params[0].AsIntegers[i] := i;
  FDQuery1.Params[1].AsStrings[i] := 'qwe';
  FDQuery1.Params[2].Clear(i);
end;

TFDCustomCommand, TFDQuery et TFDStoredProc comportent la méthode Execute (ATimes: Integer = 0; AOffset: Integer = 0). Dans ce cas, ATimes définit la longueur du tableau. AOffset est l'index du premier élément dans le tableau. La commande sera donc exécutée (ATimes - AOffset) fois, en commençant par la ligne AOffset. ATimes doit être égal ou inférieur à Params.ArraySize. Par exemple : 

FDQuery1.Execute(100, 0);

Après l'exécution de Array DML, la propriété RowsAffected comporte le nombre d'exécutions réussies, et non pas le nombre de lignes affectées par toutes les exécutions. Par exemple : 

ShowMessage(IntToStr(FDQuery1.RowsAffected));

Gestion des erreurs

TFDAdaptedDataSet, TFDQuery et TFDStoredProc ont la capacité d'intercepter des erreurs en utilisant des gestionnaires d'événement OnExecuteError. Si le gestionnaire d'erreur n'est pas assigné et qu'une erreur se produit, Execute déclenche une exception et RowsAffected est mis à jour.

Si le gestionnaire d'événement TFDAdaptedDataSet.OnExecuteError est assigné, il obtient l'objet exception d'origine, l'heure et le décalage en cours, et peut retourner une valeur AAction, indiquant l'action suivante à effectuer. AError.Errors[...] contient une ou plusieurs erreurs. AError.Errors[i].RowIndex est un index de la ligne ayant échoué. Notez que OnExecuteError n'est pas appelé en cas d'erreurs de syntaxe ou lorsque ATimes = 1.

Par exemple : 

procedure TForm1.FDQuery1ExecuteError(ASender: TObject; ATimes,
  AOffset: Integer; AError: EFDDBEngineException; var AAction: TFDErrorAction);
begin
  if AError.Errors[0].Kind = ekUKViolated then
    AAction := eaSkip
  else
    AAction := eaFail;
end;

Le comportement exact dépend du SGBD et de son mode Array DML correspondant :


Mode Array DML Description
aeOnErrorUndoAll L'exécution est interrompue à la première erreur. Tous les éléments de tableau appliqués avec succès sont annulés. FireDAC bascule ensuite en mode d'exécution pas à pas et exécute à nouveau l'ensemble du tableau. Ce mode est similaire au mode aeUpToFirstError. Voir aeUpToFirstError ci-dessous.
aeUpToFirstError L'exécution est interrompue à la première erreur. Tous les éléments de tableau appliqués avec succès sont enregistrés. Le SGBD retourne l'index du premier élément de tableau ayant échoué. RowsAffected = nombre d'éléments de tableau appliqués avec succès. La collection d'erreurs dans AError.Errors[...] contient une ou plusieurs erreurs faisant référence à une seule ligne ayant échoué. AError.Errors[i].RowIndex est l'index de la ligne ayant échoué.
aeCollectAllErrors Tous les éléments de tableau sont exécutés. Tous les éléments de tableau appliqués avec succès sont enregistrés. Le SGBD retourne un par un l'index de chaque élément de tableau ayant échoué. RowsAffected = nombre d'éléments de tableau appliqués avec succès. La collection d'erreurs dans AError.Errors[...] contient une erreur pour chaque ligne ayant échoué. AError.Errors[i].RowIndex est un index de la ligne ayant échoué.
Remarque: Le fait de définir ResourceOptions.ArrayDMLSize sur 1 définit implicitement le mode d'exécution de tableau sur aeUpToFirstError. Pour obtenir le mode Array DML du SGBD actuellement connecté, utilisez : 
if FDConnection1.ConnectionMetaDataIntf.ArrayExecMode = aeOnErrorUndoAll then
  ....


Dépannage

Il est important de définir correctement les paramètres, y compris la propriété Size des paramètres chaînes. Par exemple, dans le cas d'Oracle, FireDAC alloue 4 000 octets pour chaque paramètre ftString / ftWideString lorsque la propriété Size n'est pas explicitement spécifiée. Pour 10 000 valeurs, la taille de tampon allouée est donc de 40 Mo. Si les paramètres sont nombreux, l'application risque d'utiliser toute la mémoire système disponible.

La plupart des SGBD limitent implicitement la taille de Array DML. Cela dépend de la taille du tampon de la bibliothèque client du SGBD ou de la quantité maximale de paquets réseau autorisée. Lorsqu'une limite est atteinte, utilisez l'option ResourceOptions.ArrayDMLSize pour diviser de manière transparente de grands tableaux Array DML en portions plus réduites.


Exemple 1

Array DML avec IFDPhysCommand : 

var
  oCmd: IFDPhysCommand;
……
  with oCmd do begin
    CommandText := 'insert into Customers (ID, Name) values (:ID, :Name)';
    // Set up parameter types
    Params[0].DataType := ftInteger;
    Params[1].DataType := ftString;
    Params[1].Size := 40;
    // Set up parameters' array size
    Params.ArraySize := 10000;
    // Set parameter values
    for i := 0 to 10000 - 1 do begin
      Params[0].AsIntegers[i] := i;
      Params[1].AsStrings[i] := 'Somebody ' + IntToStr(i);
    end;
    // Execute batch
    Execute(10000, 0);
  end;


Exemple 2

Array DML avec TFDQuery et gestion des erreurs : 

procedure TForm1.FDQuery1ExecuteError(ASender: TObject; ATimes,
  AOffset: Integer; AException: EFDDBEngineException; var AAction: TFDErrorAction);
begin
  case AException.Errors[0].Kind of
  ekPKViolated:
    begin
      // fix ID to be unique
      FDQuery1.Params[0].AsIntegers[AException.Errors[0].RowIndex] := AException.Errors[0].RowIndex;
      AAction := eaRetry;
    end;
  ekFKViolated:
    // if Region with RegionID is not found, then just skip row
    AAction := eaSkip;
  else
    AAction := eaFail;
  end;
end;

procedure TForm1.Button1Click(ASender: TObject);
begin
  with FDQuery1 do begin
    SQL.Text := 'insert into Customers (ID, RegionID, Name, Note) values (:ID, :RegionID, :Name, :Note)';
    // Set up parameter types
    Params[0].DataType := ftInteger;
    Params[1].DataType := ftInteger;
    Params[2].DataType := ftString;
    Params[2].Size := 40;
    Params[3].DataType := ftMemo;
    // Set up parameters' array size
    Params.ArraySize := 10000;
    // Set parameter values
    for i := 0 to 10000 - 1 do begin
      if i mod 100 = 0 then
        // force PK violation
        Params[0].AsIntegers[i] := i - 1
      else
        Params[0].AsIntegers[i] := i;
      Params[1].AsIntegers[i] := GetRegionIdForCustomer(i);
      Params[2].AsStrings[i] := 'Somebody ' + IntToStr(i);
      Params[3].Clear(i);
    end;
    // Execute batch
    Execute(10000, 0);
  end;
end;

Voir aussi

Exemples

Récupérée de « https://docwiki.embarcadero.com/RADStudio/Athens/f/index.php?title=Array_DML_(FireDAC)&oldid=214535 »