using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using MySql.Data.MySqlClient;

/*
 * Shows the problem http://bugs.mysql.com/bug.php?id=17046
 */
/* Create a database mysqltest with table name 'foo' before.
DROP TABLE IF EXISTS `mysqltest`.`foo`;
CREATE TABLE  `mysqltest`.`foo` (
  `id` int(10) unsigned NOT NULL default '0',
  `val` int(10) unsigned default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;     
*/
/*
FIX:
   command.cs method Consume()

			// if we were executing a stored procedure and we are out of sql buffers to execute, 
			// then we need to perform some additional work to get our inout and out parameters
//OLD			if (storedProcedure != null && sqlBuffers.Count == 0)
			if (storedProcedure != null && (sqlBuffers == null || sqlBuffers.Count == 0))
				storedProcedure.UpdateParameters(Parameters);
*/

namespace mysqltest
{
    class Program
    {
        readonly static string connstr = "Server=localhost;Database=mysqltest;Username=tom;Password=tom;allowzerodatetime=true;";

        static void Main(string[] args)
        {
            using (MySqlConnection con = new MySqlConnection(connstr))
            {
                con.Open();
                Console.WriteLine("Server version: " + con.ServerVersion);

                CreateSP(con);
                CallSP(con);
            }
        }

        static void AddParam(IDbCommand cmd, string n, object v)
        {
            IDataParameter p = cmd.CreateParameter();
            p.ParameterName = n;
            p.Value = v;
            cmd.Parameters.Add(p);
        }

        static void CallSP(IDbConnection con)
        {
            using (IDbCommand cmd = con.CreateCommand())
            {
                cmd.CommandText = "mySP";
                cmd.CommandType = CommandType.StoredProcedure;
                AddParam(cmd, "pp", 10);
                cmd.Prepare();
                IDataReader rdr = cmd.ExecuteReader();
                ShowResult(rdr);
                rdr.Close(); // <- a simple check in command.cs is missing
            }
        }

        static void CreateSP(IDbConnection con)
        {
            try
            {
                using (IDbCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = "create procedure mySP ( IN pp INTEGER ) " +
                                       "select * from foo where id > pp ";
                    Console.WriteLine("Rows=" + cmd.ExecuteNonQuery());
                }
            }
            catch (Exception) { ; } // was defined before
        }

        static void ShowResult(IDataReader rdr)
        {
            int n = 0;
            while (rdr.Read())
            {
                Console.Write("[" + n + "] "); n++;
                for(int f = 0; f < rdr.FieldCount; f++)
                {
                    if (f > 0)
                        Console.Write(" ");
                    Console.Write(rdr.GetName(f));
                    Console.Write("=");
                    object o = rdr.GetValue(f);
                    Console.Write(o);
                }
                Console.WriteLine("");
            }
        }
    }
}