MySQL Bugs: #14836: Can't get output parameter after executing SP by DataReader

Bug #14836	Can't get output parameter after executing SP by DataReader
Submitted:	10 Nov 2005 15:52	Modified:	20 Sep 2006 22:17
Reporter:	Markus Popp	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	1.0.5/MySQL 5.0.15	OS:	Windows (Windows)
Assigned to:	Reggie Burnett	CPU Architecture:	Any

Description:
This is a follow up to bug report 14835.

After adding the select privilege for the user user@localhost to the mysql.proc table, I was able to execute the Stored Procedure testProc and got the results.

However, it failed to return the output parameter of the testProc procedure in this line:

int outputParameter = (int)cmd.Parameters[1].Value;

and returned a NullReferenceException.

However, after changing the bottom to

cmd.ExecuteNonQuery();

int outputParameter = (int)cmd.Parameters[1].Value;

Console.WriteLine("The value of the output parameter is " +
				outputParameter);

con.Close();
Console.ReadLine();

I got the appropriate output:

The value of the output parameter is 25

How to repeat:
The code of the Stored Procedure is:

DELIMITER $$

DROP PROCEDURE IF EXISTS `mpopp_test`.`testProc`$$
CREATE PROCEDURE `testProc`(IN _val INT, OUT square INT)
    SQL SECURITY INVOKER
BEGIN
  select concat(_val, ' times two is ', (_val * 2)) as multiply;
  select concat(_val, ' divided by two is ', (_val / 2)) as divide;
  select _val * _val into square;
END$$

DELIMITER ;

Suggested fix:
It seems, ExecuteNonQuery() returns the output parameter, while ExecuteReader() does not. I think, ExecuteReader() should return the output parameter as well.

I've got same result like in bug 14835. How were you able to execute the Stored Procedure testProc and got the results ?

The stored procedure can be executed from MySQL monitor without any problems (as mentioned in Bug report #14835). In C# I had to make a workaround. I first executed the SP using ExecuteNonQuery() to get the result from the output parameter and afterwards executed it a second time to see the results using ExecuteReader(). It did not work if I first used ExecuteReader and then ExecuteNonQuery().

Here's the complete code of the example that produced the desired output:

using System;
using System.Data;
using MySql.Data.MySqlClient;

namespace TestSP
{
	class TestSP
	{
		private MySqlConnection con = null;		
		
		[STAThread]
		static void Main(string[] args)
		{
			bool error = false;
            int param = 0;

			try
			{
				param = Int32.Parse(args[0]);
			}
			catch (Exception e)
			{
				Console.WriteLine("You must pass an integer value " +
					"as parameter!");
			
				error = true;
			}

			if (! error)
			{
				new TestSP(param);
			}
		}

		public TestSP(int param)
		{
			// set up the connection
			if (setConnection())
			{
				MySqlCommand cmd = createCommandObject(param);

				// first get the output parameter
				int outputParameter = getOutputParameter(cmd);

				if (outputParameter != -1)
				{
					// set up the SQL command
					cmd = createCommandObject(param);

					// call the Stored Procedure setting the
					// input parameter
					callSP(cmd);

					// output the output parameter
					Console.WriteLine("The value of the output parameter is " +
						outputParameter);
				}
		
				// close the connection
				closeConnection();
			}
		}

		private bool setConnection()
		{
			// set up connection
            string constring = "Data Source=localhost;" +
				"User Id=root;" +
				"Password=pass;" +
				"Database=test";

			con = new MySqlConnection(constring);

			try 
			{
				con.Open();
			} 
			catch (MySqlException e)
			{
                Console.WriteLine("Could not establish connection!");
				return false;
			}

			return true;
		}

		private MySqlCommand createCommandObject(int param)
		{
			// create command object and set values
			MySqlCommand cmd = new MySqlCommand();
				
			cmd.Connection = con;
			cmd.CommandText = "testProc";
			cmd.CommandType = CommandType.StoredProcedure;

			// set input parameter
			cmd.Parameters.Add("?_val", param);
			cmd.Parameters["?_val"].Direction = ParameterDirection.Input;

			// set output parameter
			cmd.Parameters.Add("?square", MySqlDbType.Int32);
			cmd.Parameters["?square"].Direction = ParameterDirection.Output;

			return cmd;
		}

		private bool callSP(MySqlCommand cmd)
		{
			try
			{
				bool moreResults = true;

				// execute Stored Procedure
                MySqlDataReader reader = cmd.ExecuteReader();

				// repeat while as long as there are more results
				while (moreResults)
				{
					// output result
					while (reader.Read())
					{
						Console.WriteLine(reader.GetString(0));
					}

					// ask if there are more results
					moreResults = reader.NextResult();
				}
			}
			catch (MySqlException e)
			{
                return false;
			}

			return true;
		}

		private int getOutputParameter(MySqlCommand cmd)
		{
			try
			{
                // execute Stored Procedure to get output parameter
				cmd.ExecuteNonQuery();

				// store output parameter
				object outputParameter = cmd.Parameters[1].Value;

				return (int)outputParameter;
			}
			catch (MySqlException e)
			{
				return -1;
			}
		}

		private void closeConnection()
		{
			// close the connection
			try 
			{
				con.Close();
			}
			catch (MySqlException ignored)
			{
			}
		}
	}
}

Thanks for your test case. I was able to reproduce the bug. I had to change some code in your test case, because You wrote working way. The changed test case is attached. I tested on 1.0.6.

Test case

Attachment: 14836.zip (application/force-download, text), 6.46 KiB.

Am getting the error: 42000 select command denied to user username@'IP' for table proc

when trying to execute a stored procedure using connector 1.0.7. 

on examination it appears that the code (StoredProcedures.cs GetParameterList()) queries this table (mysql.proc) for calling the sp, however, by default, users do not have access to this table (this may have been a recent change). In shared hosting environments, it is not possible to obtain these rights. The result is that the connector cannot be used for mysql 5 sps in a shared hosting environment.

What is the status of this?

We are having the same problem too.
Any status on this?

I'd like to add my "vote" to have this addressed soon. As mentioned, it's a real PITA for those of us using the connector with SPs in a shared hosting situation where we don't have access to the mysql.proc table.

Another vote.

In a production, .net connector can not be used for SP unfortunately.

Just in case ite helps anyone, there is a fudge described here:
http://forums.mysql.com/read.php?98,41655,41655,quote=1

created by me which I have been using in development for a month now with no issues (though it may only work with my particular .Net methods)

FYI:

I have found that this problem appears to be unique to Linux.  I have been developing a C# application using a Windows MySQL server.  All the stored procedures that I call - parameters & readers - work fine.

When I moved the Database to a Linux box I encountered this problem.  I tried it again on another machine with the same result.

Fred

The previous comment that this is limited to linux is false. I am having this issue on windows 2003.

This regards the prior post as to this being a linux only issue:

I have stored procedures running just fine on my development machine which is Windows XP Pro and also on three different Windows 2000 servers.

I have not tried to install MySQL on Windows 2003 as indicated in the prior message.

Just to add another voice, I am seeing this problem on Windows Server 2003, .NET Connector 1.0.7, and MySQL 5.0.18. I am able to add the select privilege on MySQL.proc in my situation, but I would certainly prefer that my web application account *not* have any privileges to the MySQL database.

Thank you for your bug report. This issue has already been fixed in the latest released version of that product, which you can download at

  http://www.mysql.com/downloads/

haha!!!!

here's the trick...

the output parameters are returned within a seperate resultset - so after your ExecuteReader() statement bind the reader to your grid/repeater or whatever and then do datareader.nextresult(); 

-- always willing to help!
Ratnakar Garikipati

sorry mate...I wasn't quite thinking, I didnot complete...

after the datareader.nextresult() statement then read the values off the output parameters and it works

Ratnakar

While this may appear to work, you should not read them this way.  The reason is that MySQL doesn't support output parameters so we have to implement them using some trickery.  This trickery sometimes causes the loss of type.  If you read the resultset yourself, you will be exposed to this loss of type.  The documentation states that output and return parameters are not valid until the reader has been closed.  So the correct course of action would be the following:

reader = ExecuteReader();
reader.Read();
... use resultset...
reader.Close();
... use output and return parms....

I know this is closed; however, it should be noted that this problem is not specific to MySQL.  

Rather the problem appears to be either with the version of ADO.Net or the Enterprise Library installed on the local machine.  I have duplicated this exact same problem with MS SQL 2005 as the backend instead of MySQL.