| Bug #12781 | Exception asking for non-existent parameter thrown | ||
|---|---|---|---|
| Submitted: | 24 Aug 2005 9:24 | Modified: | 14 Sep 2005 17:16 |
| Reporter: | Paul Reeder | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | Connector / NET | Severity: | S2 (Serious) |
| Version: | 1.0.4 | OS: | Windows (WinXP) |
| Assigned to: | Reggie Burnett | CPU Architecture: | Any |
[25 Aug 2005 9:40]
Vasily Kishkin
Thanks for the bug report. When I tried to reproduce the bug I had another error message: Invalid attempt to access a field before calling Read() I use mysql 5.0.11. What version of mysql do you use ?
[25 Aug 2005 10:08]
Paul Reeder
I am using 5.0.11. Specifically, according to MySqlAdministrator, it is "MySQL 5.0.11-beta-nt". Interesting that you are seeing the "Invalid attempt to access a field before calling Read()" error. I've seen this one before, but only when using the ExecuteReader() function to return a MySqlDataReader object (normal error - my fault). Paul
[25 Aug 2005 11:17]
Paul Reeder
Only right that I do my duty to the open source community. I walked through the code, and found that the error is in the handling of the "INT UNSIGNED" type.
The problem is in the StoredProcedure.Prepare() function at lines 90 - 92 of StoredProcedure.cs. In line 90, the parameter is split into a string array (parts[]). In the case of "IN ChAddress INT UNSIGNED", this becomes the array {"IN", "ChAddress", "INT", "UNSIGNED"}.
In lines 91 and 92, the length of the parts[] array ( "== 3") is used to decide whether the direction ("in", "out', "inout") is included in the parameter definition from the server or is the default "in".
------------ FIX --------------
It looks to me that the parameter type returned from the server is ignored in favor of the type given by the MySqlParameter object. Thus, the fix is simply to change the "== 3" in lines 91 and 92 to ">= 3".
This fix seems to work properly with both the given procedure and another I recently realized had the same issue.
--------- SIDE NOTE --------
It wasn't easy to use the debugger with this, as the CLR was selecting to use the non-debug build from the GAC instead of the local debug build. In my case, I did so by renaming the output assembly, but this *probably* could have been done by changing the version. You might want to add either an option to the installer to install the assembly to the GAC -OR- add some text to the README file that comes with the source that points this out.
[30 Aug 2005 8:59]
Vasily Kishkin
Thanks for bug report and proposal of bug fixing.
[14 Sep 2005 17:15]
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 should be fixed in 1.0.5
[14 Sep 2005 17:16]
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 should be fixed in 1.0.5
Description: I am getting an excpeption of "Parameter '?in' must be defined" when executing this stored procedure (shown in "How to repeat"). Trapping the server queries using --log=<filename> on the server instance netted only the following query when this is executed: SELECT param_list FROM mysql.proc WHERE db=_latin1 'wctp' AND name=_latin1 'GetETEInfo' Executing this parameter at the console mysql client yields the following: mysql> select param_list from mysql.proc where db='wctp' and name='GetETEInfo'; +----------------------------------------------------------------------------------------------------------------------+ | param_list | +----------------------------------------------------------------------------------------------------------------------+ | IN ChAddress INT UNSIGNED, OUT Valid BOOLEAN, OUT UseETE BOOLEAN, OUT Uri VARCHAR(255), OUT WctpAddress VARCHAR(255) | +----------------------------------------------------------------------------------------------------------------------+ I thought that it might have something to do with the BOOLEAN types (and the lack of a corresponding type in MySqlDataType. So I changed the BOOLEAN fields to TINYINT and that did not fix this problem. How to repeat: -------- TABLE ----- DROP TABLE IF EXISTS `wctp`.`device`; CREATE TABLE `device` ( `Address` int(10) unsigned NOT NULL default '0', `ImpliedETE` tinyint(1) unsigned default '0', `ETEURI` varchar(255) NOT NULL, `ETEAddress` varchar(255) NOT NULL, PRIMARY KEY (`Address`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Custom Information about devices'; -------- PROCEDURE ---- DELIMITER $$ DROP PROCEDURE IF EXISTS `wctp`.`GetETEInfo`$$ CREATE PROCEDURE `wctp`.`GetETEInfo`(IN ChAddress INT UNSIGNED, OUT Valid BOOLEAN, OUT UseETE BOOLEAN, OUT Uri VARCHAR(255), OUT WctpAddress VARCHAR(255)) BEGIN SELECT ImpliedETE, ETEURI, ETEAddress INTO UseETE, Uri, WctpAddress FROM device WHERE Address=ChAddress LIMIT 1; SELECT FOUND_ROWS() INTO Valid; END$$ DELIMITER ; -------- C# Code --------- MySqlCommand cmd = conn.CreateCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "GetETEInfo"; //address cmd.Parameters.Add("ChAddress", MySqlDbType.Int32); cmd.Parameters["ChAddress"].Direction = ParameterDirection.Input; cmd.Parameters["ChAddress"].IsUnsigned = true; cmd.Parameters["ChAddress"].Value = Address; //valid cmd.Parameters.Add("Valid", MySqlDbType.Byte, 1); cmd.Parameters["Valid"].Direction = ParameterDirection.Output; //UseETE cmd.Parameters.Add("UseETE", MySqlDbType.Byte, 1); cmd.Parameters["UseETE"].Direction = ParameterDirection.Output; //Uri cmd.Parameters.Add("Uri", MySqlDbType.VarChar, 255); cmd.Parameters["Uri"].Direction = ParameterDirection.Output; //WctpAddress cmd.Parameters.Add("WctpAddress", MySqlDbType.VarChar, 255); cmd.Parameters["WctpAddress"].Direction = ParameterDirection.Output; cmd.ExecuteNonQuery();