output parameter example
output parameter example
Are there any examples of using an output parameter with MySQLDirect.NET?
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: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: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(IN param1 INTEGER(11), OUT param2 INTEGER(11))
BEGIN
SET param2=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("call testproc(10, @param2);select @param2", myConn);
using (IDataReader reader = command.ExecuteReader()) {
if (reader.Read())
Console.WriteLine("@param2 = " + reader[0]);
}
myConn.Close();
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();