Обсуждение: PG 8.0.3 - PreparedStatement Can't Use Query Methods exception

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

On Wed, 10 Aug 2005, David Wall wrote:

> 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.
>
> 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
>
> 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.
>

The JDBC 3 Spec 13.2.4 says, "If any of the PreparedStatement execute
methods is called with an SQL string as a parameter, an SQLException is
thrown."

Kris Jurka

Thanks, Kris.  I didn't even SEE that I had submitted the query string
in both places (in the prepareStatement as well as the executeQuery
methods), which is clearly not what I had intended, though I got away
with it before I suppose.

David

>The JDBC 3 Spec 13.2.4 says, "If any of the PreparedStatement execute
>methods is called with an SQL string as a parameter, an SQLException is
>thrown."
>
>Kris Jurka
>
>
>