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:Microsoft Windows (Window XP)
Assigned to: Reggie Burnett CPU Architecture:Any

[28 Sep 2005 23:01] [ name withheld ]
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.
[30 Sep 2005 6:45] Vasily Kishkin
Thanks for bug report. I was able to reproduce it. My test case is attached.
[30 Sep 2005 6:45] Vasily Kishkin
Test case

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

[3 Oct 2005 14:57] Reggie Burnett
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.
[3 Oct 2005 20:31] [ name withheld ]
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
[3 Oct 2005 20:34] Reggie Burnett
Neil

There is no fix to email.  It still needs to be fixed in the server.
[22 May 2006 16:50] Reggie Burnett
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.
[22 May 2006 16:52] Bugs System
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