Thread: query returns wrong result set

query returns wrong result set

From
"E. Harold Williams"
Date:
Hi,
 
I am not sure if this is the right place to ask this question, but I am having a problem and suspect the jdbc driver. Any assistance in this matter would be greatly appreciated.
 
Thanks,
 
Harold Williams
 
 
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