Bug #21398 ADO incorrectly reporting DECIMAL field type through MySQL ODBC
Submitted: 1 Aug 2006 17:58 Modified: 9 Aug 2006 15:34
Reporter: Brian Marks Email Updates:
Status: Not a Bug Impact on me:
None 
Category:Connector / ODBC Severity:S1 (Critical)
Version:3.51.12 OS:Windows (Windows XP)
Assigned to: CPU Architecture:Any
Tags: ADO, field, MySQL, ODBC, recordset, type

[1 Aug 2006 17:58] Brian Marks
Description:
ADO recordset shows an incorrect field type value for decimal fields returned from MySQL.  Other fields may be affected.  I didn't check 'em all.

How to repeat:
On the MySQL server do the following:

	create database mytest;
	create table mytest.mytest (test decimal(6,4));

The following code reproduces the error when running on Windows XP using VBS (the error was originally found in M$ Visual FoxPro 7.0 SP1):

	set oDBConn = CreateObject("ADODB.Connection")
	oDBConn.Open( _
		"Provider=MSDASQL.1;" & _
		"DRIVER={MySQL ODBC 3.51 Driver};" & _
		"SERVER=whatever;" & _
		"UID=******;PWD=******;")

  	set oRS 	= CreateObject("ADODB.recordset")

	with oRS
	  	.activeconnection 	= oDBConn
		.source 		= "`mytest`.`mytest`"
		.open
		
		for each oFld in oRS.Fields

			cFldName 	= oFld.Name
			nFldType 	= oFld.Type
			nFldSize 	= oFld.DefinedSize
			nFldScale	= oFld.NumericScale
			nFldPrecision 	= oFld.Precision

			WScript.echo cFldName, nFldType,nFldSize, nFldScale,nFldPrecision

		next

	end with

nFldType is set to the DataTypeEnum value of 6, which is associated with adCurrency.  adDecimal (14) is what I was expecting.  Is this an error in the MyODBC driver or ADO?  See <http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdcstdatatypee...> for a complete list of the ADO field types from Microsoft.

Suggested fix:
The 'show columns from {table}' command will show the correct field types from the actual MySQL table, although this is not a direct replacement for the ADO field type property.
[2 Aug 2006 19:36] MySQL Verification Team
Access displaying decimal data type

Attachment: bug21398.PNG (image/png, text), 15.24 KiB.

[2 Aug 2006 19:41] MySQL Verification Team
Thank you for the bug report. I created the below table:

mysql> show create table mydec\G
*************************** 1. row ***************************
       Table: mydec
Create Table: CREATE TABLE `mydec` (
  `id` int(11) NOT NULL,
  `xdec` decimal(10,2) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.03 sec)

mysql> select version();
+---------------------+
| version()           |
+---------------------+
| 5.0.23-community-nt |
+---------------------+
1 row in set (0.01 sec)

mysql>

Then I created a DSN with the ODBC manager and MyODBC linking the table
in Access the field type is decimal according with the create table
definition (see attached picture). Then I guess is an ADO issue.
[2 Aug 2006 21:15] Brian Marks
Access converted the decimal field type correctly using ODBC in my tests as well.  

It appears that the Access ODBC import/link wizard uses some other mechanism than ADO to access the field types, lengths and decimal places, otherwise I would expect it to have the same problem.  Does someone know how Access does this without using ADO?  I would like to use the same mechanism.
[8 Aug 2006 9:09] Tonci Grgin
Hello Brian.
There's subtle difference in the way VBS and VBA work resulting in errors in VBS while VBA works... I'm searching MS site for documentation on this for quite some time now but with no luck. So your vbs example has nothing to do with Access problem, at least I can't see the connection.
Can you provide me with following info:
    * MS Access versions
    * The MDAC version (if on Windows)
    * An ODBC trace of the operation of the driver when it is malfunctioning 
    * OPTION= values from your connection string, in optA+optB+... form
    * Any other info you deem relevant to help me reproduce reported behavior
[8 Aug 2006 12:51] Brian Marks
Thanks for your reply.  Here's the information that you requested:

Client:
Windows XP SP2
Microsoft Office Access 2003 (11.6566.8028) SP2
MDAC 2.8 SP1
MySQL ODBC 3.51 Driver

Server:
MySQL 4.1.1

Connection options: None.

Here's the VB script to reproduce the error.  You should be able to copy the code snippet directly into a VBS file on any windows 2000/XP machine with the MySQL ODBC driver installed and run it, once the connection options are adjusted for your specific server.  The value of 'nFldType' is 6, although I was expecting a return value of 14.

<-Code Begins->

set oDBConn = CreateObject("ADODB.Connection")
oDBConn.Open( _
	"Provider=MSDASQL.1;" & _
	"DRIVER={MySQL ODBC 3.51 Driver};" & _
	"SERVER=server;" & _
	"UID=;PWD=;")

oDBConn.Execute("drop database if exists mytest;")
oDBConn.Execute("create database mytest;")
oDBConn.Execute("CREATE TABLE `mytest`.`mytest` (`test` decimal(6,4) default NULL) TYPE=MyISAM DEFAULT CHARSET=latin1;")

set oRS 	= CreateObject("ADODB.recordset")

with oRS
  	.activeconnection 	= oDBConn
	.source 		= "`mytest`.`mytest`"
	.open
	
	for each oFld in oRS.Fields

		cFldName 	= oFld.Name
		nFldType 	= oFld.Type
		nFldSize 	= oFld.DefinedSize
		nFldScale	= oFld.NumericScale
		nFldPrecision 	= oFld.Precision

		WScript.echo _
			cFldName & chr(10) & _
			" Type: " & nFldType & chr(10) & _
			" Size: " & nFldSize & chr(10) & _	
			" Scale: " & nFldScale & chr(10) & _
			" Precision: " & nFldPrecision

	next

end with

<-Code Ends->
[8 Aug 2006 14:12] Tonci Grgin
Brian, thanks for info provided. So, this is VBS report, nothing to do with MS Access... I think I had a report similar to this one so I'll search the database first.
[8 Aug 2006 15:24] Tonci Grgin
C++ test case

Attachment: bug21398.cpp (text/x-c++), 4.90 KiB.

[8 Aug 2006 15:28] Tonci Grgin
Output from attached test case using table structure provided by Miguel:
Connecting to myodbc1
COL TYPE -> 3
COL LENGTH -> 10
COL Scale -> 4
--
/* SQL data type codes */
#define	SQL_UNKNOWN_TYPE    0
#define SQL_CHAR            1
#define SQL_NUMERIC         2
#define SQL_DECIMAL         3
--
As you can see, MyODBC is functioning correctly in standalone test. I suppose there's something wrong with VBS/ADO.
[8 Aug 2006 15:58] Tonci Grgin
VB script

Attachment: bug21398.vbs (application/octet-stream, text), 1.62 KiB.

[8 Aug 2006 15:58] Tonci Grgin
VB script result

Attachment: 21398.jpg (image/jpeg, text), 35.56 KiB.

[8 Aug 2006 16:05] Tonci Grgin
Brian, I was unable to repeat reported behavior on
  XP Pro SP2
  myodbc3.dll ver.3.51.12.0 (1,552,384 bytes)
  MySQL server 5.0.25BK on Suse 10 host
as you can see from attached script and image of result... 
C test case alone was enough to declare this report as "Not a bug".
[8 Aug 2006 17:34] Brian Marks
Output from script on user's machine

Attachment: mysqlerr.JPG (image/pjpeg, text), 7.76 KiB.

[9 Aug 2006 9:10] Tonci Grgin
Brian, thanks for your log file! I believe I see the reason for this behavior. Making new test case.
[9 Aug 2006 13:47] Tonci Grgin
Brian, I modified test cases to use unicode functions (W) like shown in your log and still can't repeat reported behavior. Notice my SQL.log is the same as yours (I used DECIMAL(10,4) so we are actually different on row SQLULEN * but that's not important):
SQL.log
wscript bug2139 9c4-158	ENTER SQLDescribeColW 
		HSTMT               01A72240
		UWORD                        1 
		WCHAR *             0x00000000 
		SWORD                        0 
		SWORD *             0x00000000
		SWORD *             0x0013E1EC
		SQLULEN *           0x0013E1FC
		SWORD *             0x0013E1F8
		SWORD *             0x0013E1DC

wscript bug2139 9c4-158	EXIT  SQLDescribeColW  with return code 1 (SQL_SUCCESS_WITH_INFO)
		HSTMT               01A72240
		UWORD                        1 
		WCHAR *             0x00000000 
		SWORD                        0 
		SWORD *             0x00000000
		SWORD *             0x0013E1EC (3)
		SQLULEN *           0x0013E1FC (10)
		SWORD *             0x0013E1F8 (4)
		SWORD *             0x0013E1DC (1)
MyODBC log:
>SQLDescribeCol
| info: col: 1 type: 3  precision: 10  decimals: 4
| exit: SQL_SUCCESS_WITH_INFO
<SQLDescribeCol

MyODBC 3.51.xx does not support unicode so original command SQLDescribeColW was replaced with SQLDescribeCol producing correct result (Type 3= SQLDecimal).

From what I see in your SQL log you are getting correct info back but your scripting host missinterprets it. Please check myodbc3.dll size (it should be 1,552,384 bytes) and version again. I'll upload files with versions from my test machine.
[9 Aug 2006 13:48] Tonci Grgin
Files with rellevant SW versions

Attachment: MDACfiles.zip (application/x-zip-compressed, text), 3.43 KiB.

[9 Aug 2006 13:51] Tonci Grgin
New C++ test case using unicode function SQLDescribeColW

Attachment: bug21398.cpp (text/x-c++), 5.06 KiB.

[9 Aug 2006 15:34] Tonci Grgin
Brian, you can download MDAC utility from 
http://www.microsoft.com/downloads/info.aspx?na=46&p=9&SrcDisplayLang=en&SrcCategoryId=&Sr...
MYODBCDriver5.dll is alpha version of MyODBC 5, not released yet. It has no consequence on this test. MyODBC log is tagged with version used:
start: Driver name: Connector/ODBC  Version: 3.51.12
>my_malloc
| my: size: 2  my_flags: 0
...
so there could be no mistake.

We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.

Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/

Thank you for your interest in MySQL.

Explanation:
It is not our job to check SW versions from your user's machine. Miguel and I did tests on two separate boxes using same VB script and got correct results. I've established that MyODBC returned exactly the same result as you posted (SQL.log) on both test machines. Only difference we have is comming from ADO interpreting result of SQLDescribeColW.