MySQL Bugs: #36180: ODBC indicates no Accessible Procedures even though Stored Procedures supported

Bug #36180	ODBC indicates no Accessible Procedures even though Stored Procedures supported
Submitted:	17 Apr 2008 14:58	Modified:	24 Apr 2008 15:00
Reporter:	brad hanson	Email Updates:
Status:	Verified	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51.24 and 5.1	OS:	Windows
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
I am trying to connect to MySQL stored procedure and the calling application (Cisco ACS) indicates that stored procedures are not supported, yet I can use another application (Excel, etc) and it works just fine. 

Found the driver information seems to be returning information indicating that they are not (Is this the Accessible Procedures = No?). 

This is what was summarized by a ODBC Query Tool: 

ODBC Driver Information 
----------------------- 
Cursor Library is Not Loaded 
Driver Information 
------------------ 
Data Source Name - acsRADIUSexecute 
Database Name - acsradius 
User Name - acsprocedure 
Server Name - nisdb1 via TCP/IP:0 
DBMS - MySQL version 5.0.27-community-nt 
Driver - myodbc3.dll version 03.51.24 
Driver ODBC Ver. - 03.51 
ODBC Version - 03.52.0000 
API Conformance - Level 1 
SQL Conformance - Core 
SAG CLI Conformance - Yes 
Integrity - Not Supported 
Active Connections - No Limit 
Active Statements - No Limit 
Multiple Result Sets - Supported 
File Usage - <n/a>, Driver not single-tier 
Read/Write Status - Data source is read-write 
Accessible Procedures - No 
Accessible Tables - Yes

How to repeat:
Does not require a query, it is a status returned when polling the ODBC driver information regarding what is supported.

Suggested fix:
The ODBC driver should return status indicating "Yes" to Accessible Procedures if the MySQL version connected to supports stored procedures.

not certain the log captured the attempt to determine if stored procedures supported. hope so.

Attachment: SQL.LOG (application/octet-stream, text), 449.30 KiB.

Hi Brad and thanks for your report. This should be either missconfigured server or a "bad" client as SQLProcedures() API call works on my box with generic Microsoft ODBC client:
SQLProcedures
	Return:	SQL_SUCCESS=0

Get Data All:
"PROCEDURE_CAT", "PROCEDURE_SCHEM", "PROCEDURE_NAME", "NUM_INPUT_PARAMS", "NUM_OUTPUT_PARAMS", "NUM_RESULT_SETS", "REMARKS", "PROCEDURE_TYPE"
"test", <Null>, "test35199_function", <Null>, <Null>, <Null>, "This function provides lookup/insert/update behavior for table t", 2
"test", <Null>, "test_function", <Null>, <Null>, <Null>, "This function provides lookup/insert/update behavior for table t", 2
"test", <Null>, "test_function35199", <Null>, <Null>, <Null>, "This function provides lookup/insert/update behavior for table t", 2
3 rows fetched from 8 columns.

Will probably know more after looking into trace attached.

Brad, 12320 (!) lines of trace is something *completely* unusable, more so as it doesn't even start from the beginning. I think a trace of that Cisco client connecting to MySQL server should be enough (if I got you right). Please attach another trace.

Brad, I see no trace attached yet? "Accessible Procedures - No " could mean anything and I'm not about to start learning Cisco ODBC client...

This problem could be related to known server issue (*if* your client issues SQLProcedureColumns and bases it's answer on that). Upon call to SQLProcedureColumns, MyODBC will return:
MessageText = "[MySQL][ODBC 5.1Driver][mysqld-5.0.58-pb1083-log]MySQL server does not provide the
requested information" because there is none in `information_schema`.`ROUTINES` table.

So please attach trace of what's your client actually doing during initial phase so I can verify my assumption.

Brad sorry, I was to swift in my call.

Verified as described by reporter:
SQL_DBMS_NAME = MySQL
SQL_DBMS_VERSION = 5.0.58-pb1083-log
SQL_DRIVER_VERSION = 03.51.25
SQL_Accessible_Procedures = N
<Driver Connection Test> completed

Relevant part of my test case:
	rc = SQLGetInfo(hDbc, SQL_ACCESSIBLE_PROCEDURES, &infoBuffer, sizeof(infoBuffer), NULL);
	if(rc == SQL_SUCCESS)
	{
		wcstombs(chararray, infoBuffer, sizeof(infoBuffer));
		cout << "SQL_Accessible_Procedures = " << chararray << endl;
		outfile << "SQL_Accessible_Procedures = " << chararray << endl;
	}

thanks for all your help so far looking into this. I apologize that I haven't been able to focus one this. I am also waiting for Cisco to respond as I have asked how they determine if stored procedures are avialable or not.

The other status I recieved was from another ODBC Query client that will provide information about the ODBC driver-- that is not a Cisco product.

I'm thinking both are returning what you have also fount to occur.

thanks for all your help so far looking into this. I apologize that I haven't been able to focus one this. I am also waiting for Cisco to respond as I have asked how they determine if stored procedures are avialable or not.

The other status I recieved was from another ODBC Query client that will provide information about the ODBC driver-- that is not a Cisco product.

I'm thinking both are returning what you have also fount to occur.

Cisco duplicated it in their lab using MySQL 5.0.45-community-nt server and client version 5.1.11  they report that the connection for stored procedures is successful.

My sql version is 5.0.27-community-nt and I'm now not so certain my client version. but seems it isn't as up todate even though i thought i downloaded and installed what was current.   

Is this a version problem?

Brad, just did tests with 5.1 server and found the bug still there:

<Driver Connection Test>
SQLAllocEnv ... ok
SQLAllocConnect ... ok
SQL_QUIET_MODE ... supported
SQL_LOGIN_TIMEOUT ... supported
SQLDriverConnect ... ok

SQL_DBMS_NAME = MySQL
SQL_DBMS_VERSION = 5.1.24-rc-pb1604-log
SQL_DRIVER_VERSION = 03.51.25
SQL_Accessible_Procedures = N
<Driver Connection Test> completed

and

<Driver Connection Test> 
SQLAllocEnv ... ok
SQLAllocConnect ... ok
SQL_QUIET_MODE ... supported 
SQL_LOGIN_TIMEOUT ... supported
SQLDriverConnect ... ok

SQL_DBMS_NAME = MySQL
SQL_DBMS_VERSION = 5.1.24-rc-pb1604-log
SQL_DRIVER_VERSION = 05.01.0004
SQL_Accessible_Procedures = N
<Driver Connection Test> completed

info.c shows driver does nothing for this call so no need to test different servers/clients:
  case SQL_ACCESSIBLE_PROCEDURES:
  case SQL_ACCESSIBLE_TABLES:
    MYINFO_SET_STR("N");

Tonci has misunderstood what SQL_ACCESSIBLE_PROCEDURES means. That simply indicates whether the user is guaranteed to have execution rights for the procedures returned by SQLProcedures(), not whether procedures are supported at all. (SQL_PROCEDURES indicates that procedures are available.)

This is probably related to the lack of useful information from SQLProcedureColumns().

Jim, thanks for clarification.