Exception on ExecuteArray

Exception on ExecuteArray

Postby Jung » Thu 07 Feb 2013 07:08

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
Jung
 
Posts: 6
Joined: Thu 07 Feb 2013 06:49

Re: Exception on ExecuteArray

Postby Pinturiccio » Mon 11 Feb 2013 13:06

We have reproduced the issue. We will investigate it and notify you about the results as soon as possible.
Pinturiccio
Devart Team
 
Posts: 1955
Joined: Wed 02 Nov 2011 09:44

Re: Exception on ExecuteArray

Postby Jung » Wed 20 Feb 2013 06:51

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?
Jung
 
Posts: 6
Joined: Thu 07 Feb 2013 06:49

Re: Exception on ExecuteArray

Postby Pinturiccio » Mon 25 Feb 2013 14:20

We have reproduced the issue. We will investigate it and notify you about the results as soon as possible.
Pinturiccio
Devart Team
 
Posts: 1955
Joined: Wed 02 Nov 2011 09:44

Re: Exception on ExecuteArray

Postby Pinturiccio » Wed 27 Feb 2013 10:31

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
Devart Team
 
Posts: 1955
Joined: Wed 02 Nov 2011 09:44

Re: Exception on ExecuteArray

Postby Pinturiccio » Fri 01 Mar 2013 11:02

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
Pinturiccio
Devart Team
 
Posts: 1955
Joined: Wed 02 Nov 2011 09:44

Re: Exception on ExecuteArray

Postby Jung » Wed 10 Apr 2013 09:23

Any news regarding the bug in Direct Mode?
Jung
 
Posts: 6
Joined: Thu 07 Feb 2013 06:49

Re: Exception on ExecuteArray

Postby Pinturiccio » Mon 15 Apr 2013 12:01

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.
Pinturiccio
Devart Team
 
Posts: 1955
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for Oracle