Java Database Connectivity (JDBC)

Using JDBC, Tomcat, and Oracle, I ran across a problem in testing the application where an invalid column name in a statement caused the call to prepareStatement to throw an exception. As such, no statement was never created, hence I couldn't call close. To make matters worse, the application uses connection pooling so the connection is never physically closed -- a behavior that would close the cursor. Eventually, the application would run out of cursors.

I am appending a piece of sample code that simulates connection pooling and has a statement which will cause prepareStatement to throw an invalid column exception. This should fairly well illustrate my point.

I thus pose the question: Is this a known problem with the JDBC thin driver for Oracle? Is this particular to only Oracle? Where is this documented?

Thanks in advance for any insight you may be able to provide.

Andy Meadows



-------------------



import oracle.jdbc.driver.*;
import javax.sql.*;
import java.sql.*;

/*
* This application demonstrates a problem believed to occur within the
* prepareStatement member function of the Connection class. When the
* prepareStatement method is called and throws an exception, the statement
* is never assigned and can not be closed. However, the cursor opened
* internally by the method is never closed before the exception is thrown.
* Hence, the cursor remains open and the application eventually runs out
* of cursors.
*
* The application loops after opening a connection to simulate pulling
* connections from a queue. The loop is for an iteration of 60 as our
* current configuration has a cursor limit of 50. The resulting output
* shows 50 invalid_column exceptions and 10 too many open cursor
* exceptions. This would seem to illustrate that the invalid_column
* calls opened a cursor that was not closed.
*/

class DBTest
{
public static String HOSTNAME = "";
public static String SID = "";
public static String USERNAME = "";
public static String PASSWORD = "";
public static String TABLENAME = "";
public static String GOODFIELDNAME = "";


public static void main( String s[] )
{
try
{
Class.forName( "oracle.jdbc.driver.OracleDriver" );
}
catch( ClassNotFoundException e )
{
System.err.println( e ) ;
System.exit( -1 ) ;
}


try
{
// open connection to database
Connection connection = DriverManager.getConnection(
"jdbc:oracle:thin:@" + HOSTNAME + ":1521:" + SID,
USERNAME, // ## fill in User here
PASSWORD // ## fill in Password here
);
// build query
String query = "insert into " + TABLENAME + "(" + GOODFIELDNAME +
",bad_field) values(?,?)" ;
System.err.println( query );
// Don't close the connection after each query because we want
// to simulate a connection going into and out of a pool rather
// than a single connection being opened and closed.
for( int i = 0; i < 60; i++ )
{
try
{
// execute query
PreparedStatement statement = connection.prepareStatement( query ) ;
statement.setObject( 1, "1" );
statement.setObject( 2, "2" );

statement.execute( ) ;

statement.close( );

System.out.println( "WILL NEVER PRINT: Successfully executed statement." ) ;
}
catch( java.sql.SQLException sqle )
{
System.err.println( sqle );
System.err.flush( );
}
}

connection.close( ) ;
}
catch( java.sql.SQLException e )
{
System.err.println( e ) ;
System.exit( -1 ) ;
}
}
}