PG 8.0.3 - PreparedStatement Can't Use Query Methods exception

From: David Wall <d(dot)wall(at)computer(dot)org>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: PG 8.0.3 - PreparedStatement Can't Use Query Methods exception
Date: 2005-08-10 16:41:01
Message-ID: 42FA2E1D.1060109@computer.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

We are converting from a 7.3 PG database to 8.0.3 and mostly all is
working okay. We are using postgresql-8.1dev-400.jdbc3.jar for JDBC.

Previously, we never received this sort of error. The idea is that
certain SQLExceptions are passed to a routine in our connection pool
that attempts to see if the Connection object is still valid or not by
doing a simple SELECT 1 command. This command still works fine via psql.

Here's our exception:

SQLState: 22023
ErrorCode: 0
SQLException: ConnectionPool.closeIfBadConnection(bpn) dummy SQL (SELECT
1) detected problematic Connection; closing it:
Message: Can''t use query methods that take a query string on a
PreparedStatement.
org.postgresql.util.PSQLException: Can''t use query methods that take a
query string on a PreparedStatement.
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:218)
at
com.xx.db.ConnectionPool.closeIfBadConnection(ConnectionPool.java:431)
at
com.xx.db.ConnectionPool.rollbackIgnoreException(ConnectionPool.java:379)
at
com.xx.bpn.BpnBackgrounder.doDailyCleanup(BpnBackgrounder.java:104)
at
com.xx.bpn.BpnBackgrounder.doBackgroundTasks(BpnBackgrounder.java:123)
at com.xx.bpn.BpnBackgrounder.run(BpnBackgrounder.java:142)
at java.lang.Thread.run(Thread.java:595)

The Java code that actually issues this is as follows:

public final void closeIfBadConnection(Connection con, SQLException e)
{
if ( con != null )
{
// First, let's try to the SQLState check since it's
straightforward.
if ( e != null )
{
if ( e.getSQLState() != null &&
e.getSQLState().startsWith("08") ) // 08 class is for connection exceptions
{
if ( app.isDebugEnabled() )
app.sqlerr(e,"ConnectionPool.closeIfBadConnection("
+ name + ") SQLState (" + e.getSQLState() + ") detected problematic
Connection; closing it");
else
app.err("ConnectionPool.closeIfBadConnection(" +
name + ") SQLState (" + e.getSQLState() + ") detected problematic
Connection; closing it - " + e.getMessage());

try
{
con.close(); // it's no good, and our pool will
reopen it when it's requested again later
}
catch( SQLException e2 ) {}

return; // we already handled matters here
}
}

// Well, we're not sure, so let's do a dummy query and see if
anything bad happens.
PreparedStatement stmt = null;
String dummyQuery = ( isOracle() ) ? "SELECT 1 FROM dummy_table"
: "SELECT 1";
try
{
stmt = con.prepareStatement(dummyQuery);
stmt.executeQuery(dummyQuery);
}
catch(SQLException e2)
{
if ( app.isDebugEnabled() )
app.sqlerr(e2,"ConnectionPool.closeIfBadConnection(" +
name + ") dummy SQL (" + dummyQuery + ") detected problematic
Connection; closing it");
else
app.err("ConnectionPool.closeIfBadConnection(" + name +
") dummy SQL (" + dummyQuery + ") detected problematic Connection;
closing it - " + e2.getMessage());
try
{
con.close(); // it's no good, and our pool will reopen
it when it's requested again later
}
catch( SQLException e3 ) {}
}
finally
{
if ( stmt != null )
try { stmt.close(); } catch( Exception e4 ) {}
}
}
}

What am I doing wrong here? It seems so straightforward. We create a
preparedStatement with a simple SELECT and then execute the query and
ignore the result set that may come back.

Thanks,
David

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Kris Jurka 2005-08-10 16:50:36 Re: PG 8.0.3 - PreparedStatement Can't Use Query Methods
Previous Message Richard DeGrande 2005-08-10 16:05:18 unsubscribe