Quantcast

odbc module lacks in a mode after executing stored procedures

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

odbc module lacks in a mode after executing stored procedures

cymoon
I am using Erlang together with an Oracle 11g database on a windows machine. When I execute a stored procedure using param_query, the odbc module executes further statements as if they where also stored procedures.

%%%%%%%%%%%%%%%%%%%%%%%%%%%%
odbc:start(permanent),
{ok, Ref} = odbc:connect(ConnectStr, Options),

Result1 =odbc:param_query(Ref, "select ? from dual",[{ sql_integer,[1]}]),
io:format("~p~n",[Result1]),
% returns:  {selected,[":1"],[{1.0}]}

Result2 =odbc:param_query(Ref, "{ ? = call NVL (2,2) }",[{sql_integer,out,[1]}]),
io:format("~p~n",[Result2]),
% returns:  {executed,1,[{2}]}

Result3 =odbc:param_query(Ref, "select ? from dual",[{ sql_integer,[3]}]),
io:format("~p~n",[Result3]),
% returns:  {executed,0,[{}]}  but should return  {selected,[":1"],[{3.0}]}
%%%%%%%%%%%%%%%%%%%%%%%%%%%%


To make sure that it is a bug in erlang, I wrote the same test in C#. It works as expected:

/////////////////////////////////////////
OdbcConnection con = new OdbcConnection();
con.ConnectionString = "DSN=flexdoc_dev;UID=nn2;PWD=nn2";
con.Open(); OdbcCommand cmd; OdbcDataReader dr;

cmd = new OdbcCommand("SELECT ? val FROM dual", con);
cmd.Parameters.AddWithValue("", 1);
dr = cmd.ExecuteReader(); dr.Read();
Console.WriteLine(dr["val"].ToString());
// returns 1

cmd = new OdbcCommand("{ ? = CALL NVL(2,2) }", con);
cmd.CommandType = CommandType.StoredProcedure;
OdbcParameter parameter = cmd.Parameters.Add("returnVal", OdbcType.Int);
parameter.Direction = ParameterDirection.ReturnValue;
dr = cmd.ExecuteReader(); dr.Close();
Console.WriteLine(cmd.Parameters["returnVal"].Value);
// returns 2

cmd = new OdbcCommand("SELECT ? val FROM dual", con);
cmd.Parameters.AddWithValue("", 3);
dr = cmd.ExecuteReader(); dr.Read();
Console.WriteLine(dr["val"].ToString());
// returns 3
/////////////////////////////////////////

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

odbc module lacks in a mode after executing stored procedures

Ingela Anderton Andin-2
Hi!

It looks like the contributer of input/output parameter-handling forgot
to clear the state which would explain your problem.
Could you test if the following patch solves your problem?

diff --git a/lib/odbc/c_src/odbcserver.c b/lib/odbc/c_src/odbcserver.c
index 11e311d..6ef3988 100644
--- a/lib/odbc/c_src/odbcserver.c
+++ b/lib/odbc/c_src/odbcserver.c
@@ -2011,6 +2011,7 @@ static void clean_state(db_state *state)
     free_column_buffer(&(columns(state)), nr_of_columns(state));
     columns(state) = NULL;
     nr_of_columns(state) = 0;
+    out_params(state) = FALSE;
 }

Regards Ingela Erlang/OTP team - Ericsson AB


cymoon wrote:

> I am using Erlang together with an Oracle 11g database on a windows machine.
> When I execute a stored procedure using param_query, the odbc module
> executes further statements as if they where also stored procedures.
>
> %%%%%%%%%%%%%%%%%%%%%%%%%%%%
> odbc:start(permanent),
> {ok, Ref} = odbc:connect(ConnectStr, Options),
>
> Result1 =odbc:param_query(Ref, "select ? from dual",[{ sql_integer,[1]}]),
> io:format("~p~n",[Result1]),
> % returns:  {selected,[":1"],[{1.0}]}
>
> Result2 =odbc:param_query(Ref, "{ ? = call NVL (2,2)
> }",[{sql_integer,out,[1]}]),
> io:format("~p~n",[Result2]),
> % returns:  {executed,1,[{2}]}
>
> Result3 =odbc:param_query(Ref, "select ? from dual",[{ sql_integer,[3]}]),
> io:format("~p~n",[Result3]),
> % returns:  {executed,0,[{}]}  but should return  {selected,[":1"],[{3.0}]}
> %%%%%%%%%%%%%%%%%%%%%%%%%%%%
>
>
> To make sure that it is a bug in erlang, I wrote the same test in C#. It
> works as expected:
>
> /////////////////////////////////////////
> OdbcConnection con = new OdbcConnection();
> con.ConnectionString = "DSN=flexdoc_dev;UID=nn2;PWD=nn2";
> con.Open(); OdbcCommand cmd; OdbcDataReader dr;
>
> cmd = new OdbcCommand("SELECT ? val FROM dual", con);
> cmd.Parameters.AddWithValue("", 1);
> dr = cmd.ExecuteReader(); dr.Read();
> Console.WriteLine(dr["val"].ToString());
> // returns 1
>
> cmd = new OdbcCommand("{ ? = CALL NVL(2,2) }", con);
> cmd.CommandType = CommandType.StoredProcedure;
> OdbcParameter parameter = cmd.Parameters.Add("returnVal", OdbcType.Int);
> parameter.Direction = ParameterDirection.ReturnValue;
> dr = cmd.ExecuteReader(); dr.Close();
> Console.WriteLine(cmd.Parameters["returnVal"].Value);
> // returns 2
>
> cmd = new OdbcCommand("SELECT ? val FROM dual", con);
> cmd.Parameters.AddWithValue("", 3);
> dr = cmd.ExecuteReader(); dr.Read();
> Console.WriteLine(dr["val"].ToString());
> // returns 3
> /////////////////////////////////////////
>
>
>
> --
> View this message in context: http://erlang.2086793.n4.nabble.com/odbc-module-lacks-in-a-mode-after-executing-stored-procedures-tp3696283p3696283.html
> Sent from the Erlang Bugs mailing list archive at Nabble.com.
> _______________________________________________
> erlang-bugs mailing list
> erlang-bugs
> http://erlang.org/mailman/listinfo/erlang-bugs
>
>  


Loading...