MySQL Bugs: #13590: ExecuteScalar returns only Int64 regardless of actual SQL type

Bug #13590	ExecuteScalar returns only Int64 regardless of actual SQL type
Submitted:	28 Sep 2005 23:01	Modified:	22 May 2006 16:50
Reporter:	[ name withheld ]	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S1 (Critical)
Version:	1.0.5	OS:	Windows (Window XP)
Assigned to:	Reggie Burnett	CPU Architecture:	Any

Description:
I am attempting to use execute scalar to execte a stored procedure and return a simple value:

CREATE PROCEDURE `mydb`.`myProc`
(
)
BEGIN
	DECLARE myVar INT;
	
	SET MyVar := 1;	
		
	SELECT myVar;
END

Regardless of the numerical type declared for 'myVar' (INT, BIGINT, TINYINT, SMALLINT, MEDIUMINT), ExecuteScalar() always returns a result casted to a .NET Int64!  I expected an Int32 from the procedure definition above.

Likewise if I do a 'SELECT id from TABLE' call, and 'id' is an 'int' type column in my database, Int64 is returned.  So basically, always an Int64 for SQL declared variables or database type mappings.

I understand that the return byte of the column field meta data is used to determine the MySqlDbType to use, and I have debugged this code and for sure it always an Int64.  Additionally the values I get for the ColumnLength seem rather random and there may be a bug there too.

Anyone know of this problem, a fix, or any help at help at all?

Regards,

---
Neil

How to repeat:
Create this stored procedure in MySQL 5.0:

CREATE PROCEDURE `mydb`.`myProc`
(
)
BEGIN
	DECLARE myVar INT;
	
	SET MyVar := 1;	
		
	SELECT myVar;
END

Call it using ExecuteScalar from some .NET code.  The return type will be Int64.

Thanks for bug report. I was able to reproduce it. My test case is attached.

Test case

Attachment: 13590.zip (application/x-zip-compressed, text), 5.58 KiB.

Neil

This is a problem in the 5.0 server and not in the connector.  You can work around this by using ExecuteReader and the GetInt32 methods.  This should be fixed by the time 5.0 is released.

Thanks.  Any chance of sending me the fix so I can compile my own version from the source, unfortunately our particular solution does not permit using the ExecuteReader workaround at this time.

If not, do you have any expected date for the official release which will contain this fix (approximately, of course)?

---
NC

Neil

There is no fix to email.  It still needs to be fixed in the server.

Thank you for taking the time to report a problem.  Unfortunately
you are not using a current version of the product your reported a
problem with -- the problem might already be fixed. Please download
a new version from http://www.mysql.com/downloads/

If you are able to reproduce the bug with one of the latest versions,
please change the version on this bug report to the version you
tested and change the status back to "Open".  Again, thank you for
your continued support of MySQL.

Additional info:

This was a server bug that is fixed now.

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/6727