MySQL Bugs: #40496: Problems with output parameters

Bug #40496	Problems with output parameters
Submitted:	4 Nov 2008 12:12	Modified:	17 Apr 2009 15:40
Reporter:	Faik OZGUR	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	5.2.3	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	net connector, ParameterDirection

Description:
I have a problem with output parameters.

I'm running code sniplet coded in "How to repeat section".

But in MySqlHelper.ExecuteNonQuery statement i get following error:
"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 'NULL = LAST_INSERT_ID()' at line 1"

Output parameter PARM_id has been parsed/serialized incorectly. 

I have tried to execute this code with Connector-5.2.3 and Connector-5.0.9.

How to repeat:
        private const String PARM_id = "?id_number";
        private const String PARM_deger = "?deger";

        private const String SQL_ADD = @"INSERT INTO Test3Table (deger) VALUES " + PARM_deger + "); SET " + PARM_id + " = LAST_INSERT_ID(); ";

        public int InsertRow(int deger)
        {
            MySqlParameter[] parms = new MySqlParameter[]{
                new MySqlParameter(PARM_id, MySqlDbType.Int32),
                new MySqlParameter(PARM_deger, MySqlDbType.Int32),
            };

            int index = 0;
            parms[index++].Direction = ParameterDirection.Output;
            parms[index++].Value = deger;

            MySqlHelper.ExecuteNonQuery(MySQLHelper.MySQLDBConnectionString, SQL_ADD, parms);

            return (int)parms[0].Value;
        }

to create test table

CREATE TABLE `Test3Table` (
  `id` int(11) NOT NULL auto_increment,
  `deger` int(11) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Hi Faik and thanks for your report.

I must be tired as I do not understand what is it about... Can you please attach complete, one procedure, test case with table and SP DDL so I can test?

I planning to use Mysql Connector.NET library in my C# application.

I have mysql table which can be created with the following sql statement: 
CREATE TABLE `Test3Table` (
  `id` int(11) NOT NULL auto_increment,
  `deger` int(11) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Im trying to insert a row to this table and get the id of the inserted row with the follwing c# code:

-------- code of Test.cs --------------

using System.Text;
using System.Data;
using MySql.Data.MySqlClient;

namespace DataAccessLibrary.Operations
{
    public class Test.cs
    {
       private const String PARM_id = "?id_number";
        private const String PARM_deger = "?deger";

        private const String SQL_ADD = @"INSERT INTO Test3Table (deger) VALUES " +
PARM_deger + "); SET " + PARM_id + " = LAST_INSERT_ID(); ";

        public int InsertRow(int deger)
        {
            MySqlParameter[] parms = new MySqlParameter[]{
                new MySqlParameter(PARM_id, MySqlDbType.Int32),
                new MySqlParameter(PARM_deger, MySqlDbType.Int32),
            };

            int index = 0;
            parms[index++].Direction = ParameterDirection.Output;
            parms[index++].Value = deger;

            MySqlHelper.ExecuteNonQuery(MySQLHelper.MySQLDBConnectionString, SQL_ADD,
parms);

            return (int)parms[0].Value;
        }
    }
}

-------- end of code of Test.cs --------------

Faik

You are trying to use output parameters incorrectly.  output parameters are only usable with stored procedures.  you cannot use them with select statements such as in select @myparm = <some select statement>

Reggie,

The following link uses Output parameter just as Faik thinks it shoud be used:
http://weblogs.asp.net/andrewrea/archive/2008/02/19/examples-of-using-system-data-paramete...

The difference is that the last example uses SQL Server, but it works because SqlClient namespace implements Parameter class to allow to use Output parameters in text commands. MySql.Data implementation has a different behavior and only allows to use Output parameters in stored procedures, but why? 

Shouldn't all implementations of Parameter class behave the same way?

I don't see a strong reason to avoid implementing the right behavior on Parameter class, so I still think this is a bug instead of a design restriction.

Dax, this is a server problem which none of connectors can work around reliably. Please see discussion in http://bugs.mysql.com/17898 and many many other reports.

Faik

You are assuming that the server is capable of this.  Open up a MySQL command line client and try this:

select @myvar = 1;

What you'll get is a NULL value.  At least that is what I get on 5.1.30 x64.  Right now the server doesn't allow me to support such constructs.  You would have to use 2 sql statements.  set @myvar = 1; select @myvar

Reggie,

Im using SET @var = .... statement and i think it will work.

Anyway,
Workaround: Im using "INSERT INTO ..... ; SELECT LAST_INSERT_ID()" statement as CommandText and then executing ExecuteReader method of DBCommand.