A standard way to obtain custom SQLSTATE issued by batches, triggers and stored procedures

Nobody has claimed this yet.

Rather any RDBMS is able to define custom SQLSTATEs. This becomes useful when writing batches, triggers and stored procedures, as the business logic inside that SQL programs has the ability to stop processing and inform the caller on business level exceptions (like "Account limit exceeded." encoded as SQLSTATE 'ATM13' when a user invokes "CALL ATM_withdraw(5000.00)" in a banking application). Sophisticated front ends (hence, calling applications) might want to differentiate between several custom SQLSTATEs (e. g. SQLSTATE 'ATM13' compared to 'ATM27' which might be a completely different business level reason to stop processing of the SQL program).

JDBC defines an unambiguous way to obtain these custom SQLSTATEs by SQLException.getSQLState(). Unfortunately, JPA does not but enforces product-specific workarounds. While some standard outcomes are provided as specialized PersistenceExceptions (e. g. EntityExistsException, EntityNotFoundException, etc.) there is no special exception for "custom causes". Also, it is not clearly said that EACH compliant entity manager MUST provide an SQLException containing the root cause to the PersistenceExceptions. While EclipseLink does provide this SQLException, it does not as a direct cause of PersistenceException. It seems to be even valid that compliant entity managers do not provide the causing SQLException at all.

As a solution I could imagine two alternatives:

(A) Define in the JPA spec that in case an entity manager is using JDBC to execute SQL statements, any thrown SQLExceptions MUST be returned DIRECTLY by PersistenceException.getCause().

(B) Define in the JPA spec that in case a database backend operation results in an unknown SQLSTATE, the returned PersistenceException MUST be an instance of the new class UnknownSQLState, and can be obtained by invoking UnknownSQLState.getSQLState().

While (A) is expected to be rather simple to implement by ORM vendors, in fact it is (B) which would provide the most unambiguous and simple solution to the application programmer.

Review the JPA specification's PersistenceException and JDBC SQLException handling requirements, then compare alternatives (A) and (B) against the EclipseLink behavior described here. Done means the issue has a decided, unambiguous specification change for retrieving custom SQLSTATEs.

Written by the indexing model from the issue text.