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, Aug 23 '12 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, Aug 23 '12 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, Aug 23 '12 at 10:32
@TLama: I don't think it's neccssary to explicitly typecasting the values - delphi should be able to determine the correct dataype and it's pretty common also in quite proffesional code. Also, that doesn not explain why _q.Params[0].Value := AStartDateTime;_ doesn't work and _q.ParamByName('SomeDate').Value := AStartDateTime;_ is ok... — user1619275, Aug 23 '12 at 10:42
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, Aug 23 '12 at 10:59
Just out of curiousity, why are you using AnyDAC instead of Advantage's own database controls? The ADSQuery supports pretty full SQL, stored procedures, UDFs, and scripting, is aware of the ADS-specific functionality, and is optimized for use with ADS. They're also compatible with all VCL data-aware controls via the normal TDataSource. — Ken White, Aug 23 '12 at 11:10
i think it does explain. One should look what is that .Params property. In IBX it is TStringList. In BDE some array of records (according to XE2 docs) and in ADO something yet different. According to Data.DB.TParams docs one should compare **q.Params.Items[i]** with **q.Params.ParamByName('...')**, not with **q.ParamByName('...')** - those are methods of different objects and obviously may produce different results. In general one should look into what objects are returned by **q.ParamByName** and **q.Params.ParamByName** and maybe **q.Params.Items[i]** is yet another 3rd kind of beast. — Arioch 'The, Aug 23 '12 at 11:14
I've asked Dmitry from DA-Soft (they created AnyDac) to check out this question. — Jeroen Wiert Pluimers, Aug 23 '12 at 13:53
Note that posting English error messages gives you a higher chance of getting an answer. It is the number one reason that I develop/test/deploy both on English and target-language systems (English to get the untranslated English error messages, target-language (mostly Dutch or German) for the corner cases that you otherwise will miss. — Jeroen Wiert Pluimers, Aug 23 '12 at 13:56
What is your Delphi and Advantage versions ? Also, how the StartAm and EndeAm defined ? — da-soft, Aug 23 '12 at 16:18

TLama · Answer 1 · 2012-08-23T11:38:23.597

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 '12 at 11:38

answered Aug 23 '12 at 11:21

TLama

75,147
17
214
392

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.. – user1619275 Aug 23 '12 at 11:43
@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('...')** – Arioch 'The Aug 23 '12 at 11:57
3

@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. – Ken White Aug 23 '12 at 12:30
1

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. – Jeroen Wiert Pluimers Aug 23 '12 at 13:59
1

+1. Any way to avoid the overhead of using variants is better in my book. – Ken White Aug 23 '12 at 15:41

Assignment error when assign value to parameter of a parametized query

1 Answers1