MySQL Bugs: #25970: Error 1414 - OUT or INOUT argument 2 for routine is not a variable or NEW pseudo

Bug #25970	Error 1414 - OUT or INOUT argument 2 for routine is not a variable or NEW pseudo
Submitted:	31 Jan 2007 10:46	Modified:	8 Nov 2008 9:43
Reporter:	Jean-Pierre DUVAL	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: C API (client library)	Severity:	S2 (Serious)
Version:	5.0.27	OS:	Windows (WinXP)
Assigned to:		CPU Architecture:	Any
Tags:	API C, CALL, OUT, Parameter

Description:
Using the C API for MySql 5.0.27 on Windows XP, while calling a stored procedure with a out parameter, I receive the message :

Error 1414 :
OUT or INOUT argument 2 for routine scott.MaProcedure is not a variable or NEW pseudo-variable in BEFORE trigger.

In fact: 
A) From Sql through mysql.exe, IN and OUT parameters are working whatever the their types.
B) From C program through C API, IN parameters are working whatever the their types; OUT parameters are not working whatever the their types.

See code below.

How to repeat:
SQL Script - Extract :
======================

-- GRANT USAGE ON *.* TO 'scott'@'localhost' IDENTIFIED BY 'tiger';

-- CREATE DATABASE scott;

USE scott;

DROP PROCEDURE MaProcedure;

DROP TABLE emp;

CREATE TABLE emp
   (
   empno INTEGER NOT NULL COMMENT 'Numero de l''employe',
   ename VARCHAR(10) UNICODE NOT NULL COMMENT 'Nom de l''employe',
   job VARCHAR(9) UNICODE COMMENT 'Profession de l''employe',
   mgr INTEGER COMMENT 'Numero du superieur hierarchique de l''employe',
   hiredate DATE COMMENT 'Date d''embauche de l''employe',
   sal FLOAT COMMENT 'Salaire de l''employe',
   comm FLOAT COMMENT 'Commission de l''employe',
   deptno INTEGER NOT NULL COMMENT 'Numero du departement de l''employe'
   ) COMMENT 'Employes' ENGINE=INNODB;
   
INSERT INTO emp VALUES (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, NULL, 20);
INSERT INTO emp VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);
INSERT INTO emp VALUES (7521, 'WARD', 'SALESMAN', 7698, '1981-02-20', 1250, 500, 30);
INSERT INTO emp VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, NULL, 20);
INSERT INTO emp VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1981-07-28', 1250, 1400, 30);
INSERT INTO emp VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, NULL, 30);
INSERT INTO emp VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, NULL, 10);
INSERT INTO emp VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1982-12-09', 3000, NULL, 20);
INSERT INTO emp VALUES (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10);
INSERT INTO emp VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30);
INSERT INTO emp VALUES (7876, 'ADAMS', 'CLERK', 7788, '1983-01-12', 1100, NULL, 20);
INSERT INTO emp VALUES (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, NULL, 30);
INSERT INTO emp VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, NULL, 20);
INSERT INTO emp VALUES (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, NULL, 10);

COMMIT;

DELIMITER @

CREATE PROCEDURE MaProcedure(IN NumeroDepartement INTEGER, OUT SalaireMin FLOAT, OUT SalaireMax FLOAT) 
LANGUAGE SQL
BEGIN
SELECT MIN(sal), MAX(sal) INTO SalaireMin, SalaireMax FROM emp WHERE deptno=NumeroDepartement;
END
@

DELIMITER ;

CALL scott.MaProcedure(10,@SalaireMin,@SalaireMax);
SELECT @SalaireMin, @SalaireMax;

C Program - Extract :
=====================
MYSQL *HandleConnexion;
MYSQL_STMT *HandleStatement;
char *Sql;
long Size;
MYSQL_BIND Parameters[2];
double SalaireMin;
unsigned long SizeSalaireMin;
double SalaireMax;
unsigned long SizeSalaireMax;

/* Connexion. */
HandleConnexion=...;

/* Prepare. */
HandleStatement=mysql_stmt_init(HandleConnexion);
if (!HandleStatement)
   CheckError();
Sql="CALL scott.MaProcedure(10,?,?)";
Size=strlen(Sql);
if (mysql_stmt_prepare(HandleStatement,Sql,Size))
   CheckError();

/* Initialization. */
memset(Parameters,0,sizeof(MYSQL_BIND)*2);
SalaireMin=0.0;
SizeSalaireMin=sizeof(double);
SalaireMax=0.0;
SizeSalaireMax=sizeof(double);

/* Bind parameters. */
Parameters[0].buffer_type=MYSQL_TYPE_DOUBLE;
Parameters[0].buffer=(void *)&SalaireMin;
Parameters[0].length_value=sizeof(double);
Parameters[0].length=&SizeSalaireMin;
Parameters[1].buffer_type=MYSQL_TYPE_DOUBLE;
Parameters[1].buffer=(void *)&SalaireMax;
Parameters[1].length_value=sizeof(double);
Parameters[1].length=&SizeSalaireMax;
if (mysql_stmt_bind_param(HandleStatement,Parameters))
   CheckError();

/* Execute -> Error 1414. */
if (mysql_stmt_execute(HandleStatement))
   CheckError(); 

printf("SalaireMin=%lf\nSalaireMax=%lf\n",SalaireMin,SalaireMax);

Suggested fix:
The behavior must be the same, calling the stored procedure through a Sql script ou through a C program.

JPD.

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Please read about which statements can be used as C API prepared statements at http://dev.mysql.com/doc/refman/5.0/en/c-api-prepared-statements.html

I am very surprised by your reply since:

1) This "strange-behavior-that-is-not-a-bug" is related by many others guys using Php, Jdbc or Odbc. 
2) Any other database engines are working properly for the call stored procedures using OUT parameters with the same algorithm - Db2, Oracle, Sql Server, etc. Just C API names and parameters are changing.
3) I remind you it is working with mysql.exe but not through API C. And you cann't have two behaviors for your database kernel!
4) I read again the documentation of prepared statement on My Sql web site, as you suggest me:
4.1) No restriction nor advice in mysql_stmt_by_param for output parameters.
4.2) No restriction nor advice in CALL Statement Syntax.
4.3) No advice or warning in My Sql 5.0 Faq - Stored Procedures. 
4.4) No sample in documentation nor in distribution. 
5) This issue seems to be very close from bug #14635.

I wait for your answer, which may include a sample showing me it is working properly.

Jean-Pierre DUVAL - Product Manager - www.up-comp.com

We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.

Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/

Thank you for your interest in MySQL.

MySQL can call stored procedure with OUT parameters from application which uses C API. Just as you do it in the mysql command line client.

Any news about this BUG. 

And my friendly advice to "Sveta Smirnova", please try to test what the users bring before teaching them that they are submitting feature requests.

If I remember well, the only solution I found at this time was to automatically transform the following order given by a user to our 5GL database engine:

CALL scott.MaProcedure(10,@SalaireMin,@SalaireMax);

into three orders when there is one OUT / IN OUT parameter:

SET @P1=0.0, @P2=0.0;
CALL scott.MaProcedure(10,@P1,@P2);
SELECT @P1, @P2;

Not only it is not efficient since we submit three orders instead of one but also it was not documented into My Sql C API documentation for a workaround that is not trivial.

My Sql should support the first syntax like any other major database engines do for a long time - DB2, Informix, MaxDB, Oracle, Postgress, Sql Server, Sybase. My Sql should be distinct from others competitors by good and easy functions instead of strange behaviors that can be tagged as a bug by a user.

Here are important functions, according the the My Sql's 5.0.x offer, that we can expect to:
- Array fectch for Select.
- Bulk processing for Update, Delete, Insert.
- Xa support.

I hope this post will help you.

I am with the original poster on this.
This is not just a bug but a show stopper.
Trying to switch a dot net app. with bound controls from SQLServer cannot be done until this behaviour conforms.