Mysql stored procedure OUTPUT params

[have_problem]

I have a problem when trying to execute procedure with output params (MySQL - 5.0.18-nt, MySQL Direct NET provider 3.20).
Problem ocures with procedure both via code or Command generator - execute query also does not work ;(
When using SQL comand line it works fine:

Code: Select all

CREATE PROCEDURE `sp_CreateBillNumber`(OUT billNumber varchar(20)) ...........
call sp_CreateBillNumber(@a);
select @a;

How should I use it from C#, I tried this way:

Code: Select all

this.cmdSel_miscellaneus.Parameters.Clear();
this.cmdSel_miscellaneus.ParameterCheck = true;
this.cmdSel_miscellaneus.CommandType = System.Data.CommandType.StoredProcedure;
MySqlParameter myRetParam = new MySqlParameter();
myRetParam.Direction = System.Data.ParameterDirection.Output;
myRetParam.ParameterName = "billNumber";
this.cmdSel_miscellaneus.Parameters.Add(myRetParam);
this.cmdSel_miscellaneus.CommandText = "sp_CreateBillNumber";
// here I got an !!!_E_R_R_O_R_!!! with such message:
// "OUT or INOUT argument 1 for routine db.sp_CreateBillNumber is not a variable"
this.cmdSel_miscellaneus.ExecuteNonQuery();

What should I do to get output params?

[Serious]

Here is a simple example of using out parameters with MySQLDirect .NET:

Code: Select all

  CREATE PROCEDURE testproc(IN param1 INTEGER(11), OUT param2 INTEGER(11))
  BEGIN
  SET param2=param1*2;
  END

Code: Select all

MySqlCommand command = new MySqlCommand("call testproc(10, @param2);select @param2", myConn);
using (IDataReader reader = command.ExecuteReader()) {
  if (reader.Read())
    Console.WriteLine("@param2 = " + reader[0]);
}

For additional information please refer to MySQLDirect .NET documentation (Using Parameters article).

In next version (which will be published this week) you'll be able to use following simplified syntax:

Code: Select all

MySqlCommand command = new MySqlCommand("testproc", connection);
command.CommandType = CommandType.StoredProcedure;

command.Parameters.Add("param1", MySqlType.Int);
command.Parameters["param1"].Value = 10;
command.Parameters.Add("param2", MySqlType.Int);
command.Parameters["param1"].Direction = ParameterDirection.Output;

command.ExecuteNonQuery();

foreach (MySqlParameter parameter in command.Parameters)
  Console.WriteLine(parameter.ParameterName + ":" + parameter.Value.ToString());