Hi, Using Oracle's trigger and sequence way of generating Auto Increment field values. I read that LLblGen Pro does not support refetching these keys after a Entity.Save();

Is there any way I can get around with this. I tried DataAdapter approach, but the Generated DataAdapter classes does not support EntityCollections, so I have lots of code I cannot change now.

Also I cannot change the way the sequence fields are generated. There is no way I cannto have triggers to generate keys ( company policy)

So please , please help me to find a way "As soon as I save an entity, I need to know the KeyValue in the auto field"

I tried the following but no luck

1) MyEntity.Save(); OutPut(MyEntity.SomeProperty) ; // thinking accessing some property will refetch

MyEntity.ID ; // this is 0 , this is the field autogenerated by oracle

2) MyEntity.Save(); MyEntity.Refetch() ;

MyEntity.ID ; // this is 0 , this is the field autogenerated by oracle

Please, Please this is important that I solve it quickly. I dont care if there is performance hit or I need to hit the DB etc..

Thanx a lot

You have to change the DQE's code a bit. I'll give you an example how to do that in the morning. The DQE generates a sequence retrieval query (see the CreateInsertDQ in the runtime library sourcecode) which is executed BEFORE the insert as SELECT sequence.NEXTVAL FROM DUAL;. If you change this to SELECT sequence.CURRVAL FROM DUAL; and set the flag for execution before the insert to false, it will get the right value.

Please keep in mind that triggers for ID's are not supported. You can get around it by this workaround. it's not ideal, as with an update of the Oracle DQE, you have to update your code, but that's pretty minor. A setting for this will be added to the designer in the april/may timeframe. Till then you have to work with the adjusted code of the runtime library.

I am just wondering how are people doing it and how is the Database specific Adapter doing it.

Also my triggers sometimes may have some logic while getting next sequence values ( not for this instance though, so I can use the code you will send me. )

Thanx and I will appreciate you posting without fail. Just a request as I started using 2-3 ORMappers for this projects and failing in every instance.

jakkamma wrote:

I am just wondering how are people doing it and how is the Database specific Adapter doing it.

Most people don't use triggers for this, however sometimes people have to work with a legacy database which has triggers in place. (or are forced to use them through corporate policies, like your situation)

Thanx and I will appreciate you posting without fail. Just a request as I started using 2-3 ORMappers for this projects and failing in every instance.

I'll add a .config setting to the upcoming 1.0.2004.2 version, which allows you to signal the Oracle DQE what kind of sequence queries to execute. This was first scheduled to be a gui setting, but that was postponed, though a .config setting is easily added.

If you open the sourcecode of the Oracle DQE's DynamicQueryEngine.cs, and look in the CreateInsertDQ routine you'll see:

// line 184
if(persistenceInfo.IsIdentity)
{
    insertQuery.AddParameterFieldRelation(new ParameterFieldRelation(field, newParameter));

    // create SequenceRetrievalQuery
    ISequenceRetrievalQuery sequenceQuery = new SequenceRetrievalQuery();
    sequenceQuery.SequenceRetrievalCommand = CreateCommand();
    sequenceQuery.SequenceRetrievalCommand.Connection = connectionToUse;
    sequenceQuery.ExecuteSequenceCommandFirst = true;
    sequenceQuery.SequenceRetrievalCommand.CommandText = String.Format("SELECT {0}.NEXTVAL FROM DUAL", persistenceInfo.IdentityValueSequenceName);
    sequenceQuery.SequenceParameters.Add(newParameter);

    insertQuery.SequenceRetrievalQueries.Add(sequenceQuery);
}

This creates for every sequenced field a sequence query and sets its flag to execute it before the actual query. You should change this code into

if(persistenceInfo.IsIdentity)
{
    insertQuery.AddParameterFieldRelation(new ParameterFieldRelation(field, newParameter));

    // create SequenceRetrievalQuery
    ISequenceRetrievalQuery sequenceQuery = new SequenceRetrievalQuery();
    sequenceQuery.SequenceRetrievalCommand = CreateCommand();
    sequenceQuery.SequenceRetrievalCommand.Connection = connectionToUse;
    sequenceQuery.ExecuteSequenceCommandFirst = false;
    sequenceQuery.SequenceRetrievalCommand.CommandText = String.Format("SELECT {0}.CURRVAL FROM DUAL", persistenceInfo.IdentityValueSequenceName);
    sequenceQuery.SequenceParameters.Add(newParameter);
    insertQuery.SequenceRetrievalQueries.Add(sequenceQuery);
}

Change the assemblyinfo.cs files (there are 2) so that the strong key isn't referenced and build the source with the makefile (first execute vsvars32.bat): nmake /nologo /f makefile_11 clean nmake /nologo /f makefile_11

The .cmd file builds all 3 versions so you probably don't want that

Where do i find these source files ( like DynamicQueryEngine.cs). I donot see them in the install directory

jakkamma wrote:

Where do i find these source files ( like DynamicQueryEngine.cs). I donot see them in the install directory

They're in the runtime library source code folder in the full install.

The beta (1.0.2004.2) has a setting for this, so you have to bridge the period of the beta with your modification.

I am curious why the product does not work for this situation now. It appears that it is using the sequence to insert the record with the correct ID, how ever it does not appear that the value retuned by the sequence query is not being set back to the field in the Entity itself.

jakkamma wrote:

I am curious why the product does not work for this situation now. It appears that it is using the sequence to insert the record with the correct ID, how ever it does not appear that the value retuned by the sequence query is not being set back to the field in the Entity itself.

It does this, but you have to specify for a field which sequence to use. You do this in the designer: open the entity in its editor (right click on the entity -> edit) then select the field and then check the is identity checkbox and select the sequence to use. regenerate the code and it should work.

Trigger inserted sequence values are supported in 1.0.2004.2 through a setting in the config file (1.0.2004.2 is currently in beta), which means that all sequences have to be inserted through triggers.