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!

So why not to use the AsType casting ? It's better readable and you can be sure the internal mechanism of query parameter builder will handle the values properly. I don't know AnyDAC, but if they take this from Delphi, then the Value is of type Variant what doesn't bring you nothing safe in my view... — TLama
– TLama, Commented Aug 23, 2012 at 10:15
+1 on effort but where is the q coming from? Your last variant uses dm1.qCustom, your other two use q. How is q declared? Can you show us the declaration of both parameters. When you say it is a valid datetime, for all we know, AStartDateTime is declared as string with '01-01-2012' as value. Why the change from :0 as parameter to :SomeDate? — Lieven Keersmaekers
– Lieven Keersmaekers, Commented Aug 23, 2012 at 10:16
sorry for that dm1.qCustom - I replaced it with 'q' just to shorten the code-snippet and forgot some...AStartDate is declared as TDateTime, not as string. I've changed :0 to :SomeDate just to show the use of named parameters. — user1619275
– user1619275, Commented Aug 23, 2012 at 10:32
Yeah, it doesn't explain why. Anyway, Delphi is not responsible to determine the right data type and doesn't know how to translate that datetime stamp passed through the Variant type to the query at all. The same probably happens also to indexed access to parameters, because, I think, it doesn't internally determine the data type of the parameters, whilst the named access does. And, I can't see anything professional using Value in this case, using indexed way yes (because it's faster), but using direct AsType typecast before Variant always wins for me (you can't mismatch parameters). — TLama
– TLama, Commented Aug 23, 2012 at 10:59

TLama · Accepted Answer · 2012-08-23 11:38:23Z

3

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.

edited Aug 23, 2012 at 11:38

answered Aug 23, 2012 at 11:21

TLama

76.9k17 gold badges226 silver badges418 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user1619275 Over a year ago

Hey TLama, thanks for that answer. I've seen the access over the .value property often, in - for example - code from devExpress and other components. So my thought was it must be safe to use ;) Back to the problem: It seems there's no real reason why it doesn't work, so it would be probalby better to contact AnyDAC support - I want to know whats wrong there..

Arioch 'The Over a year ago

@user1619275 there is a reason. As i wrote before - you use methods of DIFFERENT objects. Why do you insist that DIFFERENT objects worked eexactly the same ? compare q.Params.Items[i] with q.Params.ParamByName('...'), not with q.ParamByName('...')

Ken White Over a year ago

@Arioch'The: Please stop repeating that, because it's wrong. Open the IDE, wire up a database connection, and test it for yourself. Also, it's not necessary for you to start a big discussion over every single post here - this is not a chat room.

Jeroen Wiert Pluimers Over a year ago

This would be my first guess. Variants often are "evil" as they do behind-the-scene conversions by merely guessing what the datatype is. Since DateTime often is represented as a floating point value, that guess can easily go wrong here.

Ken White Over a year ago

+1. Any way to avoid the overhead of using variants is better in my book.

Collectives™ on Stack Overflow

Assignment error when assign value to parameter of a parametized query

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related