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 9:57	Modified:	11 Nov 11:10
Reporter:	Gert Brigsted
Status:	To be fixed later
Category:	Connector/Net	Severity:	S3 (Non-critical)
Version:	5.0.27	OS:	Microsoft Windows (Windows XP Pro)
Assigned to:		Target Version:
Tags:	LastInsertedId, MySqlCommand, Connector/Net

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