Re: MyODBC - Support For Stored Procedures
Posted by:
Brett
Date: May 10, 2006 05:54AM
I have exactly the same problem:
MySQL 5.0.17-nt, ASP/VBscript 5.6.8827, ODBC connector 3.51.12.
...
DELIMITER $$;
CREATE PROCEDURE `mytest`(IN bloggs VARCHAR(10))
BEGIN
END$$
DELIMITER ;$$
...
oCmd.CommandText = "mytest"
Set oParameter = oCmd.CreateParameter("bloggs", adVarChar, adParamInput, 10, "data")
oCmd.Parameters.Append oParameter
oCmd.CommandType = adCmdStoredProc
oCmd.Execute
...
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[MySQL][ODBC 3.51 Driver][mysqld-5.0.17-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 mytest('data') }' at line 1
...
The error here appears to be caused by the surrounding {} braces, if I cut and paste the entire string to the client then I can repeat the error message, if I loose the {} the all is fine.
I am not sure of the correct parameter naming syntax ('bloggs' or '@bloggs') or if there is a different requirement for IN/OUT/INOUT paramters etc, but I have noticed that if used the @ is removed from the resultant sql string anyway.
If I adjust the above example for an INOUT parameter then I get no change at all in the resultant error message, however if I change it to an OUT paramter then I notice the paramters name is removed from the sql string { call mytest('') } which may or may not indicate another problem.
Another point to note is that Refresh does not seem to populate the parameters object as I understand it should, for example:
set oCmd = server.CreateObject("ADODB.Command")
Set oCmd.ActiveConnection = oConn
oCmd.CommandType = adCmdStoredProc
oCmd.CommandText = "mytest"
oCmd.Parameters.Refresh
for each param in oCmd.Parameters
' never get here
next
So one has to guess what type a parameter should be and in this example I assume VARCHAR(10) maps to adVarChar 10 etc.
I have no problem calling the procedure via adCmdText and passing in values using the ? parameter syntax, but if I want my parameters updated then at the moment I have to either return a result set from the procedure containing the new values, or if thats needed for something else, then initialise some mysql variables first using the ? param syntax, call the procedure with them hard wired as parameters, then read them back in a second recordset, in otherwords three command executes.
Actually I have only just realised that the MySQL version above is 5.0.17 (thought I was running 5.1) but I suspect this makes no difference as I think the issue is with the ODBC connector.
Edited 1 time(s). Last edit at 05/10/2006 06:22AM by Brett .