PG 8.0.3 - PreparedStatement Can't Use Query Methods exception - Mailing list pgsql-jdbc
From | David Wall |
---|---|
Subject | PG 8.0.3 - PreparedStatement Can't Use Query Methods exception |
Date | |
Msg-id | 42FA2E1D.1060109@computer.org Whole thread Raw |
Responses |
Re: PG 8.0.3 - PreparedStatement Can't Use Query Methods
|
List | 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
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
pgsql-jdbc by date: