Reuse TSQLQuery Missing Params

Question

I'm using Delphi XE2 and a TSQLQuery object. It works the first time that I use it. If I immediately reuse it, then it doesn't parse the new SQL for its parameters and rebuild the qry.Params list:

var
  qry: TSQLQuery;
begin
  qry := TSQLQuery.Create(nil);
  try
    qry.MaxBlobSize := -1;
    qry.SQLConnectin := AnExistingConnection;

    qry.CommandText := 'select field1 from table1 where fieldX = @valueX';
    qry.ParamByName('valueX').Value := 1;
    qry.Open;

    // ... use data ...

    qry.Close;
    qry.Params.Clear; // <- works the same with or without this
    qry.CommandText := 'select field2 from table2 where fieldY = @valueY';
    qry.ParamByName('valueY').Value := 2; // <- Error: 'valueY' Param not found
    qry.Open;
  finally
    FreeAndNil(qry);
  end;
end;

It doesn't matter what I do, it doesn't parse the 2nd SQL statement for its parameters so I can't bind the 'valueY' parameter by name.

I can think of two workarounds:

1. Manually build the qry.Params list myself.
2. Destroy and recreate the qry object in between the two commands.

I shouldn't have to do either of these. Perhaps there is a property or something on the qry object that will cause it to reparse parameters each time a new SQL statement is assigned to its CommandText property?

Answer 1

Turned out to be a syntax issue. Params must be prefaced with a : not a @. I had local SQL variables throughout the real first query, so there was a mixture of @param and :param variables throughout the SQL. By using the :param syntax for all bound parameters, the TSQLQuery does properly parse the parameters each time, like it is supposed to do.

var
  qry: TSQLQuery;
begin
  qry := TSQLQuery.Create(nil);
  try
    qry.MaxBlobSize := -1;
    qry.SQLConnectin := AnExistingConnection;

    qry.CommandText := 'select field1 from table1 where fieldX = :valueX';
    qry.ParamByName('valueX').Value := 1;
    qry.Open;

    // ... use data ...

    qry.Close;
    qry.CommandText := 'select field2 from table2 where fieldY = :valueY';
    qry.ParamByName('valueY').Value := 2;
    qry.Open;
  finally
    FreeAndNil(qry);
  end;
end;

Answer 2

Use the TSQLQuery.SQL property instead of the TSQLQuery.CommandText property:

qry.SQL.Text := 'select field1 from table1 where fieldX = @valueX'; 
...
qry.SQL.Text := 'select field2 from table2 where fieldY = @valueY'; 

No need to call Params.Clear in between, the SQL property will handle that for you.