Mysql stored procedure OUTPUT params

Mysql stored procedure OUTPUT params

Postby have_problem » Sun 19 Feb 2006 22:23

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.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.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"

What should I do to get output params?

Postby Serious » Mon 20 Feb 2006 06:42

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))
  SET param2=param1*2;
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;


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

Return to dotConnect for MySQL