How to Bind Parameter by Position not by Name

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
quangnd
Posts: 5
Joined: Thu 28 Jan 2010 07:03

How to Bind Parameter by Position not by Name

Post by quangnd » Mon 07 Nov 2011 15:40

Hi,
We have SQL: update tmp t set t.f1 = t.f1+1 where t.f2=:p_f2 returning t.f1 into :p_f1

In ODP.Net, It is OK when we add parameter like this:
- cmd.Parameters.Add("p1"...).Value = 10;
- cmd.Parameters.Add("p2"...).Direction = ....ReturnValue;
But in DotConnect (5.2) we get ORA-01036 error. So that we have to add parameter with exact name:

- cmd.Parameters.Add("p_f2"...).Value = 10;
- cmd.Parameters.Add("p_f1"...).Direction = ....ReturnValue;
I think that, DotConnect check the binding name! For us, names is not important but positions!

How can we use the old way like in ODP.Net?

We are using VS2005 SP1

quangnd
Posts: 5
Joined: Thu 28 Jan 2010 07:03

Any help please

Post by quangnd » Tue 08 Nov 2011 02:13

Any help please

Shalex
Site Admin
Posts: 8247
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Tue 08 Nov 2011 14:54

Please refer to http://www.devart.com/dotconnect/oracle ... eters.html:
"Parameters [in the query] require accordance with names of OracleParameter instances in the collection [OracleCommand.Parameters]."

Shalex
Site Admin
Posts: 8247
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Wed 09 Nov 2011 10:07

As a workaround, you can use the OracleCommand.ParametersCheck property (refer to the mentioned article) to synchronize automatically query text and OracleCommand.Parameters collection. After this you can work with parameters via their indexes (not names):

Code: Select all

var cmd = new OracleCommand(...);
cmd.CommandText = "update tmp t set t.f1 = t.f1+1 where t.f2=:p_f2 returning t.f1 into :p_f1";
cmd.ParameterCheck = true;
cmd.Parameters[0].Value = 10;
cmd.Parameters[1].Direction = ....ReturnValue;
...

Post Reply