How to pass parameters to an ADOQuery object?

Question

I am using ADOQuery in Delphi 7 and Oracle. I am getting error while passing parameters to ADOQuery. I have used following line. Please help me to identify error.

ADOQuery.Sql.text:= 'select * from temp_table '+
        'where column1 in (select column from table2 where id=:id) and id=:id';
ADOQuery.Parameters.ParamByValue('id').value= 'abc';
ADOQuery.open;

when I open the query i will get following error:

Parameter object is improperly defined. Inconsistent or incomplete information is provided.

Answer 1

We have the same problem, we ended "masking" the class TParameters like this:

Declaration:

TMyParameter = class(TParameter)
private
  function GetAsValue: variant;
  Procedure SetAsValue(const Value: variant);
public
  property Value: variant read GetAsValue write SetAsValue;
end;

Implementation:

procedure TMyParameter.SetAsValue(const Value: variant);
var
  iPar: Integer;

begin
  for iPar:= 0 to Collection.Count - 1 do
    if (Name = TParameter(Collection.Items[iPar]).Name) then
      TParameter(Collection.Items[iPar]).Value:= Value;
end;

function TMyParameter.GetAsValue: variant;
begin
  Result:= inherited Value;
end;

And how to use:

TMyParameter(ADOQuery.Parameters.ParamByName('id')).AsValue:= 'abc';

I hope it helps.

Answer 2

You need to distinguish between the two id;s:

ADOQuery.Sql.text:= 'select * from temp_table a where column1 in (select column from table2 b where b.id=:id) and a.id=:id'; 
ADOQuery.Parameters.ParamByValue('id').value= 'abc'; 
ADOQuery.open;

Answer 3

for i:=0 to ADOQuery.Parameters.Count-1 do
begin
  if ADOQuery.Parameters.Items[i].Name = 'id' then
    ADOQuery.Parameters.Items[i].Value := 'abc';
end;

Answer 4

In the SQL code declare a variable of the necessary type, assign to that variable the parameter; you will be able to use that variable as many times as necessary:

ADOQuery.Sql.text:= 'declare @param varchar(50); set @param = :id; '+
'select * from temp_table '+
'where column1 in (select column from table2 where id=@param) and id=@param';

ADOQuery.Parameters.ParamByValue('id').value= 'abc';
ADOQuery.open;

Regards