| Bug #29633 | DESCRIBE table only returns first field using ADO and Excel CopyFromRecordset | ||
|---|---|---|---|
| Submitted: | 8 Jul 2007 22:37 | Modified: | 15 Jul 2007 12:35 |
| Reporter: | John Wilson | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / ODBC | Severity: | S2 (Serious) |
| Version: | 5.00.11 | OS: | Windows (XP SP2) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | ADO, DESCRIBE, recordset, SHOW CREATE | ||
[12 Jul 2007 19:38]
Tonci Grgin
Hi John and thanks for your report. I don't quite get it so I'll propose a thing or two: - Please concentrate on MyODBC 3.51.16 - Please provide ODBC trace log - Please give me instructions on how (and where) to put VB code in Excel - Have you considered http://msdn2.microsoft.com/en-us/library/aa223845(office.11).aspx? - How I see it, the code should be like this: Const DSN = "Driver={MySQL ODBC 3.51 Driver};Uid=root;Pwd=;Server=localhost;Database=test;OPTION=1 + 2 + 8 + 16384" Const adOpenForwardOnly = 0 Const adOpenKeyset = 1 Const adOpenDynamic = 2 Const adOpenStatic = 3 Const adOpenUnspecified = -1 Const adUseNone = 1 Const adUseServer = 2 Const adUseClient = 3 Const adLockReadOnly = 1 Const adLockPessimistic = 2 Const adLockOptimistic = 3 '---- ParameterDirectionEnum Values ---- Const adParamUnknown = &H0 Const adParamInput = &H1 Const adParamOutput = &H2 Const adParamInputOutput = &H3 Const adParamReturnValue = &H4 '---- CommandTypeEnum Values ---- Const adCmdUnknown = &H8 Const adCmdText = &H1 Const adCmdTable = &H2 Const adCmdStoredProc = &H4 Const adCmdFile = &H100 Const adCmdTableDirect = &H200 Const adInteger = 3 Const adDate = 7 Const adVarChar = 200 Dim cnxDatabase Dim strSQL Dim iCols ' connecting database Set cnxDatabase = CreateObject("ADODB.Connection") cnxDatabase.Open (DSN) 'WScript.Echo "Connected" cnxDatabase.Execute ("USE test") cnxDatabase.Execute ("DROP TABLE IF EXISTS bug29633") cnxDatabase.Execute ("CREATE TABLE `bug29633` (`f1` char(10) , `f2` char(10),`f3` decimal(3,1),`f4` decimal(3,1),`f5` char(10))") ' querying data strSQL = "DESCRIBE `bug29633`" Dim rs Set rs = CreateObject("ADODB.Recordset") With rs .ActiveConnection = cnxDatabase .CursorLocation = adUseClient .LockType = adLockOptimistic .CursorType = adOpenDynamic .Properties("Update Criteria").Value = 0 .Open (strSQL) End With For iCols = 0 To rs.Fields.Count - 1 ws.Cells(1, iCols + 1).Value = rs.Fields(iCols).Name Next ws.Range(ws.Cells(1, 1), _ ws.Cells(1, rs.Fields.Count)).Font.Bold = True ws.Range("A2").CopyFromRecordset rs rs.Close cnxDatabase.Close Set rs = Nothing Set cnxDatabase = Nothing From what appears on the first look, Excel has problems reading the BLOB fields thus breaking on first one (Field 2: `Type`): C:\mysql507\bin>mysql -uroot -T test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.0.44-max-nt-log Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> DESCRIBE bug29633; Field 1: `Field` Catalog: `def` Database: `` Table: `COLUMNS` Org_table: `` Type: VAR_STRING Collation: latin1_swedish_ci (8) Length: 64 Max_length: 2 Decimals: 0 Flags: NOT_NULL Field 2: `Type` Catalog: `def` Database: `` Table: `COLUMNS` Org_table: `` Type: BLOB Collation: latin1_swedish_ci (8) Length: 196605 Max_length: 12 Decimals: 0 Flags: NOT_NULL BLOB Field 3: `Null` Catalog: `def` Database: `` Table: `COLUMNS` Org_table: `` Type: VAR_STRING Collation: latin1_swedish_ci (8) Length: 3 Max_length: 3 Decimals: 0 Flags: NOT_NULL Field 4: `Key` Catalog: `def` Database: `` Table: `COLUMNS` Org_table: `` Type: VAR_STRING Collation: latin1_swedish_ci (8) Length: 3 Max_length: 0 Decimals: 0 Flags: NOT_NULL Field 5: `Default` Catalog: `def` Database: `` Table: `COLUMNS` Org_table: `` Type: BLOB Collation: latin1_swedish_ci (8) Length: 196605 Max_length: 0 Decimals: 0 Flags: BLOB Field 6: `Extra` Catalog: `def` Database: `` Table: `COLUMNS` Org_table: `` Type: VAR_STRING Collation: latin1_swedish_ci (8) Length: 20 Max_length: 0 Decimals: 0 Flags: NOT_NULL +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | f1 | char(10) | YES | | NULL | | | f2 | char(10) | YES | | NULL | | | f3 | decimal(3,1) | YES | | NULL | | | f4 | decimal(3,1) | YES | | NULL | | | f5 | char(10) | YES | | NULL | | +-------+--------------+------+-----+---------+-------+ 5 rows in set (0.02 sec) mysql>
[13 Jul 2007 18:19]
John Wilson
> From what appears on the first look, Excel has problems reading the BLOB > fields thus breaking on first one (Field 2: `Type`): > Ah yes, I was aware that BLOB fields gave unexpected results in general but never looked into why, and hadn't realised DESCRIBE would involve BLOB fields until you pointed this out. Anyway, I used your suggested code to see if it produced the same result, and to my surprise your code returned the correct result set! :-) [BTW, your code was missing "Set ws = ActiveSheet"] The line which made all the difference was: rs.CursorLocation = adUseClient It turns out that the default for CursorLocation is adUseServer, and that BLOB fields "will fail or return abnormal results when used with a server-side cursor." http://dev.mysql.com/tech-resources/articles/vb-cursors-and-locks.html provides a very good overview of ADO cursor usage with MySQL. "...certain functionality, such as the RecordCount property of a Recordset and the GetChunk and Appendchunk function for handling BLOB data, will fail or return abnormal results when used with a server-side cursor." So, I can confirm that: DESCRIBE test_table; SHOW CREATE TABLE test_table; both work fine with the client side cursor setting. BTW, why does the adUseServer server side cursor setting not handle BLOB fields correctly? Will this be fixed in the future or is there something fundamental about the design of the ODBC stack that means a server side cursor (i.e. ODBC side cursor) cannot be made to handle data which contains BLOB fields correctly? BTW, even with adUseServer, ODBC driver 3.51.06 against a 4.1.6 database never had any problems with the queries: DESCRIBE test_table; SHOW CREATE TABLE test_table; Also, as a matter of interest: DESCRIBE test_table; works fine with 3.51.06 and 5.0.42 database (with adUseServer) although there are still other BLOB field issues with 3.51.06 (especially when used with a 5.0.42 database). > Please give me instructions on how (and where) to put VB code in Excel > Tools, Macro, Visual Basic Editor Thank you for the sample code without which I would never have realised the problem was due to the adUseServer default for CursorLocation.
[15 Jul 2007 12:35]
Tonci Grgin
John, I'm glad the problem is solved. Now we can discuss other questions leisurely :) > BTW, why does the adUseServer server side cursor setting not handle BLOB fields correctly? Will this be fixed in the future or is there something fundamental about the design of the ODBC stack that means a server side cursor (i.e. ODBC side cursor) cannot be made to handle data which contains BLOB fields correctly? We are aware of SS cursors problems for some time now and actively working on a solution. You'll find a lot of bug reports regarding this problem in bugs-db. I can't tell exactly when the solution will become available but it will be soon. > BTW, even with adUseServer, ODBC driver 3.51.06 against a 4.1.6 database never had any problems with the queries: DESCRIBE test_table; SHOW CREATE TABLE test_table; Also, as a matter of interest: DESCRIBE test_table; works fine with 3.51.06 and 5.0.42 database (with adUseServer) although there are still other BLOB field issues with 3.51.06 (especially when used with a 5.0.42 database). This is due to differences in the way of work between the versions. Ver.5 relies on I__S database to provide the info requested but that is whole another problem which you can catch a glimpse of in Bug#10491. That also represents the root cause of DESCRIBE table problems in latest versions of MyODBC 3.51 (from v. 14 up).
Description: If the Excel CopyFromRecordset ADO method is used to return the results of "DESCRIBE test_table", only the first field is returned. However if you iterate through the recordset without using CopyFromRecordset then works correctly. This might have suggested a bug with CopyFromRecordset method, but MySQL ODBC driver 3.51.16 works fine against a 4.1.6 database. However, neither 3.51.16 or 5.00.11 work against a 5.0.42 database. How to repeat: CREATE TABLE `test_table` (`f1` char(10) , `f2` char(10),`f3` decimal(3,1),`f4` decimal(3,1),`f5` char(10)); Excel VBA code ============== Sub test() Dim conn As ADODB.Connection Dim rs As ADODB.Recordset Dim field_number As Integer Set conn = New ADODB.Connection Set rs = New ADODB.Recordset conn.ConnectionString = "DSN=your_test_DSN" conn.Open rs.Open "DESCRIBE test_table", conn Range("a1").CopyFromRecordset rs rs.Close Set rs = Nothing conn.Close Set conn = Nothing End Sub ------------------------ Above code will return just the first field. However, remove CopyFromRecordset line and replace with: For field_number = 0 To rs.Fields.Count - 1 MsgBox rs.Fields(field_number).Name & ": " & rs(field_number) Next i.e. Iterating through the recordset does return the correct result. ------------------------ Now change the query in the code to: SHOW CREATE TABLE test_table This time using CopyFromRecordset appears to return an empty recordset, and iterating through the recordset this time returns garbage ------------------------ Using MDAC 2.8 SP1 on Windows XP SP2, and ADO 2.8 (i.e. latest versions)