Query parameters and direct connection

Query parameters and direct connection

Postby fulvion » Fri 06 Oct 2006 10:08

Hi all,
i'm trying to execute a simple query with a parameter inside with no success. It works perfectly if i use oracle client to connect to the DB but i need to use direct mode and in this scenario it always generates an exception with Exception.Message=NULL.
Here's the code:

OracleConnection Con = new OracleConnection("User ID=USER; Password=PASSWORD; Direct=true; Data Source=MainDB.Domain.com; Port=1521; SID=MAINDB;");
Con.Open();
OracleCommand Cmd = new OracleCommand();
Cmd.Connection=Con;
Cmd.CommandText="begin\n select username from users where password=:password; \nend;";
OracleParameter p=new OracleParameter();
p.ParameterName=":password";
p.OracleDbType=OracleDbType.VarChar;
p.Direction=ParameterDirection.Input;
p.Value="mypwd";
Cmd.Parameters.Add(p);
OracleDataReader res = Cmd.ExecuteReader(CommandBehavior.CloseConnection);

the last line generates an excpetion and as i said it works ok if i don't use direct mode (but i need to use it).
Of course there's something wrong with parameters because if i insert the pwd in the query it works ok.
i'm using oradirect.net 3.20.7.0 with BDS2006

any help?
thanks in advance.
Fulvio
fulvion
 
Posts: 18
Joined: Fri 06 Oct 2006 09:47

Postby Alexey » Fri 06 Oct 2006 10:54

First of all use our latest version (3.50.13).
Second problem is that you set ParameterName as ":password". You should omit colon.
And then third problem will arise telling you that an INTO clause is expected in this SELECT statement. To resolve this erase "begin" and "end" keywords from the statement.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby fulvion » Fri 06 Oct 2006 11:15

thanks alexey it now works well. i'll update soon.
i thought to have tried all the possibile combinations but not this one (without colon and begin/end)! :roll:

i wonder if it works using oci.dll instead of direct connection just to know if i can write a "connection mode independent" class... but i think i'll figure it out by myself.

thanks again.
fulvion
 
Posts: 18
Joined: Fri 06 Oct 2006 09:47

Postby Alexey » Fri 06 Oct 2006 11:22

You are welcome.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43


Return to dotConnect for Oracle