How to pass a parameter to a query using dbExpress in Delphi

Question

I want to use a dbExpress TSQLQuery component. But i do not know how to write the SQL to add a parameter. I will give an example maybe it will be more clear what my problem is.

In a TADOQuery the following works:

SELECT*
FROM sometable
WHERE sometable.id = :value;

Now in the above example you pass the parameter to the query using the colon (:) before the parameter name. But when I try and do that with the TSQLQuery, I get the following error:

dbExpress driver does not support the TDBXTypes.UNKNOWN data type. Vendor Error Message.

Now if this is not the way that you pass a parameter in a TSQLQuery component, can someone please assist me. This is new territory for me.

Im using a Firebird database, and Im using Delphi XE2

Answer 1

To set the properties of a parameter you must use the Params property. From here you can access each paramer using a index or the name, to set the value of the parameter use one of the properties AsString, AsInteger an so on, depeding of the type of the field.

Check this sample

var
  LSQLQuery : TSQLQuery;
begin
  LSQLQuery:=TSQLQuery.Create(nil);
  try
    LSQLQuery.SQLConnection:=SQLConnection1;
    LSQLQuery.CommandText:='Select FIRST_NAME from EMPLOYEE Where EMP_NO=:Param1';
    LSQLQuery.Params.ParamByName('Param1').AsInteger:=2;//or using the index of the parameter LSQLQuery.Params[0].AsInteger:=2; 
    LSQLQuery.Open;//Execute the query
    ShowMessage(LSQLQuery.FieldByName('FIRST_NAME').AsString); //get the data
    LSQLQuery.Close;
  finally
    LSQLQuery.Free;
  end;
end;