Obtain ID after INSERT in Output CommandType.Text parameter

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for MySQL
Post Reply
asapronov
Posts: 5
Joined: Wed 01 Aug 2012 13:02

Obtain ID after INSERT in Output CommandType.Text parameter

Post by asapronov » Wed 12 Sep 2012 14:11

Hi

I am trying to execute SQL code like this:

INSERT INTO test.table1(`Name`, `X`, `Y`, `Geometry`) VALUES('hello4', 0.5, 0.5, NULL );
SET @MyLastID = LAST_INSERT_ID();

in _cmd.ExecuteNonQuery();

_cmd is MySqlCommand with CommandType.Text
@MyLastID - MySqlParameter with Output direction

But I got error on setting output parameter

Is there a way how to implement output parameter value retrieving without stored procedure and without using of DataReader ? (My project architecture requirement)

Thanks

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Obtain ID after INSERT in Output CommandType.Text parameter

Post by Shalex » Thu 13 Sep 2012 12:18

MySQL does not support OUT parameters in protocol yet. dotConnect for MySQL allows to handle this situaltion via reader (docs).

As a workaround, try the following code:

Code: Select all

        using (MySqlConnection conn = new MySqlConnection("server=db;port=3309;uid=root;pwd=root;database=test;")) {
            conn.Open();
            MySqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "insert into testtable (field) values ('myvalue'); select LAST_INSERT_ID()";
            int i3 = Convert.ToInt32(cmd.ExecuteScalar());
        }

asapronov
Posts: 5
Joined: Wed 01 Aug 2012 13:02

Re: Obtain ID after INSERT in Output CommandType.Text parameter

Post by asapronov » Thu 13 Sep 2012 13:30

OK

Yesterday I already read this doc, but was not sure it is actual till now.
Because in another doc I saw:
Gets or sets a value indicating whether the parameter is input-only, output-only, bidirectional, or a stored procedure return value parameter. As of MySQL version 4.1 and earlier, input-only is the only valid choice.
http://dev.mysql.com/doc/connector-net/ ... -direction

I have already used workaround similar like yours.
But now I am sure this was correct way.
Thanks a lot

asapronov
Posts: 5
Joined: Wed 01 Aug 2012 13:02

Re: Obtain ID after INSERT in Output CommandType.Text parameter

Post by asapronov » Thu 13 Sep 2012 13:38

Ah!

It is another netConnector for MySql!
The document I mentioned relates to another .NET Connector, not by Devart.
This was part of reason of my misunderstanding.
So I use theirs dotNetConnector, not yours.

But anyway your answer is important because if it is really not implemented in protocol, then their connector most probably can't get output parameters too.

Sorry

Post Reply