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
3789 Views

Similar Articles

[PageSpeed] 44

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:

Similar Artilces:

Type mismatch for field 'xxx', expecting: WideString actual: String
Development Enviornment Delphi XE2 oci.dll 11.2.0.1 sqlncli10.dll 2007.100.2531.0 MS SQL Server 2000, 2005, 2008. Collated Arabic_CI_AS Oracle 11g NLS Characterset WE8ISO8859P1 Application using datasnap/dbexpress driver Windows XP Problem Area We have an application that we are converting to runs on both Oracle and MS SQL Server. The Oracle driver brings back Varchar2 as TWideStringField. The SQL Server driver brings back Varchars as TStringField. This becomes a problem for our persistent fields. The error we receive is "Type mismatch for field 'xxx', ex...

Cast from type ''''DBNull'''' to type ''''String'''' is not valid.
How do I avoid getting this error: Cast from type ''''DBNull'''' to type ''''String'''' is not valid. I am trying to get values from the database into a form for updating using a Datareader, SQL is set to allow nulls for certain fields as they are not required but the only way I can get the form to display is by adding a space in SQL. Is there another way around this? Check if it is equal to DBNull.Value first and if it is, don't do the cast.Stanley Tan theSpoke Blog Where in the code does it need to go? I am using the following and it's the profile that isn't always requi...

Cast from type 'Field' to type 'String' is not valid
I am getting the error msg on this code...Response.Write(objRSCount("iCount") & " records for " & frmDispOption & " '" & frmText & "'. They are listed below:")I have tried converting the frmDispOption and frmText variables to strings using CStr after getting the values from the DB but still get the same msg.  Can someone please help me out, thanks.David. What is "objRSCount"? ie what type is it? You also need to make sure that objRSCount("iCount") is string.ThanksMark post(s) as "Answer" that helped youElectronic ScrewWebsite||Blog||Dub@i.net Hi, i f...

Cannot convert type 'type' to 'type'
I am learning all of this, so I am still fresh, so please bear with me :)I have a page that has some txtboxes in it, simple registration form, and I have a button that will save those txt boxes to the db. I am using:- I am using Visual Web Developer 2008 Express Edition- C# When I try to compile the project I get error: Cannot convert type 'System.Web.UI.WebControls' to 'String' This is content of my web.config    <profile> <properties> <add name="FirstName"/> <add name="LasttName"/>...

Type Mismatch field 'EventDate' expecting: Date actial:Widestring
Hi In an SQL database I have a field with a data type of ‘Date’. In FireDAC I have this field also setup as a TDateField. I have used LiveBindings to connect a TEdit to the ‘EventDate’ data field, which is where I imagine the problem comes from. What I can’t understand is why it works fine on my local copy of SQL and the ‘live’ SQL Server from my own laptop (which is using Delphi XE5) but gives an error message when run on a clients PC. On a Users PC I get an error message ‘Exception eDatabaseError in module …. At ….., fdqCalendar: Type mismatch for field ‘EventDate’, expecting: Date actua...

''''''''''''''''''''
Name: haznen Email: haznenatyahoodotcom Product: Gran Paradiso Alpha 8 Summary: '''''''''''''''''''' Comments: '''''''''''''''''''''''''''''''''''' Browser Details: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9a1) Gecko/20061204 UGES/1.7.2.0 GranParadiso/3.0a1 From URL: http://www.mozilla.org/projects/granparadiso/ Note to readers: Hendrix gives...

table_info('','','','%') cannot return any types
I was recently confirming table_info special cases and discovered the case for getting table_types cannot work. table_info('','','','%') should return a list of table types but it returns a list of empty strings instead: my @types = $h->tables('', '', '', '%'); print "all types:\n", join("xxx\n", @types), "\n"; # should output something like: # "dbo" # "INFORMATION_SCHEMA" # "sys" # and actually outputs: xxx xxx It seems to be down to the following ...

'''''
Name: mario Email: ramar17atfastwebnetdotit Product: Gran Paradiso Alpha 2 Summary: ''''' Comments: K: Browser Details: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9a2) Gecko/20070206 GranParadiso/3.0a2 ...

Unable to cast object of type 'TYPE' to type 'TYPE'.
I've got a custom class and collection...I want to use them to databind a Grid, and persist the state using a Session variable.  However I keep getting the above error **Sometimes...There will be times when it works fine, and times when it totally craps out.System.InvalidCastException: Unable to cast object of type 'ApprovalCycleCollection' to type 'ApprovalCycleCollection'. This should work fine, no?  I want a property to use in all my foreach loops   public ApprovalCycleCollection NewApprovalCycle { get { if (Se...

Operator '=' is not defined for type 'Char' and type 'Boolean'.
Hello, In my application when i click the button i'm getting this " Operator '=' is not defined for type 'Char' and type 'Boolean'. " But when i test it locally there is no problem at all!!!! Please tell me why this error is occuring...Thanks SwapnaPlease click "mark as answer" if this post helped you. swapnasamsonvarkey: Hello, In my application when i click the button i'm getting this " Operator '=' is not defined for type 'Char' and type 'Boolean'. " But when i test it lo...

Operator '*' is not defined for type 'DBNull' and type 'Decimal'.
 I am getting an error from the following line   <asp:Label ID="Label1" runat="server" Text='<%# String.Format("{0:C}", Eval("ProductQty") * Eval("Price"))   %>' ></asp:Label> I think I may need to convert/define the Variables but not quite sure how to do it. Any help would be great. it's not a matter of converting...its the fact that the value from the DB is Null... You'll have to wrap an IF check around it or change your query to return 0 instead of Null. If the field ...

Operator '>' is not defined for type 'DBNull' and type 'Integer'.
this is my code I don't get why I'm getting this error SubModel is a number :   Example : 823 If Ds.Tables(0).Rows(0).Item("SubModel") > 0 Then Dim crst As New DataSet SqlStr = "SELECT SubModelName FROM SubModel,AcesData WHERE AcesData.SubModel = SubModel.SubModelID and (SubModelID = ?)" Dim da2 As New Data.OleDb.OleDbDataAdapter(SqlStr, conn) da2.SelectCommand.Parameters.Add("?", Data.OleDb.OleDbType.VarChar).Value = Ds.Tables(0).Rows(0).Item("SubModel")...

'FROM' and 'SUBJECT' fields
Hello, Last week I upgraded to SeaMonkey 2.29. (Fedora distribution 12) The 'From' and 'Subject' fields are now mixed up (inaccurate) and incorrect on many emails as displayed in the thread pane. They don't match what the email itself says. Thanks. ...

E2094 'operator+' not implemented in type 'XXX' for arguments of type 'int'
Okay I have a problem; I am trying to extend a string class that I wrote for a friend so I can use it another project I am working on. I have an error that makes absolutely no sense. Here is the code for the copy constructor: String(const String &str) // for copying into a new string object { Size = str.Size; Data = new char[Size+1]; for (int i = 0; i<Size; i++) Data[i] = str[i]; // recieving the following error on the above line: [BCC32 Error] Mystring.h(33): // E2094 'operator+' not implemented in type 'String' for arguments of typ...

Web resources about - Type mismatch for field 'FieldName', expecting: AutoInc actual: LongWord - embarcadero.delphi.firedac

Resources last updated: 2/12/2016 9:35:38 PM