Adding params to sql query

[Matija]

Hello

I have two sql sentence in Delphi 10.

Code: Select all

  SqlText[0] := 'UPDATE table SET column1 = 1 WHERE id = 10;';
  SqlText[1] := 'INSERT INTO table (column1, column2) VALUES (:column1, :column2);';

I add params for second sql sentence.

Code: Select all

  sqlConn1.Query1.SQL.Text := SqlText[1];
  sqlConn1.Query1.ParamByName('column1').Value := 10;
  sqlConn1.Query1.ParamByName('column2').Value := 15;

When I run this in a loop, I have a problem, that params are empty on second run.

Code: Select all

  for i := Low(SqlText) to High(SqlText) do
  begin
    Query1.SQL.Text := SqlText[i];
    Query1.ExecSQL;
  end;

I try different thing with ParamCheck but nothing seem to work.

Code: Select all

  for i := Low(aSqlText) to High(aSqlText) do
  begin
    if i= 0 then Query1.ParamCheck := False;
    if i= 1 then Query1.ParamCheck := True;

    Query1.SQL.Text := aSqlText[i];
    Query1.ExecSQL;
  end;

Is there a way, that I pass parameters to Query1 when is needed?
Regards,
Matija

[ViktorV]

The TUniQuery.ParamCheck property is responsible for enabling (disabling) the automatic parameters generation when changing SQL property : https://www.devart.com/unidac/docs/inde ... mcheck.htm
To solve the issue you can use the following recommendations:
1. If TUniQuery.ParamCheck = True:
you should manually set the parameters values only for queries in which they are present. For example:

Code: Select all

...
  Query1.ParamCheck := True;
  for i := 0 to 1 do begin
    Query1.SQL.Text := SqlText[i];
    if i = 1 then begin
      Query1.ParamByName('column1').Value := 10;
      Query1.ParamByName('column2').Value := 15;
    end;
    Query1.ExecSQL;
  end;

2. If TUniQuery.ParamCheck = False:
you should manually create the required parameters and set their values manually. For example:

Code: Select all

...
  Query1.ParamCheck := False;
  Query1.Params.CreateParam(ftInteger, 'column1', ptInput);
  Query1.Params.CreateParam(ftInteger, 'column2', ptInput);
  Query1.ParamByName('column1').Value := 10;
  Query1.ParamByName('column2').Value := 15;
  for i := 0 to 1 do begin
    Query1.SQL.Text := SqlText[i];
    Query1.ExecSQL;
  end;

[Matija]

Hello Viktor
Thank you for replay. The solution is good, but not for my case.
I generate one or more sql sentence in SqlText[x] in different classes, then I pass this SqlText as parameter to one class "dataClass" where I execute them one by one.

Code: Select all

  for i := Low(SqlText) to High(SqlText) do
  begin
    Query1.SQL.Text := SqlText[i];
    Query1.ExecSQL;
  end;

When I define parameters in one class it works only for first execution. After that parameters are cleaned.
So I make workaround. If I have more sql sentence with parameters I call "dataClass" more times.
Thank you and regards,
Matija

[ViktorV]

We are glad to see you have found a solution.
Feel free to contact us if you have any further questions about our products.