MySQL Bugs: #40617: boolean output parameters return different value than mssql

Bug #40617	boolean output parameters return different value than mssql
Submitted:	10 Nov 2008 15:51	Modified:	13 Nov 2008 11:10
Reporter:	Alberto Rodriguez Morales	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / NET	Severity:	S4 (Feature request)
Version:	5.2.3	OS:	Windows (System.Data.Common)
Assigned to:		CPU Architecture:	Any

Description:
Hello. 

I´m trying to migrate an application developed in C# from mssql to mysql. 
First of all change from System.Data.SqlClient to System.Data.Common. 
After change all my calls from SqlCommand, SqlParameter, SqlConnection, etc... 
to DbCommand, DbParameter, DbConnection, etc... 
My app work so much with stored procedures and output parameters. It works fine except with bool types: RETURNS DIFFERENT VALUES.

Server: Ubuntu 8.10. MySql 5.0
Client: WinXP SP3, MySql connector/Net 5.2.3, C# 2005 express

?pOk 
{@Ok} 
[MySql.Data.MySqlClient.MySqlParameter]: {@Ok} 
base {System.MarshalByRefObject}: {@Ok} 
DbType: Boolean 
Direction: Output 
IsNullable: false 
ParameterName: "@Ok" 
Size: 0 
SourceColumn: null 
SourceColumnNullMapping: false 
SourceVersion: Current 
Value: 1 
?pOk 
{@Ok} 
[System.Data.SqlClient.SqlParameter]: {@Ok} 
base {System.MarshalByRefObject}: {@Ok} 
DbType: Boolean 
Direction: Output 
IsNullable: false 
ParameterName: "@Ok" 
Size: 0 
SourceColumn: "" 
SourceColumnNullMapping: false 
SourceVersion: Current 
Value: true 

If anybody can help me I will thanks. 
(sorry, I´m from spain and my english is very bad)

How to repeat:
File Prueba.cs "compile in Microsoft .Net Framework 2.0 SDK csc Prueba.cs"

namespace Lamee.Migracion
{
using System;
using System.Data;
using System.Data.Common;

public class PruebaParametroBooleano
{
   public static void Main(string[] args)
   {
   	 PruebaParametroBooleano aplicacion = new PruebaParametroBooleano();
         aplicacion.Run();
   }
   public void Run()
   {
      DbProviderFactory motorSQL = DbProviderFactories.GetFactory("MySql.Data.MySqlClient");
      DbConnection sqlConexion = motorSQL.CreateConnection();
      sqlConexion.ConnectionString = "Database=PRUEBAS;Data Source=172.16.0.6;User Id=alberto;Password=Boqueron69";
      try
      {
      sqlConexion.Open();
      DbCommand sqlGraba = motorSQL.CreateCommand();
      sqlGraba.Connection = sqlConexion;
      sqlGraba.CommandType = System.Data.CommandType.StoredProcedure;
      sqlGraba.CommandText = "apPruebaParametroBooleano";
      DbParameter pOk = motorSQL.CreateParameter();
      pOk.ParameterName = "@Ok";
      pOk.Direction = System.Data.ParameterDirection.Output;
      pOk.DbType = System.Data.DbType.Boolean;
      sqlGraba.Parameters.Add(pOk);
      sqlGraba.ExecuteNonQuery();
      bool Ok = bool.Parse(pOk.Value.ToString());
      } 		              
      catch (Exception e)
      {
         throw (e);
      }
      finally
      {
         sqlConexion.Close();
      }
   }
}
}
execute this script:
CREATE PROCEDURE `PRUEBAS`.`apPruebaParametroBooleano` (OUT Ok BOOL)
BEGIN
   SET Ok = true;
END

Hi Alberto and thanks for your report.

I must understand I do not understand you... Anyway, let's try to clear things.

Dealing with OUT parameters is generally troublesome, see Bug#17898 for explanation.
Dealing with BOOL type is generally troublesome as MySQL server does not support it natively. See http://dev.mysql.com/doc/refman/5.0/en/other-vendor-data-types.html.

To work around both problems, c/NET introduced connection string property 'Treat Tiny As Boolean'. Please see Bug#34052 and related reports or c/NET changelog:
  - Added connection string option 'Treat Tiny As Boolean' so applications that expect TINYINT(1)
    to return an integer will not break (bug #34052)

My test was like this (no problems detected):
Server version: 5.0.68-pb10-log MySQL Pushbuild Edition, build 10

mysql> create table bool_test (a BOOL, b VARCHAR(20));
Query OK, 0 rows affected (0.05 sec)

mysql> insert into bool_test values (true, "test true"), (false, "test false"),
(true, "test true again");
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from bool_test;
+------+-----------------+
| a    | b               |
+------+-----------------+
|    1 | test true       |
|    0 | test false      |
|    1 | test true again |
+------+-----------------+
3 rows in set (0.00 sec)

Now I created *exact* SP as you did.

mysql> SET @Ok = FALSE;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT @Ok;
+------+
| @Ok  |
+------+
| 0    |
+------+
1 row in set (0.00 sec)

mysql> CALL TestBool(@Ok);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @Ok;
+------+
| @Ok  |
+------+
| 1    |
+------+
1 row in set (0.14 sec)

So, from server side, all is fine and within specs for now.

Then I tested in VS2005:

            MySqlConnection conn = new MySqlConnection();
            conn.ConnectionString = "DataSource=localhost;Database=test;UserID=root;Password=*****;PORT=****;Allow Zero Datetime=True;Treat Tiny As Boolean=True";// <<<<
            conn.Open();
            MySqlCommand cmd = new MySqlCommand("TestBool", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("?Ok", DbType.Boolean); //MySqlDbType.Byte);
            cmd.Parameters[0].Direction = ParameterDirection.Output;
            cmd.ExecuteNonQuery();

            object o = cmd.Parameters[0].Value;
            Console.WriteLine("Done Test OUT.");

All was just fine.

Then I added your code:  bool Ok = bool.Parse(cmd.Parameters[0].Value.ToString());
and got exception but that is due to property System.Boolean.TrueString which evaluates to TRUE and can not be matched with "1" MySQL returns. But that is System.Format exception and I do not see bug there. You can always write your own bool.Parser.

Now, c/NET correctly calls SP with TINYINY(1) parameter and produces correct result. Where is the bug?

From my point of view the bug is in the property value of DbParameter. Returns 1 in MySql and true in MsSql, and I want that my Application works indepently RDBMS use, I wan´t write two different bool.Parse() to convert the parameter.
I suppose that using System.Data.Common all the providers works the same.
Thanks.

Ok, the solution is not use DbType.Boolean at output Params.
If define pOk as:
pOk.DbType = System.Data.DbType.Int16;
...
bool Ok = (pOk.Value.ToString() == "1");

Works the same in MsSql and MySql when the original type is 
BOOL in MySql & BIT in MsSql.

Thank so very much

Alberto, I am glad the problem is solved but I would argue your conclusion.
It is not "not use DbType.Boolean at output Params" but *not use bool.Parse(cmd.Parameters[0].Value.ToString());*. I think I can make perfectly legal case stating that .NET framework is faulty as it can not map 1 to TRUE when dealing with boolean values... This way of representing bool values has been around forever. Anyhow, it is always been like that with MS, they support only stuff they have or the way they did it and push people to use their way.
Final note, I am not an .NET expert and there might even be a way to use bool parser the way you want but I don't know of it.

I agree with you, BIT always returned 1 or 0 in all versions of MSSQL, is a new feature of .Net SqlClient map to boolean value, and MS should have keep the old way of mapping.

Thank you very much again.