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.
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.