output parameter example

[kevink]

Are there any examples of using an output parameter with MySQLDirect.NET?

[Serious]

Unfortunately MySQL does not support OUT parameters in protocol yet. However MySQLDirect .NET allows to handle OUT parameters using server-side variables. Consider the following CREATE statement:

Code: Select all

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

The procedure takes input parameter, multiplies it by two, and stores outcome in the out parameter. To retrieve value of out parameter declare temporary server variable and then query its value as follows:

Code: Select all

MySqlConnection myConn = new MySqlConnection("user id=root;database=demobase;host=localhost;password=root");
myConn.Open();
MySqlCommand command = new MySqlCommand("call testproc(10, @param2);select @param2", myConn);
using (IDataReader reader = command.ExecuteReader()) {
  if (reader.Read())
    Console.WriteLine("@param2 = " + reader[0]);
}
myConn.Close();

Same technique can be used to handle INOUT parameters. To assign initial value to the parameter use SET statement that preceedes a call to stored procedure. The next example shows how to reform previous call to stored procedure using single INOUT parameter.

Code: Select all

  CREATE PROCEDURE testproc(INOUT param1 INTEGER(11))
  BEGIN
  SET param1=param1*2;
  END

Code: Select all

MySqlConnection myConn = new MySqlConnection("user id=root;database=demobase;host=localhost;password=root");
myConn.Open();
MySqlCommand command = new MySqlCommand("set @param1=11;call testproc(@param1);select @param1", myConn);
using (IDataReader reader = command.ExecuteReader()) {
  if (reader.Read())
    Console.WriteLine("@param1 = " + reader[0]);
}
myConn.Close();