Wednesday, 30 May 2007

Don't constrain yourself: displaying alternative database constraint error messages with ADF BC

An existing SQL database such as Oracle will have a full range of table constraints, including primary keys, unique keys, foreign keys, not nulls and check constraints. JDeveloper's ADF Business Components (ADF BC) provides a number of different mechanisms for validating data before it hits the database. However does ADF BC provide any mechanisms for overriding the database constraint error messages if they do occur, supplying an alternative "user friendly" error message?

The following post was raised on request for a client to explain the ADF BC facility provided. The discussion assumes you are familiar with Oracle's default schema "HR" and its associated table employees, and the constraints implemented in the employees table:

CREATE TABLE employees
(employee_id NUMBER(6,0)
,first_name VARCHAR2(20)
,last_name VARCHAR2(25) CONSTRAINT emp_last_name_nn NOT NULL
,email VARCHAR2(25) CONSTRAINT emp_email_nn NOT NULL
,phone_number VARCHAR2(20)
,hire_date DATE CONSTRAINT emp_hire_date_nn NOT NULL
,job_id VARCHAR2(10) CONSTRAINT emp_job_nn NOT NULL
,salary NUMBER(8,2)
,commission_pct NUMBER(2,2)
,manager_id NUMBER(6,0)
,department_id NUMBER(4,0)
,CONSTRAINT emp_salary_min CHECK (salary > 0)
,CONSTRAINT emp_email_uk UNIQUE (email)
,CONSTRAINT emp_emp_id_pk PRIMARY KEY (employee_id)
,CONSTRAINT emp_dept_fk FOREIGN KEY (department_id)
REFERENCES departments (department_id)
,CONSTRAINT emp_job_fk FOREIGN KEY (job_id)
REFERENCES jobs (job_id)
,CONSTRAINT emp_manager_fk FOREIGN KEY (manager_id)
REFERENCES employees (employee_id));

Overriding the database constraint error messages

The Oracle database isn't known for displaying particularly useful or friendly error messages when a table constraint is violated. For example if we create a new HR.employees record with an unknown DepartmentId value, the database throws the following error:

ORA-02291: integrity constraint (HR.EMP_DEPT_FK) violated - parent key not found

This message is not particular useful to the average user as they wont know what EMP_DEPT_FK is, what an integrity constraint is, or why the parent key is not found.

Via ADF BC can we provide a more useful message?

Section "25.8.3 How to Customize Error Messages for Database Constraint Violations" of the "Application Development Framework Developer's Guide for Forms/4GL Developers" manual outlines that a standard ADF BC project can be extended with a custom message bundle to customise the database's constraint error messages when caught by the midtier. Note to implement this, as noted in section 25.8.3, you must follow steps 1 to 5 of section 25.8.1. Take care to follow the special Note in step 3 if you're using JDev 10.1.3+, though it appears unnecessary in JDev 11g.

Section 25.8.3 shows us how to create a custom message bundle class for our ADF BC project, which is comprised of key-value message pairs, where we can list our constraint names and the custom message we wish to show respectively.

If we consider the HR.employees tables named constraints, of which there are 4 not null constraints, 1 uk, 1 pk, and 3 fk constraints, we could create the following custom message bundle class to cover off all the constraints:

public class MessageBundle1 extends ListResourceBundle {

private static final Object[][] sMessageStrings = new String[][] {
{ "EMP_LAST_NAME_NN" ,"A not null error message0" }
,{ "EMP_EMAIL_NN" ,"A not null error message1" }
,{ "EMP_HIRE_DATE_NN" ,"A not null error message2" }
,{ "EMP_JOB_NN" ,"A not null error message3" }
,{ "EMP_SALARY_MIN" ,"A check error message4" }
,{ "EMP_EMAIL_UK" ,"A unique key error message5" }
,{ "EMP_EMP_ID_PK" ,"A primary key error message6" }
,{ "EMP_DEPT_FK" ,"A foreign key error message7" }
,{ "EMP_JOB_FK" ,"A foreign key error message8" }
,{ "EMP_MANAGER_FK" ,"A foreign key error message9" }};

protected Object[][] getContents() {
return sMessageStrings;
}
}

Some caveats to be aware of:

The constraint names in the message bundle class must be in uppercase to match the case-specific constraint name raised by the database.

The HR schema has named the not null constraints within its tables explicitly. In your schema if you choose not to name the not null constraints, Oracle will generate a constraint name of the format SYS_C00000n where n is a counter incremented for each auto named constraint created by the database. It's fine to refer to these constraint names instead in your custom message bundle. However be aware if you recreate your schema and Oracle chooses to name the constraints starting at a different n value, obviously the ADF BC custom message bundle approach above will not work.

If you've setup your ADF BC Entity Object (EO) to have the same primary key as the table, the EO includes a mechanism for validating primary key values within the ADF BC mid-tier before it gets to the database. Because of this the alternative error message above may or may not be shown.

The EO pk routine checks any created/updated pk value against all pk values for the existing EO that have been cached in the midtier at submit time. If it finds a matching value (or in other words that the values aren't unique) it throws the following JBO (JDeveloper) exception where X is the primary key value(s):

JBO-25013: Too many objects match the primary key oracle.jbo.Key[X ].

In this case you will not see your message, but the JBO error above.

EOs and their associated View Objects (VOs) are by default configured to lazy fetch records from the database into the midtier in ranges (record sets of say 10 records) based on user requests. This lazy loading feature is designed to make it unnecessary to load all the records from a table into the midtier for speed and memory efficiencies while taking advantage of the fact the user probably doesn't want to view all 1000 employee records at once, but will be happy viewing a sub set at a time.

As noted above, if the record has been fetched into the midtier's ADF BC memory structures, the JBO-25013 error will display (without our custom message) when a new record's pk violates the pk constraint at submit time. However if the record with the matching pk value hasn't yet been fetched into the midtier, the check will be left to the database and is done at commit time. In this case the database will raise the error message, and our custom message bundle message will also be shown.

To my knowledge JDeveloper 10.1.3+ has no facility with the EOs to validate uk values similar to the pk check above. The JDeveloper 11g Technical Preview release introduces a new uk mechanism as separate to the pk facility and should be considered.

Beating the database constraints at their own game in ADF BC

In my opinion however, good user interface (UI) design dictates that it would be better to stop the database errors occurring in the first place to provide a relatively more satisfying experience for the user. How do you do this? Well you stop the user from ever entering data that violates the constraints in the first place.

The first set of suggestions here are obvious but need to be stated:

Using and generating surrogate read-only primary keys - if you're allowing the user to create or edit records, and the table has a surrogate primary key generated via a sequence in the database that guarantees uniqueness, make the field read-only (or hidden). Then within the ADF BC EO for the associated table, populate the pk programmatically on creating a new record via the sequence. In this fashion the user can never enter a non unique pk for the primary key value.

Use poplists and list of values (LOVs) for foreign keys - if the user in creating or editing a record needs to enter a foreign key value, don't allow them to manually type potentially invalid foreign key values. Instead provide poplists and LOVs for the user to select a valid value from.

Make mandatory database fields mandatory in ADF BC objects and the UI components - the ADF BC EO and VO attributes have a property Mandatory that will enforce the user entering a value before it hits the database. In turn the UI technologies usually provide facilities to check this too. For instance the 10.1.3 ADF Faces component has a required boolean attribute, that shows a star next to the item at runtime indicating it is mandatory, and fires Javascript to check for a value and raises an error otherwise at runtime. (Admittedly this is done by default, but some users may miss this point)

Beyond the obvious ideas, JDeveloper also provides a number of validators at the EO level that enforce data integrity checks and supply custom error messages if a problem is found before they hit the database. For the brevity of this post they will not be considered here. I note that Jan Kettenis under the recent post UML Rules is currently writing a whitepaper on implementing business rules in ADF BC which hopefully will cover the majority of the validators and a useful source of information beyond Section "6.7 Using Declarative Validation Rules" in Oracle's developer's guide.

13 comments:

John Stegeman said...

Chris - nice post. This question comes up on a regular basis, and it's nice not to have to say "go find the not-yet-or-ever-to-be-finished JSF Toy Store demo."

Chris Muir said...

Thanks John. Thought of it while I was "at the pub" ;)

Anonymous said...

I would like save error codes and corresponding custom error messages not in the Java code, but in a delegated database table. So my question is, how would you advice to read database table content and create a two-dimensional array of it?

Chris Muir said...

Anonymous (it would be better if you posted with a real name), I've been meaning to write up such a post for some time but haven't the time just yet.

Possibly have a look at Jan Kettenis's blog entry How to Pimp ADF BC Exception Handling. This may give you the seeds of where the choke point is in ADF BC for overriding the default behaviour to retrieve from the message bundles, and instead write code to retrieve from the database.

Hope this helps.

CM.

dil said...

when we allow users to enter data via the front end, that we should never allow the user to eneter the primary key, and
instead should use db sequences as primary keys?

dil said...

is there any restriction in adf table can't allow enter data via front ends for primary key fields

Chris Muir said...

Dil, in answer to your 2 questions:

1) If your PK column is based on a sequence it makes sense for ADF BC to fetch the seq number for the user and the user doesn't enter a value.

2) Not sure what you mean by "restrictions". If you mean can we stop the user overwriting the fetched seq number, yes you can by selecting the EO or VO attribute in the appropriate design time editor, then setting it's Update on Insert option to false.

For your reference both questions would have been better posted on the OTN JDeveloper forums.

Regards,

CM.

Patrik said...

Hi Chris,

I have a question regarding this topic, see my OTN Forum post at http://forums.oracle.com/forums/thread.jspa?messageID=3736085#3736085

Thanks,
Patrik

Chris Muir said...

Hi Patrik

Yes, I can see your forum post. As somebody else has answered I was waiting for you to give them reply, a courtesy as they've taken time out to post for you.

Regards,

CM.

Patrik Varga said...

Chris,

sorry but I don't get your point as somebody else has answered to somebody else (not me), so I don't think I got any replies.
Btw, I was posting to that specific forum because it seemed the most appropriate place for this question (and not your blog's comment area), especially after your post there.

Thanks again,
Patrik

Chris Muir said...

See my forum post.

Edwin W in Chicago said...

Nice blog, cant seem to find the reference to 25.8.1 steps 1-5, can you direct me?

Chris Muir said...

Assuming you specifically want the 10.1.3 documentation, the guide can be found here:

http://download.oracle.com/docs/cd/B32110_01/web.1013/b25947/toc.htm

...and section 25.8.1 here:

http://download.oracle.com/docs/cd/B32110_01/web.1013/b25947/bcadvgen.htm#sthref2387

CM.