Page 1 of 1
Parameter order in ParameterCollection?
Posted: Thu 07 Jun 2007 19:49
by br_ns8
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
Posted: Mon 11 Jun 2007 07:40
by Alexey
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?
Posted: Mon 11 Jun 2007 19:46
by br_ns8
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
Posted: Wed 13 Jun 2007 05:54
by Alexey
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.
Posted: Fri 15 Jun 2007 01:19
by br_ns8
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
Posted: Fri 15 Jun 2007 08:29
by Alexey
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;
Posted: Sat 16 Jun 2007 18:42
by br_ns8
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
Posted: Mon 18 Jun 2007 07:26
by Alexey
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.