Friday, January 18, 2013

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.

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:

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 backORA-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)

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).

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.

HiThank 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..

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.

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