Obtain ID after INSERT in Output CommandType.Text parameter

Obtain ID after INSERT in Output CommandType.Text parameter

Postby 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
asapronov
 
Posts: 5
Joined: Wed 01 Aug 2012 13:02

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

Postby 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());
        }
Shalex
Devart Team
 
Posts: 7533
Joined: Thu 14 Aug 2008 12:44

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

Postby 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/en/connector-net-ref-mysqlclient-mysqlcommandmembers.html#connector-net-ref-mysqlclient-mysqlparameter-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

Postby 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
asapronov
 
Posts: 5
Joined: Wed 01 Aug 2012 13:02


Return to dotConnect for MySQL