JDev - alternative uses for the ADF BC DBSequence datatype

Thanks to Steve Muench on the OTN JDev Forums, and possibly poor lateral thinking on my part, I've discovered another use for the DBSequence datatype within ADF BC entity objects which I'd like to share.

Under section "6.6.3.8 Trigger-Assigned Primary Key Values from a Database Sequence" of the ADF Developer's Guide for Forms/4GL Developers 10g it details that the DBSequence datatype is useful for entities with a PK populated via a database trigger assigning a sequence. In addition before the record has hit the database, ADF BC will assign a unique negative number as a temporary value to flag to the user the real sequence number has yet to be retrieved.

With this in mind and thanks to Steve's help, for a current client we found that the DBSequence was useful in another scenario not actually involving a database sequence.

My client has an EO with a mandatory PK that is generated by a database function, not a sequence number. The internals of the function are irrelevant, but the function guarantees to generate unique numbers when called. Obviously a sequence number would be a better replacement but there are business reasons why the number must come from the function, including that it has check digits, starts with a 2 digit year, and the main counter starts from 1 each year.

My client also has a business case that the numbers generated from the function aren't wasted. The database doesn't guarantee sequence numbers are allocated contiguously so are a bad fit in this case. As such the numbers should be allocated when the record is committed only, rather than relying on the EntityImpl.create() method, as users may rollback changes after the create() method is called causing gaps in the number chain.

What approaches could we take in attempting to solve this problem?

The first possible answer is to place this logic in the EntityImpl.doDML() method. Yet this isn't appropriate because the method fires after EO validation and doDML() hasn't yet had a chance to assign a value to the mandatory PK. Maybe there is a more appropriate method to override in the EntityImpl to do this? My understanding of the order of EntityImpl methods called are as follows:

EntityImpl.create()
~ user in UI inserts some attributes and submit/commits ~
LOOP through each new EO {
  EntityImpl.validateEntity()
  EntityImpl.prepareForDML()
  EntityImpl.doDML()
} END LOOP
LOOP through each new EO {
  EntityImpl.beforeCommit()
} END LOOP

Because validateEntity() is called before prepareForDML() and doDML(), neither method provide an appropriate place to default the PK attribute as validation has already fired and the mandatory PK error will raise itself.

We could place logic in the validateEntity() to default the value on a new record. However validateEntity() is also called when navigating rows so we run the risk of the user rolling back their changes and losing the generated PK.

We could make the PK non mandatory and update it on the doDML() method. But we'd still like the visual indicator on the UI that the field is mandatory and to derive this from the EO attribute properties rather than some properties hack which the next programmer will miss.

So we seem to be a bit stuck in finding a solution. DBSequence to the rescue!

The workaround is to set the PK datatype to a DBSequence datatype in combination with the doDML() method. When EntityImpl.create() is called the DBSequence sets the PK attribute to -1. Within our EntityImpl.doDML() method, we detect if we're executing an Insert, if the value is -1, and call our database function to derive the attribute, such as:

protected void doDML(int operation, TransactionEvent e) {
  if (operation == DML_INSERT) {
    // Call our function to update the PK attribute
  }
  super.doDML(operation, e);
}

As you can see the DBSequence provides a useful work around here. In fact it wouldn't be a work around at all if the datatype wasn't called DBSequence which makes you think it's for sequences only.