MySQL Bugs: #20878: decimal(m,d) stored procedure parameter

Bug #20878	decimal(m,d) stored procedure parameter
Submitted:	6 Jul 2006 2:15	Modified:	13 Jul 2006 13:00
Reporter:	Mauricio Orizola	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	1.0.7 svn (branches)	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any

Description:
When you have a stored procedure with a decimal(m,d) parameter (i.e. decimal(10,2)). You'll get "Unhandled type encountered" error when you try to call the stored procedure through MySqlCommand and MySqlParameter.

How to repeat:
Create a stored procedure with a decimal(m,d) parameter (i.e. decimal(10,2)) and try to call it from a .NET application using MySqlCommand and MySqlParameter objects.

Suggested fix:
The problem appears because the parameters are splited using a simple "," (cs.Split(parms, ",")) so decimal(m,d) is considered as two parameters ("decimal(m" and "d)"). To fix it update the "ParseBody" method in MySqlClient\StoredProcedure.cs file to:

        private ArrayList ParseBody(string body, string sqlMode)
        {
            bool ansiQuotes = sqlMode.IndexOf("ANSI_QUOTES") != -1;
            bool noBackslash = sqlMode.IndexOf("NO_BACKSLASH_ESCAPES") != -1;
            string quotePattern = ansiQuotes ? "``\"\"" : "``";

            ContextString cs = new ContextString(quotePattern, !noBackslash);
            
            int leftParen = cs.IndexOf(body, '(');
            Debug.Assert(leftParen != -1);

            // trim off the first part
            body = body.Substring(leftParen + 1);

            int rightParen = FindRightParen(body, quotePattern);
            Debug.Assert(rightParen != -1);
            string parms = body.Substring(0, rightParen).Trim();

            quotePattern += "()";
            ArrayList parmArray = new ArrayList();

//            string[] paramDefs = cs.Split(parms, ",");

			Regex rex = new Regex(@"([a-zA-Z]+ +)*[a-zA-Z0-9_]+ +[a-zA-Z0-9]+( *\( *[0-9]+( *, *[0-9]+ *)? *\))?( *[a-zA-Z]+)*");
			foreach(Match def in rex.Matches(parms))
				parmArray.Add(ParseParameter(def.Value, cs, sqlMode));

			/*
			if (paramDefs.Length > 0)
                foreach (string def in paramDefs)
                    parmArray.Add(ParseParameter(def, cs, sqlMode));
			*/

            body = body.Substring(rightParen+1).Trim().ToLower(CultureInfo.InvariantCulture);
            if (body.StartsWith("returns"))
                parmArray.Add(ParseParameter(body, cs, sqlMode));
            return parmArray;
        }

Hi Mauricio and thanks for your problem report. I was unable to verify it with test case attached.

Environment: 
  MySQL server 5.0.24BK on Suse 10.0 host
  MySQL connector/NET 1.0.7 svn
  MySQL.Data.dll 1.0.7.35475
  System.* 2.0
  System.Data.SQLClient 3.0.36
  adodb 7.0.33
  ADO 2.8
  WinXP SP2, VS2005

Output:
Connecting to Mysql DB

Testing NET SP Bugreport 20878, DECIMAL

SP definition: CREATE PROCEDURE spTest(val decimal(10,3)) begin select val; end

SP param definition:
  Value:21.05
  Precision:10
  Scale:3

Executing SP:

Decimal RetVal:21.050

CLEANING UP

Press any key to exit

VS2005 C# program

Attachment: Program.cs (text/plain), 2.47 KiB.

Tonci,

I assign parameter directly, not using a temporal variable, like this:

MySqlCommand cmd = new MySqlCommand(command, connecction);
cmd.SelectCommand.Parameters.Add(name, MySqlDbType.Decimal).Value = value;

Anyway you can analyze this piece of code under MySqlClient.StoredProcedure.ParseBody:

string[] paramDefs = cs.Split(parms, ",");

If "parms" contains "decimal(10,3)", it'll be taken as two parameters ("decimal(10" and ",3)"

Regards,
Mauricio

Mauricio, so you're saying that you use comma as decimal separator and do not check for parameters validity? If so, please provide me with at least full info on your locales and a test project and then I can look into sources.

Tanci,

Yes, I do. This is my locales:
NumberFormatInfo.NumberDecimalSeparator = ","
NumberFormatInfo.NumberGroupSeparator = "."

But in the MySqlCliente code that I showed you, "," is explicitly used.

I attached a database backup to reproduce and here is the C# code:

MySqlConnection cnn = new MySqlConnection(ConnectionString);
cnn.Open();
MySqlCommand cmd = new MySqlCommand("sp_PedidosEncabezados_insertar", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("?_cliente_ID", MySqlDbType.Int32).Value = 0;
cmd.Parameters.Add("?_PedidoEncabezado_numero", MySqlDbType.VarChar, 50).Value = "P1";
cmd.Parameters.Add("?_PedidoEncabezado_fecha", MySqlDbType.Date).Value = DateTime.Today;
cmd.Parameters.Add("?_modalidad_ID", MySqlDbType.Int32).Value = 0;
cmd.Parameters.Add("?_clausula", MySqlDbType.VarChar, 3).Value = "FOB";
cmd.Parameters.Add("?_FormaPago_ID", MySqlDbType.Int32).Value = 0;
cmd.Parameters.Add("?_PedidoEncabezado_DiasPlazoPago", MySqlDbType.Int32).Value = 0;
cmd.Parameters.Add("?_TipoPlazo_ID", MySqlDbType.Int32).Value = 0;
cmd.Parameters.Add("?_notify_ID", MySqlDbType.Int32).Value = 0;
cmd.Parameters.Add("?_consignee_ID", MySqlDbType.Int32).Value = 0;
cmd.Parameters.Add("?_ViaTransporte_ID", MySqlDbType.Int32).Value = 0;
cmd.Parameters.Add("?_TransportistaInternacional_ID", MySqlDbType.Int32).Value = 0;
cmd.Parameters.Add("?_PedidoEncabezado_ComisionExterior", MySqlDbType.Decimal, 10).Value = 0;
cmd.Parameters.Add("?_PedidoEncabezado_FechaEstimadaEmbarque", MySqlDbType.Date).Value = DateTime.Today;
cmd.Parameters.Add("?_PedidoEncabezado_InstruccionesEspeciales", MySqlDbType.VarChar, 100).Value = "";
cmd.Parameters.Add("?_PedidoEncabezado_EmbarquesParciales", MySqlDbType.Byte).Value = false;
cmd.Parameters.Add("?_PedidoEncabezado_NumeroEmbarques", MySqlDbType.Int32).Value = 0;
cmd.Parameters.Add("?_PedidoEncabezado_observaciones", MySqlDbType.VarChar, 1000).Value = "";
cmd.Parameters.Add("?_moneda_ID", MySqlDbType.Int32).Value = 0;
cmd.Parameters.Add("?_PedidoEncabezado_TipoCambio", MySqlDbType.Decimal, 10).Value = 0;
cmd.Parameters.Add("?_PedidoEncabezado_paridad", MySqlDbType.Decimal, 10).Value = 0;
cmd.Parameters.Add("?_PedidoEncabezado_UsuarioCreacion", MySqlDbType.VarChar, 50).Value = "";
cmd.Parameters.Add("?_PedidoEncabezado_EmpresaCreacion", MySqlDbType.VarChar, 50).Value = "";
cmd.Parameters.Add("?_PedidoEncabezado_DireccionCreacion", MySqlDbType.VarChar, 20).Value = Request.ServerVariables["REMOTE_ADDR"];
MySqlDataReader drd = cmd.ExecuteReader();
drd.Read();
uint PedidoEncabezado_ID = (uint) drd["PedidoEncabezado_ID"];
drd.Close();
cnn.Close();

bug20878.sql

Attachment: bug20878.sql (text/plain), 7.09 KiB.

Tonci,

By the way, which connector-net version are you testing with? I'm testing using the version under svn.mysql.com/svnpublic/connector-net/branches/1.0.

Regards,
Mauricio

Mauricio, I'm using different repo but the version is the same... I will reopen this report now that you provided necessary info and test case.

Hi Mauricio.
In C/C++ decimal type does not exist, so it is replaced by float/double as you can see from definition of Decimal ParamType:
Decimal
    Member of System.Data.DbType

Summary:
A simple type representing values ranging from 1.0 x 10 -28 to approximately 7.9 x 10 28 with 28-29 significant digits.

which means it's absolutely unnecessary to declare it with width,precision since those atributes are declared in stored procedure. You can check this by extending my test case with following code:

cmd.Parameters.Add("?val", MySqlDbType.Decimal, 0).Value = 21.050505;
Console.WriteLine("\nSP param definition 2:");

Console.WriteLine("\nExecuting SP:");
object id2 = cmd.ExecuteScalar();
Console.WriteLine("\nDecimal RetVal:" + id2.ToString());

You will see that SP returns 21.051 as "val" parameter is declared DECIMAL(10,3) even thou I set, what you think is width, to 0 in my call.

I am changing status to "Not a bug".

Tonci,

The problem comes up before taken the value into a C/C++ variable. It does when the connector is analizing the string containing the paramaters declaration in the MySQL Server.

Anyway, which repo are you using?

Regards,
Mauricio