Assignment error when assign value to parameter of a parametized query

Question

I'm using Delphi XE2 with AnyDac Components and Advantage Database 10. In my code I'm using parametrized querys like this:

q.SQL.Text := 'SELECT * FROM Table1 ' +
  'LEFT JOIN Table2 ON (Table1.ID = Table1 .ID_Table2) ' +
  'WHERE ' +
  ':0 BETWEEN Table1.StartAm AND Table1.EndeAm ' +
  'AND Table2 = :1';
q.Params[0].Value := AStartDateTime;
q.Params[1].Value := AIDRessourcenGruppe;
q.Open;

this ends up in an exception:

Exception der Klasse EADSNativeException mit der Meldung '[AnyDAC][Phys][ADS] Error 7200: AQE Error: State = 22018;
NativeError = 2112; [iAnywhere Solutions][Advantage SQL Engine]Assignment error' aufgetreten.

of course AStartDateTime is a valid delphi TDateTime value, AIDRessourcenGruppe is a integer value.

interestingly, these two variants work:

q.SQL.Text := 'SELECT * FROM Table1 ' +
  'LEFT JOIN Table2 ON (Table1.ID = Table1 .ID_Table2) ' +
  'WHERE ' +
  ':0 BETWEEN Table1.StartAm AND Table1.EndeAm ' +
  'AND Table2 = :1';
q.Params[0].AsDateTime:= AStartDateTime;
q.Params[1].AsInteger:= AIDRessourcenGruppe;
q.Open;

-

q.SQL.Text := 'SELECT * FROM Table1 ' +
  'LEFT JOIN Table2 ON (Table1.ID = Table1 .ID_Table2) ' +
  'WHERE ' +
  ':SomeDate BETWEEN Table1.StartAm AND Table1.EndeAm ' +
  'AND Table2 = :ID_PT_Ressourcengruppe';
q.ParamByName('SomeDate').Value := AStartDateTime;
q.ParamByName('ID_PT_Ressourcengruppe').Value := AIDRessourcenGruppe;
q.Open;

Do I miss something? Thanks for any help!

Answer 1

Answer - just a guess:

I would say the indexed write access to Value property of the parameters doesn't determine data type of parameters whilst the named access does. And if that's correct, then you're in trouble because you're passing all the values through Variant type which must converted to a proper value format before the query is performed. But all of that is just my guess - I don't know AnyDAC at all!

AsType value access:

I'm posting this just because I don't like Value access to parameters to be called professional :-)
It's better to use AsType typecast at least because:

• it is faster because you directly say what type you're passing to a certain parameter, thus the query parameter engine doesn't need to determine this, and in comparison with Value access, it doesn't need to convert Variant type

• it's safer for you, because you can't pass e.g. string value to AsDateTime accessed parameter, so you have an extra protection against parameter mismatch

What I commend in your example, is the use of indexed access to parameters, instead of commonly used named which needs to search the parameter list before the access and which is slower.