Diffrent behaviour between version 8.4.313.0 and 8.4.333.0

Diffrent behaviour between version 8.4.313.0 and 8.4.333.0

Postby 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.
avsvischenko
 
Posts: 1
Joined: Tue 03 Feb 2015 16:25

Re: Diffrent behaviour between version 8.4.313.0 and 8.4.333.0

Postby 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.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17


Return to dotConnect for Oracle