SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll : 2.6.8.1013 SD.LLBLGen.Pro.LinqSupportClasses.NET35.dll : 2.6.8.1110 SD.LLBLGen.Pro.DQE.Oracle10g.NET20.dll : 2.6.8.1009

.NET 3.5, Adapter, General2008, Oracle 9i

I have read through all the posts here with "OracleTriggerSequences" as well as the help topic on the subject but I am still getting the below exception and am out of ideas for the moment:

Exception:

SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException occurred

Message="An exception was caught during the execution of an action query:

ORA-01400: cannot insert NULL into (\"CRMA\".\"BATCHES\".\"BATCH_ID\").

Check InnerException, QueryExecuted and Parameters of this exception to

examine the cause of this exception."

Source="SD.LLBLGen.Pro.ORMSupportClasses.NET20"

RuntimeBuild="10132008"

RuntimeVersion="2.6.0.0"

QueryExecuted="\r\n\tQuery: INSERT INTO \"CRMA\".\"BATCHES\" (\"BATCH_ID\",

\"BILLING_AREA_ID\", \"BILLING_CENTER_ID\", \"CODER_ID\", \"REVIEWER_ID\",

\"STATUS_CODE_ID\", \"DATA_SOURCE_ID\", \"REVIEW_DATE\", \"SERVICE_DATE\",

\"BATCH_PERIOD\", \"REVIEW_TYPE\") VALUES (:BatchId1, :BillingAreaId2,

:BillingCenterId3, :CoderId4, :ReviewerId5, :StatusCodeId6, ataSourceId7,

:ReviewDate8, :ServiceDate9, :BatchPeriod10, :ReviewType11)\r\n\tParameter:

:BatchId1 : Int64. Length: 0. Precision: 0. Scale: 0. Direction:

Input. Value: <undefined value>.\r\n\tParameter: :BillingAreaId2 :

Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input.

Value: 4059.\r\n\tParameter: :BillingCenterId3 : Int64. Length: 0.

Precision: 0. Scale: 0. Direction: Input. Value: 3.\r\n\tParameter:

:CoderId4 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input.

Value: 2809.\r\n\tParameter: :ReviewerId5 : Int64. Length: 0. Precision: 0.

Scale: 0. Direction: Input. Value: 2808.\r\n\tParameter: :StatusCodeId6

Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1. \r\n\tParameter: ataSourceId7 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.\r\n\tParameter: :ReviewDate8 : Date. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 11/11/2008 12:00:00 AM.\r\n\t Parameter: :ServiceDate9 : Date. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 11/4/2008 12:00:00 AM.\r\n\tParameter: :BatchPeriod10 : Date. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 11/1/2008 12:00:00 AM. \r\n\tParameter: :ReviewType11 : String. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: \"M\".\r\n" StackTrace: at SD.LLBLGen.Pro.ORMSupportClasses.UnitOfWork2.Commit(IDataAccessAdapter adapterToUse, Boolean autoCommit) at SD.LLBLGen.Pro.ORMSupportClasses.UnitOfWork2.Commit(IDataAccessAdapter adapterToUse) at CRMA.ServiceLibrary.DataServer.CommitUnitOfWork(UnitOfWork2 uow) in C:\Source\CRMA\Dev.Lgen\DAL\CRMA.ServiceLibrary\DataServer\DataServer.cs:line 128 InnerException: Oracle.DataAccess.Client.OracleException Message="ORA-01400: cannot insert NULL into (\"CRMA\".\"BATCHES\".\"BATCH_ID\")" Source="Oracle Data Provider for .NET" ErrorCode=-2147467259 DataSource="CRMADEV1" Number=1400 Procedure="" StackTrace: at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure) at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src) at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery() at SD.LLBLGen.Pro.ORMSupportClasses.ActionQuery.Execute() InnerException:

Save Code:

        public int CommitUnitOfWork(UnitOfWork2 uow)
        {
            int entitiesAffected = 0;

            using (IDatabaseAdapter adapter = DataAccessAdapterFactory.Create())
            {
                string triggerSeq = System.Configuration.ConfigurationManager.AppSettings[
                    "OracleTriggerSequences"];
                Debug.WriteLine(string.Format("OracleTriggerSequences: {0}", triggerSeq));
                entitiesAffected = uow.Commit(adapter);
                return entitiesAffected;
            }
        }

Generated code in DB specific assembly (hand edited the identity and sequence name values for now):

        /// <summary>Inits BatchesEntity's mappings</summary>
        private void InitBatchesEntityMappings()
        {
            base.AddElementMapping( "BatchesEntity", "CRMADEV1", @"CRMA", "BATCHES", 16 );

            //base.AddElementFieldMapping( "BatchesEntity", "BatchId", "BATCH_ID", false, (int)OracleDbType.Decimal, 22, 0, 10, false, "", null, typeof(System.Int64), 0 );

            this.AddElementFieldMapping("BatchesEntity", "BatchId", "BATCH_ID", false, (int)OracleDbType.Decimal, 22, 0, 10, true, "CRMA.BATCHES_SEQ", null, typeof(System.Int64), 0);

// ... remaining removed
}

Output:

OracleTriggerSequences: true Method Enter: UnitOfWork2.Commit(2) Method Enter: DataAccessAdapterBase.DetermineActionQueues(7) Method Exit: DataAccessAdapterBase.DetermineActionQueues(7) Method Enter: DataAccessAdapterBase.StartTransaction Method Enter: DataAccessAdapterBase.OpenConnection Method Exit: DataAccessAdapterBase.OpenConnection Method Exit: DataAccessAdapterBase.StartTransaction Method Enter: DataAccessAdapterBase.PersistQueue Method Enter: CreateInsertDQ Method Enter: CreateSingleTargetInsertDQ Generated Sql query: Query: INSERT INTO "CRMA"."BATCHES" ("BATCH_ID", "BILLING_AREA_ID", "BILLING_CENTER_ID", "CODER_ID", "REVIEWER_ID", "STATUS_CODE_ID", "DATA_SOURCE_ID", "REVIEW_DATE", "SERVICE_DATE", "BATCH_PERIOD", "REVIEW_TYPE") VALUES (:BatchId1, :BillingAreaId2, :BillingCenterId3, :CoderId4, :ReviewerId5, :StatusCodeId6, ataSourceId7, :ReviewDate8, :ServiceDate9, :BatchPeriod10, :ReviewType11) Parameter: :BatchId1 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: <undefined value>. Parameter: :BillingAreaId2 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 4059. Parameter: :BillingCenterId3 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 3. Parameter: :CoderId4 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 2809. Parameter: :ReviewerId5 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 2808. Parameter: :StatusCodeId6 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1. Parameter: ataSourceId7 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1. Parameter: :ReviewDate8 : Date. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 11/11/2008 12:00:00 AM. Parameter: :ServiceDate9 : Date. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 11/4/2008 12:00:00 AM. Parameter: :BatchPeriod10 : Date. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 11/1/2008 12:00:00 AM. Parameter: :ReviewType11 : String. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: "M".

Sequence query: SELECT "CRMA"."BATCHES_SEQ".CURRVAL FROM DUAL
Executes before INSERT: False

Method Exit: CreateSingleTargetInsertDQ Method Exit: CreateInsertDQ Method Enter: DataAccessAdapterBase.ExecuteActionQuery Method Enter: DataAccessAdapterBase.OpenConnection Method Exit: DataAccessAdapterBase.OpenConnection Method Exit: DataAccessAdapterBase.ExecuteActionQuery Method Enter: DataAccessAdapterBase.Rollback Method Exit: DataAccessAdapterBase.Rollback

Trigger:

CREATE OR REPLACE TRIGGER batches_seq_trg before insert on crma.batches referencing new as new old as old for each row WHEN ( new.batch_id = 0 ) begin select batches_seq.nextval into :new.batch_id from dual; end;

Geoff, did you select the sequence in the designer?

No as indicated I set the sequence by hand in the code the designer generates. I am assuming this would be no different than setting it in the designer. In the code I supplied the commented out line is what was originally done by designer and I replaced it with the next line that indicates field is identity and specifies the sequence name.

Generated code in DB specific assembly (_hand edited the identity and sequence name values for now_):

Why would you alter the code by hand and not the project in the designer?

Have you tried without the trigger, if that results in a valid value (and thus setting the trigger setting in the config to false) ?

The trace is clear: the sequence query is executed AFTER the insert. So the procedure is this: - insert takes place - trigger is activated, should insert proper value into row. <<<<<< This fails. - sequence query is ran (see tracelog) and pulls value from CURRVAL - sequence query reports back sequence value to o/r core, which inserts the value in the entity field.

ORA-01400: cannot insert NULL

It appears to me that either the sequence or the trigger is not working correctly. As the query generated by LLBLGen Pro looks fine to me passing 0 for the BatchId, and if the trigger was working then it shouldn't have complained about the insertion of a NULL value.

It might be that the follwoing query returns null.

select batches_seq.nextval
from dual;

The error is in the trigger I think: WHEN ( new.batch_id = 0 )

this only selects rows where new.batch_id is 0, but is that the case? It's not set to a value yet, so it should be NULL.

Yeah that is probably the issue. I do not currently have the permisison to modify the trigger to test it out though. Since the field does not allow null I suppose there was no reason to check for null instead of 0 in the trigger.

I assumed that since the primary key field (BATCH_ID) does not allow null, and since the entity had 0 for the value, that zero would be passed in. Looking at the output though, I see Undefined Value. Would there be an easy way to change things on the .NET side to pass zero instead of null in this situation? I am not opposed to altering the trigger but it looks like every trigger on each table is written this same way so...

You should set the field (BatchId) to 0 then.

That is what I am saying though; BatchId was/is zero but LLBLGEN appears to be sending null across as the oracle paramater value. BatchId is not nullable. I am guessing there is something in LLBLGEN to not pass a value / pass null if the field is a sequence field?

I did get to the point where I was able to modify the trigger and if I check it for NULL as well, it works. However I would prefer not to have to modify every sequence trigger if possible and am still curious why null is being sent across and not zero?

Mmm. Will test that.

null is sent, because the field has no value, so it's set to null.

If you set the field to 0, would that work? It's possible the field is marked as readonly due to the fact it's set as an identity field and setting it isn't possible, could you try?

Setting the BatchId property on the entity to zero worked. It seemed a bit odd to do that though as the property already had a value of zero. Prior to setting the property, the field's CurrentValue was null, after explicitly setting it to the same value, zero.

The field was not marked as readonly.

I overrode OnInitialized and set the key sequence field to zero when field state was new.

Thanks

If I'm following you correctly, the following should be true.

• The entity field showed a value of Zero by default, and that's because the field doesn't allow nulls.

• When Saving the value of Zero, is not sent to the database, most probably that's because the field's IsChanged property is set to false, so the framework ignores it when creating the INSERT query. a workaround is to set the IsChanged property to true.

• Now I supppse everything is working when you explicitly set the field value to Zero in the OnIntialized event.