Problems with inserting null-values

Problems with inserting null-values

Postby marco.schwingel » Fri 30 Nov 2012 14:08

Hello,
we’ve got a problem after upgrading to the latest dotConnect for Oracle (and LinqConnect) version 7.3.132. Now our entities behave strangely and LinqConnect creates parameters for Insert-statements of properties that haven't been changed at all:

Here’s a small sample (only the ID has been changed):
Code: Select all
Model.clsTest_Entity entity = new Model.clsTest_Entity();
entity.Id = 1;
entity.Save();


Generated insert-statement (INS_USERNAME is set to NULL):
Code: Select all
INSERT INTO RE_TEST (ID, INS_DATIME, INS_USERNAME, NAME) VALUES (:p1, :p2, :p3, :p4) RETURNING ID INTO :ret1
-- p1: Input Number (Size = 0; DbType = Decimal) [1]
-- p2: Input Date (Size = 0; DbType = DateTime) [01.01.0001 00:00:00]
-- p3: Input VarChar (Size = 0; DbType = String) []
-- p4: Input VarChar (Size = 0; DbType = String) []
-- ret1: Output Number (Size = 0; DbType = Decimal) []
-- Context: Devart.Data.Oracle.Linq.Provider.OracleDataProvider Model: MetaModel Build: 4.1.137.0


Exception (due to null-value for INS_USERNAME-column):
Code: Select all
Eine Ausnahme (erste Chance) des Typs "Devart.Data.Linq.LinqCommandExecutionException" ist in Devart.Data.Linq.dll aufgetreten.
Eine Ausnahme (erste Chance) des Typs "Devart.Data.Linq.LinqCommandExecutionException" ist in AxiCenter.DAL.Entities.dll aufgetreten.
Eine Ausnahme (erste Chance) des Typs "Devart.Data.Linq.LinqCommandExecutionException" ist in AxiCenter.DAL.Entities.dll aufgetreten.

{"ORA-01400: Einfügen von NULL in("INS_USERNAME") nicht möglich"}


Oracle table layout (with default value for INS_USERNAME-column):
Code: Select all
CREATE TABLE RE_TEST
(
  ID NUMBER(*, 0) NOT NULL
, NAME VARCHAR2(200)
, INS_DATIME DATE DEFAULT sysdate NOT NULL
, INS_USERNAME VARCHAR2(200) DEFAULT user NOT NULL
, CONSTRAINT CNS_TEST_PRIM PRIMARY KEY
  (
    ID
  )
  ENABLE
);


Entity-Object (only relevant properties):
Code: Select all
[Table(Name = "RE_TEST")]
   public partial class clsTest_Entity : INotifyPropertyChanged, INotifyPropertyChanging
   {
#region long Id
      private long _id;
      /// <summary>
      /// Gets or set the id
      /// </summary>
      /// <value>
      /// The id
      /// </value>
      [Column(Storage = "_id", Name = "ID", DbType = "NUMBER", IsPrimaryKey = true, IsDbGenerated = false, AutoSync = AutoSync.OnInsert, UpdateCheck = UpdateCheck.Never, CanBeNull = false)]
      public long Id
      {
         get
         {
            return _id;
         }
         set
         {
            if (value != _id)
            {
               OnPropertyChanging();
               _id = value;
               OnPropertyChanged("Id");
            }
         }
      }
      #endregion

      #region DateTime InsDatime
      private DateTime _insDatime;
      /// <summary>
      /// Gets or set the ins datime
      /// </summary>
      /// <value>
      /// The ins datime
      /// </value>
        [Column(Storage = "_insDatime", Name = "INS_DATIME", DbType = "DATE", AutoSync = AutoSync.Never, UpdateCheck = UpdateCheck.Never, CanBeNull = false)]
      public DateTime InsDatime
      {
         get
         {
            return _insDatime;
         }
         set
         {
            if (value != _insDatime)
            {
               OnPropertyChanging();
               _insDatime = value;
               OnPropertyChanged("InsDatime");
            }
         }
      }

      #endregion

      #region string InsUsername

      private string _insUsername;
      /// <summary>
      /// Gets or set the ins username
      /// </summary>
      /// <value>
      /// The ins username
      /// </value>
      [Column(Storage = "_insUsername", Name = "INS_USERNAME", DbType = "VARCHAR2", AutoSync = AutoSync.Never, UpdateCheck = UpdateCheck.Never, CanBeNull = false)]
      public string InsUsername
      {
         get
         {
            return _insUsername;
         }
         set
         {
            OnPropertyChanging();
            _insUsername = value;
            OnPropertyChanged("InsUsername");
         }
      }

      #endregion

      …

      #region ctor

      /// <summary>
      /// Gets or set the clsTest_Entity
      /// </summary>
      /// <value>
      /// The clsTest_Entity
      /// </value>

      #endregion

      public clsTest_Entity( )
      {
      }
   }


Problem and question:
Why does LinqConnect create parameters for NULL-value properties that never have been changed (like INS_USERNAME) or how can we avoid this ORA-01400-exception?
We’d like to have the database create these values if they weren’t set in C# code, otherwise the C# code should win against the databases default value (so the IsDbGenerated-attribute doesn't work for us).


Thank you!
Cheers, Marco
marco.schwingel
 
Posts: 2
Joined: Thu 19 May 2011 14:58

Re: Problems with inserting null-values

Postby MariiaI » Mon 03 Dec 2012 15:38

Thank you for the report. We will consider the possibility to change this behavior so that the non-nullable properties with the default values will not be included in the generated queries and inform you about the results as soon as possible.

As a workaround, you could try redefining an "insert" method for the entity, that includes such properties (e.g., exclude them from the inserts).
For more information about customizing insert operations, please refer to:
http://www.devart.com/linqconnect/docs/CustomizingOperations.html
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17


Return to LinqConnect (LINQ to SQL support)