Sunday, January 27, 2008
Thursday, May 31, 2007
yadda yadda yadda another test
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 (
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
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.
If we consider the HR.employees tables named constraints, of which there are 4 not null constraints, 1
public class MessageBundle1 extends ListResourceBundle {
private static final Object[][] sMessageStrings =
{ "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" }};
return sMessageStrings;
}
}
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
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
To my knowledge JDeveloper 10.1.3+ has no facility with the EOs to validate
Beating the database constraints at their own game in
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
Very big title about nothing in particular that goes with cheesse
test blah test blah bigwordjoinedtogethertest blah test blah bigwordjoinedtogethertest blah test blah bigwordjoinedtogethertest blah test blah bigwordjoinedtogethertest blah test blah bigwordjoinedtogethertest blah test blah bigwordjoinedtogethertest blah test blah bigwordjoinedtogethertest blah test blah bigwordjoinedtogethertest blah test blah bigwordjoinedtogethertest blah test blah bigwordjoinedtogether.
test blah test blah bigwordjoinedtogethertest blah test blah bigwordjoinedtogethertest blah test blah bigwordjoinedtogethertest blah test blah bigwordjoinedtogethertest blah test blah bigwordjoinedtogethertest blah test blah bigwordjoinedtogethertest blah test blah bigwordjoinedtogethertest blah test blah bigwordjoinedtogether.