Friday, January 18, 2013

How To Implement Gapless Sequence in ADF BC

We all know how to insert new records using ADF BC. But if you have a sequence in the DB for primary key attribute and there is requirement to make sure assigned sequence values are always gapless - few extra steps are needed. When you hit Create button, ADF will assign sequence value immediately - often user may rollback transaction and sequence value will be lost. In order to prevent gaps in the sequence we can use two sequences - one temporary for Create and then real sequence will be applied during commit time from doDML method.

Download sample application - CreateSequenceApp.zip. You can access and run this sample deployed on Oracle Cloud instance - CreateSequenceApp on Oracle Cloud live.

There are two ADF BC framework methods overridden on EO level (you can do the same in generic class) - create() and doDML(). Firstly from create() method we are getting temporary value from the dummy sequence and assigning it:


Then finally when user decides to commit transaction and insert new row, in doDML() method we are substituting temporary key value with real value from the gapless sequence:


Sequence names are retrieved from attribute custom property:


Both sequences are defined in Oracle Cloud DB:


You can test it directly on Oracle Cloud live application instance - press Create button to insert new record. Temporary value 7 is assigned (temporary value can be hidden for new records, I made it visible only for the demo):


Type all mandatory values and commit:


Gapless sequence value 208 will be assigned:


Do a few more tests - insert new record but don't commit and press Undo:


Do the same thing twice, you should see that temporary sequence value is increasing with each time:


Insert new record again and complete transaction, you will see that next gapless value 209 is assigned:

14 comments:

Muhammad Nasir said...

Nice Work ! :)

manohar veerapaneni said...

Hi Andrejus,
Thank you for publishing new concepts.

I am trying to implement the same with master-detail relationship, which has a join on the sequence number. I tried to insert a record into master table with dummy sequence and created another row in child using its viewlink which is defined on the sequence of parent/master. While saving the data I am getting parent key not found error.
Below is the stack trace...
[1073] java.sql.SQLTransactionRollbackException: ORA-02091: transaction rolled back
ORA-02291: integrity constraint (PRISM_DEVP1.REFBIP_PATH641) violated - parent key not found

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:457)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:397)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:389)
at oracle.jdbc.driver.T4C7Ocommoncall.processError(T4C7Ocommoncall.java:100)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:476)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:204)
at oracle.jdbc.driver.T4C7Ocommoncall.doOCOMMIT(T4C7Ocommoncall.java:80)
at oracle.jdbc.driver.T4CConnection.doCommit(T4CConnection.java:607)
at oracle.jdbc.driver.PhysicalConnection.commit(PhysicalConnection.java:5061)
at oracle.jdbc.driver.PhysicalConnection.commit(PhysicalConnection.java:5078)
at oracle.jbo.server.DefaultTxnHandlerImpl.handleCommit(DefaultTxnHandlerImpl.java:115)
at oracle.jbo.server.DBTransactionImpl.doCommit(DBTransactionImpl.java:5142)
at oracle.jbo.server.DBTransactionImpl.commitInternal(DBTransactionImpl.java:2141)
at oracle.jbo.server.DBTransactionImpl.commit(DBTransactionImpl.java:2374)


Thanks,
Manohar

Andrejus Baranovskis said...

Hi,

This is because ADF doesnt guarantee that Master record will be posted first to DB. You need to override post changes method on Detail EO and post Master record first from there (is described in Oracle ADF dev guide on OTN).

Andrejus

manohar veerapaneni said...

Thanks Andrejus for your response.

We have defered the foreign key constraint to Commit time.

I just tried my scenario by removing commitSequence (doDML updating sequence) and I could create master-detail records with NO issues.

I am suspecting that when I create a detail record via VL, master column of detail VO attribute will be populated with CreateSequenceName sequence value. While committing as we are changing the master column with commitSequence the detail record couldn't find the parent.

Thanks,
Manohar

Andrejus Baranovskis said...

Please implement postChanges method as per developer guide - it will work for gapless sequence as well. It will work just same as DBSequence type works, but then you need to have DB trigger.

Andrejus

jana said...

Just a bit curios andrejus. DO we need to override post changes in the Detail EO , even if the association is a "composition association" ?

Tata Venkat Guru said...

Hi
Thank you for publishing this post, I did the same but , generating sequence and inserting is working fine but the thing is it is not going to effect in database, when i gave committed the records at Application Module test, May i know the reason why it is not affecting as such..
Thanks in Advance..

Ahmad Salman said...

Thank you for all your post, it is really helpful.

I follow your your example but the first sequence (Temporary sequence) that is assigned to the primary key in Create method is stored in DB. it seems to me that the application did not reach dml method.

Andrejus Baranovskis said...

This should work I'm quite sure.

Andrejus

Lovin Joy Varghese said...

How to hide temporary key generated while create operation?

Andrejus Baranovskis said...

You can check if current row is new, then hide key value - until row status is changed.

Andrejus

Dilini Chathurani said...

Hi Andrejus,
If table control had more than data in view area (therefore table had scoll bar) and when user edit record in not initial display area (drag scoll down and record got to display area), After save record go down (non display area). That mean scoll go up. how to handle this? I changed table display row property in to selected. Then it work in my machine , But after deployed in Weblogic it doesn't work

Anil Sharma said...

When I run the application i got following error
(oracle.jbo.DMLException) JBO-27200: JNDI failure. Unable to lookup Data Source at context database.

Andrejus Baranovskis said...

This means you need to define data source on your WLS.

Andrejus