Bug #6033 openschema (adSchemaColumns) fails above 4.0.17-nt
Submitted: 11 Oct 2004 16:00 Modified: 16 Aug 2007 21:16
Reporter: Bernhard Schmidt Email Updates:
Status: Closed Impact on me:
Category:Connector / ODBC Severity:S3 (Non-critical)
Version:3.51 OS:Windows (XP)
Assigned to: CPU Architecture:Any

[11 Oct 2004 16:00] Bernhard Schmidt
The  openschema(adschemaColumns) doesnt recognize existing fields.
This behaviour is NEW above 4.0.17-nt: 4.0.17 still works, 4.0.21 don't.

How to repeat:
Public Function fieldExists(ByVal Tablename$, ByVal FieldName$)
    With pscn.OpenSchema(adSchemaColumns, Array(Empty, Empty, Tablename, FieldName))
        fieldExists = Not (.BOF Or .EOF)
    End With
End Function

Tested with MyODBC 3.51.08 Client against 4.0.17-nt and 4.0.21-nt server (identic database) with similar connect:
driver={MySQL ODBC 3.51 Driver};server=Laurin1;db=PS-IPAQ;user=root;option=17411;
[11 Oct 2004 16:09] Bernhard Schmidt
following Code Fails with 4.0.21 (working with 4.0.17):
     with pscn.OpenSchema(adSchemaColumns, Array(Empty, Empty, Tablename,FieldName))
         fieldExists = Not (.BOF Or .EOF)

following Code is working with both releases
    Dim rs As ADODB.Recordset    
    Set rs = pscn.OpenSchema(adSchemaColumns, Array(Empty, Empty,Tablename, FieldName))
    With rs
        fieldExists = Not (.BOF Or .EOF)
[11 Oct 2004 20:25] MySQL Verification Team
Showing Column Names from mysql.user

Attachment: testing-bug6033.PNG (image/png, text), 39.86 KiB.

[11 Oct 2004 20:29] MySQL Verification Team

Running MySQL 4.0.21 version, using Access 2003 and the method
openschema (adSchemaColumns) I was able for to get the column
names from the table mysql.user. Please see attached figure using
the Files tabs.
[12 Oct 2004 15:42] Bernhard Schmidt
Hi, Miguel Solorzano 
your scenario doesn't hit the bug:
- Yes, i can get with openschema and no field filtered all fields, (as you showed)
- No, i can't get an recordset back when filtering:
     OpenSchema(adSchemaColumns, Array(Empty, Empty,Tablename,FieldName))

my shown code worked with 4.0.17-nt servers and switching to an 4.0.21-nt server, the recordset stayes empty

sincerly, Bernhard Schmidt
[12 Oct 2004 15:54] MySQL Verification Team
Hi Bernhard,

I misunderstood you so, could you please post a complete test case
(table schema and some data and the VB code) such as I can to verify on Access
(I don't have VB).

[12 Oct 2004 17:29] Bernhard Schmidt
testcase; using the mysql/user-table as data

Attachment: testcase.bas (application/octet-stream, text), 825 bytes.

[14 Oct 2004 7:53] Bernhard Schmidt
Testcase appended!

[14 Oct 2004 14:36] MySQL Verification Team
Thank for the test case.
[9 Mar 2005 10:05] Andreas Krantz
The problem can be reproduced using the following VBS script.
Just save it to a file and it should be usable in almost any windows version.
Both Message boxes should contain table and column names.

option explicit

dim connectionString : connectionString = "DSN=mySQL;uid=mysql;pwd="

' Open ADO connection
Dim Conn : set conn = CreateObject("ADODB.Connection")
conn.ConnectionString = connectionString

dim txt : txt = ""

' Done in the plugin
' Has to work to make the database available for the plugin

Dim ColumnSchema : Set ColumnSchema = Conn.OpenSchema(4) 
Do While Not ColumnSchema.EOF

  dim tblName : tblName = ColumnSchema("TABLE_NAME")
  dim clName : clName = ColumnSchema("COLUMN_NAME")
  txt = txt & tblName & "." & clName & "; "


MsgBox txt

txt = ""

' Another way 

Dim TablesSchema : Set TablesSchema = Conn.OpenSchema(20) ' this line is also used in the plugin
Do While Not TablesSchema.EOF

  dim tableName : tableName = TablesSchema("TABLE_NAME")
  txt = txt & tableName & VBCRLF & " -> "

  'Get all columns of the current table
  Dim ColumnsSchema : Set ColumnsSchema = Conn.OpenSchema(4, Array(Empty, Empty, tableName))

  ' Add all channels
  Do While Not ColumnsSchema.EOF

    dim columnName : columnName = ColumnsSchema("COLUMN_NAME")
    txt = txt & columnName & ";"


  txt = txt & VBCRLF

  ' Next table

MsgBox txt
[20 Apr 2006 22:21] Joep Oude Veldhuis
This bug still exists with ODBC Driver Version 3.51.12 and MySQL 5.0.20-nt-max-log. Bit puzzled by status S3 non-critical though, cause adSchemaTables and adSchemaColumns are quite essential (and I think one of few really required functions?). Does anyone know: is there a workaround and/or anything known about the timeframe needed to solve this bug?
[9 Aug 2007 17:48] MySQL Verification Team
Could you please test with released version 3.51.18. Thanks in advance.
[10 Aug 2007 9:31] Bernhard Schmidt
The subject seems to be fixed (after three years !!!).

The testcase, when run against an 4.0.17-nt-Server 
can now retrieve the filtered recordset, 
as it did with older servers before.

This should be checked by the other users also, 
because i have not other servers above 4.0.x running for testing.
[16 Aug 2007 21:16] MySQL Verification Team
Thank you for the feedback.