Hello,
I use the standard edition of dotConnect for Oracle and tried the following code once with direct=true in the connection string, and once with direct=false :
*******
OracleConnection con = new OracleConnection(connectionString);
con.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = con;
cmd.CommandText = "insert into T_PROJECT_SESSION(C_USER_ID,C_TIMESTAMP,C_PROJECT_ID,C_PROJECT_SESSION_ID)values(:p1,:p2,:p3,SEQ_T_PROJECT_SESSION.nextval);"
+"select SEQ_T_PROJECT_SESSION.CurrVal into :p0 from DUAL;\n";
cmd.CommandText += "insert into PATENTREPORT.T_TEST_PARAM(PARAM)values(:p0);\n";
cmd.CommandText = "declare\n begin\n" + cmd.CommandText + " end;";
// Parameters
OracleParameter param0 = cmd.CreateParameter();
param0.DbType = System.Data.DbType.Int32;
param0.Direction = System.Data.ParameterDirection.Output;
param0.ParameterName = "p0";
param0.Value = null;
cmd.Parameters.Add(param0);
OracleParameter param1 = cmd.CreateParameter();
param1.DbType = System.Data.DbType.String;
param1.Direction = System.Data.ParameterDirection.Input;
param1.ParameterName = "p1";
param1.Value = "user";
cmd.Parameters.Add(param1);
OracleParameter param2 = cmd.CreateParameter();
param2.DbType = System.Data.DbType.Date;
param2.Direction = System.Data.ParameterDirection.Input;
param2.ParameterName = "p2";
param2.Value = DateTime.Now;
cmd.Parameters.Add(param2);
OracleParameter param3 = cmd.CreateParameter();
param3.DbType = System.Data.DbType.Int32;
param3.Direction = System.Data.ParameterDirection.Input;
param3.ParameterName = "p3";
param3.Value = 847;
cmd.Parameters.Add(param3);
cmd.ExecuteNonQuery();
con.Commit();
con.Close();
*********
The first Insert works fine in both modes. But then it seems that in direct mode the :p0 value is Null after the SELECT INTO statement. With direct=false, the value of this parameter is the correct one (the SEQ_PROJECT_SESSION.Currval).
I red something about a bug with repeated parameters using direct mode ? Is this the problem ?
If it isn't, how can I do to make this code work correctly ?
If this code cannot work in direct mode, is there a possibility to use the Devart assemblies with direct=false but no Oracle Client (for example add the oci.dll in our Deployment, or something like that) ?
The version of dlls I use are :
Devart.Data 5.0.1.0
Devart.Data.Oracle 5.20.29.0
Thanks in advance for your response.
Command Parameters with direct mode
I have tried the following code with the 5.20.29 version of dotConnect for Oracle and the 10.2.0.1 version of Oracle server:
It works properly (the p0 parameter contains the actual value from the database) in both cases:
in the direct mode and with Oracle client. Please specify the version of Oracle server
you are using and send us (support at devart*com) a small test project that reproduces the problem.
Please include the definitions of database objects and avoid using third-party components.
Code: Select all
conn.Open();
OracleCommand command = conn.CreateCommand();
command.CommandText = "insert into table values (1);\n";
command.CommandText += "select ID_SEQ.CurrVal into :p0 from dual;\n";
command.CommandText = "begin\n" + command.CommandText + "end;";
OracleParameter param0 = command.CreateParameter();
param0.DbType = System.Data.DbType.Int32;
param0.Direction = System.Data.ParameterDirection.Output;
param0.ParameterName = "p0";
command.Parameters.Add(param0);
command.ExecuteNonQuery();
conn.Close();
in the direct mode and with Oracle client. Please specify the version of Oracle server
you are using and send us (support at devart*com) a small test project that reproduces the problem.
Please include the definitions of database objects and avoid using third-party components.
The new build of dotConnect for Oracle 5.20.33 is available for download now.
It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to http://www.devart.com/forums/viewtopic.php?t=14909 .
It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to http://www.devart.com/forums/viewtopic.php?t=14909 .