Requête paramétrée, variable selon conditions

Version imprimable

Bonjour,

Je coince sur un bête problème, j'ai besoin de mettre à jour 1 ou 2 champs sur une table, ce nombre étant fonction de la valeur de 2 variables...
Un peu de code pour expliquer:

Code:

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
procedure TfrmMain.btnApplyPropertiesClick(Sender: TObject);
var
  i1, iType, iWeight: Integer;
begin
  if (not chkType.Checked) and (not chkWeight.Checked) then
    Exit;
 
  iType := -1;
  iWeight := -1;
  if chkType.Checked then
    iType := cbbType.ItemIndex + 1;
  if chkWeight.Checked then begin
    case cbbWeight.ItemIndex of
      0: iWeight := 20;
      1: iWeight := 30;
      2: iWeight := 40;
      3: iWeight := 50;
      4: iWeight := 60;
    end;
  end;
 
  with qry do begin
    SQL.Clear;
    SQL.Add('UPDATE Boxes SET');
    SQL.Add('Type = CASE WHEN :p0 = -1 THEN Type ELSE :p0 END,'); // Si iType = -1, on ne modifie pas le champ, sinon on lui donne la valeur iType
    SQL.Add('Weight = CASE WHEN :p1 = -1 THEN Weight ELSE :p1 END'); // Si iWeight = -1, on ne modifie pas le champ, sinon on lui donne la valeur iWeight
    SQL.Add('WHERE ID = :p2;');
    StartTransaction;
    Params[0].AsInteger := iType;
    Params[1].AsInteger := iWeight;
    for i1 := 0 to il.Count - 1 do begin  // il de type TGpIntegerList
      Params[2].AsInteger := il[i1];
      ExecSQL;
    end;
    Commit;
  end;
end;

Hélas ça ne marche pas, j'ai essayé différentes modifications mais rien n'est mis à jour dans ma BDD avec ce code.
Du coup pour l'instant je passe par deux lots requêtes, ça marche mais ça me gêne de faire 2 requêtes pour ça:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
  with qry do begin
    if iType > -1 then begin
      SQL.Clear;
      SQL.Add('UPDATE Boxes SET Type = :p0 WHERE ID = :p1;');
      StartTransaction;
      Params[0].AsInteger := iType;
      for i1 := 0 to il.Count - 1 do begin
        Params[1].AsInteger := il[i1];
        ExecSQL;
      end;
      Commit;
    end;
    if iWeight > -1 then begin
      SQL.Clear;
      SQL.Add('UPDATE Boxes SET Weight = :p0 WHERE ID = :p1;');
      StartTransaction;
      Params[0].AsInteger := iWeight;
      for i1 := 0 to il.Count - 1 do begin
        Params[1].AsInteger := il[i1];
        ExecSQL;
      end;
      Commit;
    end;

Comment faire pour exécuter une seule requête, quel que soit le nombre de paramètres entrant en jeu, sans non plus passer par des tas de tests pour construire ma requête, genre "si param1 existe mais pas param2 alors SQL=...", "si param2 existe mais pas param1 alors SQL=...", "si param1 existe et param2 existe alors SQL=...", etc. ?
Info: j'utilise SQLite.

peut-être avec ça :

Code:

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
procedure TfrmMain.btnApplyPropertiesClick(Sender: TObject);
var
  i1, iType, iWeight: Integer;
	champs : string;
begin
  ....
  with qry do begin
    champs := '';
		if iType <> -1 then
		  champs := 'Type = :p0';
		if iWeight <> -1 then
		begin
		  if champs <> '' then 
			  champs := champs + ', ';
			champs := champs + 'Weight = :p1';
		end;
 
	  if champs <> ''
		begin
		  SQL.Clear;
      SQL.Add('UPDATE Boxes SET ');
      SQL.Add(champs);
      SQL.Add(' WHERE ID = :p2;');
      StartTransaction;
      Params[0].AsInteger := iType;
      Params[1].AsInteger := iWeight;
      for i1 := 0 to il.Count - 1 do begin  // il de type TGpIntegerList
        Params[2].AsInteger := il[i1];
        ExecSQL;
      end;
      Commit;
		end;
  end;
end;

21/01/2012, 20h30
GoustiFruit

Non, ça ne marche pas; j'avais essayé une idée similaire, en utilisant un TStringList à la place de ta variable "champs" pour y ajouter les lignes SQL, et ce code pour ne pas avoir à gérer la virgule moi-même:

Code:

SQL.Add(sl.CommaText); // SQL.Add(champs);

mais ensuite le nombre de Params ne correspond pas forcément: si il n'y a qu'un champs à mettre à jour, je me retrouve avec des "index out of bounds" pour les Params. J'ai aussi essayé en nommant les paramètres (puis Params.ParamByName('monparam').AsInteger pour les affectations...) mais ça ne passait pas non plus (je ne me souviens plus de l'erreur)...
21/01/2012, 20h55
sevyc64

Ah effectivement j'avais pas fait attention aux paramètres.

Oui, il faudra passer par des paramètres nommés, comme ça tu n'aurais pas le problème de passer 3 paramètres alors que ta requete n'en utilise seulement 2

Je pense qu'il y a un bug dans le potage parce qu'en simplifiant avec une seule variable...

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
with qry do begin
  SQL.Add('UPDATE Boxes SET');
  SQL.Add('Type = CASE WHEN :p0 THEN :p0 ELSE Type END');
  SQL.Add('WHERE ID = :p1;');
  StartTransaction;
  if iType <> -1 then
    Params[0].AsInteger := iType
  else begin
    Params[0].Clear;
    Params[0].Bound := TRUE;
  end;
  for i1 := 0 to il.Count - 1 do begin
    Params[1].AsInteger := il[i1];
    ExecSQL;
  end;
  Commit;
end;

...les champs sont mis à jour mais avec la valeur de :p1 au lieu de :p0 !!! :cry:

J'en reviens à mon code mais avec des paramètres nommés :

Code:

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
	with qry do begin
		champs := '';
		if iType <> -1 then
			champs := 'Type = :p0';
		if iWeight <> -1 then
		begin
			if champs <> '' then 
				champs := champs + ', ';
			champs := champs + 'Weight = :p1';
		end;
 
		if champs <> ''
		begin
			SQL.Clear;
			SQL.Add('UPDATE Boxes SET ');
			SQL.Add(champs);
			SQL.Add(' WHERE ID = :p2;');
			StartTransaction;
			Parameters.ParamByName('p0').Value := iType;
			Parameters.ParamByName('p1').Value := iWeight;
			for i1 := 0 to il.Count - 1 do begin  // il de type TGpIntegerList
				Parameters.ParamByName('p2').Value := il[i1];
				ExecSQL;
			end;
			Commit;
		end;
	end;

22/01/2012, 10h47
GoustiFruit

Même problème, par ex. dans le cas ou iWeight reste à -1 je me retrouve avec une erreur "Parameter 'p1' not found".

Les paramètres nommés sont pas très bien gérés alors.

Tu peux essayer ceci peut-être :
Code:

1 2 if iType<>-1 then Parameters.ParamByName('p0').Value := iType; if iWeight<>-1 then Parameters.ParamByName('p1').Value := iWeight;

Citation:

Envoyé par GoustiFruit

Même problème, par ex. dans le cas ou iWeight reste à -1 je me retrouve avec une erreur "Parameter 'p1' not found".

Normal puisque tu n'as pas utilisé le paramètre p1 dans ta requête (: le bloc conditionnel n'est pas exécuté quand iWeight vaut -1) mais tu renseignes sa valeur par les Parameters.ParamByName !!

donc tu peux par exemple tester une nouvelle fois tes valeurs avant de définir les paramètres :

Code:

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
 
// code non testé !
        	with qry do begin
		champs := '';
		if iType <> -1 then
			champs := 'Type = :p0';
		if iWeight <> -1 then
		begin
			if champs <> '' then 
				champs := champs + ', ';
			champs := champs + 'Weight = :p1';
		end;
 
		if champs <> ''
		begin
			SQL.Clear;
			SQL.Add('UPDATE Boxes SET ');
			SQL.Add(champs);
			SQL.Add(' WHERE ID = :p2;');
			StartTransaction;
                      {ici}    
			if iType <> -1 then
                                Parameters.ParamByName('p0').Value := iType;
                      {et là} 
              		if iWeight <> -1 then
		        	Parameters.ParamByName('p1').Value := iWeight;
			for i1 := 0 to il.Count - 1 do begin  // il de type TGpIntegerList
				Parameters.ParamByName('p2').Value := il[i1];
				ExecSQL;
			end;
			Commit;
		end;
	end;

[EDIT] Grillé ! faut pas s'interrompre sur ce forum ! ;)

22/01/2012, 13h36
GoustiFruit

Je suis d'accord avec vous, mais c'est justement pour ça que j'avais précisé dans la question initiale que je voulais éviter de tester tous les cas possibles pour construire ma requête ! Dans cet exemple il n'y a que deux variables, mais il pourrait y en avoir plus.

Apparemment la construction avec "CASE WHEN" permettrait de résoudre ce dilemme mais pour une raison qui m'échappe encore, ça coince au niveau du passage de paramètres (car ça fonctionne bien quand je le fais en ligne de commande)...
22/01/2012, 13h58
sevyc64
Visiblement le problème vient de la façon dont TQuery gère les paramètres.

Personnellement, je suis plus habitué à .Dotnet dans lequel on peut passer un tableau de paramètres nommés à coté de la requete. Rien n'interdit de passer 10 paramètres même si la requete n'en utilise que 2.

Avec TQuery, visiblement, on ne peut passer en paramètre nommé que les paramètres déjà définis dans la requete.

Les seules solutions, semble-t-il, sont soit de tester chaque cas et de ne passer que les paramètres réellement présent dans la requete, soit effectivement de se débrouiller pour avoir systématiquement tous les paramètres dans la requete (d'où le CASE).

Perso je n'ai jamais utiliser de CASE dans un update. Essaye peut-être avec cette syntaxe :
Code:

1 2 3 4 SQL.Add('UPDATE Boxes SET'); SQL.Add('Type = CASE :p0 WHEN -1 THEN Type ELSE :p0 END,'); SQL.Add('Weight = CASE :p1 WHEN -1 THEN Weight ELSE :p1 END'); SQL.Add('WHERE ID = :p2;');

Même résultat avec cette syntaxe.

Mais ça m'a fait penser à un truc... Le code suivant fonctionne:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 with qry do begin SQL.Clear; SQL.Add('UPDATE Boxes SET'); SQL.Add('Type = CASE :p0 WHEN -1 THEN Type ELSE :p1 END,'); // Si iType = -1, on ne modifie pas le champ, sinon on lui donne la valeur iType SQL.Add('Weight = CASE :p2 WHEN -1 THEN Weight ELSE :p3 END'); // Si iWeight = -1, on ne modifie pas le champ, sinon on lui donne la valeur iWeight SQL.Add('WHERE ID = :p4;'); StartTransaction; Params[0].AsInteger := iType; Params[1].AsInteger := iType; Params[2].AsInteger := iWeight; Params[3].AsInteger := iWeight; for i1 := 0 to il.Count - 1 do begin // il de type TGpIntegerList Params[4].AsInteger := il[i1]; ExecSQL; end; Commit; end;
Il y a donc un problème avec le passage des paramètres ? Il semble que je ne puisse pas réutiliser un paramètre 2 fois dans une même requête de ce type, pourtant j'ai d'autres types de requêtes où ça marche !?
Ex. qui fonctionne très bien ailleurs dans mon code:

Code:

qry.SQL.Text := 'INSERT INTO Manager (Name, Area, Url) SELECT :p0, :p1, :p2 WHERE NOT EXISTS (SELECT 1 FROM Manager WHERE Name = :p0 AND Area = :p1);';

8O8O8O