Query parameters and direct connection

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
fulvion
Posts: 18
Joined: Fri 06 Oct 2006 09:47

Query parameters and direct connection

Post by 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

Alexey
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by 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.

fulvion
Posts: 18
Joined: Fri 06 Oct 2006 09:47

Post by 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.

Alexey
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Fri 06 Oct 2006 11:22

You are welcome.

Post Reply