IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Voir le flux RSS

tourlourou

[Actualité] Objet gérant les requêtes SQL paramétrées

Note : 5 votes pour une moyenne de 3,40.
par , 17/12/2018 à 17h43 (6187 Affichages)
Introduction

Pour me faciliter la vie, j'ai développé un objet gérant des requêtes paramétrées simples. Il apporte une commodité en élaborant des requête au format texte à partir du SQL fourni et de paramètres dont les valeurs peuvent être affectées dans leur type, évitant à l'utilisateur de les convertir. Destiné à ma librairie pour SQLite, il n'utilise cependant pas l'API dédiée du SGBD, prenant simplement en entrée une requête textuelle avec des noms de paramètres, permettant leur affectation et délivrant en sortie une requête textuelle où les noms des paramètres ont été remplacés par leurs valeurs.

Définition du problème

Versant SQLite

Les requêtes, comme toutes les chaînes pour SQLite, sont par défaut attendues codées au format UTF-8. Les formats de données reconnus par SQLite sont INTEGER, REAL, TEXT, BLOB, et NULL.

Ce dernier type correspond à l'absence de donnée dans un champ de la table. C'est une valeur particulière correspondant justement à l'absence de valeur, donc non représentable par l'une d'entre elles !
Les BLOBs (Binary Large Objects) ont une gestion particulière qui ne permet pas de les traiter comme les autres champs en tant que paramètres d'une requête dans ce cadre (elle est cependant prévue dans l'API implémentant les requêtes préparées, grâce à des fonctions spécifiques pour leur création et leur accès).

Au total, il s'agit d'offrir une prise en charge des champs NULL et des types entier, réel et texte.

NB : SQLite n'utilise pas de typage strict des champs, mais un typage dynamique basé sur l'affinité de type. Cela n'interdit pas d'être rigoureux et de faire comme s'il était nécessaire de respecter à la lettre le type d'une colonne.

Versant objet

Que doit être une requête paramétrée ? C'est une requête SQL (une chaîne de caractères) dont certaines portions désignées devront être remplacées par les valeurs voulues : dans SELECT champs FROM matable WHERE condition on peut vouloir rendre variables (paramétrer) les champs à récupérer en remplaçant 'champs' par 'nom' ou 'adresse' ou 'nom, adresse' et 'condition' par 'id < 5' ou 'id = 12' par exemple. Mais ce pourrait aussi être le cas de 'matable'.

Il faut donc un moyen syntaxique de différencier un paramètre d'un identifiant fixe, et un analyseur syntaxique (parser, ou tokenizer). Classiquement, le nom d'un paramètre est précédé des caractères ':' ou '@'.

Dans un souci de simplicité, l'analyse syntaxique reste ici sommaire. J'ai choisi d'entourer le nom d'un champ du caractère ':', ce qui donne pour notre exemple : SELECT :champs: FROM matable WHERE :condition:.

On voit de suite la limitation liée à une mauvaise interprétation d'une portion de la chaîne qui intégrerait le caractère ':' sans représenter le nom d'un paramètre. On rencontrerait ce problème avec l'exemple suivant : INSERT INTO :table_voulue: (macolonne) VALUES ( ''exemple : mauvais'' ).

Le problème peut se régler simplement : si l'on doit utiliser le caractère ':' dans une chaîne, il suffit de le faire dans un paramètre : INSERT INTO :table_voulue: (macolonne) VALUES ( :monparam: ) et d'affecter ensuite la valeur ''exemple : bon'' à monparam.

On va donc d'abord définir le délimiteur choisi :

Code Pascal : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
unit lySQLite3Param;  
 
{$mode objfpc}{$H+}
 
interface
 
uses
  Classes, SysUtils;
 
const
  // mettre les noms des paramètres entre délimiteurs : 'SELECT * FROM :TableX: WHERE id = :Identité_Client:'
  ParamDelimiter = ':' ;
 
  // caractère entourant les chaînes SQL au format texte (autre possibilité : '''' = simple quote)
  QuoteChar = '"';

Et la constante QuoteChar, qui servira dès le chapitre suivant.

On aura besoin, outre l'analyseur syntaxique envisagé plus loin, de champs paramétrables établissant la correspondance entre nom et valeur.

Objet « Champ » ou « Paramètre » TlyField

Un paramètre devra être de ce fait une structure qui contiendra son type, son nom et sa valeur, en proposant les moyens de l'affecter et de la lire à partir ou à destination d'un ou plusieurs types de données.

Le type TlyField est une classe simple, descendant directement du type Tobject. Ses données internes (champs) seront son nom, sa valeur, son type (énumération déclarée ci-après), un booléen indiquant si elle est affectée, et un autre si elle est de type textuel :

Code Pascal : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
type
  // Types de champs reconnus (ou pas !)
  TlyFieldType = (ftUnknown, ftSQL, ftText, ftInteger, ftBoolean, ftFloat, ftDateTime);
 
  // Objet présentant les valeurs d'un champ
  TlyField = class
  private
    FName: string;
    FText: string;
    FNull: Boolean;
    bText: Boolean;
    FType: TlyFieldType;

Sa valeur est stockée sous forme de la chaîne FText (au prix éventuel d'une conversion), afin de pouvoir remplacer le nom du paramètre dans la requête sans conversion. Les champs sont déclarés privés pour n'être visibles comme propriétés qu'au travers d'accesseurs (méthodes getter/setter) les protégeant des manipulations directes, et seuls à même de tenir compte du fait qu'une valeur peut ne pas être définie ou par quel type de donnée elle a été affectée.

Lors de la consultation de la valeur d'un paramètre non défini (NULL), il n'y a pas de valeur de retour possible dans le type attendu et l'accesseur (getter) devra donc lever une exception.

Pour les propriétés et méthodes publiques, on a tout d'abord la remise à zéro de la valeur (à NULL, non affectée), les constructeurs (avec ou sans paramètres), et des indicateurs d'état : FieldType, IsText, IsNull.

Tester avec IsNull avant la consultation d'une valeur évite de lever une exception si elle n'est pas définie.

Code Pascal : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
  public
    procedure Clear;
    constructor Create; overload;
    constructor Create(aName: string); overload;
    constructor Create(aName: string; aType: TlyFieldType); overload;
    property Name: string 
    property FieldType: TlyFieldType   
    property IsText: Boolean 
    property IsNull: Boolean

Compte tenu du petit nombre de types gérés par SQLite, on pourra se contenter de peu de propriétés : valeur entière, réelle, texte. Mais ceci est un peu court par rapport à la richesse du Pascal et aux besoins courants des programmeurs. On va donc dériver le type booléen, d'ailleurs géré dans SQLite comme un entier, et interfacer le TDateTime comme un Double qu'il est :

Code Pascal : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
     property AsSQL: string  // par défaut UTF-8 pour SQLite 3.7.13 ; quoté si nécessaire (texte)  
 
    property AsText: string  // valeur brute de la chaîne, sans quotes éventuelles
 
 
    property AsInteger: int64  // stockage interne dans SQLite sous forme d'entier de 1 à 8 octets
 
    property AsFloat: Double  // c'est le format de stockage interne des réels dans SQLite
 
    property AsBoolean: Boolean  // stockage interne en tant qu'entier : False=0 et True=1
 
    property AsDateTime: TDateTime  // pas de stockage par défaut dans SQLite : traité comme réel

Par malchance (?) SQLite ne possède pas de format spécifique pour les données de temps, pour lesquelles il dispose cependant de fonctions capables de gérer les formats :
1) TEXT : chaîne ISO-8601 jusqu'au format 'YYYY-MM-DD HH:MM:SS.SSS' ;
2) INT : temps Unix, correspondant au nombre de secondes écoulées depuis le 01/01/1970 à 00:00:00 UTC ;
3) FLOAT : jour Julien, correspondant au nombre de jours écoulés depuis l'origine de la période julienne, soit le 24/11/4714 avant JC à 00:00:00 UTC. La partie décimale codant la fraction de jour, donc l'heure.

Pour sa part, Lazarus utilise le TDateTime, au format FLOAT aussi, mais avec pour origine le 30/12/1899 à 00:00:00.

Des conversions peuvent être nécessaires, certaines assurées par les fonctions disponibles dans SQLite, mais si l'on se contente de stocker des TDateTime comme réels et les lire comme tels, point besoin de telles considérations !
Il nous faut donc maintenant les accesseurs nécessaires pour les propriétés définies, certains setters mettant à jour plusieurs champs ou réalisant des conversions, et les getters devant vérifier l'initialisation de la valeur ou réaliser une conversion :

Code Pascal : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
  protected
    procedure SetNull(aValue: Boolean);

    procedure SetText(aValue: string);

    procedure SetSQL(aValue: string);

    function  GetSQL: string;

    procedure SetInt(aValue: int64);

    function  GetInt: int64;

    procedure SetFloat(aValue: Double);

    function  GetFloat: Double;

    procedure SetBool(aValue: Boolean);

    function  GetBool: Boolean;

    procedure SetTime(aValue: TDateTime);

    function  GetTime: TDateTime;

Certaines propriétés sont en lecture seule (Name, par ex.), sont lisibles directement à partir du champ correspondant sans nécessiter de getter (IsNull, par ex.), ou font appel à des accesseurs. D'où les déclarations complètes :

Code Pascal : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
    property Name: string read FName; // affectable seulement par le constructeur
 
    property FieldType: TlyFieldType read FType;  // affectable seulement par le constructeur ou le type de la valeur affectée
 
    property IsText: Boolean read bText;
 
    property IsNull: Boolean read FNull write SetNull;
 
    property AsSQL: string read GetSQL  write SetSQL; // par défaut UTF-8 pour SQLite 3.7.13 ; quoté si nécessaire (texte)
 
    property AsText: string read FText write SetText; // valeur brute de la chaîne, sans quotes éventuelles
 
    property AsInteger: int64 read GetInt write SetInt; // stockage interne dans SQLite sous forme d'entier de 1 à 8 octets
 
    property AsFloat: Double read GetFloat write SetFloat; // c'est le format de stockage interne des réels dans SQLite
 
    property AsBoolean: Boolean read GetBool write SetBool; // stockage interne en tant qu'entier : False=0 et True=1
 
    property AsDateTime: TDateTime read GetTime write SetTime; // pas de stockage par défaut dans SQLite : traité comme réel  
 
end ;

Une fois l'objet décrit, reste à l'implémenter. Tout d'abord sa création :

Code Pascal : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
implementation
 
// ne peut servir qu'à rendre NULL : l'inverse se fait en affectant qqch
procedure TlyField.SetNull(aValue: Boolean);
begin
  if aValue
  then begin
    FNull:=True;
    FText:='NULL';
    bText:=False; //  car mot réservé
  end;
end;
 
procedure TlyField.Clear;
begin
  SetNull(True);
end;
 
constructor TlyField.Create;
begin
  inherited;
  Clear;
  FName:='';
  FType:=ftUnknown;
end;
 
constructor TlyField.Create(aName: string);
begin
  Create;
  FName:=aName;
end;
 
constructor TlyField.Create(aName: string; aType: TlyFieldType);
begin
  Create(aName);
  FType:=aType;
end;

On voit que le constructeur initialise le paramètre comme non affecté ( Clear appelle SetNull(True) ), ce qui a pour conséquence de fixer la valeur de sa propriété SQL à 'NULL'.

En effet, même si SQLite renvoie une chaîne vide pour un champ NULL, seul un champ passé comme 'NULL' est reconnu comme tel (et non une chaîne vide). C'est ainsi que dans l'exemple ci-dessous, le premier SELECT renvoie age=30 et le second age=40 :
'INSERT INTO employes ( nom, age ) VALUES ( NULL, 30 )'
'INSERT INTO employes ( nom, age ) VALUES ( "", 40 )'
'SELECT age FROM employes WHERE nom ISNULL'
'SELECT age FROM employes WHERE nom NOTNULL'

L'objet ne nécessite pas de destructeur spécifique, l'appel à Tobject.Destroy lui suffisant (pas de libérations à effectuer).
Il reste à envisager les couples getter/setter pour chaque propriété, le stockage de la valeur s'effectuant sous forme texte :

La propriété SQL doit prendre en compte les impératifs de SQLite :
1) pas de caractère nul dans une chaîne (dans ce cas, utiliser un BLOB) ;
2) les valeurs TEXT doivent être quotées. Le caractère '"' a été choisi arbitrairement, mais ''' aurait convenu aussi bien.
J'ai choisi par commodité de laisser la possibilité d'affecter du texte qui sera mis entre quotes (affecté par la propriété AsText, pour les contenus au format TEXT) ou non (affecté par la propriété AsSQL, pour les noms de champs, ou pour des chaînes déjà quotées, par ex.).

Code Pascal : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
procedure TlyField.SetText(aValue: string); // valeur textuelle qui devra être quotée
begin
  if Pos(#0, aValue) > 0
  then raise Exception.Create('Présence du caractère nul, interdit dans une chaîne')
  else FText:=aValue;
  FType:=ftText;
  bText:=True;
  FNull:=False;
end;
 
procedure TlyField.SetSQL(aValue: string); // valeur textuelle qui ne devra pas être quotée
begin
  SetText(aValue);
  FType:=ftSQL;
  bText:=False;
end;  
 
function TlyField.GetSQL: string;
begin
  if IsText
  then Result:=AnsiQuotedStr(FText, QuoteChar) // quoté car assigné comme texte
  else Result:=FText; // non quoté car assigné par valeur (dont NULL)
end;

Comme SQLite utilise le typage dynamique, passer une valeur texte à une colonne d'affinité numérique est possible*: SQLite tentera la conversion en fonction de l'affinité de la colonne et - s'il n'y parvient pas – stockera telle quelle la chaîne transmise.

Pour les entiers, le problème est simple ; il suffit de gérer les erreurs en cas de valeur non affectée ou non convertible :

Code Pascal : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
procedure TlyField.SetInt(aValue: int64);
begin
  FText:=IntToStr(aValue);
  FType:=ftInteger;
  bText:=False;
  FNull:=False;
end;
 
function TlyField.GetInt: int64;
begin
  if FNull
  then raise Exception.Create('Le paramètre est indéfini')
  else begin
    try
      Result:=StrToInt64(FText);
    except
      raise Exception.Create('Paramètre non entier');
    end;
  end;
end;

Sur le même modèle, on a pour les réels :

Code Pascal : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
procedure TlyField.SetFloat(aValue: Double);
begin
  FText:=FloatToStr(aValue);
  // pour s'affranchir des locales et fournir une valeur au format texte convertible par SQLite
  FText:=StringReplace(FText, ThousandSeparator , #0 , []);
  FText:=StringReplace(FText, DecimalSeparator , '.' , []);
  FType:=ftFloat;
  bText:=False;
  FNull:=False;
end;
 
function  TlyField.GetFloat: Double;
var
  SQLFormatSettings: TFormatSettings;
begin
  if FNull
  then raise Exception.Create('Le paramètre est indéfini')
  else begin
    if not TryStrToFloat(FText, Result) // au format local ?
    then begin
      SQLFormatSettings.ThousandSeparator:=#0;
      SQLFormatSettings.DecimalSeparator:='.';
      if not TryStrToFloat(FText, Result, SQLFormatSettings) // au format SQL ?
      then raise Exception.Create('Paramètre non réel');
    end;
  end;
end;

Si l'on affecte une chaîne représentant un réel avec d'autres séparateurs décimal ou des milliers que celui courant ou ceux compris de SQLite, une exception sera levée lors de la conversion, mais du fait du typage dynamique, on pourra récupérer le réel au format texte, stocké tel quel.

Il ne reste plus à traiter que les types dérivés booléen et temps :

Code Pascal : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
procedure TlyField.SetBool(aValue: Boolean);
begin
  if aValue
  then SetInt(1)
  else SetInt(0);
  FType:=ftBoolean;
end;
 
function  TlyField.GetBool: Boolean;
begin
  Result := ( GetInt = 1 ) ;
end;
 
procedure TlyField.SetTime(aValue: TDateTime);
begin
  SetFloat(aValue);
  FType:=ftDateTime;
end;
 
function  TlyField.GetTime: TDateTime;
begin
  Result:=GetFloat;
end;

La « mécanique » interne est en place, pour offrir à l'utilisateur une interface commode répondant à ses besoins. Cet objet « champ » a tout d'abord vocation à être utilisé par l'objet « requête paramétrée » dont nous allons maintenant traiter.

Objet « Requête paramétrée » TlyParamSQL

Cet objet doit analyser la requête passée pour identifier les champs paramétriques dont les noms sont entre délimiteurs, permettre de les affecter en les appelant par indice ou par leur nom, et fournir la chaîne résultante. Ceci conduit à définir leur interface publique :

Code Pascal : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
  TlyParamSQL = class
  private
 
  protected
    procedure SetRequest(aValue: string);
 
    function  GetRequest: string;
 
    function  GetCount: integer;
 
    function  GetParam(Index: integer): TlyField; 
 
  public
    constructor Create; overload;
 
    procedure   Clear;
 
    destructor  Destroy; override;
 
    property ParamCount: integer read GetCount;
 
    function ParamByName(aName: string): TlyField;
 
    property Params[Index: integer]: TlyField read GetParam;
 
    property Request: string read GetRequest write SetRequest;
 
  end;

Il s'agit de découper une chaîne en fonction d'un délimiteur, avec des portions qui seront le SQL et d'autres les noms des paramètres. Rien de tel que le TStringList pour stocker des chaînes et les découper. On en utilisera deux, stockés dans un tableau : le premier pour les portions SQL, et l'autre pour les noms des paramètres. Le TstringList permettant de stocker des objets dans une liste parallèle à celle des chaînes, ce sera idéal pour y loger les TlyField associés aux noms des paramètres identifiés.

Code Pascal : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
  TlyParamSQL = class
  private
    FQuery: TstringList;
 
    FParams: TstringList;
 
    Explode: array[False..True] of TstringList;
 
  protected

Tout d'abord, la vie de l'objet :

Code Pascal : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
constructor TlyParamSQL.Create;
begin
  inherited;
  FQuery:=TStringList.Create;
  Explode[False]:=FQuery;
  FParams:=TStringList.Create;
  Explode[True]:=FParams;
end;
 
procedure TlyParamSQL.Clear;
var
  i: integer;
begin
  FQuery.Clear;
  for i:=0 to FParams.Count-1 do Fparams.Objects[i].Free; // libération des champs
  FParams.Clear;
end;
 
destructor TlyParamSQL.Destroy;
begin
  Clear;
  FQuery.Free;
  FParams.Free;
  inherited;
end;

Puis l'analyse de la requête :

Code Pascal : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
procedure TlyParamSQL.SetRequest(aValue: string);
var
  IsParam: Boolean;
  tsl: TStringList;
  i,j: integer;
  S: string;
 
begin
  Clear; 
 
  aValue:='  '+aValue; // la chaîne débutera par des espaces (éliminés au Trim) 
 
  IsParam:=False; // => jamais par un paramètre
 
  tsl:=TStringList.Create;
 
  tsl.Delimiter:=ParamDelimiter;
 
  tsl.StrictDelimiter:=True;
 
  tsl.DelimitedText:=aValue; // explosion dans tsl selon le délimiteur
 
  for i:=0 to tsl.Count-1
  do begin
    S := Trim( tsl[i] ) ;
 
    j:=Explode[IsParam].Add(S);  
 
    if IsParam then Fparams.Objects[j]:=TlyField.Create(FParams[j]);
 
    IsParam := not IsParam;
  end;
 
  tsl.Free;
end;

En ajoutant quelques espaces en début de chaîne, on s'est assuré qu'elle ne débutera pas par un paramètre. En utilisant la fonction Trim, qui supprime les espaces en début et fin de chaîne, on obtient - selon que la requête débute par un paramètre ou pas - soit une chaîne vide qui sera invisible à la restitution, soit la chaîne initiale.
Une fois la chaîne scindée, il suffit d'attribuer alternativement les portions à la liste selon leur nature, en créant le paramètre TlyField associé dans la propriété Objects[i] correspondant à son nom.

Et pour la restitution, il suffit de procéder à l'inverse, en utilisant la valeur du champ et en ajoutant les espaces entre portions, qui ont sauté au Trim :

Code Pascal : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
function  TlyParamSQL.GetRequest: string;
var
  p, q, max: integer;
  IsParam: Boolean;
  S: string;
begin
  Result:=EmptyStr;
  p:=0;
  q:=0;
  max:=FParams.Count+FQuery.Count;
  IsParam:=False;
  while (p+q)<max
  do begin
    if IsParam
    then begin
      S:=TlyField(FParams.Objects[p]).AsSQL;
      Inc(p);  
    end
    else begin
      S:=FQuery[q];
      Inc(q); 
    end;
    if S>EmptyStr then Result:=Result+' '+S; 
    IsParam := not IsParam;
  end;
  Result:=Trim(Result);
end;

Le codage des fonctions restantes est trivial :

Code Pascal : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
function TlyParamSQL.GetCount: integer;
begin
  Result:=FParams.Count;
end;
 
function TlyParamSQL.GetParam(Index: integer): TlyField;
begin
  if (Index<0) or (Index>FParams.Count)
  then Result:=nil
  else Result:=TlyField(FParams.Objects[Index]);
end;
 
function TlyParamSQL.ParamByName(aName: string): TlyField;
var
  i: integer;
begin
  i:=FParams.IndexOf(aName);
  if i<0
  then Result:=nil
  else Result:=TlyField(FParams.Objects[i]);
end;

Exemple d'utilisation

Un court exemple valant mieux qu'un long discours, munissons-nous d'une fiche avec un bouton et un Memo pour illustrer ces requêtes paramétrées :

Code Pascal : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
unit Unit1;
 
{$mode objfpc}{$H+}
 
interface
 
uses
  Classes, SysUtils, FileUtil, Forms, Controls, Graphics, Dialogs, StdCtrls,
  lySQLite3Param; 
 
type
 
  { TForm1 }
 
  TForm1 = class(TForm)
    Button1: TButton;
    Memo1: TMemo;
    procedure Button1Click(Sender: TObject);
  private
    { private declarations }
  public
    { public declarations }
  end;
 
var
  Form1: TForm1;
 
implementation

Dans le code du bouton, à titre d'exemples :
1) erreur due à un caractère séparateur dans une chaîne d'une requête,
2) solution de passer cette chaîne avec séparateur en paramètre,
3) modification d'une requête en faisant varier ses paramètres,
4) assignation de réels à un champ et conversions,
5) assignation de texte à un champ.

Code Pascal : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
{ TForm1 }
 
procedure TForm1.Button1Click(Sender: TObject);
var
  ParamSQL: TlyParamSQL;
  Champ: TlyField;
  i, j: integer;
  SQL: string;
 
begin
  ParamSQL:=TlyParamSQL.Create;
 
  Memo1.Lines.Add('***** erreur : délimiteur dans la chaîne *****');
  SQL := 'INSERT INTO matable (montexte) VALUES ( "exemple : mauvais" )' ;
  Memo1.Lines.Add('');
  Memo1.Lines.Add(SQL);
  Memo1.Lines.Add(' est interprétée comme paramétrée : ');
  ParamSQL.Request := SQL;
  j:=ParamSQL.ParamCount;
  Memo1.Lines.Add('  nombre de paramètres = '+IntToStr(j));
  for i:= 0 to j-1
  do Memo1.Lines.Add('  nom du paramètre d''indice '+IntToStr(i)+' = '+ParamSQL.Params[i].Name);
  Memo1.Lines.Add(' et est traduite actuellement en :');
  Memo1.Lines.Add(ParamSQL.Request);
  Memo1.Lines.Add('');
 
  Memo1.Lines.Add('***** solution au délimiteur dans la chaîne *****');
  SQL := 'INSERT INTO matable (montexte) VALUES ( :value: )' ;
  Memo1.Lines.Add('');
  Memo1.Lines.Add(SQL);
  ParamSQL.Request := SQL;
  Memo1.Lines.Add(' peut accueillir dans son paramètre toute chaîne,');
  Memo1.Lines.Add(' notamment renfermant le délimiteur : ');
  ParamSQL.ParamByName('value').AsText := 'exemple : bon' ;
  Memo1.Lines.Add(' et sera correctement traduite en :');
  Memo1.Lines.Add(ParamSQL.Request);
  Memo1.Lines.Add('');
 
  Memo1.Lines.Add('***** exemples d''utilisation *****');
  SQL := 'SELECT :champs: FROM matable WHERE age :condition: :limite:' ;
  Memo1.Lines.Add('');
  Memo1.Lines.Add(SQL);
  ParamSQL.Request := SQL;
  ParamSQL.ParamByName('champs').AsSQL := 'nom' ;
  ParamSQL.ParamByName('condition').AsSQL := '=' ;
  ParamSQL.ParamByName('limite').AsSQL := '20' ;
  Memo1.Lines.Add('');
  Memo1.Lines.Add(ParamSQL.Request);
  ParamSQL.Params[0].AsSQL := 'nom, age' ;
  ParamSQL.Params[1].AsSQL := '<' ;
  ParamSQL.Params[2].AsInteger := 50 ;
  Memo1.Lines.Add('');
  Memo1.Lines.Add(ParamSQL.Request);
  Memo1.Lines.Add('');
 
  ParamSQL.Free;
 
  Champ:=TlyField.Create('test', ftFloat);
 
  Memo1.Lines.Add('***** exemples d''assignation de réels *****');
  Memo1.Lines.Add('');
  Champ.AsFloat := 3.14 ;
  Memo1.Lines.Add(Champ.AsSQL+' = '+FloatToStr(Champ.AsFloat));
  Champ.AsSQL:='3500,12';
  Memo1.Lines.Add(Champ.AsSQL+' = '+FloatToStr(Champ.AsFloat));
  Champ.AsText:='3 712,512';
  try
    Memo1.Lines.Add(Champ.AsSQL+' = '+FloatToStr(Champ.AsFloat));
  except
    on E: Exception
    do Memo1.Lines.Add(Champ.AsSQL+' non convertible car séparateur des milliers : '+E.Message);
  end;
  Memo1.Lines.Add('');
 
  Memo1.Lines.Add('***** exemples d''assignation de texte *****');
  Memo1.Lines.Add('');
  Champ.AsSQL:='AsSQL => le texte récupéré par AsSQL sera non quoté';
  Memo1.Lines.Add(Champ.AsSQL);
  Champ.AsText:='AsText => le sera, par contre';
  Memo1.Lines.Add(Champ.AsSQL);
  Champ.AsText:='sauf si on le récupère par AsText';
  Memo1.Lines.Add(Champ.AsText);
 
  Champ.Free;
 
end;
 
end.

Vous trouverez les unités ici : Billet_numero_2.zip

Conclusion

L'élaboration d'un objet jouant le rôle d'intermédiaire permet une gestion aisée des champs et des requêtes paramétrées.

L'encapsulation dans un objet de la gestion d'une base de données SQLite et des requêtes simples ou paramétrées, basée sur le wrapper présenté précédemment et les outils détaillés ici fera l'objet d'un prochain billet.

Le code, prévu pour Lazarus, est très aisément adaptable pour Delphi.

Envoyer le billet « Objet gérant les requêtes SQL paramétrées » dans le blog Viadeo Envoyer le billet « Objet gérant les requêtes SQL paramétrées » dans le blog Twitter Envoyer le billet « Objet gérant les requêtes SQL paramétrées » dans le blog Google Envoyer le billet « Objet gérant les requêtes SQL paramétrées » dans le blog Facebook Envoyer le billet « Objet gérant les requêtes SQL paramétrées » dans le blog Digg Envoyer le billet « Objet gérant les requêtes SQL paramétrées » dans le blog Delicious Envoyer le billet « Objet gérant les requêtes SQL paramétrées » dans le blog MySpace Envoyer le billet « Objet gérant les requêtes SQL paramétrées » dans le blog Yahoo

Mis à jour 27/12/2018 à 19h46 par tourlourou

Catégories
Programmation , librairie Pascal pour SQLite

Commentaires

  1. Avatar de SergioMaster
    • |
    • permalink
    Bonjour,

    le billet un peu long, pourquoi ne pas en avoir fait un tutoriel ? Tout y est.
    Par contre je trouve que c'est la porte ouverte à de l'injection SQL.

    En tout cas on y retrouve presque tout ce qu'offre les fonctionnalités de Firedac (paramètres, macros) et ça c'est :cool:

  2. Avatar de Malick
    • |
    • permalink
    Salut,

    Citation Envoyé par SergioMaster
    le billet un peu long, pourquoi ne pas en avoir fait un tutoriel ? Tout y est.
    Il a cependant été publié sur les différents portails concernés comme tutoriel pour une meilleure visibilité.

    Excellent tutoriel

    Merci encore
  3. Avatar de tourlourou
    • |
    • permalink
    Citation Envoyé par SergioMaster
    Par contre je trouve que c'est la porte ouverte à de l'injection SQL.
    Merci, Serge.

    Ta remarque est très intéressante sur la faiblesse par rapport à l'injection SQL. Je ressors des vieux trucs du placard, et le format blog me plaisait pour avoir justement ces retours sur des bugs ou évolutions/suggestions.

    Pourquoi, au bout du compte, ne pas retravailler l'ensemble en fonction des remarques, et en faire une synthèse sous une forme un peu différente ? L'avenir le dira...