Database Exception Handling In JDBC

Database Exception Handling

- The truth is errors always occur in software programs.
- Often, database programs are critical applications.
- And it is imperative (very important) that errors be caught (fixed) and handled gracefully.
- Programs should recover and leave the database in a consistent (secure) state.
- Rollback-s used in conjunction with Java exception handlers are a clean way of achieving such a requirement.
- The client (program) accessing a server (database) needs to be aware of any errors returned from the server.
- JDBC give access to such information by providing two levels of error conditions:

1) SQLException and
2) SQLWarning.

- SQLExceptions are Java exceptions.
- Which, (if not handled ) , will terminate the application.
- SQLWarnings are subclasses of SQLException.
- But they represent non-fatal (non-serious) errors or unexpected conditions, and, can be ignored.
- In Java, statements which are expected to “throw” an exception or a warning are enclosed in a try block.
- If a statement in the try block throws an exception or a warning, it can be “caught” in one of the corresponding catch statements.
- Each catch statement specifies which exceptions it is ready to “catch”.


- Here is an example of catching an SQLException, and using the error condition to rollback the transaction:


- try
- {
- con.setAutoCommit(false);
-
- stmt.executeUpdate("CREATE TABLE Sells (bar VARCHAR2(40), " + 'book VARHAR2(40), price REAL)");
- stmt.executeUpdate("INSERT INTO Sells VALUES " + "('Barfi', 'Bluffmaster', 2.00)");
-
- con.commit();
- con.setAutoCommit(true);
-
- }
- catch(SQLException ex)
- {
- System.err.println("SQLException: " + ex.getMessage());
- con.rollback();
- con.setAutoCommit(true);
- }


- In this case, an exception is thrown.
- Because book is defined as VARCHAR2.
- which is a mis-spelling.
- There is no such data type in our DBMS.
- So, an SQLException is thrown.
- The output in this case would be:
- Message: ORA-00902: invalid datatype


- If wer datatypes were correct, an exception might be thrown in case wer database size goes over space quota.
- And is unable to construct a new table.
- SQLWarnings can be retrieved from :
1) Connection objects,
2) Statement objects, and
3) ResultSet objects.


- Each only stores the most recent SQLWarning.
- So if we execute another statement through wer Statement object, any earlier warnings will be discarded.
- Here is a code snippet which illustrates the use of SQLWarnings:


- ResultSet rs = stmt.executeQuery("SELECT book FROM Sells") ;
- SQLWarning warn = stmt.getWarnings() ;
- if (warn != null)
- System.out.println("Message: " + warn.getMessage()) ;
- SQLWarning warning = rs.getWarnings() ;
- if (warning != null)
- warning = warning.getNextWarning() ;
- if (warning != null)
- System.out.println("Message: " + warn.getMessage()) ;


- SQLWarnings as opposed to SQLExceptions.
- SQLWarnings are actually rather rare - the most common is a DataTruncation warning.
- The latter indicates that there was a problem while reading or writing data from the database.