1197-1199

Previous Table of Contents Next

Page 1197

INOUT parameters must be both set and registered. Assume the InsertEmp function accepts empno as input but generates a new one if the input value is not unique. The INOUT parameter might be set and registered as follows :

 cstmt.setInt(2, empno); cstmt.registerOutParameter(2, Types.INTEGER); 

The SQL datatype passed to registerOutParameter() must match the set() method used. Some drivers map datatypes differently than you might expect, so INOUT parameters can be difficult to use with JDBC. For example, an Oracle7 driver from a well-known vendor maps numeric input parameters to the OCI datatype CHARZ (97 is a null- terminated string) and numeric output parameters to OCI type VARCHAR2 (1 is a character array of up to 2000 bytes). It then makes two calls to the OCI obdnra() function for the parameter, the second preempting the first. The net result is that only CHAR INOUT parameters are accessible using the driver. This is one example of why it is necessary to test JDBC drivers thoroughly before selection and application design.

Handling JDBC Exceptions

Exception handling was omitted from the previous examples because it is a topic that deserves special consideration. Nearly every JDBC call an application makes is capable of throwing an exception and must be enclosed in a try block. JDBC defines three types of exceptions in a hierarchical relationship. The hierarchy, from top to bottom, is

 SQLException SQLWarning DataTruncation 

SQLException is thrown by JDBC methods when a database error occurs. It has several methods that you can call to retrieve information about the error:

int getErrorCode() returns a database-specific error code. String getSQLState() returns the X/Open SQLState associated with the error. String getMessage() is inherited from java.lang.Exception (returns a description of the error).

JDBC exceptions can be chained so that if more than one error occurs during a single execution, information on all errors is retained. The getNextException() method returns a reference to the next exception in the chain or null if no more exceptions exist.

SQLWarning is an exception that is not thrown but chained to the specific ResultSet or statement object to which it applies. Like SQLException, more than one SQLWarning can be chained. The getNextWarning() method of SQLWarning returns the next warning in the chain or null if no more warnings exist. For example, when processing a result set, an application can check for warnings using getWarnings() as follows:

 // rs references a valid result set java.sql.SQLWarning warn = rs.getWarnings(); 

Page 1198

 while (warn != null) {     // do something with the warning     warn = warn.getNextWarning(); } 

DataTruncation is a special case of SQLWarning that applies to column and parameter values. DataTruncation is chained to the result set object but thrown when it applies to a statement object. It provides additional methods to determine the column or parameter index to which it applies, the actual and expected lengths, and so on. When DataTruncation is chained to a result set, it can be distinguished from other warnings based on its SQLState of "01004". You can extend the previous code segment to check for data truncation :

 // rs references a valid result set int ColNum; int JavaLen; int DBLen; java.sql.SQLWarning warn = rs.getWarnings(); while (warn != null) {     if (warn.getSQLState() == "01004") {         ColNum = ((DataTruncation)warn).getIndex();         JavaLen = ((DataTruncation)warn).getTransferSize();         DBLen = ((DataTruncation)warn).getDataSize();         // do something with this info     }     else {         // it is some other SQLWarning     } warn = warn.getNextWarning(); } 

getTransferSize() and getDataSize() apply to the length of the JDBC column or parameter and the database column or parameter. DataTruncation is more likely to be thrown as the result of a parameter value that is too long. In this case, it is not chained as a warning but thrown as an exception that must be caught:

 // cstmt references a valid CallableStatement int ParmNum; int JavaLen; int DBLen; try {     cstmt.executeUpdate(); } catch (SQLException e) {     while (e != null) { if (e.getSQLState == "01004") {             ParmNum = ((DataTruncation)e).getIndex();         JavaLen = ((DataTruncation)e).getTransferSize();             DBLen = ((DataTruncation)e).getDataSize();             // do something with this info     }         else {             // it is some other SQLException     }         e = e.getNextException();     } } 

Page 1199

DataTruncation can be caught as a SQLException because it is derived from SQLException. When it is caught as a SQLException, it must be explicitly cast to DataTruncation before you can access the methods of that interface.

The handling of a trapped SQLException varies greatly, based on the specific needs of the application. In some cases, it might be useful to simply re-throw the trapped exception or throw an application-specific exception to be handled at a higher level. When throwing an exception from within a catch block, you should consider a number of issues. For those already familiar with Java, the first point is obvious: Throwing exceptions from nested try blocks is usually a bad idea. Consider the following example, which throws an application-defined extension of java.lang.Exception defined as DBException:

 try {     // some Java object instantiations, assignments, etc.     try {         // some  JDBC calls     }     catch (SQLException sqle) {         // clean up and throw the exception so that it         // can be handled at a higher level         throw new DBException(sqle.getMessage(), sqle.getErrorCode());     } } catch (java.lang.Exception je) {     // do something } 

The new DBException thrown in the inner block is caught in the outer block because DBException is a descendant of java.lang.Exception. You can accomplish the desired result using multiple catches for the outer try:

 try {     // some Java object instantiations, assignments, etc. // some  JDBC calls } catch (SQLException sqle) { // clean up and throw the exception so that it can be handled at a higher level     throw new DBException(sqle.getMessage(), sqle.getErrorCode()); } catch (java.lang.Exception je) {     // do something } 

The exceptions must be caught from the lowest to the highest level in the hierarchy. If java.lang.Exception is caught before DBException, the DBException block is never reached. In the previous example, information from the first SQLException caught is copied to a new DBException. If multiple SQLExceptions are chained, this information is not preserved. The chain can be preserved if DBException extends java.sql.SQLException using the setNextException() method:

 catch (SQLException sqle) { DBException first = new DBException(sqle.getMessage(), sqle.getErrorCode()); sqle = sqle.getNextException(); 

Previous Table of Contents Next