Bug #55870 SQLTables() have different behavior for ODBC driver 3.5.1 and 5.1.6
Submitted: 10 Aug 2010 5:55 Modified: 18 Nov 2010 16:23
Reporter: allen Kong Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / ODBC Severity:S1 (Critical)
Version:5.1.6 OS:Microsoft Windows
Assigned to: Lawrenty Novitsky CPU Architecture:Any

[10 Aug 2010 5:55] allen Kong
Description:
Dear Experts,
I try to get all the tables for a accout(root and normal user) in our application. Unfortunately, I have problem when using latest 5.1.6 driver.
1. Use SQLTables and the row number for driver 5.1.6 is -1. For 3.51, it return  correct number 3, as I have 3 sachems in the database. 
		sqlRet = SQLTables(sql_hStmt,
			(SQLCHAR*)"%", SQL_NTS, 
			NULL, 0,
			NULL, 0,
			(SQLCHAR*)"TABLE,VIEWS", SQL_NTS);
                SQLNumResultCols( sql_hStmt, &nCols );
		SQLRowCount( sql_hStmt, &nRows );

2. If I create a user and assign all the privileges of on database. THe error 1044 will be raised. 
 [MySQL][ODBC 5.1 Driver][mysqld-5.0.45-community-nt]Access denied for user 'ds'@'%' to database '%' (1044) 
 If I use root, then both there is no such error.

How to repeat:
 1. Install 32 bit driver for 3.51 and 5.1.6.
2. Create 2 data source named MySQL3 and MySQL5.
3. Run the test program as below.
4. Change the variable db to MySQL3 or MySQL5.
5. Here is the output for the MySQL3.

----------- MySQL 3.51 -----------
Connected to database
Connection Info: [ MySQL5 ]

SQLTables() Success for [MySQL5] ... Row:1 Col: 5
Row
Column test
Column
Column friends
Column TABLE
Column InnoDB free: 4096 kB
Row
Column test
Column
Column tt
Column TABLE
Column InnoDB free: 4096 kB

----------- MySQL 5.1.6 -----------
Connected to database
Connection Info: [ MySQL5 ]

SQLTables() Success for [MySQL5] ... Row: -1 Col: 5
Press any key to continue . . .

#include "stdafx.h"
#include  <windows.h>    
#include <sql.h>
#include <sqlext.h>
#include <sqltypes.h>
#include <iostream>
using namespace std;
int _tmain(int argc, _TCHAR* argv[])
{
	SQLHENV sql_hEnv = 0;
	SQLHDBC sql_hDBC = 0;
	SQLHSTMT sql_hStmt = 0;
	SQLCHAR szDNS[1024] ={0};
	SQLSMALLINT nSize = 0;
	SQLRETURN sqlRet =
		SQLAllocHandle( SQL_HANDLE_ENV,
		SQL_NULL_HANDLE,
		&sql_hEnv );
	sqlRet =
		SQLSetEnvAttr( sql_hEnv,
		SQL_ATTR_ODBC_VERSION,
		(void*) SQL_OV_ODBC3,
		0 );
	sqlRet =
		SQLAllocHandle( SQL_HANDLE_DBC,
		sql_hEnv,
		&sql_hDBC );

	// if 0 - use SQLDriverConnect
	// if 1 - use SQLConnect
char* db = "MySQL5";
//MySQL3 Use 3.51 driver
//MySQL5 Use 5.1.6 driver

sqlRet =
		SQLConnect( sql_hDBC,
		(SQLCHAR*)db,
		SQL_NTS,
		(SQLCHAR*)"ds",
		SQL_NTS,
		(SQLCHAR*)"titan",
		SQL_NTS );

	if( SQL_SUCCEEDED( sqlRet ) )
	{
		cout << "Connected to database " << endl
			<< "Connection Info: [ " << db<<" ]"<< endl
			<< szDNS << endl;

		sqlRet =
			SQLAllocHandle( SQL_HANDLE_STMT,
			sql_hDBC,
			&sql_hStmt );
	
		SQLSMALLINT nCols = 0;
		SQLINTEGER nRows = 0;
		SQLINTEGER nIdicator = 0;
		SQLCHAR buf[1024] = {0};
	

		//get list of tables
		sqlRet = SQLTables(sql_hStmt,
			(SQLCHAR*)"%", SQL_NTS, /* no specific catalog */
			NULL, 0, /* no specific schema */
			NULL, 0, /* no specific table */
			(SQLCHAR*)"TABLE,VIEWS", SQL_NTS); /* only tables, no views */

		if( SQL_SUCCEEDED( sqlRet ) )
		{

			SQLNumResultCols( sql_hStmt, &nCols );
			SQLRowCount( sql_hStmt, &nRows );
			cout << "SQLTables() Success for ["<<db<<"] ... "<<"Row: "<<nRows<<" Col: "<<nCols<<endl;
			while( SQL_SUCCEEDED( sqlRet = SQLFetch( sql_hStmt ) ) )
			{
				cout << "Row " << endl;
				for( int i=1; i <= nCols; ++i )
				{
					sqlRet = SQLGetData( sql_hStmt,
						i,
						SQL_C_CHAR,
						buf,
						1024,
						&nIdicator );
					if( SQL_SUCCEEDED( sqlRet ) )
					{
						cout << "Column " << buf << endl;
					}
				}
			} 
		}

		SQLFreeHandle( SQL_HANDLE_STMT, sql_hStmt );
		SQLDisconnect( sql_hDBC );
	}
	else
	{
		cout << "Failed to connect to the database" << endl;
	}
	SQLFreeHandle( SQL_HANDLE_DBC, sql_hDBC );
	SQLFreeHandle( SQL_HANDLE_ENV, sql_hEnv );
	return 0;
}

Suggested fix:
ODBC 5.1.6 should have the same behavior with3.51.
[9 Oct 2010 10:24] Lawrenty Novitsky
patch

Attachment: bug55870.patch (application/octet-stream, text), 13.54 KiB.

[9 Oct 2010 10:41] Lawrenty Novitsky
Fix is similar to what have been used and approved for SQLProcedureColumns.
Not only SQLTables but other affected catalog functions have been fixed.

Patch contains separate testcase, but for some functions tests were incorporated into old testcases.

Since similar patch has been already approved, this patch has been pushed as rev#933
[27 Oct 2010 8:48] Lawrenty Novitsky
forgot to change the status.will be released in 5.1.8 if nothing goes wrong
[18 Nov 2010 16:23] Tony Bedford
An entry has been added to the 5.1.8 changelog:

When used after a call to SQLTables(), SQLRowCount() did not return the correct value.