Bug #39957 SQLTablesW is broken in the MySQL ODBC driver 5.1.5
Submitted: 9 Oct 2008 16:58 Modified: 12 Nov 2008 11:38
Reporter: John Water Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / ODBC Severity:S2 (Serious)
Version:5.1.5 OS:Microsoft Windows (Win-XP)
Assigned to: Jess Balint CPU Architecture:Any
Tags: ODBC driver, SQLTablesW
Triage: D3 (Medium)

[9 Oct 2008 16:58] John Water
Description:
The SQLTablesW API in the MyODBC driver 5.1.5 gives wrong or incompleted results.

Run the repro against a MySQL server 5.1.22 with MyODBC version 5.1.3 and it will show the following results
Test start ...

--------------------
SQLTableW results returned with SchemaName "test" and TableName "user_name1" specified

Table Catalog: user_name1
Table Schema:  
Table Name:    test
Table Type:    TABLE
Remarks:       

--------------------
SQLTableW results returned with only SchemaName "user_name1" specified:

Table Catalog: user_name1
Table Schema:  
Table Name:    con_stoobigd
Table Type:    TABLE
Remarks:       

Table Catalog: user_name1
Table Schema:  
Table Name:    con_stoobign
Table Type:    TABLE
Remarks:       

Table Catalog: user_name1
Table Schema:  
Table Name:    con_stoobigv
Table Type:    TABLE
Remarks:       

Table Catalog: user_name1
Table Schema:  
Table Name:    crsrst
Table Type:    TABLE
Remarks:       
.....
--------------------
SQLTableW results returned with only TableName "test" specified:

Table Catalog: user_name1
Table Schema:  
Table Name:    test
Table Type:    TABLE
Remarks: 

However, with MyODBC driver 5.1.5, the repro shows only

Test start ...

--------------------
SQLTableW results returned with SchemaName "test" and TableName "yguo" specified

--------------------
SQLTableW results returned with only SchemaName "yguo" specified:

Table Catalog: <NULL>
Table Schema:  
Table Name:    <NULL>
Table Type:    <NULL>
Remarks:       <NULL>

--------------------
SQLTableW results returned with only TableName "test" specified:

Table Catalog: 
Table Schema:  
Table Name:    test
Table Type:    TABLE
Remarks:       

How to repeat:
A repro, odbcbug.zip will be attached and the odbcbug.zip file contains the following files
odbcbug.c --    source code
odbcbug.exe -- executable file for Windows
out.515 -- repro output file that was generated with MyODBC driver 5.1.5
out.513 -- repro output file that was generated with MyODBC driver 5.1.3
[9 Oct 2008 18:51] Jess Balint
John,

A number of issues have been fixed (from bug#39561) to improve adherence to the spec. The ODBC driver maps catalogs in ODBC to databases in MySQL. This requires changing your testcase to swap the catalog and schema arguments to the SQLTables() call. After this modification the expected results would be as follows:

SQLTables with catalog and table specified:
SQLTables(stmt, "catalog", SQL_NTS, NULL, 0, "table", SQL_NTS, NULL, 0);
This will return a description for the table in the given catalog/database.

SQLTables with catalog specified and table blank:
SQLTables(stmt, "catalog", SQL_NTS, NULL, 0, "", SQL_NTS, NULL, 0);
This will return an empty result as no table is named "". This behaviour is specified on the "Pattern Value Arguments" page of the ODBC docs: a zero-length search pattern — that is, a valid pointer to a string of length zero — matches only the empty string ("").
An alternative to this is to specify the table as NULL in which case all tables for the given catalog would be returned.

SQLTables with catalog blank and table specified:
SQLTables(stmt, "", SQL_NTS, NULL, 0, "table", SQL_NTS, NULL, 0);
This will also return an empty set as no catalog is named "". By specifying NULL as the catalog here, the table matching the given name from the current database will be returned.

The only bug I see here is that the catalog is blank in this last case.
[9 Oct 2008 18:59] Jess Balint
Fix pushed to:

https://code.launchpad.net/~myodbc-developers/myodbc/bug39957
[11 Nov 2008 18:07] Jess Balint
Pushed as rev 809.
[12 Nov 2008 11:38] Tony Bedford
An entry was added to the 5.1.5 changelog:

The SQLTablesW API gave incorrect results. For example, table name and table type were returned as NULL rather than as the correct values.