Last post Mar 11, 2016 02:50 PM by eespitia.rea
Jul 20, 2012 12:50 AM|magicmike2011|LINK
Hi there. I recently made the switch from MS SQL to MySql and MariaDB (MySql-based) and I'm getting used to differences (and liking it very much!) however I've run into an issue and not sure how to proceed.
In my database, I am storing bool-type values using TinyInt(1) and my connection string to the db is configured (by default) to treat bool values being passed in as TinyInt(1) (As well as handling Guid's as Binary(16)).
All is working great, however I am trying to declare some output parameter's to be returned from the stored procedures and the TinyInt is not available in the .Net Connector as a MySqlDbType!!!
I do remember reading somewhere that Bit(1) is also used as a synonym db-side for TinyInt(1). Is this what I should be passing the output parameter in as? See, when I pass in a standard Input parameter of bool-type, I use the Parameters.AddWithValue() method
which automatically handles assigning the proper type to pass the variable value in as, and all works fine, it's saved in a TinyInt(1) column without issue.
The syntax I prefer for declaring my output parameters is as follows:
cmd.Parameters.Add(new MySqlParameter("@MyParam", MySqlDbType.Bit, 1));
cmd.Parameters["@MyParam"].Direction = ParameterDirection.Output;
However, as I mentioned above, the MySqlDbType enumeration .TinyInt is not availablable...
EDIT: I just noticed the MySqlDbType.Byte... would the signed version of this be the corresponding datatype I am looking for?
Jul 20, 2012 06:31 AM|Mudasir.Khan|LINK
you may simply pass the value without mentioning the type
cmd.Parameters.Add(new MySqlParameter("@MyParam", 1));
Jul 20, 2012 12:29 PM|magicmike2011|LINK
The parameters type can't be set implicitly in this case because if assigning a value to the parameter before executing the query, it will implicitly map the param to a datatype. In this case, since it would require a value of 1 or 0 to satisfy the requirements
in the database, it will automatically map it as an integer value.
I have to explicitly set the datatype in the method before executing, the problem is I can't seem to figure out which type will map properly.
EDIT: After spending some serious time looking up this issue, it seems to be a common bug with the MySql Connector/NET driver. While it supports options for handling bool-type parameters as returned values from queries, mapping them as an
ouput parameter is notoriously bugged... There are "apparent" bug fixes for this issue as it has been reported an enormous amount of times, but they as well do not work and the developer (oracle) does not seem very motivated to correct this... Good ole' Oracle...
I will leave this thread as unresolved and monitor the thread in case anyone does have a valid solution for this but for the mean time I have chosen another route. Rather than attempting to return a boolean value directly, I am now using an integer value
returning either 1 or 0 and to return a bool value from the method I simply do an if else clause on the returned integer. A zero value would equate to false as per standard, and any non-zero value would be treated as false.
// insert code here that calls the procedure and performs query...
return (int)cmd.Parameters["@MyParam"].Value == 0 ? false : true;
If you use the solution I provided above, you may trim down the data type to be returned to a smaller numeric value if desired, such as a byte value, etc to conserve memory allocation. Hope this helps, and would love to hear a "proper" fix to this problem
if anyone has one.
Mar 11, 2016 02:50 PM|eespitia.rea|LINK
I've use the MySqlDbType.Bit type for boolean values and it works. Like this:
command.Parameters["@parameter"].Value = booleanValue;
command.Parameters.Add("@parameter", MySqlDbType.Bit, 1);
command.Parameters["@parameter"].Direction = ParameterDirection.Output;
var booleanValue = Convert.ToBoolean(command.Parameters["@parameter"].Value);