DAO.Recordset Error to Retrieve Decimal Value From MySQL Database

[abduhsuryadi]

Haloo...
I have access 2007 as Fronte End and MySQL database as Back End. Database name: myshop, table name : Sales and one of field name: Price. The type of the field is Decimal (19,4). The question is why these three code below get different result?

First code with OpenDatabase("",false,false,ConnString)

dim db as DAO.database
dim rst as DAO.recordset
set db=opendatabase("",false,false,"ODBC;Driver=MySQL ODBC 3.51 Driver;server=localhost;Database=myshop")
set rst=db.openrecordset("Sales")
debug.print rst!price
'Result : 3500.0000
debug.print rst!price + 1
'result : 35000001

Second code with currentdb

dim db as DAO.database
dim rst as DAO.recordset
set db=CurrentDb
set rst=db.openrecordset("Sales")
debug.print rst!price
'Result : 3500
debug.print rst!price + 1
'result : 3501

Third code with ADODB

dim db as New ADODB.Connection
dim rst as New ADODB.recordset
db.open "Driver=MySQL ODBC 3.51 Driver;server=localhost;Database=myshop"
rst.open "Sales",db
debug.print rst!price
'Result : 3500
debug.print rst!price + 1
'result : 3501

What is the problem in the first code? is there an option in connection string to avoid this?

 

[Uncle Gizmo]

I note that your post has not received a reply, this may be because no one has an answer for you. Just in case it's been missed, I have bumped it up the list...

 

[The_Doc_Man]

I don't know if this will confuse you more or help you understand the situation better. But your question intrigued me enough to at least do a little bit of web searching.

I can tell you what happened but not why. Your example that gives you 35000001 is looking at the value as a non-scaled number. For the dynamic DAO connection, that scaled integer datatype is not being correctly interpreted.

For the static DAO connection (because CurrentDB IS a DAO type), it can see that the number is scaled. For the ADO connection, it can see that the number is scaled. I have NO CLUE as to why the dynamic DAO cannot see that.

I think the better test is this: For the dynamic DAO connection case, try your debug.print test with rst!price + 1.0

Now, I can't tell you why DAO works one way and ADO works another way and DAO works differently between static and dynamic connection. But I suspect it has to do with what data type it thinks it is looking at when the sees your [Price] field. So try THIS experiment with each of your test cases:


debug.print rst!price.type

You might then have to use the Object Browser to translate the data type code numbers into actual data types. (Hint: in Object Browser, look at DataTypeEnum.) However, the more significant issue is whether all three show the same data type. I suspect they will not.

I found this item online that indicates there may be some issues with DECIMAL data type in Access, enough to recommend not using DECIMAL:

http://www.fmsinc.com/microsoftaccess/database-design/decimal_data_type/index.htm

I cannot comment on that article except to report it by reference and to state that the source, FMSINC, typically has good and knowledgeable articles. I have not used DECIMAL types in so many years that I can't tell you when I used them, but I know that I don't use them now.

Internally, it is POSSIBLE (but I can't confirm this because as usual, Microsoft obscures this detail) that a DECIMAL data type is 14 bytes of BCD, i.e. two BCD digits per byte, plus three bytes of control info, which makes sense because you specify the number of digits you want to keep (that can be less than 28) AND the number of decimal places involved in your scaling (which can be from 0 to the number of digits).

If DECIMAL really IS stored as BCD, then a different set of instructions in the hardware handles the number. Old INTEL 8008 hardware defined data types for either binary integers or BCD integers, and so far as I know, the data type has never faded away. I checked and the the latest generation of Intel CPUs - including I7 and IA64 - still supports that data type. They call them "packed integers" when dealing with the hardware instructions. So I guess that different rules apply to that class of scaled integer.