Why use DBSequence at all?

[Jernej Kaše]

On a recent project we were using DBSequence for PK sequencing, but we had nothing but trouble with them:

-          DBSequence PK can't be directly used in LOV, as DBSequence can't be assigned to an Integer field

-          No matter what we did we couldn't get around »could not fould the owning Entity...« error in one case; interesting enough, the same use case worked on a simple adf page, but not in bounded TF jsff

 

So we were discussing good reasons to use DBSequence and.. we came up with nothing. Well, among the ideas were:

1.       DBSequence prevents gap in sequencing
Which is NOT TRUE, a transaction can start by inserting master record, getting sequence from DB and fails while inserting detail record(s) – and there's a gap in your sequencing

2.       DBSequence does not require an extra roundtrip to the DB when a record is created
Which is an interesting point, but we came up with an idea to implement:
- s sequence on a db layer which is incremented lat's say by 100
- a singleton which reads next sequence from DB, but then assigns values from the memory
- base entity to implement reading from the singleton

 

So right now I see no benefit of using DBSequence whatsoever, it's just a source of potential problems. Are we missing something?

Thanks
Jernej

[Steve Muench]

The main purpose of DBSequence is to provide an implementation of a temporary unique key in memory, which is refreshed by some DB-assigned key (not necessarily using a SEQUENCE, but typically done using one) at creation time. I'm not familiar with the problem with LOVs, but if you want you can try this alternative which leverages ADF's, perhaps underdocumented, built-in eager unique key ID generating mechanism used by Fusion Applications:

1. Define an application resources connection in your application named ROWIDAM_DB and point it to the database where you ran the SQL script in #1
2. Use the following one-line Groovy expression for the expression-valued default value of your Entity Object's primary key attribute of type NUMBER(18) in the database:
• oracle.jbo.server.uniqueid.UniqueIdHelper.nextId

If you want to force the unique id mechanism to use a datasource named "java:comp/env/jdbc/YourDatasourceDS", then you can add the following code in a static initializer block somewhere in your app where it will be called before the first time any unique id is requested. If you do this, then you need not define the ROWIDAM_DB connection mentioned in #2 above.

if (PropertyDefaultContext.getInstance().getDirect(PropertyMetadata.ENV_ROWID_AM_DS_NAME.pName) == null){

PropertyDefaultContext.getInstance().setDirect(PropertyMetadata.ENV_ROWID_AM_DS_NAME.pName,"java:comp/env/jdbc/YourDatasourceDS");

}

If you get an error about a missing table when you try the above, then do the following:

• Run the SQL script ./oracle_common/modules/oracle.adf.model_11.1.1/bin/bc4jUniqueId.sql

The reason I put this last is because it would already have been run if your schema is related to a Oracle Applications schema, and you would *NOT* want to re-run this SQL script in a production database as it would reset the sequence number generator.

--
You received this message because you are subscribed to the ADF Enterprise Methodology Group (http://groups.google.com/group/adf-methodology). To unsubscribe send email to adf-methodolo...@googlegroups.com
 
All content to the ADF EMG lies under the Creative Commons Attribution 3.0 Unported License (http://creativecommons.org/licenses/by/3.0/). Any content sourced must be attributed back to the ADF EMG with a link to the Google Group (http://groups.google.com/group/adf-methodology).

[Jernej Kaše]

Very interesting helper thanks, but from what I see the main purpose is to have a cross-db sequencing, right?

 

If target DB is Oracle, then a simpler approach should be OK. I'm faimilliar with DBSequence, but in practice it seems to create more problems than it solves.

[Steve Muench]

Yes, the DBSequence isolates the DB-assigned key from the technology used to assign the key so if DB agnosticism is important, that would be a "pro" in favor of choosing DBSequence.

[Aino]

Hi,

We've used DBSequence in the past (10g), but that caused more problems that in solved, especially with handling master-details in one transaction. In the end we've always retrieved the id from a db sequence via an overridden create method or Groovy expression and never use dbsequence anymore.

That said, this is experience from the past and I've not tried the dbsequence again, but I'm still hesitant to use it.

Ciao

  Aino