Devart Forums

Posted: **Tue 20 Dec 2005 18:32**

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

Posted: **Wed 21 Dec 2005 08:16**

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();

Devart Forums

output parameter example

output parameter example