MySQL Bugs: #15635: MyODBC won't handle ADO Command type adCmdStoredProc

Bug #15635	MyODBC won't handle ADO Command type adCmdStoredProc
Submitted:	9 Dec 2005 13:02	Modified:	12 Jun 2007 10:26
Reporter:	Scott Harris	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	3.51-12	OS:	Windows (Windows)
Assigned to:	Bogdan Degtyariov	CPU Architecture:	Any

Description:
When trying to access MySQL 5.0 stored procedures using ADO, with CommandType set to adCmdStoredProc, the following error occurs.

[MySQL][ODBC 3.51 Driver][mysqld-5.0.16-nt]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '{ call spInsertMfg;('Test Company', 'TC', 38928736) }' at line 1

Stored procedures can be accessed using CommandType = adCmdText will work, however parameters are not available in that cirumstance.

MyODBC also incorrectly converts the adInteger output parameter (passed with no value) type to hold a value of 38928736.

How to repeat:
Create a table:

CREATE TABLE `Test` (
  `ID` INTEGER UNSIGNED NOT NULL DEFAULT 0 AUTO_INCREMENT,
  `MfgName` VARCHAR(45) DEFAULT '',
  `MfgAbrv` VARCHAR(45) DEFAULT '',
  PRIMARY KEY(`ID`)
)
ENGINE = InnoDB;

Create Stored Procedure:

DELIMITER $$
    DROP PROCEDURE IF EXISTS `spInsertMfg` $$
    CREATE PROCEDURE `spInsertMfg` (IN _MfgName VARCHAR(45), IN _MfgAbrv VARCHAR(45), OUT _NewMfgID INT)
    BEGIN
        
        START TRANSACTION;
            
            INSERT INTO Manufacturer_Master(MfgName, MfgAbrv) VALUES (_MfgName, _MfgAbrv);
            SELECT LAST_INSERT_ID() INTO _NewMfgID;
            
            COMMIT;
          END IF;
    END$$
DELIMITER ;

Create a simple script to call the stored procedure from ASP/ADO:

Dim cmdUpdate, objRS
Dim MfgName, MfgAbrv, intOutput

Set cmdUpdate = Server.CreateObject("ADODB.Command")

With cmdUpdate
'Set up command properties
	.ActiveConnection = CONN_DSN
	.CommandText = "spInsertMfg;"
	.CommandType = adCmdStoredProc
     
        'Add input variables
	.Parameters.Append .CreateParameter ("_MfgName", adVarChar, adParamInput, 100, "Test Company")
	.Parameters.Append .CreateParameter ("_MfgAbrv", adVarChar, adParamInput, 5, "TC")
'Add output variables
	.Parameters.Append .CreateParameter ("_NewMfgID", adInteger, adParamOutput)
	
	'Execute the stored procedure
	.Execute

'Extract the output variable
	intOutput = .Parameters("_NewMfgID")
End With

'Display the result
Response.Write(intOutPut)

Suggested fix:
Unknown

Just by reviewing the error message again, it appears that MyODBC is placing a ; between the SP name and parameter list.

...near '{ call spInsertMfg;('Test Company', 'TC', 38928736) }' at line 1

should be spInsertMfg('Test Company', 'TC', 38928736);

Not sure if the curly braces are beign passed as well?

Ignore the above observation, I had the ; in my code.  However, removing it makes no differece to the end result.

The updated code to call the SP from ADO/ASP to avoid the misplaced ;  would be:

Dim cmdUpdate, objRS
Dim MfgName, MfgAbrv, intOutput

Set cmdUpdate = Server.CreateObject("ADODB.Command")

With cmdUpdate
'Set up command properties
	.ActiveConnection = CONN_DSN
	.CommandText = "spInsertMfg"
	.CommandType = adCmdStoredProc
     
        'Add input variables
	.Parameters.Append .CreateParameter ("_MfgName", adVarChar, adParamInput, 100,
"Test Company")
	.Parameters.Append .CreateParameter ("_MfgAbrv", adVarChar, adParamInput, 5,
"TC")
'Add output variables
	.Parameters.Append .CreateParameter ("_NewMfgID", adInteger, adParamOutput)
	
	'Execute the stored procedure
	.Execute

'Extract the output variable
	intOutput = .Parameters("_NewMfgID")
End With

'Display the result
Response.Write(intOutPut)

I guess the problem in SQLProcedureColumns(). Now it is not supported by myodbc driver. My test case is attached.

Test case

Attachment: 15635.asp (application/octet-stream, text), 1.05 KiB.

This bug is really critical. Without a fix, mySQL is useless for ASP programming.

To summary the bug:
MyODBC does not support ADODB.Command object with CommandType = adCmdStoredProc. It does not work for stored procedure with no paramaters, it does not work with stored procedure with IN or OUT or INOUT parameters. In each case the error is:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[MySQL][ODBC 3.51 Driver][mysqld-5.0.20a-nt]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '{ call spNoInOut }' at line 1

Please, can someone escalate this bug soon because my project decision is hinged upon being able to use adCmdStoredProc. 

--------- test case: a.asp: ASP code starts	 
<%
Dim currConn,cm
set currConn 	= Server.CreateObject("ADODB.Connection")
Set cm 		= Server.CreateObject("ADODB.Command")
currConn.Open	MyODBC_connectionString	'-- use your own connection string
cm.ActiveConnection = currConn
cm.CommandType	= 4 'adCmdStoredProc	
cm.CommandText	= "spNoInOut"
cm.Execute
%>
<html><body>
<p>Test MySQL stored procedure using MyODBC's ADODB.Command object
</body></html>
--------- test case: a.asp: ASP code ends	 

--------- test case: create procedure 
delimiter ^
CREATE PROCEDURE spNoInOut()
BEGIN
	declare  var1 varchar(60);
	set var1="haha";
	select var1;
END^
delimiter ;
call spNoInOut();  -- this works in mysql command window, value of var1 displayed.

I also need this ASAP

How can it be non-critical severity!

Appropriate fix has been added to the source tree

We can't document fix without knowing which product version(s) the fix will appear in. Please furnish version numbers so we can document and close. Thanks!

Hello there,

I also face the exact same and I find it a real show stopper to use MySQL as a database for my solution at this time.

I am using MySQL server: mysqld-5.0.24a-community-nt
The version of MyODBC = 3.51.12
Basically the stable version available for download today.

Regards
Patrick.

Patrick,

Bugfix has been committed to the source repository.
It will be included in MyODBC 3.51.13 which is going to be released in about 3 weeks.

Why the bug has not been addressed for 14 months??
The initial submitted date is 9 DEC 2005. God!

This bug has been fixed and the proper patch has been committed to the sources. However, as the release of MyODBC 3.51.13 is being delayed I would suggest trying this preliminary windows build: 

ftp://ftp.mysql.com/pub/mysql/download/mysql-connector-odbc-3.51.13r125_20061005_1714-win3...

A note has been added to the 3.51.13 changelog.