Help declaring a TinyInt(1) output parameter using MySqlConnector?

Question

User-1002157272 posted

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...

Any suggestions?

EDIT: I just noticed the MySqlDbType.Byte... would the signed version of this be the corresponding datatype I am looking for?

Answer 1

User-661350001 posted

you may simply pass the value without mentioning the type

cmd.Parameters.AddWithValue("@MyParam",  1);

or

cmd.Parameters.Add(new MySqlParameter("@MyParam", 1));

Answer 2

User-1002157272 posted

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... lol

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.

Answer 3

User-1284555475 posted

I've use the MySqlDbType.Bit type for boolean values and it works. Like this:

//Simple assignation
command.Parameters.Add("@parameter", MySqlDbType.Bit);
command.Parameters["@parameter"].Value = booleanValue;
//Stored procedure
command.Parameters.Add("@parameter", MySqlDbType.Bit, 1);
command.Parameters["@parameter"].Direction = ParameterDirection.Output;
command.ExecuteNonQuery();
var booleanValue = Convert.ToBoolean(command.Parameters["@parameter"].Value);