2.5 Database Exception Handling

2.5 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.