MySQL Bugs: #39957: SQLTablesW is broken in the MySQL ODBC driver 5.1.5

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:	Windows (Win-XP)
Assigned to:	Jess Balint	CPU Architecture:	Any
Tags:	ODBC driver, SQLTablesW

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

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.

Fix pushed to:

https://code.launchpad.net/~myodbc-developers/myodbc/bug39957

Pushed as rev 809.

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.