I have a mysql (v5.6.25 running on windows 8.1) stored proc which does the following:
DELIMITER $$
CREATE PROCEDURE `usp_GetData`(IN param1 int, IN param2 datetime, IN param3 datetime, OUT outparam varchar(2054))
BEGIN
SELECT things FROM stuff WHERE a = param1 AND b = param2 AND c = param3;
SET outparam = (SELECT GROUP_CONCAT(otherthings) FROM stuff WHERE a = param1 AND b = param2 AND c = param3);
END;
I call it from c# via mysqlconnector using the following:
MySql.Data.MySqlClient.MySqlCommand command = new MySqlCommand();
command.CommandText = StoredProcName;
command.CommandType = System.Data.CommandType.StoredProcedure;
MySql.Data.MySqlClient.MySqlParameter outparamobj = new MySqlParameter("outparam",MySqlDbType.VarChar);
outparamobj.Direction = System.Data.ParameterDirection.Output;
command.Parameters.Add(outparamobj);
command.Parameters.AddWithValue("param1", 1);
command.Parameters.AddWithValue("param2", startdate);
command.Parameters.AddWithValue("param3", enddate);
command.Connection = DBConn; //object set elsewhere but is an open connection.
using (MySql.Data.MySqlClient.MySqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
//populate data object
}
//to make out parameter work - ensure the reader is closed.
if(!reader.IsClosed) reader.Close();
string outparamvalue = command.Parameters["outparam"].Value.ToString();
}
The value of the parameter remains dbnull in the mysqlparameter object, even though it returns a value if i make the call in MySQL Workbench. If i change the out parameter type to int and return a count of items, this will return successfully so i suspect i am not defining the type of the parameter correctly but have fiddled about with no success.
Any help much appreciated!
