TADOQuery sometimes ignoring parameters

Hi all

I'm using TADOQuery and some of the SQL queries I make have parameters. Those objects are either connected to MSSQL or Oracle server.
For an unkown reason, some of these parameterized queries do not get handled properly and end up ignoring the parameters.
The SQL queries on which this happens are not the same for MSSQL or Oracle, but it happens on both. Here is the code used:

function getQuery(sSQLCommand: string;
                  Parameters : array of string): TADOQuery;
var
  qrySQLOrder       : TADOQuery;
  iCurrentParameter : Integer;
begin
  result := nil;
  qrySQLOrder := nil;
  try
    qrySQLOrder := TADOQuery.Create(nil);
    qrySQLOrder.Connection := SharedConnection;
    qrySQLOrder.SQL.Text := sSQLCommand;
    qrySQLOrder.Parameters.Refresh;
    if Length(Parameters) <> qrySQLOrder.Parameters.Count then
    begin
      log('Unable to execute : ' + sSQLCommand);
      log('Expected parameters count: ' +
            IntToStr(qrySQLOrder.Parameters.Count));
      log('Received parameters count: ' +
            IntToStr(Length(Parameters)));
      Result := nil;
      exit;
    end;

    for iCurrentParameter := 0 to Pred(qrySQLOrder.Parameters.Count) do
    begin
      qrySQLOrder.Parameters[iCurrentParameter].Value :=
              Parameters[iCurrentParameter];
    end;

    if not qrySQLOrder.Prepared then
      qrySQLOrder.Prepared := True;
    qrySQLOrder.Open;
    result := qrySQLOrder;
  except
    on E : Exception do
    begin
      log('SQL Error : [' + E.Message + ']');
      qrySQLOrder.Free;
    end;
  end;
end;

This is done this way so that we may have a cache of parameterized queries to be preparsed by the server at the beginning and reused later on. When the problem occurs, the expected parameters count is 0 and of course the received parameters count is greater than 0.
Here is an example of a non functionning query on SQL Server

      SELECT CODNAT
      FROM PNATSUJ
      WHERE CODNAT IN
        (SELECT CODNAT FROM PNATLST WHERE PNATLST.CODTEC = '.EZX')
      AND TYPSUJ =?

and one on Oracle:

      SELECT * FROM PEVTCAL
      WHERE SRVDST = :SrvDst

Note that changing from named parameters to question marks and vice versa has no impact on the result.

It also happens that the parameters count is the correct one, but the query returns 0 rows. If I go to TOAD (for Oracle) and run the same query with the parameters replaced, I get 6 rows.
I'm very lost here, any help would be appreciated.
This happens on Windows XP SP1, MDAC 2.8 connecting to Oracle 8i and MSSQL Server 7

Cheers
Olivier Sannier
.

Relevant Pages