Coding Tips and Troubleshooting

Basic Debugging Procedures

This section describes four strategies for debugging a JDBC program.

Trapping Exceptions

Most errors that occur in JDBC programs are handled as exceptions. Java provides the try...catch statement to catch the exception and the printStackTrace() method to print the stack trace.

The following code fragment illustrates how you can catch SQL exceptions and print the stack trace.

 try { <some code> } 
 catch(SQLException e){ e.printStackTrace (); }

To illustrate how the JDBC drivers handle errors, the following incorrect code was intentionally added to the Employee.java sample:

// Iterate through the result and print the employee names 
// of the code 
 
try { 
  while (rset.next ()) 
System.out.println (rset.getString (5)); }  // incorrect column index
  catch(SQLException e){ e.printStackTrace (); }

Notice an error was intentionally introduced by changing the column index to 5. When you execute the program you get the following error text:

java.sql.SQLException: Invalid column index 
at oracle.jdbc.dbaccess.DBError.check_error(DBError.java:235) 
at oracle.jdbc.driver.OracleStatement.prepare_for_new_get(OracleStatemen 
t.java:1560) 
at oracle.jdbc.driver.OracleStatement.getStringValue(OracleStatement.jav 
a:1653) 
at oracle.jdbc.driver.OracleResultSet.getString(OracleResultSet.java:175 
) 
at Employee.main(Employee.java:41)

For more information on how the JDBC drivers handle errors, and the SQLException() and the printStackTrace() methods, see "Error Messages and JDBC".

Logging JDBC Calls

You can use the java.io.PrintStream.DriverManager.setLogStream() method to log JDBC calls. This method sets the logging/tracing PrintStream used by the DriverManager and all drivers. Insert the following line at the location in your code where you want to start logging JDBC calls:

DriverManager.setLogStream(System.out);

Net8 Tracing to Trap Network Events

You can enable client and server Net8 trace to trap the packets sent over Net8. You can use client-side tracing only for the JDBC OCI driver; it is not supported for the JDBC Thin driver. You can find more information on tracing and reading trace files in the Net8 Administrator's Guide.

The trace facility produces a detailed sequence of statements that describe network events as they execute. "Tracing" an operation lets you obtain more information on the internal operations of the event. This information is output to a readable file that identifies the events that led to the error. Several Net8 parameters in the SQLNET.ORA file control the gathering of trace information. After setting the parameters in SQLNET.ORA, you must make a new connection for tracing to be performed. You can find more information on these parameters in the Net8 Administrator's Guide.

The higher the trace level, the more detail is captured in the trace file. Because the trace file can be hard to understand, start with a trace level of 4 when enabling tracing. The first part of the trace file contains connection handshake information, so look beyond this for the SQL statements and error messages related to your JDBC program.

Note:
The trace facility uses a large amount of disk space and might have significant impact upon system performance. Therefore, enable tracing only when necessary.

Client-Side Tracing

Set the following parameters in the SQLNET.ORA file on the client system.

Note:
Ensure that the name you choose for the TRACE_FILE_CLIENT file is different from the name you choose for the TRACE_FILE_SERVER file.

TRACE_UNIQUE_CLIENT

Default Value:

OFF

Example:

TRACE_UNIQUE_CLIENT = ON

Server-Side Tracing

Set the following parameters in the SQLNET.ORA file on the server system. Each connection will generate a separate file with a unique file name.

TRACE_LEVEL_SERVER

TRACE_DIRECTORY_SERVER

Purpose:

Specifies the destination directory of the trace file

Default Value:

$ORACLE_HOME/network/trace

Example:

TRACE_DIRECTORY_SERVER=/oracle/traces

TRACE_FILE_SERVER

Purpose:

Specifies the name of the server trace file

Default Value:

SERVER.TRC

Example:

TRACE_FILE_SERVER= svr_Connection1.trc

Note:
Ensure that the name you choose for the TRACE_FILE_SERVER file is different from the name you choose for the TRACE_FILE_CLIENT file.

Using Third Party Tools

You can use tools such as JDBCSpy and JDBCTest from Intersolv to troubleshoot at the JDBC API level. These tools are similar to ODBCSpy and ODBCTest.

Purpose:	Turns tracing on/off to a certain specified level
Default Value:	0 or OFF
Available Values:	0 or OFF - No trace output 4 or USER - User trace information 10 or ADMIN - Administration trace information 16 or SUPPORT - WorldWide Customer Support trace information
Example:	TRACE_LEVEL_CLIENT=10

Purpose:	Specifies the name of the client trace file
Default Value:	SQLNET.TRC
Example:	TRACE_FILE_CLIENT=cli_Connection1.trc

Purpose:	Specifies the destination directory of the trace file
Default Value:	$ORACLE_HOME/network/trace
Example:	TRACE_DIRECTORY_SERVER=/oracle/traces

Purpose:	Specifies the name of the server trace file
Default Value:	SERVER.TRC
Example:	TRACE_FILE_SERVER= svr_Connection1.trc