Bug #59798 MySqlCommand.CommandType.Text multiple statements, output param won't work
Submitted: 28 Jan 2011 15:40 Modified: 14 Feb 2011 22:04
Reporter: Sean Drun Email Updates:
Status: Not a Bug Impact on me:
None 
Category:Connector / NET Severity:S1 (Critical)
Version:6.3.6 OS:Microsoft Windows (Windows 7 Ultimate)
Assigned to: CPU Architecture:Any
Tags: MySqlCommand MySqlParameter OUTPUT

[28 Jan 2011 15:40] Sean Drun
Description:
MySql server version: 5.5.8
Our project is aimed to multiple DBMS. Currently it work on SQL/Oracle. We are working to add MySql to the list. The problem we encounter is we cannot get back Identity value from dynamic command text with multiple statements. Connect/NET issues an exception {"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"}

How to repeat:
CREATE TABLE `customer` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(40) CHARACTER SET utf8 NOT NULL,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `Name` (`Name`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin;

string connString = @"Server=localhost;User=root;Database=test;Port=3306;Password=sql;
Allow Batch=true;Allow User Variables=true;";
using (MySqlConnection conn = NewMySqlConnection(connString))
{
   MySqlCommand cmd = new MySqlCommand(@"INSERT INTO Customer(Name) VALUES(@Name); SET @Id := last_insert_id();", conn);
   cmd.CommandType = CommandType.Text;
   MySqlParameter paOut = new MySqlParameter("@Id", MySqlDbType.Int32);
   paOut.Direction = ParameterDirection.InputOutput;
   paOut.SourceColumn = "Id";
   cmd.Parameters.Add(paOut);
   MySqlParameter pa = new MySqlParameter("@Name", MySqlDbType.VarString);
   pa.Direction = ParameterDirection.Input;
   pa.Value = "Doe,John";
   cmd.Parameters.Add(pa);
   conn.Open();
   try
   {
      int iRows = cmd.ExecuteNonQuery();
      Console.WriteLine(paOut.Value);
   }
   catch (Exception e)
   {
      Console.WriteLine(e.Message);
   }
   finally
   {
      conn.Close();
   }

Suggested fix:
SQL .NET provider allows multiple statements in CommandText like
"INSERT INTO Customer (Name) VALUES (@Name); SET @Id = SCOPE_IDENTITY();".
ORACLE .NET provider does not provide multiple statements. But ORACLE insert provides returning clause so we can use "INSERT INTO Customer (Name) VALUES (@Name) RETURNING Id INTO @Id" to get Identity value.
Even though MySqlCommand has property 'LastInsertedId' that returns Identity value, this just won't work for the setting this type of MySqlCommand to MySqlDataAdapter.InsertCommand. In that case, we want to update co-related tables in DataSet, get Identity value from root table (table.Rows[0]["Id"] afterwards.

Suggest, fix CommandText allowing multiple statements with parameters defined in .NET program or add RETURNING clause to INSERT statement.

Thanks.
[2 Feb 2011 9:42] Tonci Grgin
Hi Sean and thanks for your report.

Does the top of http://dev.mysql.com/doc/refman/5.5/en/connector-net-faq.html page answer your question?
[4 Feb 2011 14:53] Sean Drun
Thanks for the link of possible get around.
But we are porting working project from MSSQL/ORACLE to MySql,
we don't have freedom of changing coding structure.
We are using MySqlCommand to build CommandText with MySqlParameter
dynamically. The structure of table varies on deployed customer.
One case is using MySqlCommand.ExecuteNonQuery to get back last row id.
The other case is setting MySqlCommand to DataAdapter.InsertCommand with a MySqlParameter.SourceColumn set to Identity column.
The only thing certain is OUTPUT MySqlParameter works only in stored procedure.
In reality, it is just not possible to build stored procedure for all database tables.
[14 Feb 2011 22:04] Reggie Burnett
This is not a bug.  Connector/Net doesn't currently support output parameters when not using stored procedure or stored functions.  We have created an internal worklog to track adding this feature.

The user should be aware that Connector/Net fully supports updating datatables and datasets with generated ids but this is not done with parameters. The user can write his insert statement like this:

"INSERT (col) VALUES (val); SELECT last_insert_id() AS 'Id'"

and then set the UpdatedRowSource property on the command to FirstReturnedRecord and then the command will work with the data adapter and will update the id column of every insert in the data table.
[12 Sep 2012 11:44] Alexander Sapronov
And what about now?

I have exactly same problem as Sean Drun in Jan 2011
I need to get output parameters after call of CommandType.Text
The code architecture can't be changed and it dictates just this way of output data handling. Not using stored procedures and not by DataReader.

Thanks