1194-1196

Previous Table of Contents Next

Page 1194

The first form of the setObject() method can be very powerful because it requires no design-time knowledge of datatype. However, when you pass null object references as parameters, you cannot expect drivers to determine the datatype, so you must use the second or third form of setObject() to pass null values. Alternatively, an application can use setNull() instead of setObject() when a null value is encountered . You can use the following function to encapsulate the binding of all parameters for a prepared statement:

 // accepts a prepared statement reference and a java.util.Vector // containing all parameter Objects public void SetAllParms(PreparedStatement pstmt, Vector parms) { int i;           int NumParms = parms.size();           Object obj = null;           for (i = 0; i < NumParms; i++) {         obj = parms.elementAt(i);                  if (obj != null)                      pstmt.setObject(i + 1, obj);         else                      pstmt.setNull(i + 1, Types.NULL);     } return; } 

The sample function uses a combination of setObject() and setNull() to bind all parameters, regardless of the datatype. TheTypes.NULL constant can be viewed as indicating an "unknown datatype." It is up to the driver to determine how it should be mapped. The null value is a special case that applies to any datatype with most databases and JDBC drivers, so its type is unimportant. However, conversion from Types.NULL is not supported by all drivers.

The PreparedStatement has a few additional methods worth noting. You can use the clearParameters() method at any time to release external resources in use by parameters. This is typically used to reclaim memory used by prepared statements with many parameters that are kept open but executed infrequently.

In addition to the executeQuery() and executeUpdate() methods, you can use the execute() method to create result sets or apply DML transactions. The execute() method is provided for situations in which a complex SQL or procedural statement returns multiple result sets, multiple row counts, or a combination of both. After calling execute(), an application uses getMoreResults() to determine if the next result is a result set or a row count. It returns True if the next result is a result set. A return of False indicates that the next result is a row count or that there are no more results.

After calling getMoreResults(), an application uses getResultSet() or getRowCount() to retrieve the current result. A ResultSet interface is returned by getResultSet(), whereas getRowCount() returns an integer. The results of a complex procedure might be handled as follows :

 // pstmt is a valid PreparedStatement interface that has been executed java.sql.ResultSet rs = null; int   RowsAffected = -1; 

Page 1195

 boolean isResultSet = false; while (pstmt.getMoreResults()  (RowsAffected = pstmt.getUpdateCount()) != -1) {     if (RowsAffected != -1) {         // do something based on the number of rows affected     }     else {         rs = pstmt.getResultSet();         // do something with the current result set     }     RowsAffected = -1; } 

In the example, the short-circuit OR is used instead of the logical OR. This is to ensure that the right-hand side of the expression isn't evaluated if the left-hand side is True, preventing getUpdateCount() from being called when the current result is a ResultSet. Note that getUpdateCount() returns _1 if there are no more results or if the current result is a result set. In the example, RowsAffected must be set to this value prior to each evaluation of the while expression to ensure that result sets are retrieved.

Accessing Functions and Procedures

Stored procedures and functions are accessed using the java.sql.CallableStatement interface and SQL escape syntax. The CallableStatement interface extends the PreparedStatement interface, which in turn extends the Statement interface. CallableStatement exists to provide additional methods specific to calling stored procedures and functions and dealing with output parameters.

The SQL escape syntax for calling a stored procedure from JDBC has the generic form of

 {call proc_name(arg1, arg2, ...)} 

A function call executed through JDBC has this generic form:

 {? = call func_name(arg1, arg2, ...)} 

A reference to a CallableStatement is obtained directly from the Connection interface, in much the same way as a PreparedStatement. Although PreparedStatements are obtained using the prepareStatement() method, CallableStatements are obtained using the prepareCall() method. Assume that the insert into the emp table presented in the previous section was encapsulated in an Oracle stored procedure named InsertEmp. The JDBC code to perform the insert might look like the following segment:

 // DbConn references a valid Connection interface String SQL = "{call InsertEmp(?, ?, ?, ?, sysdate, ?, ?, ?)}"; java.sql.CallableStatement cstmt = DbConn.prepareCall(SQL); int RowsAffected = cstmt.executeUpdate(); 

The calls to set parameters were omitted from this segment because you can use any of the previously described methods for setting input parameters. In fact, you could have executed this procedure using the PreparedStatement interface instead of the CallableStatement interface, because

Page 1196

the only CallableStatement methods required are inherited from PreparedStatement. However, it is a good practice to use the CallableStatement interface for executing procedures to minimize the impact of a change in the underlying procedure. You must use the CallableStatement interface to access output parameters, which are discussed in the next section.

Up to this point, I have omitted schema names from the source code examples. Schema names are unimportant if Oracle public synonyms are employed, but when schema names are required, accessing Oracle packaged procedures and functions requires an unusual syntax. Assume that the insert procedure in the previous example is in a package named EmpPack, which has no public synonym. Users (other than scott) then use the following call to access the packaged procedure:

 String SQL = "{call scott.EmpPack.InsertEmp(?, ?, ?, ?, sysdate, ?, ?, ?)}"; 

Most Oracle drivers support all three combinations of schema and package notations:

 {call schema_name.proc_name} {call package_name.proc_name} {call schema_name.package_name.proc_name} 

Using Output Parameters

Often it is useful to use output parameters in Oracle procedures and functions. The JDBC CallableStatement interface provides the methods for retrieving these values. Assume that the emp table insert described in the previous sections is written as a packaged function and that the function returns 0 for success or an Oracle error code. For the sake of the example, assume further that the value for the empno column is generated by an Oracle sequence and that the new empno is an output parameter to the function. The following code segment demonstrates this function call and the retrieval of output parameters:

 String SQL = "{? = call scott.EmpPack.InsertEmp(?, ?, ?, ?, sysdate, ?, ?, ?)}"; java.sql.CallableStatement cstmt = DbConn.prepareCall(SQL); cstmt.registerOutParameter(1, Types.INTEGER); cstmt.registerOutParameter(2, Types.INTEGER); // set input parameters int RowsAffected = cstmt.executeUpdate(); int retcode = cstmt.getInt(1); int newempno = cstmt.getInt(2); 

As far as JDBC is concerned , an Oracle function is a procedure with at least one output parameter. There is no special treatment of a function return value. The arguments to the registerOutParameter() method are a one-based parameter index and the SQL datatype of the parameter. The values of output parameters are retrieved after execution using the appropriate get() method (getString(), getFloat(), and so on). The get() method used must be compatible with the type passed to the registerOutParameter() method. The CallableStatement get() methods are identical to the ResultSet get() methods that take the column index as the argument. Also, like the ResultSet equivalent, the wasNull() method can determine if the last output parameter read was null.

Previous Table of Contents Next