Using parameters is not clear

Using parameters is not clear

Postby torely » Thu 26 Mar 2009 02:41

Using parameters is not clear
I ‘m using parameters with stored procedures and components behavior is not so that I expect.
Here is the sample that should help you understand what exactly I mean.

1st scenario (using OracleCommand with ParameterCheck = false)
Package:
Code: Select all
create or replace package TEST is
  type REF_CURSOR is ref cursor;
end TEST;

create or replace package body TEST is
  Function Sample1(
    P1 in number,
    P2 in varchar := ‘test’,
    P3 in varchar := ‘test2’) return REF_CURSOR;
Begin

End;

end TEST;


C# code:
Code: Select all
pRes = new OracleParameter("RETURN_VALUE", OracleDbType.Cursor);
pRes.Direction = ParameterDirection.ReturnValue;

oracleCommand1.CommandText = " TEST. Sample1";

oracleCommand1.Parameters.Clear();
oracleCommand1.Parameters.Add("P1 ", 1);
oracleCommand1.Parameters.Add("P3 ", "not test");
oracleCommand1.Parameters.Add(pRes);
oracleCommand1.ExecuteNonQuery();

It works, but value of P3 parameter is assigned to P2 parameter and it doesn’t depend of the parameter name (i.e. I can write oracleCommand1.Parameters.Add("aaa ", "not test") and it still aasignes value “not test” to P2 parameter) it depends only of the order of parameter.
Here is serious problem. I should always check parameters order because if write
Code: Select all
oracleCommand1.Parameters.Add("P1 ", 1);
oracleCommand1.Parameters.Add("P3 ", "param3");
oracleCommand1.Parameters.Add("P2 ", "param2");

then indeed P2 value will be “param3” and P3 value “param2”.

2nd scenario (using OracleCommand with ParameterCheck = true)
Package:
Code: Select all
create or replace package TEST is
  type REF_CURSOR is ref cursor;
end TEST;

create or replace package body TEST is
  Function Sample1(
    P1 in number,
    P2 in varchar := ‘test’,
    P3 in varchar := ‘test2’) return REF_CURSOR;
Begin

End;

  Function Sample1(
    P1 in number,
    P2 in varchar := ‘test’) return REF_CURSOR;
Begin

End;

end TEST;


C# code:
Code: Select all
pRes = new OracleParameter("RETURN_VALUE", OracleDbType.Cursor);
pRes.Direction = ParameterDirection.ReturnValue;

oracleCommand1.CommandText = " TEST. Sample1";

oracleCommand1.Parameters.Clear();
oracleCommand1.Parameters.Add("P1 ", 1);
oracleCommand1.Parameters.Add("P3 ", "not test");
oracleCommand1.Parameters.Add(pRes);
oracleCommand1.ExecuteNonQuery();


In this case it will not work because return parameter name is unknown (even if there no return parameter (for procedure) I loose my defaul values as it described in help). And another problem: How components will define what function to use with 3 or 2 parameters (as it described in help, all unassigned parameters values will set to NULL).

Exactly what I need is to use OracleCommand with ParameterCheck = false, and not to check parameters order.
torely
 
Posts: 7
Joined: Thu 24 Jul 2008 04:24

Postby AndreyR » Fri 27 Mar 2009 14:50

The OracleCommand class will contain the new PassParametersByName property.
Setting it to true will fix the situation.
These improvements are available in the new build of dotConnect for Oracle.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby torely » Mon 30 Mar 2009 23:09

The version of my dotConnect for Oracle is 5.00.22. It is the latest release and there no such a property.
Is this functionality realized in 5.20 Beta?
Can you inform me when this functionality will be realized in the release?
torely
 
Posts: 7
Joined: Thu 24 Jul 2008 04:24

Postby AndreyR » Tue 31 Mar 2009 08:41

We plan to release the new 5.0.25 Release build of dotConnects this week. It will contain the requested fix.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16


Return to dotConnect for Oracle