Parameter order in ParameterCollection?

Parameter order in ParameterCollection?

Postby 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
br_ns8
 
Posts: 4
Joined: Thu 07 Jun 2007 19:32

Postby 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?
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby 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
br_ns8
 
Posts: 4
Joined: Thu 07 Jun 2007 19:32

Postby 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.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby 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
br_ns8
 
Posts: 4
Joined: Thu 07 Jun 2007 19:32

Postby 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;
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby 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
br_ns8
 
Posts: 4
Joined: Thu 07 Jun 2007 19:32

Postby 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.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43


Return to dotConnect Universal