Diffrent behaviour between version 8.4.313.0 and 8.4.333.0

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
avsvischenko
Posts: 1
Joined: Tue 03 Feb 2015 16:25

Diffrent behaviour between version 8.4.313.0 and 8.4.333.0

Post by avsvischenko » Tue 03 Feb 2015 16:33

Hi Devart, we found that in our application with new version 8.4.333.0 changed behaviour of returning value for OracleParameter.
In version 8.4.313.0 we had for
command.Parameters[parameterName].Value equal null if our command execute return -1 value.
In version 8.4.333.0 we have the value which we have befor executing our command with command execute return 0.

Please take a look at this sample:
DDL:

Code: Select all

CREATE TABLE TEST
(
  ID                   NUMBER(19)               NOT NULL,  
  NAME                 VARCHAR2(1000 CHAR)      NOT NULL,
  LAST_UPDATED_DT      DATE                     NOT NULL  
);


CREATE UNIQUE INDEX AK_TEST_NAME ON TEST
(NAME);

CREATE OR REPLACE TRIGGER BIU_TEST_DT
    BEFORE INSERT OR UPDATE 
    ON TEST     REFERENCING NEW AS New OLD AS Old
    for each row
begin
      :new.last_updated_dt := sys_extract_utc(SYSTIMESTAMP);
end;

INSERT INTO TEST(ID,NAME)
VALUES(1,'TEST DATA');

INSERT INTO TEST(ID,NAME)
VALUES(2,'TEST DATA2');

COMMIT;
C#:

Code: Select all

using System;
using System.Data;
using System.Transactions;
using Devart.Data.Oracle;
using IsolationLevel = System.Transactions.IsolationLevel;

namespace BulkWithClob
{  
   internal class Program
   {
      private static void Main(string[] args)
      {
         //string connectionString = "connection string"; 
         string connectionString = "User Id=;Password=;Direct=true;Data Source=;SID=;Pooling=true;Unicode=true";
            using (var transaction = new TransactionScope(TransactionScopeOption.Required,
               new TransactionOptions {IsolationLevel = IsolationLevel.ReadCommitted}))
            {
               using (OracleConnection connection = new OracleConnection(connectionString))
               {
                  connection.Open();
                  using (var command = connection.CreateCommand())
                  {
                     var array = new decimal?[1];
                     array.SetValue((decimal?) 2, 0);
                     var arrayGenericName = new String[1];
                     arrayGenericName.SetValue("TEST DATA_2", 0);
                     var arrayLastUpdatedDate = new DateTime?[1];
                     var lastUpdatedDate = new DateTime();
                     arrayLastUpdatedDate.SetValue(lastUpdatedDate, 0);
                     command.CommandText = "update  TEST set name = :name where id = :id and LAST_UPDATED_DT = :LastUpdatedDt RETURNING  LAST_UPDATED_DT INTO  :LastUpdatedDt";
                     command.PassParametersByName = true;
                     var idParam = new OracleParameter("id", OracleDbType.Number)
                     {
                        Value = array,
                        Direction = ParameterDirection.InputOutput
                     };
                     var genericNameParam = new OracleParameter("name", OracleDbType.VarChar)
                     {
                        Value = arrayGenericName,
                        Direction = ParameterDirection.Input
                     };
                     var lastUpdatedDateParam = new OracleParameter("LastUpdatedDt", OracleDbType.Date)
                     {
                        Value = arrayLastUpdatedDate,
                        Direction = ParameterDirection.InputOutput
                     };
                     command.Parameters.Add(genericNameParam);
                     command.Parameters.Add(idParam);
                     command.Parameters.Add(lastUpdatedDateParam);

                     var result = command.ExecuteArray(array.Length);
                     if (result == 0)
                     {
                        var newLastUpdateDate = ((Array) command.Parameters["LastUpdatedDt"].Value).GetValue(array.Length-1);
                        if (newLastUpdateDate != null)
                           throw new Exception(string.Format("Value of LastUpdatedDt is not null {0} in devart version 8.4.333.0", newLastUpdateDate));                        
                     }
                     if (result == -1)
                     {
                        var newLastUpdateDate = ((Array)command.Parameters["LastUpdatedDt"].Value).GetValue(array.Length - 1);
                        if (newLastUpdateDate == null)
                           throw new Exception("Value of LastUpdatedDt is default for DateTime type it works in devart version 8.4.313.0");
                        if ((DateTime)newLastUpdateDate ==  default(DateTime))
                           throw new Exception("Value of LastUpdatedDt is default for DateTime type it works in devart versions 8.4.313.0");                        
                     }                     
                  }
               }
               transaction.Complete();
            }        
      }

   }
}
This is unexpected behaviour for us.
We prepared example for that and sent it to devart support team.

MariiaI
Devart Team
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: Diffrent behaviour between version 8.4.313.0 and 8.4.333.0

Post by MariiaI » Fri 06 Feb 2015 11:51

This is as designed behaviour. The were some bugs related to performing the "RETURNING .. INTO ..." queries (e.g. obtaining the RowsAffected) and output parameters in DML arrays in the Direct mode. Since dotConnect for Oracle 8.4.333 these bugs are fixed: http://forums.devart.com/viewtopic.php?f=1&t=31098

If you have any further questions, feel free to contact us.

Post Reply