query returns wrong result set (apparently)

From: "E(dot) Harold Williams" <hwilliams(at)ezrez(dot)com>
To: <pgsql-jdbc(at)postgresql(dot)org>
Subject: query returns wrong result set (apparently)
Date: 2003-03-20 17:22:21
Message-ID: 00c701c2ef05$4498ba50$bb1ea8c0@ezrezwindows
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

Hi,

We have an application that is getting errors where the result set is
occasionally unrelated to the query that we posted.

The best example I have is the following:

// check for existing sessions
PreparedStatement oidFetchStatement =
queryAgent.prepareStatement(
"SELECT " + databaseColumnName() + " FROM session_data
WHERE session_dataid = ?");
oidFetchStatement.setInt(1, sessionId);
ResultSet resultSet = oidFetchStatement.executeQuery();
int oid;
PreparedStatement statement;
// create a new session
if (resultSet.getFetchSize() == 1) {
... an so on

... and in the query agent class...

private Connection _dbConnection = null;
public PreparedStatement prepareStatement(String statement) throws
SQLException {
return _dbConnection.prepareStatement(statement);
}

The QueryAgent class manages a pool of query agents, each of which has a
Connection. There are multiple threads. The _dbConnection is never
changed. It could, potentially be closed and set to null if there was a
bug in the pooling, but it could never be set to another value.

The code above usually works, but sometimes the resultSet size is not 0
or 1, as we expect, but 267 or some such value. When we search our logs,
we find another query had previously that returned this number of rows.
As you can see, the result set is a local variable, which is created by
the prepared statement, which is also local. The only semi-global
variable involved in the Connection.

So I am at a loss on how this could happen.

Elsewhere in our program, we sometimes get bugs where we get errors
because the result set does not have an expected column name. I am sure
this is the same bug manifesting itself differently.

Has anyone seen a problem like this or have any suggestions?

Thanks,

Harold Williams

Browse pgsql-jdbc by date

  From Date Subject
Next Message David Hooker 2003-03-20 17:46:41 Re: Problem with updateRow()
Previous Message Csaba Nagy 2003-03-20 17:10:31 Re: PoolConnection and several databases