Is there an error using "Prepare" on UniQuery?

[Moehre]

Hi !

I have a problem using PREPARE on SQLServer:

I set the following sql statement:

qry2.Close;
qry2.SQL.Text := 'SELECT C.NAME,I.COLUMN_ID';
qry2.SQL.Add('FROM SYS.COLUMNS C,SYS.INDEX_COLUMNS I');
qry2.SQL.Add('WHERE C.OBJECT_ID = ' + object_id);
qry2.SQL.Add('AND C.COLUMN_ID = I.COLUMN_ID');
qry2.SQL.Add('AND I.OBJECT_ID = ' + object_id);
qry2.SQL.Add('AND I.INDEX_ID = :idx');
qry2.SQL.Add('ORDER BY 2');
try
qry2.Prepare;
except
on E: Exception do
begin
GetSQLError(qry2,E.Message);
exit;
end;
end;

Then I read every index for some table and set the index_id:

qry2.Close;
qry2.Params[0].AsString := qry1.FieldByName('INDEX_ID').AsString;
try
qry2.Open;
except
on E: Exception do
begin
GetSQLError(qry2,E.Message);
exit;
end;
end;

This will alway result in 0 records! If I don't use the param and write the index id directly into the sql, the result is ok (2 columns in this case)

Using Prepare and params with Oracle and MySQL seems to work; only with SQLServer I get this problem. Is there anything I'm doing wrong or do I have to set some special properties for SQL Server to use PREPARE / Params feature?

Thx
Detlev

[Dimon]

In order to solve the problem you should specify parameters data type before calling the Prepare method.

Code: Select all

MSQuery1.ParamByName('p1').ParamType := ptInput; 
MSQuery1.ParamByName('p1').DataType := ftInteger; 
MSQuery1.Prepare; 
MSQuery1.ParamByName('p1').Value := 1; 
MSQuery1.Execute;