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

  • 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?

    Friday, July 20, 2012 12:50 AM

All replies

  • User-661350001 posted
    you may simply pass the value without mentioning the type
    cmd.Parameters.AddWithValue("@MyParam",  1);
    cmd.Parameters.Add(new MySqlParameter("@MyParam", 1));

    Friday, July 20, 2012 6:31 AM
  • 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.

    Friday, July 20, 2012 12:29 PM
  • 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;
    var booleanValue = Convert.ToBoolean(command.Parameters["@parameter"].Value);

    Friday, March 11, 2016 2:50 PM