Hi,

I am looking for advice about how best to approach a couple of problems I have with LLBLGen Pro.

The questions below relate to Oracle 8i and 9i in particular.

Firstly, all the tables in the database I wish to generate code for have their primary key set by a trigger on insert. This cannot be changed in the short term as there is a lot of existing code that relies on this particular method. Setting the primary key column as a sequence in LLBLGen does not achieve the correct result because it gets a value for the sequence before the insert, and then the database overwrites that key with a new one. Is there any other method that anyone is aware of where I can get the key value from the entity class, or am I better trying to amend the template to get the sequence CURRVAL immediately after an insert? If this is the case then some pointers on where to start would be very much appreciated.

The second problem is how best to create a new entity that is an exact copy of an existing entity, differing only by the primary key. In other words I want to duplicate a row in a table.

Any assistance anyone can give is greatly appreciated.

Regards,

Adam.

the_narv wrote:

The questions below relate to Oracle 8i and 9i in particular. Firstly, all the tables in the database I wish to generate code for have their primary key set by a trigger on insert. This cannot be changed in the short term as there is a lot of existing code that relies on this particular method. Setting the primary key column as a sequence in LLBLGen does not achieve the correct result because it gets a value for the sequence before the insert, and then the database overwrites that key with a new one. Is there any other method that anyone is aware of where I can get the key value from the entity class, or am I better trying to amend the template to get the sequence CURRVAL immediately after an insert? If this is the case then some pointers on where to start would be very much appreciated.

The problem is: the insert query consists of 2 parts: a sequence part and the actual insert. As the DQE doesn't know better, it sets the sequence part to be executed before the actual insert. The sequence has to grab the nextval as it doesn't know any better, and passes on the value to the pk field value.

Changing the NEXTVAL sequence directive to CURRVAL doesn't matter, as that will not make the sequence part be executed after the insert, as the DQE doesn't have information about the trigger. The only 'solution' is that you alter the DQE source for oracle and make it execute after the insert, and change "NEXTVAL" into "CURRVAL". It's 2 lines of code you have to change. I haven't tested that though, so it might not work with these simple changes, but it is possible to change these lines to make it work. As all tables in your project use this, it's no problem to change this in the DQE. You only have to make sure that if an update to the DQE comes in, you have to change these lines again. I can't fix this in the current code, as it requires information that's not there. I'll try to fix this in the upgrade in development.

So change:

193: sequenceQuery.ExecuteSequenceCommandFirst = true;
194: sequenceQuery.SequenceRetrievalCommand.CommandText = String.Format("SELECT {0}.NEXTVAL FROM DUAL", persistenceInfo.IdentityValueSequenceName);

into

193: sequenceQuery.ExecuteSequenceCommandFirst = false;
194: sequenceQuery.SequenceRetrievalCommand.CommandText = String.Format("SELECT {0}.CURRVAL FROM DUAL", persistenceInfo.IdentityValueSequenceName);

The second problem is how best to create a new entity that is an exact copy of an existing entity, differing only by the primary key. In other words I want to duplicate a row in a table.

fetch entity, set isnew to true set pk fields (if you use a natural pk) set entity.Fields.IsDirty to true set all IsChanged flags to true of all teh entity.Fields[index] instances. save

Thanks for the info. I am quite happy to maintain my own version of the DQE as I realise that this situation is not common to all Oracle databases, just this particular one.

I downloaded the runtime libraries sourcecode and have amended it as instructed. However, there seem to be some files missing from the archive - in particular the makefile_11 files, resulting in:

NMAKE : fatal error U1052: file 'makefile_11' not found.

Do I need this makefiles, or can I just build the assembly from the VS.NET IDE?

Regards,

Adam.

the_narv wrote:

Thanks for the info. I am quite happy to maintain my own version of the DQE as I realise that this situation is not common to all Oracle databases, just this particular one.

Ok. I'll try to get a feature in for this. You're not alone who has some triggers defined on sequenced tables.

I downloaded the runtime libraries sourcecode and have amended it as instructed. However, there seem to be some files missing from the archive - in particular the makefile_11 files, resulting in:

NMAKE : fatal error U1052: file 'makefile_11' not found.

Do I need this makefiles, or can I just build the assembly from the VS.NET IDE?

You can do that however the make files are easier. The makefiles are not zipped into the zipfile due to a bug in my Finalbuilder script. (I copy . over, which doesn't copy the makefiles). I'll correct this. You do have the makefiles on your harddrive btw, in <llblgen pro installation folder>\RuntimeLibraries\Source\Oracle

Adjust the assemblyinfo file and remove the key reference. To build using vs.net, be sure the oracle project references the right ODP.NET assembly, and in the project properties, release config, you have to add "ODPNET" to the compilation conditional constants. (as it is added in debug). Adjust the assembly name so it is named with .NET11 in teh filename and rebuild the project.

On the command line you can build it too, you should run: vsvars32.bat nmake /nologo /f makefile_11 clean nmake /nologo /f makefile_11

from the Oracle folder.

That worked well - thank you very much. If you do end up working it into the released product, and need someone to test on Oracle 8/9/10 then I'm your man.

Thanks for the excellent support, as usual.

Regards,

Adam.

the_narv wrote:

That worked well - thank you very much. If you do end up working it into the released product, and need someone to test on Oracle 8/9/10 then I'm your man.

. Well, in march the beta for the current upgrade will be up, so I hope that as much customers as possible will beta-test it . Especially the multi-schema stuff which will be present for oracle will be a big win, but also can introduce bugs and perhaps things that don't work that well in the gui, so all help is welcome

Thanks for the excellent support, as usual.

You're welcome