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: | |
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
[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 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.