Parameter order in ParameterCollection?

br_ns8 · Post by **br_ns8** » Thu 07 Jun 2007 19:49

I am currently evaluating several MySql connectors and while testing yours I found that parameter names is not used. Are you really using position for parameter collections? - or am I missing some configuration item.

-Brett

Alexey · Post by **Alexey** » Mon 11 Jun 2007 07:40

First of all what product are you evaluating? Why do you write in UniDirect's forum?
Second, parameter names are used. What makes you think that they are not?

br_ns8 · Post by **br_ns8** » Mon 11 Jun 2007 19:46

1. I am evaluating unidirect on a mysql database at the moment, but it might be used with other databases in the future.

2. I think parameter names are not used as when i move a parameter position in the IDataParameterCollection (or change parameter order in the stored procedure itself) then the stored procedure call fails with "Out of range value adjusted for column 'blah' at row 1. This does not occur with Connector/NET. By just changing the Connection object from Connectore/NET to your product I find parameter names fail to be used.

-Brett

Alexey · Post by **Alexey** » Wed 13 Jun 2007 05:54

Please send me a small test project to reproduce the problem. It is
desirable to use 'test' schema objects, otherwise include definition of your own database objects.
Use e-mail address provided in the Readme file.
Do not use third party components.

br_ns8 · Post by **br_ns8** » Fri 15 Jun 2007 01:19

I have sent the full project to the support email but the guts are below. If i pass in a "CoreLab.UniDirect.UniConnection" object it fails with the switched parameters but with the "MySql.Data.MySqlClient.MySqlConnection" object everything is fine. (It fails regardless of the existence of the parameter markers "?".)

Code: Select all

private object GetScalar(IDbConnection conn, bool switchParameters)
    {
        try
        {
            conn.Open();
            IDbCommand command = conn.CreateCommand();
            command.CommandText = "testproc";
            command.CommandType = CommandType.StoredProcedure;
            
            IDbDataParameter param1=command.CreateParameter();
            param1.ParameterName = "?p_string";
            param1.Value = "Hello";

            IDbDataParameter param2 = command.CreateParameter();
            param2.ParameterName = "?p_int";
            param2.Value = 1;

            if (switchParameters)
            {
                command.Parameters.Add(param2);
                command.Parameters.Add(param1);
            }
            else
            {
                command.Parameters.Add(param1);
                command.Parameters.Add(param2);
            }

            return command.ExecuteScalar();
        }
        finally
        {
            conn.Close();
        }

    }

Procedure is below:

Code: Select all

PROCEDURE testproc(IN p_string NVARCHAR(255), IN p_int INT)
BEGIN
  
  select CONCAT('[',p_string,':',p_int,']');

END

Alexey · Post by **Alexey** » Fri 15 Jun 2007 08:29

You should use ParameterCheck property for this to work properly:

Code: Select all

            CoreLab.MySql.MySqlCommand command = (CoreLab.MySql.MySqlCommand)conn.CreateCommand();
            command.ParameterCheck = true;

br_ns8 · Post by **br_ns8** » Sat 16 Jun 2007 18:42

So you don't follow the framework data interfaces. Take "or using ADO.NET interfaces directly." out of your specifications. Obviously if you use the ado.net interfaces directly then parameter names are not used.

Very dissapointing, we'll have to look elsewhere. Thanks for your time.

-Brett

Alexey · Post by **Alexey** » Mon 18 Jun 2007 07:26

MySQL network protocol requires stored procedure parameters to be passed in the correct order. If you set named parameters in the wrong order, you need to use ParameterCheck property. This requires additional trip to a server when executing command. This property is false by default for better performance.

If you do not want to use this property because it is not a part of ADO.NET standard interface, you need to use unnamed parameters or use named parameters in the correct order. In this case MySQLDirect .NET is fully compliant with standard interfaces and simultaneously guarantees maximum performance.