MySQL Bugs: #24724: Parameters failure ADO objects in VB

Bug #24724	Parameters failure ADO objects in VB
Submitted:	30 Nov 2006 13:56	Modified:	1 Feb 2007 3:30
Reporter:	alberto glez	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Prepared statements	Severity:	S2 (Serious)
Version:	5.0.36-BK	OS:	Linux (Linux, windows XP sp2)
Assigned to:		CPU Architecture:	Any
Tags:	ADO, MySQL 5.0, VBA

Description:
Trying to access MySQL 5.0 stored procedures using ADO from a VBA application, with CommandType set to adCmdStoredProc, says the error:

[MySQL][ODBC 3.51 Driver][mysqld-5.0.27-nt]You have an error in your SQL syntax;
out or inout argument 1 for routine producciones.prueba is not a variable or new pseudo variable in before trigger

How to repeat:
CREATE DEFINER=`root`@`localhost` PROCEDURE `prueba`(inout valore int)
BEGIN
  set @valore=22;
  select @valore;
END

Dim conexion As ADODB.Connection
Set conexion = New ADODB.Connection
conexion.Open "DSN=myodbc;UID=root;PWD="

Dim Par As New ADODB.Parameter
Dim Cmd1 As New ADODB.Command

Set Cmd1 = New ADODB.Command
Set Cmd1.ActiveConnection = conexion

Cmd1.CommandType = adCmdStoredProc
Cmd1.CommandText = "prueba"

Set Par = Cmd1.CreateParameter("v", adInteger, adParamOutput)
Cmd1.Parameters.Append Par
Cmd1.Execute , Par

Hi Alberto and thanks for your problem report.
First of all, where did you get 3.51.14? It's not even in our plans...
Did you tried with adParamInputOutput (3) which can then be mapped to SQL_PARAM_INPUT_OUTPUT in ODBC?

ADO ParameterDirectionEnum Values:
Constant Value Description 
adParamUnknown 0 Direction unknown 
adParamInput 1 Default. Input parameter 
adParamInputOutput 3 Input and output parameter 
adParamOutput 2 Output parameter 
adParamReturnValue 4 Return value

Hi again Tonci. Thanks for the answer.
I got the 3.51.14 version from mysql ftp.
I have installed the 5.00.09 odbc version, and the error changed to "mismatched brace near:"
Properties:
commandtext--> "{ call resulset(?) }"
inputoutput int parameter a value=1

Stored procedure:
CREATE DEFINER=`root`@`localhost` PROCEDURE `resulset`(inout a int)
BEGIN
  set a=a+1;
END

Hi Alberto. I stil can't track where did 3.51.14 came from... Can you try with 3.51.12 too?
No aswer came to "Did you tried with adParamInputOutput (3) which can then be mapped to SQL_PARAM_INPUT_OUTPUT in ODBC?".

already tried with adparaminputoutput 3 and 3.51.12 & 3.51.13. just it doesn't work.

ftp://ftp.mysql.com/pub/mysql/download/myodbc-3.51.14/mysql-connector-odbc-3.51.14-win32.m...

Alberto, I am waiting on my colleague to verify related test case and then I'll return to this problem.

Looks like a bug/limitation of server side prepared statements (maybe, already known). Verified with 5.0.36-BK on Linux:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.0.36 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> delimiter //
mysql> create procedure resulset(inout a int) begin set a=a+1; end;//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> set @a=1;
Query OK, 0 rows affected (0.00 sec)

mysql> select @a;
+------+
| @a   |
+------+
| 1    |
+------+
1 row in set (0.00 sec)

mysql> call resulset(@a);
Query OK, 0 rows affected (0.00 sec)

mysql> select @a;
+------+
| @a   |
+------+
| 2    |
+------+
1 row in set (0.00 sec)

mysql> prepare stmt from 'call resulset(?)';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> execute stmt using @a;
ERROR 1414 (42000): OUT or INOUT argument 1 for routine test.resulset is not a v
ariable or NEW pseudo-variable in BEFORE trigger

So, this is a server bug/problem, not really Connector/ODBC one.

This bug is a duplicate of Bug#17898 "No straightforward way to deal with output parameters"

Please can we get a resolution on this one.

Without the capability to get paramteres OUT from s Stored Proceudre we are left with working with recordsets as the return mechanism, which is very inefficient if we just want to get a couple of values back.

Many thanks for any help in getting this fixed.

Dave

Dear David,
I'm really sorry but I can not give you an ETA for this bug. This is a truly difficult one, but it's not forgotten.

Thanks for answering so quickly.
 
This bug is also referenced over many area's on the forum, and reported on in different ways as it does cause an issue for a lot of developers.
 
Is the difficulty in fixing this because of backwards compatibility.. or just an underlying archtecture change.  If it is backward compatibility then I am sure a few developers would want to move to 5.xx just to get around this problem. Clustering, replication etc is nice to keep up with what MSSQL and Oracle are doing but it does not help many of the smaller developers who are running < 1 gigabyte of data.
 
What can be done to escalate this to a higher level?.  Would subscribing to  Paid Support assist this.
 
Many thanks for any insights. I believe that MySQL is an excellect product and will continue to support it 
 
Dave

Any updates???