Tuesday, 15 May 2007

Oracle ADF Committing master and relevant detail rows simultaneously

There is no doubt that using a trigger to provide unique id's from a database sequence is the best way to handle primary keys. (By the way, I wonder which version of Oracle database will provide an auto-increment data type like MySQL?) There are cases however that require you to create master and detail records in the application server memory and then commit everything simultaneously like in the case of an e-shop where you have to keep both the order header and lines uncommitted until the user finally decides to place the order.

In cases like these, using the DBSequence type for the corresponding entity object attribute might get you into trouble, like I did just yesterday. Theory says that the value of a DBSequence key attribute is a (unique) negative number that later becomes the value assigned by the database trigger. What I did, was try to create detail rows programatically but the master attribute reference for these rows became the negative DBSequence value assigned to the header line at the start of the transaction. So when I tried to do the final commit I got a very clear error explaining that the foreign key constraint for the detail rows was violated.

The only reliable way I found to get myself out of this was to turn back to my old JDeveloper 10g Handbook (published back in 2004) and dig out the following code to allow my entities to obtain an id value directly from the database sequence, thus moving the trigger code from the DB to the application server. So overriding the create method for my custom order header entity object gave me something like this :


import oracle.jbo.server.SequenceImpl;

  ...

  /**
   * Provide a new order id from the SQ_ORDERS database sequence.
   */
  protected void create(AttributeList attributeList)
  {
      super.create(attributeList);
    
      // get a new sequence value for the order ID
      SequenceImpl sqOrders = new SequenceImpl("SQ_ORDERS", getDBTransaction());
      setOrderId( sqOrders.getSequenceNumber());
  }

4 comments :

Marcus Aurelius said...

Hi.

I am using JDeveloper 10.1.3.3.0 and have a similar (or identical?) problem. The master row usually exists in the database and i am going to create a detail row.

However, when i create the detail row, i cannot see the negative values you describe in the View Objects. According to the logs, the keys contain simply null. After commit, the detail row has the trigger-assigned ID, but the master row does not. I am currently copying the new ID to the master row and commiting again, but this is ugly.

What i would like to ask is:

- Is really SequenceImpl the recommended solution in this situation for the version of JDeveloper that i am using? I guess i will need give up using DBSequence at all (this makes me suspicious).

- Do you think i may have missed something? These are my first steps learning ADF and JDeveloper, so i may be doing something wrong. I am puzzled that i could not see those negative values. Also, i read somewhere that all the negative values (even in other View Objects) would be updated automatically to the trigger-generated ID (maybe this is the case in another version of JDeveloper?)

I hope you can help me understand the alternatives better.
Thank you very much for your attention.

Marcus Aurelius said...

Oops, i asked about a specific JDeveloper version because i thought this was an official Oracle blog (I had several tabs open ;-)).
Sorry if it sounded like you were supposed to know all the details about sequences and JDeveloper versions.

Some Oracle references do encourage DBSequence, but they do not address this situation clearly.

Athanassios Bakalidis said...

Hi, In my experience, there is no "rule of thumb" that tells you when to use that method or another.

The SequenceImpl class is very practical if you intend to create master and detail records programmatically from your model project. (At least it worked just fine for me)

On the other hand, using the DBSequence class as the domain type for your primary keys can save you a lot of trouble, but you have to let the middle tier handle the insertions. i.e. have the user create records from ADF pages.

Again the choice of action depends on the use case.

Thanassis

Marcus Aurelius said...

Thank you very much for your reply! :-)