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:Microsoft Windows (Windows)
Assigned to: Bogdan Degtyariov CPU Architecture:Any

[9 Dec 2005 13:02] Scott Harris
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
[12 Dec 2005 15:16] Scott Harris
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?
[12 Dec 2005 16:10] Scott Harris
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)
[14 Dec 2005 21:25] Vasily Kishkin
I guess the problem in SQLProcedureColumns(). Now it is not supported by myodbc driver. My test case is attached.
[14 Dec 2005 21:26] Vasily Kishkin
Test case

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

[4 May 2006 15:29] Cheong Gan
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.
[14 Jun 2006 22:36] jon eastwood
I also need this ASAP

How can it be non-critical severity!
[25 Jul 2006 9:34] Bogdan Degtyariov
Appropriate fix has been added to the source tree
[18 Aug 2006 12:55] Jon Stephens
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!
[6 Oct 2006 13:39] Patrick Vliegen
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.
[6 Oct 2006 14:55] Bogdan Degtyariov
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.
[6 Nov 2006 3:53] Bruce YANG
Why the bug has not been addressed for 14 months??
The initial submitted date is 9 DEC 2005. God!
[6 Nov 2006 15:47] Bogdan Degtyariov
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...
[12 Jun 2007 10:26] MC Brown
A note has been added to the 3.51.13 changelog.