BindByName

sprinter252 · Post by **sprinter252** » Thu 22 Nov 2007 12:37

Hello,

will there will by any upgrade including the BindByName-functionality in CrLab for Oracle? I'm wondering if I should write my own code or if I simply have to call my parameters in the right order

.

Thx, Alex

Paul · Post by **Paul** » Fri 23 Nov 2007 08:05

Can you describe the problem? Do you use direct mode for connection? Do you use one parameter several times in SQL?

sprinter252 · Post by **sprinter252** » Wed 05 Dec 2007 14:54

Hello Paul,

I'm using a direct connection to an Oracle 10g database.

In Oracles ODP.NET you can set a property "BindByName" of the command-object to true. Lets see an example. The following code is the beginning of a stored-procedure-definition in Oracle:

Code: Select all

CREATE OR REPLACE PROCEDURE "USER"."SP_MY"
( CREATOR_IN IN NUMBER,
  WORKER_IN IN NUMBER
...

Now I will add the following code to my C#-project to call this procedure:

Code: Select all

OracleCommand cmdThis = new OracleCommand("SP_MY", cnnMyConnection);
cmdThis.Parameters.Add(new OracleParameter("WORKER_IN", 13));
cmdThis.Parameters.Add(new OracleParameter("CREATOR_IN", 12));
// ODP.NET
// cmdThis.BindByName = true;
cmdThis.ExecuteNonQuery();

As you can see, I first call the WORKER_IN-parameter which is defined as the second parameter in Oracle-procudure. In Corelab this will leed to a mixup of parameters and values and even to an error if you have different parameter-types. If you use BindByName, the command-object will determine the correct order of parameter by itself.

I hope, I made it a little bit clearer. Did I?

Alex

Paul · Post by **Paul** » Thu 06 Dec 2007 13:27

OraDirect .NET does not check the real sequence of parameters in your example. You have to write parameters in the correct order or use OracleCommand.ParameterCheck = true, which makes additional roundtrip to Oracle server

sprinter252 · Post by **sprinter252** » Thu 06 Dec 2007 14:02

Hello Paul,

thank you for ParameterCheck-hint! This is what I searched for. I know about the additional roiundtrip, but if you design a application multilayered you have to make some compromises

Alex