Type mismatch for field 'FieldName', expecting: AutoInc actual: LongWord

Hi,

I get this error message with AnyDAC (FireDac) components.
To show my error, I reduce my mysql table to the minimum.

I have two MySQL table who contains many fields of type (string, integer, datetime etc...).

The first table named "ReportLog"  contain three fields:
  'ID'                     (INTEGER AutoIncrement)
  'RptDateTime'     (DATETIME)
  'fk_OperatorLog'  (INTEGER)

The second table named "OperatorLog"  contain two fields:
  'OperId'   (INTEGER AutoIncrement)
  'DKeyId'  (VARCHAR)

I put a TFDQuery (named qryLog) on a form and Set is 'SQL' propertie to:
'SELECT ID, RptDateTime, OperId, DKeyId FROM ReportLog LEFT JOIN OperatorLog ON fk_OperatorLog = OperId';

Then I open the fields editor and right click in and choiche 'Add Fields...' then a get all my MySql Table's fields in the fields editor list. The first one is the 'ID' field and it's of type TFDAutoIncField.
Until now every thing is perfectly normal.

Then I execute Query with my TFDQuery (named qryLog) but depending of what query I execute, I get the error message:
EDatabaseError Msg:qryLog: Type mismatch for field 'ID', expecting: AutoInc actual: LongWord.

Here example of a query with succes execution: 
SELECT ID, RptDateTime, OperId, DKeyId FROM ReportLog 
LEFT JOIN OperatorLog ON fk_OperatorLog = OPerId 
WHERE ((RptDateTime>="2013-01-01 00:00:00") AND (RptDateTime<="2013-01-01 23:59:59")) AND 
(DKeyId="0000000000000000") order by RptDateTime;

Here example of a query WHO GIVE ME THE ERROR: 
SELECT ID, RptDateTime, OperId, DKeyId FROM ReportLog 
LEFT JOIN OperatorLog ON fk_OperatorLog = OPerId 
WHERE ((RptDateTime>="2013-01-01 00:00:00") AND (RptDateTime<="2013-01-02 23:59:59")) AND 
(DKeyId="0000000000000000") order by RptDateTime;

The only difference of the two query is the ending date. The first one is (RptDateTime<="2013-01-01 23:59:59") and the second one is (RptDateTime<="2013-01-02 23:59:59").

I DID A SMALL PROJECT TO SHOW THIS ISSUE AND I CAN ZIP IT AND SEND IT TO SOMEONE.
(write to me at fzacharie@multisys.ca and I will send it).

At home, I have Delphi 6,7 2010 and XE5 installed then I did the test on all the version I Own.
On Delphi 6 and 7 I have no error, every things is perfect. :)
On delphi 2010 and XE5 I get the error message. :(

WITH THE SAME VERSION OF COMPONENT (ANYDAC at the time) ON DELPHI 6,7 I HAD NO ERROR AND ON DELPHI 2010 I GOT THE ERROR.

Some body Have a Idea ????
I try and try and try to understand but nope! I have no idea about what is going on.
My feeling is that something change somewhere between Delhi 7 and Delphi 2010 on database object VCL code in Delphi and the error been introduce.

Your help would be very apreciate!!!!
Thank in advance!
0
Francois
1/31/2014 3:42:38 AM
embarcadero.delphi.firedac 822 articles. 2 followers. Follow

5 Replies
5442 Views

Similar Articles

[PageSpeed] 53

Hello Francois

> I DID A SMALL PROJECT TO SHOW THIS ISSUE AND I CAN ZIP IT AND SEND IT TO SOMEONE.
> (write to me at fzacharie@multisys.ca and I will send it).

Thank you, but no need in this case. This is the issue known for me.
Probably even you reported it earlier here or in da-soft.com forum.

> EDatabaseError Msg:qryLog: Type mismatch for field 'ID', expecting: AutoInc actual: LongWord.
> The only difference of the two query is the ending date. The first one is (RptDateTime<="2013-01-01 23:59:59") and the second one is (RptDateTime<="2013-01-02 23:59:59").

This is rather an issue in MySQL, than in FireDAC. Depending on
the constant value in WHERE the MySQL client returns different
metadata for the result set columns. As a workaround you can
enforce the ID column data type. Try one of the options:
* use "ID+0 AS ID" instead of ID in the SELECT list;
* use "CAST(ID AS INTEGER) AS ID" instead of ID in the SELECT list.

-- 
With best regards,
Dmitry Arefiev / FireDAC Architect
FireDAC - Firebird, SQLite, MySQL, SQL Server, Oracle, PostgreSQL, 
DB2, SQL Anywhere, Access, Informix, ODBC high-speed data access lib
1
Dmitry
1/31/2014 5:03:06 AM
Hello Dmitry,

> Thank you, but no need in this case. This is the issue known for me.
> Probably even you reported it earlier here or in da-soft.com forum.
YES you right, I posted it one month ago on DA-Soft Support but I did not get any clear explanation of what happen and I did not get any satisfying solution.
 
> This is rather an issue in MySQL, than in FireDAC. Depending on
> the constant value in WHERE the MySQL client returns different
> metadata for the result set columns. 
If it's a issue in MySQL then WHY my test program work perfectly in Delphi 6 and 7  (no error message) with the SAME MySQL SERVER and when I run it on D2010 or XE5 I have the issue ???

>As a workaround you can
> enforce the ID column data type. Try one of the options:
> * use "ID+0 AS ID" instead of ID in the SELECT list;
> * use "CAST(ID AS INTEGER) AS ID" instead of ID in the SELECT list.
This is not completely true, if I do this (CAST(ID as UNSIGNED INTEGER) AS ID) then now I get the following error message.
*** EDatabaseError Msg:qryLog: Type mismatch for field 'ID', expecting: AutoInc actual: LargeInt. ***

To really make it work, you have too do this too: 
1. Delete the 'ID' TFDAutoIncField field from the  TFDQuery's field editor.., 
2. Create a new field by hand (right click and choice 'New Field...' in fields editor) than fill the name with 'ID',  set the type of the field to 'Largeint', set the Field Type to 'DATA' and click OK (Now you have a TLargentField rather then a TFDAutoIncField).
3. Change the 'Origin propertie' of this field to 'ID' and run program again.
THIS path Work!!! but I don't like it.... why you gonna say ???
1. I don't find this normal that I have to patch my source code like this, It's not clean and in one years I will forget why I Did that and I will get the same problem again for new table I will create (because normally I should rely on the fields editor's auto create field feature, and I should not have to cast my Fields in my query (for this case)).
2. If I pay for DB components (in the past AnyDAC (DA-Soft) and now FireDAC (Embarcadero)) I expect that the component's company take care of the SQL Server's issues (MySql or whatever else) and they do the workaround in them component code (probably just a type cast would be enough for this issue). I don't want loose my time with patching code or do workaround, I pay components for that, I should rely on the component's company to do the workaround,  my job is only to report issue when I find one. I alre
ady loose 3 days to isolate the problem and code a small program test to show the issue plus many more days to write on forum and wait for a real solution.

My questions are: 
1. WHY my test program work perfectly in Delphi 6 and 7  (no error message) with the SAME MySQL SERVER and when I run it on D2010 or XE5 I have the issue ???
2. Do you plan to make a workaround for this issue in your components ??? Or should I do the workaround in all my project code (and future project) work with Delphi XE5 ???

Thank you for your help, 
best regard,
François Zacharie

Edited by: Francois Zacharie on Jan 31, 2014 4:57 AM

Edited by: Francois Zacharie on Jan 31, 2014 5:00 AM
0
Francois
1/31/2014 1:01:55 PM
> If it's a issue in MySQL then WHY my test program work perfectly in Delphi 6 and 7  (no error message) with the SAME MySQL SERVER and when I run it on D2010 or XE5 I have the issue ???

I can only speculate. That will not help to resolve the issue ...
 
> 1. WHY my test program work perfectly in Delphi 6 and 7 (no error message) with the SAME MySQL SERVER and when I run it on D2010 or XE5 I have the issue ???

See above.

> 2. Do you plan to make a workaround for this issue in your components ??? 

Inside of the library there is no shances to get any kind of information,
that it is the time to use a workaround. IOW, no way inside of FireDAC.

> Or should I do the workaround in all my project code (and future project) work with Delphi XE5 ???

At moment you can fix that only on the application side, as I decribed earlier.

-- 
With best regards,
Dmitry Arefiev / FireDAC Architect
FireDAC - Firebird, SQLite, MySQL, SQL Server, Oracle, PostgreSQL, 
DB2, SQL Anywhere, Access, Informix, ODBC high-speed data access lib
-1
Dmitry
1/31/2014 1:54:57 PM
> > If it's a issue in MySQL then WHY my test program work perfectly in Delphi 6 and 7  (no error message) with the SAME MySQL SERVER and when I run it on D2010 or XE5 I have the issue ???
> I can only speculate. That will not help to resolve the issue ...
No, you don't have to speculate about anything, you can just test this issue in delphi 7 and compare the result with the test in XE5 and see why in D7 you don't get any error and why in XE5 you get the error.
after you can explain what is the difference between D7 and XE5 result in this forum and check how you can make a nice workaround in the component code to correct this issue. that all!

> > 2. Do you plan to make a workaround for this issue in your components ??? 
> Inside of the library there is no shances to get any kind of information,
> that it is the time to use a workaround. IOW, no way inside of FireDAC.
You don't have to go inside the mysql server code or libmysql.dll code to understand why the result is sometime a LargeInt rather then a AutoInc, you just have to find how you can do a nice workaround about this.
Can you just typecast the MySql integer result field into AutoInc field type when you get a Integer one from MySql for a TFDQuery's TFDAutoIncField one ???
After all, AutoInc and integer field are Integer in both case. 

(excuse my poor english)
Thank you!
Best regard,
François Zacharie

Edited by: Francois Zacharie on Jan 31, 2014 6:22 AM

Edited by: Francois Zacharie on Jan 31, 2014 6:24 AM

Edited by: Francois Zacharie on Jan 31, 2014 6:24 AM

Edited by: Francois Zacharie on Jan 31, 2014 6:26 AM
-1
Francois
1/31/2014 2:26:24 PM
Hi,

I'm gonna clause the ticket because I don't get anymore reply on it and I will mark it like answered because it is, you have a solution in my third reply (not a very nice one and not what I would  expect for solution too but it's work). I would expect that Dmitry will modify is component to do a work around about the MySQL issue but like he said (IOW, no way inside of FireDAC.) he will not do it .

But  Dmitry, I would really like to know what is the difference between D7 and XE5, and why it's work fine in D7 but not in XE5 with the same MySQL Server ?
PLEASE!!! I'm a programmer like you and I need to understand to be satisfy and let it go :( .
Just resumed on two or three lines .... please...

BTW, I tried the Devart's components and I don't get any error with the same small test program in D7 and XE5.
I'm using AnyDAC (FireDAC) component for long time now and I like them, they are solid and do a good job, I do program that run 24/24 for central and they never crash.
But now, slowly, I start to thing about giving a chance to Devart 's component, at least, try them in my program and see if I get the same solid result like AnyDac (FireDAC).


Thank you in advance (for your explanation about difference between D7 and XE5)!
Best regard,
François Zacharie
-1
Francois
2/4/2014 2:05:03 PM
Reply: