MySQL Bugs: #8667: OUT parameters are not being valued

Bug #8667	OUT parameters are not being valued
Submitted:	22 Feb 2005 3:00	Modified:	23 Feb 2005 14:47
Reporter:	Steven Toth	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	1.0.4	OS:	Windows (Windows XP Pro)
Assigned to:	Reggie Burnett	CPU Architecture:	Any

Description:
I have a simple SP created in MySql 5.0.2 that SETs an OUT parameter to the current date/time. It works fine from the MySql Command Line, but does not work via the MySql Connector/Net.

How to repeat:
1. Create simple SP to get current date/time

DELIMITER $$

DROP PROCEDURE IF EXISTS `mysql`.`getCurrentDateTime`$$
CREATE PROCEDURE `mysql`.`getCurrentDateTime`(OUT out_current_datetime DATETIME)
   SET out_current_datetime = NOW()$$

DELIMITER ;

2. Execute from MySql Command Line
mysql> call getCurrentDateTime(@a);
Query OK, 0 rows affected (0.01 sec)

mysql> select @a;
+---------------------+
| @a                  |
+---------------------+
| 2005-02-21 21:27:50 |
+---------------------+
1 row in set (0.00 sec)

mysql> call getCurrentDateTime(@a);
Query OK, 0 rows affected (0.00 sec)

mysql> select @a;
+---------------------+
| @a                  |
+---------------------+
| 2005-02-21 21:28:47 |
+---------------------+
1 row in set (0.00 sec)

Call SP from .NET class using MySql Connector/NET...
<See attached .NET solution>

VB .NET sample to call SP described in Bug report.

Attachment: TestOutParam.zip (application/x-zip-compressed, text), 7.52 KiB.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Steven

I fixed a problem with the code however you also had a problem in your sample code.  You are trying to retrieve the value of the out parameter before the reader is closed.  In this case you should be using ExecuteNonQuery to run the SQL.  However,  you can use ExecuteReader but you need to close the reader before accessing the out parameters.