output parameter example

output parameter example

Postby kevink » Tue 20 Dec 2005 18:32

Are there any examples of using an output parameter with MySQLDirect.NET?
kevink
 
Posts: 10
Joined: Tue 20 Dec 2005 18:30

Postby Serious » 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();
Serious
 


Return to dotConnect for MySQL