MySQL Bugs: #27668: FillSchema and Stored Proc with an out parameter

Bug #27668	FillSchema and Stored Proc with an out parameter
Submitted:	5 Apr 2007 14:44	Modified:	3 May 2007 11:23
Reporter:	Guillaume Matte	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	DB 5.0.37 community, Connector : 5.0.6	OS:	Windows (2000 pro)
Assigned to:		CPU Architecture:	Any
Tags:	FillSchema, stored procedure

Description:
Exception raised when using MySQLDataAdapter.FillSchema() on a Stored Procedure command that have an output value. Though, it works fine with no parameter and input parameter.

My development environment : 
Windows 2000 Pro
VS2005 .NET 2.0 C#
MySQL 5.0.37-community-nt
c/.NET 5.0.6

Error Message : Invalid attempt to access a field before calling Read()

How to repeat:
### PREPARE MySQL TEST ###
use test;

DROP table IF EXISTS  ATable;
create table ATable( APKColumn INT PRIMARY KEY, AStringColumn VARCHAR(20) );

insert into ATable VALUES ( 1, 'New' );
insert into ATable VALUES ( 2, 'Hello' );
insert into ATable VALUES ( 3, 'Friend' );
insert into ATable VALUES ( 4, 'My' );

DROP table IF EXISTS  AnOtherTable;
create table AnOtherTable( AFKColumn INT, Ordering INT );

insert into AnOtherTable VALUES ( 1, 3 );
insert into AnOtherTable VALUES ( 2, 1 );
insert into AnOtherTable VALUES ( 3, 4 );
insert into AnOtherTable VALUES ( 4, 2 );

DELIMITER $$

DROP PROCEDURE IF EXISTS  AStoredProc $$
CREATE PROCEDURE AStoredProc( OUT OutValue INT )
BEGIN
	DECLARE EXIT HANDLER FOR NOT FOUND
	SET OutValue = 666;
	
	SET OutValue = 0;
	
	SELECT a.AStringColumn
	FROM ATable a INNER JOIN AnOtherTable b
	ON a.APKColumn = b.AFKColumn
	ORDER BY b.Ordering ASC;
END $$

DELIMITER ;

### C# test code ###
MySqlConnection conn = new MySqlConnection( "server=localhost;database=test;Uid=root;Pwd=toor" );
MySqlCommand com = new MySqlCommand( "AStoredProc", conn );
com.CommandType = CommandType.StoredProcedure;

MySqlParameter p = com.Parameters.Add( new MySqlParameter( "?OutValue", MySqlDbType.Int32 ) );
p.Direction = ParameterDirection.Output;

MySqlDataAdapter da = new MySqlDataAdapter( com );
DataTable dt = new DataTable();

conn.Open();

com.ExecuteNonQuery();

da.Fill( dt );
da.FillSchema( dt, SchemaType.Mapped );
Console.ReadLine();

Sorry for the long SQL code, I didn't copy the brief version...

Hi Guillaume and thanks for excellent report. You are right, this should work...

Verified as described:
 - MySQL server 5.0.38BK on WinXP Pro SP2 localhost
 - latest c/NET 5 sources (SVN) with NET fw 2

Simpler test case:
    MySqlConnection conn = new MySqlConnection();
    conn.ConnectionString = "DataSource=localhost;Database=test;UserID=root;Password=;PORT=3306";
    conn.Open();
    MySqlCommand cmdCreateTable = new MySqlCommand("DROP TABLE IF EXISTS Bug27688", conn);
    cmdCreateTable.CommandTimeout = 0;
    cmdCreateTable.ExecuteNonQuery();
    cmdCreateTable.CommandText = "CREATE TABLE Bug27688 (id INT AUTO_INCREMENT, PRIMARY KEY (id)) ";
    cmdCreateTable.ExecuteNonQuery();
    cmdCreateTable.CommandText = ("drop procedure if exists spTestBug27688");
    cmdCreateTable.ExecuteNonQuery();
    cmdCreateTable.CommandText = ("CREATE PROCEDURE spTestBug27688 (OUT id INT) " +   "BEGIN INSERT INTO Bug27688 VALUES (NULL); SET id=520; END");
    cmdCreateTable.ExecuteNonQuery();
    MySqlCommand cmd = new MySqlCommand("spTestBug27688", conn);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("?id", MySqlDbType.Int32);
    cmd.Parameters[0].Direction = ParameterDirection.Output;
    MySqlDataAdapter da = new MySqlDataAdapter(cmd);
    DataTable dt = new DataTable();
    cmd.ExecuteNonQuery();
    da.Fill(dt);
    da.FillSchema(dt, SchemaType.Mapped);
    Console.ReadLine();

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/25912

Fixed in 5.0.7 and 5.1.1

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/25945

A note has been added to the 5.0.7 and 5.1.1 changelogs.