Exception on ExecuteArray

[Jung]

Hi!

I have issues when inserting data with the executeArray function in the case when the first entry produces a primary key violation. I will receive a null reference exception here. When all rows are producing a primary key violation I will receive a OracleException which is okay.
Here is a small test program to reproduce this issue:

Code: Select all

OracleConnectionStringBuilder oraCSB = new OracleConnectionStringBuilder
      {
        Direct = true,
        Server = "testserver",
        ServiceName = "testservice",
        Port = 1521,
        UserId = "TEST",
        Password = "TEST"
      };

      OracleConnection connection = new OracleConnection(oraCSB.ConnectionString);
      connection.Open();
      connection.AutoCommit = true;
      OracleCommand Command = connection.CreateCommand();
      Command.CommandText = "create table test (column1 varchar2(24) NOT NULL,column2 INTEGER, Column3 REAL, CONSTRAINT PK_TEST PRIMARY KEY  (Column1))";
      try
      {
        Command.ExecuteNonQuery();
      }
      catch (Devart.Data.Oracle.OracleException ex)
      {
        string exception = ex.Message;
      }
      Command.Dispose();
      Command = connection.CreateCommand();
      Command.CommandText = "insert into test values(:column1, :column2, :column3)";
      Collection<Object[]> ParameterList = new Collection<object[]>();
      ParameterList.Clear();
      ParameterList.Add(new object[2]);
      ParameterList.Add(new object[2]);
      ParameterList.Add(new object[2]);
      
      ParameterList[0][0] = "Entry_0";
      ParameterList[0][1] = "Entry_1";

      ParameterList[1][0] = 0;
      ParameterList[1][1] = 0;

      ParameterList[2][0] = 0;
      ParameterList[2][1] = 0;

      Command.Parameters.Add("Column1", OracleDbType.VarChar);
      Command.Parameters.Add("Column2", OracleDbType.Number);
      Command.Parameters.Add("Column3", OracleDbType.Float);
      for (int i = 0; i < 3; i++)
      {
        Command.Parameters[i].Value = ParameterList[i];
      }
      try
      {
        Command.ExecuteArray(2);
        connection.Commit();
        // Works also if I execute it a second time (will produce primary key violation)
        Command.ExecuteArray(2);
        connection.Commit();
      }
      catch (Devart.Data.Oracle.OracleException ex)
      {
        string test = ex.Message;
      }

      ParameterList[0][1] = "2ndEntry_1";
      for (int i = 0; i < 3; i++)
      {
        Command.Parameters[i].Value = ParameterList[i];
      }
      try
      {
        // will throw null reference exception
        Command.ExecuteArray(2);
        connection.Commit();
      }

      catch (Devart.Data.Oracle.OracleException ex)
      {
        string test = ex.Message;
      }

The stack trace of the error:

Code: Select all

   at Devart.Data.Oracle.by.d()
   at Devart.Data.Oracle.ca.f()
   at Devart.Data.Oracle.ca.c()
   at Devart.Data.Oracle.j.a(Int32 A_0, b7 A_1)
   at Devart.Data.Oracle.OracleCommand.InternalExecute(CommandBehavior behavior, IDisposable disposable, Int32 startRecord, Int32 maxRecords, Boolean nonQuery)
   at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
   at Devart.Data.Oracle.OracleCommand.ExecuteNonQuery()
   at Devart.Data.Oracle.OracleCommand.ExecuteArray(Int32 iters)
   at ConsoleApplication1.Program.Main(String[] args) in C:\Users\juch\CVS\views\EAnalyzer\EAnalyzer\Test\Program.cs:line 83
   at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
   at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
   at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
   at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.ThreadHelper.ThreadStart()

I am using the Devart.Data.Oracle Version 7.5.164.0

Oracle Server version is:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

If you need anything else please let me know.

Best regards
Christian

[Pinturiccio]

We have reproduced the issue. We will investigate it and notify you about the results as soon as possible.

[Jung]

There is one additional thing I would like to mention:

As a temporary workaround I would like to switch to the OCI mode (direct = false).

If I run the above code with the following modifications I will get a memory leak.
Here is the complete example:

Code: Select all

OracleConnectionStringBuilder oraCSB = new OracleConnectionStringBuilder
      {
        Direct = false,
        Server = "testserver",
        ServiceName = "testservice",
        Sid = "testsid",
        UserId = "TEST",
        Password = "TEST"
      };
      String ConnString = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=" + oraCSB.Server
                 + ")(PORT= 1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=" + oraCSB.Sid + ")));User Id="
                 + oraCSB.UserId + ";Password=" + oraCSB.Password + ";";


      OracleConnection connection = new OracleConnection(ConnString);
      connection.Open();
      connection.AutoCommit = true;
      OracleCommand Command = connection.CreateCommand();
      Command.CommandText = "create table test (column1 varchar2(24) NOT NULL,column2 INTEGER, Column3 REAL, CONSTRAINT PK_TEST PRIMARY KEY  (Column1))";
      try
      {
        Command.ExecuteNonQuery();
      }
      catch (Devart.Data.Oracle.OracleException ex)
      {
        string exception = ex.Message;
      }
      Command.Dispose();
      Command = connection.CreateCommand();
      Command.CommandText = "insert into test values(:column1, :column2, :column3)";
      Collection<Object[]> ParameterList = new Collection<object[]>();
      ParameterList.Clear();
      ParameterList.Add(new object[2]);
      ParameterList.Add(new object[2]);
      ParameterList.Add(new object[2]);
      
      ParameterList[0][0] = "Entry_0";
      ParameterList[0][1] = "Entry_1";

      ParameterList[1][0] = 0;
      ParameterList[1][1] = 0;

      ParameterList[2][0] = 0;
      ParameterList[2][1] = 0;

      Command.Parameters.Add("Column1", OracleDbType.VarChar);
      Command.Parameters.Add("Column2", OracleDbType.Number);
      Command.Parameters.Add("Column3", OracleDbType.Float);
      for (int i = 0; i < 3; i++)
      {
        Command.Parameters[i].Value = ParameterList[i];
      }
      for (int j = 0; j < 100000; j++)
      {
        try
        {
          Command.ExecuteArray(2);
          connection.Commit();
          // Works also if I execute it a second time (will produce primary key violation)
          Command.ExecuteArray(2);
          connection.Commit();
        }
        catch (Devart.Data.Oracle.OracleException ex)
        {
          string test = ex.Message;
        }
      }

So basically, the only change is that I execute the same command in a loop.

Because of the fact that I have lots of data the process will throw a SystemOutOfMemory Exception after a while.

Can you also reproduce this effect?

[Pinturiccio]

We have reproduced the issue. We will investigate it and notify you about the results as soon as possible.

[Pinturiccio]

We have fixed the bug with memory leak when handling the errors that occur during command execution in the OCI mode. We will post here when the corresponding build of dotConnect for Oracle is available for download.

[Pinturiccio]

The new build of dotConnect for Oracle 7.6.192 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://forums.devart.com/viewtopic.php?t=26101

[Jung]

Any news regarding the bug in Direct Mode?

[Pinturiccio]

We have fixed the bug with NRE when processing an Oracle error during array insertion. We will post here when the corresponding build of dotConnect for Oracle is available for download.