Getting the "Arguments are of the wrong type..." exception just by assigning query text

Question

I have downloaded and installed MySQL Connector 5.1 x64 so I can use MySQL with Delphi. I can make connection with ODBC and do a connection from my Delphi environment and from MySQL Workbench.

But, when I build my Query at runtime, I get an error saying:

Project AAA.exe raised exception class EOleException with message 'Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another'. Process stopped. Use Step or Run to continue.

My code:

qDates := TADOQuery.Create(Component);
qDates.Connection := FConnection;
qDates.SQL.Text :=
  'select ' +
  '  * ' +
  'from ' +
  '  resulttable ' +
  'where ' +
  '  oid = :oid ' +
  '  and datedial >= :datebegin and datedial <= :dateend'; // <<-- Exception here

Details:
The exception happens right on the text assignment, before I have a chance to configure parameters.
If I comment out the where clause the assignment goes fine.
This is similar to Using parameters with ADO Query (mysql/MyConnector) but the difference is that I assign whole text at once and I get the exception before I have a chance to configure parameters.

The puzzling part - exact same code works fine on my other machine, but I can not figure out what is different.

Hence the question - what could cause the above exception outside of the Delphi code and MySQL server?

Answer 1

This seems to be a quirk with the MySQL ODBC provider.

If you assign the connection after setting the SQL text, then it will work. The reason why can be found here.

qDates := TADOQuery.Create(Component);
// do net yet assign TADOConnection to prevent roundtrip to ODBC provider
qDates.SQL.Text :=
  'select ' +
  '  * ' +
  'from ' +
  '  resulttable ' +
  'where ' +
  '  oid = :oid ' +
  '  and datedial >= :datebegin and datedial <= :dateend';
qDates.Connection := FConnection;

UPDATE

This QC entry explains the exact reason for this problem.

In short, the ADODB unit, patch this line from the RefreshFromOleDB procedure :

      Parameter.Attributes := dwFlags and $FFFFFFF0; { Mask out Input/Output flags }

To:

if dwFlags and $FFFFFFF0 <= adParamSigned + adParamNullable + adParamLong then
 Parameter.Attributes := dwFlags and $FFFFFFF0; { Mask out Input/Output flags }