Why does ODBC C# return empty string while using OUT parameter in Stored Procedure in MySQL?

Question

I have a stored procedure with two OUT parameters 'test1' and test2' in MySQL running server 5.6

    CREATE PROCEDURE `test_procedure`(OUT test1 varchar(20),OUT test2 int)
    BEGIN
     SET test1 = 'testing variable 1';
     SET test2 = 3;
    END

I have ODBC driver 5.3 installed on my computer. I am using the following code to bind the parameters and get the output value:

   OdbcCommand cmdTest = new OdbcCommand();
   cmdTest.Connection = this.conn;
   cmdTest.CommandText = "{call test.test_procedure (?, ?)}";

   cmdTest.Parameters.Add(@"test1", OdbcType.VarChar, 20).Direction = ParameterDirection.Output;
   cmdTest.Parameters.Add(@"test2", OdbcType.Int).Direction = ParameterDirection.Output;

   cmdTest.CommandType = CommandType.StoredProcedure;
   using (OdbcDataReader returnRead = cmdTest.ExecuteReader())
   {
     while (returnRead.Read())
     {
      //Do something
     }
   }

   string test1 = cmdTest.Parameters[0].Value.ToString();
   string test2 = cmdTest.Parameters[1].Value.ToString();

However, the value for strings test1 and test2 in C# code are returned as empty strings. How can I get the values that I set in the stored procedure?

I have also tried ExecuteNonQuery() but it gives the same result. I know I am connecting to the database since I am able to use SELECT and INSERT with ExecuteReader().

@Shannon I thought MySQL does not allow RETURN. If I use SELECT test1, test2 then do I need an OUT parameter? — CoderBapu, Sep 06 '16 at 17:22
Nope -- return is fine in MySQL: https://www.techonthenet.com/mysql/loops/return.php — Shannon Holsinger, Sep 06 '16 at 17:24
@ShannonHolsinger My mistake MySQL does not allow RETURN in Stored Procedure but in a Function. If I try to use RETURN in Stored Procedure I get the message "Object's DDL contains syntax errors" — CoderBapu, Sep 06 '16 at 17:30
You're right -- I had it confused with function because using OUT in a procedure is not usually done. May I ask - what's the purpose of using OUT? There's usually a much better way of doing it in a procedure. — Shannon Holsinger, Sep 06 '16 at 17:41
@ShannonHolsinger I am developing a program that communicates with the MySQL database to call a stored function or run sql commands. This program will be used by others as a wrapper to write and read from the database. I was hoping to create a more versatile function in C# so that I wont have to modify it later. OUT is not required now but I will have to go back to the code if we decide to use it. — CoderBapu, Sep 06 '16 at 18:15
@Drew Ahh.. here's the other problem. I am not sure if they will stick to MySQL or migrate it to another database. — CoderBapu, Sep 06 '16 at 18:24
Well here is the link (to an answer of mine) for the most up-to-date Oracle download of the .net mysql connector ... how to write to an OUT parameter: http://stackoverflow.com/a/38706288 .... so it is the .net connector . — Drew, Sep 06 '16 at 18:28

score 1 · Accepted Answer · answered Sep 07 '16 at 01:10

There is some problems using OUT parameters using MySQL Odbc in C#, but it works for me using a select at the end of procedure:

CREATE PROCEDURE `test_procedure`()
BEGIN
   DECLARE test1 varchar(50);
   DECLARE test2 int;

   SET test1 = 'testing variable 1';
   SET test2 = 3;

   SELECT test1,test2;
END $$

And the C# code:

OdbcCommand cmdTest = new OdbcCommand();
cmdTest.Connection = MyConnection;
cmdTest.CommandText = "call test.test_procedure()";

using (OdbcDataReader returnRead = cmdTest.ExecuteReader())
{
    while (returnRead.Read())
    {
        string test1 = returnRead.GetString(0); //'testing variable 1'
        int test2 = returnRead.GetInt32(1); //3
        //Do something
    }
}

Thanks, I did not know of the `GetString` and `GetInt32` method. If I have to return a result set along with the two out variables, do I have to `SELECT` a full outer join with `test1`, `test2` and the result set? — CoderBapu, Sep 07 '16 at 17:16
Yes, you can add more columns :) , or use a full select with joins and so. — Ivan Cachicatari, Sep 07 '16 at 22:23

Why does ODBC C# return empty string while using OUT parameter in Stored Procedure in MySQL?

1 Answers1