MySQL Bugs: #26139: MySqlCommand.LastInsertedId doesn't work for stored procedures

Bug #26139	MySqlCommand.LastInsertedId doesn't work for stored procedures
Submitted:	7 Feb 2007 8:57	Modified:	21 Jan 2014 23:07
Reporter:	Gert Brigsted	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	5.0.27	OS:	Windows (Windows XP Pro)
Assigned to:		CPU Architecture:	Any
Tags:	Connector/Net, LastInsertedId, MySqlCommand

Description:
When I execute a stored procedure that performs an INSERT, I'm trying to use the LastInsertedId property of the MySqlCommand to retrieve the autogenerated id of the row inserted. But the property always return 0. If I perform the same insert using a Text type command, it returns the correct id. I am using the Connector/NET 5.0.3 with C# Express (.NET 2.0).

How to repeat:
I have the following test table (named test):

ID (int, auto increment, primary key) (default setting for the first row added when using MySQL Query Browser)
Name (varchar(200))

And the following stored procedure:

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`testproc` $$
CREATE PROCEDURE `test`.`testproc` (_ID INT, _Name VARCHAR(200))
BEGIN
insert into test(ID, Name) values(_ID, _Name);
END $$

DELIMITER ;

The following code prints the correct id:

    protected void Page_Load(object sender, EventArgs e)
    {
        MySqlConnection conn = new MySqlConnection("foobar"); // Connection not a problem, rows are actually inserted
        MySqlCommand cmd = new MySqlCommand("insert into test(ID, Name) values(0, 'testname')", conn);
        cmd.CommandType = CommandType.Text;

        conn.Open();
        cmd.ExecuteNonQuery();
        conn.Dispose();

        Response.Write(cmd.LastInsertedId);
    }

While the following code always return 0:

    protected void Page_Load(object sender, EventArgs e)
    {
        MySqlConnection conn = new MySqlConnection("foobar");
        MySqlCommand cmd = new MySqlCommand("testproc", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("?_ID", 0);
        cmd.Parameters.Add("?_Name", "testperson");

        conn.Open();
        cmd.ExecuteNonQuery();
        conn.Dispose();

        Response.Write(cmd.LastInsertedId);
    }

I have tried not to add the "?_ID" parameter, and tried adding it as NULL, but that doesn't work. Is there something I am missing here?

Suggested fix:
Make the LastInsertedId return the correct last inserted id for both CommandType.Text and CommandType.StoredProcedure.

Try with this:

MySqlConnection conn = new MySqlConnection("foobar");
MySqlCommand cmd = new MySqlCommand("testproc", conn);
cmd.CommandType = CommandType.StoredProcedure;
//To add:
MySqlParameter pId = new MySqlParameter("?_ID",MySqlDbType.Int32);
pId.Direction = System.Data.ParameterDirection.Output;
cmd.Parameters.Add(pId);

cmd.Parameters.Add("?_Name", "testperson");
conn.Open();
cmd.ExecuteNonQuery();
//It's better to close the connection before you dispose it
conn.Close();
conn.Dispose();
//Get back your id *using a cast*
int id = (int)pId.Value;
Response.Write(id.ToString());

This is a server bug.  Currently, MySQL doesn't properly return generated keys or update counts from a stored procedure.  If that is needed, you will need to use SELECT last_insert_id() into an output param.

I've added a test case to our test suite and will enable it when this is fixed in the server.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/19808

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/19809