Weired behaviour of sql-queries with fetch size set to high amount

Question

I have the following rather weired behaviour:

A fetcher executes a rather large number of queries from the database and uses a PreparedStatement which keeps the database connection open between to queries. The whole application can be launched either from a main method or deployed in a tomcat and then triggered via a web app (while the executed code remains the same in both cases).

While the local execution via main-maethod results in exactly the data I expected, the execution via tomcat does not. I have pinned down the error to the following:

/* Statement is a PreparedStatement which is initialized at 
 * the start of the execution and reused until the execution
 * is completed */
ResultSet rs = stmt.executeQuery(); 
/* Commenting out this line gives me correct data in tomcat
 * and main method, leaving it in corrupts data only in tomcat */
rs.setFetchSize(2000);
while (rs.next) {
  doStuff(rs);
}
rs.close();

Perhaps you can understand why im puzzled here. Why has ResultSet.setFetchSize() any influence on the correctness of my code even if the resultset is closed after use? And why do I have the corruption only when executed in tomcat?

Edit

The columns selected from the database don't match the data within the database, i.e. in the database there are two key-columns, a discriminator and a validity date for that discriminator. If i match the fetched data-columns from one row with the data given by the sqldeveloper, they don't match.

Example: SQL-Developer tells me to expect the following row:

 ID   |  VALIDITY  | HOUR_1 | HOUR_2 |...
------+------------+--------+--------+...
 1897 | 01.01.2013 | 100000 | 100000 |...

Yet I retrieve

 ID   |  VALIDITY  | HOUR_1 | HOUR_2 |...
------+------------+--------+--------+...
 1897 | 01.01.2013 |  14000 |  14000 |...

Interestingly enough, some columns are fetched correctly, in one example 365 rows were retrieved, the first 10 were correct (10 being the default fetchSize in Oracle - coincidence?) and the following 355 where wrong.

I have also found some hint, that meybe setting the fetchsize at all in an OracleResultSet might not be the best idea, as the javadoc tells me an SQLException is thrown, if the fetchsize set is larger than the actual resultsize. However, I don't get an Exception just silently wrong data.

Answer 1

Basically, my problem consists of two parts, a classpath-library-clash and a driver bug at the same time.

I've just realized, that next to the Oracle11 driver a springsource-oracle10 driver sneaked into the maven dependencies. When executing the application locally this was no issue, because only the webapp-project depended on the oracle10-spring driver but on the server for some reason Oracle10 was always chosen over Oracle 11.

Next, there is a bug in the Oracle 10 driver, at least when connecting to an Oracle 11 DB: If you set the fetch size too high instead of the SQLException proclaimed in the javadoc of OracleResultSet something a lot more sneaky happens.

I'll make an example to illustrate. Assuming you have several executions of the same query, where the first execution will give you 30 rows, the second execution will give you 60 rows and the third execution 30 rows. You set the fetch-size to 50.

Now, the first execution will give you the correct (expected) result, as will the second. But the third query execution will only give you 10 correct rows (Oracle standard-fetch-size), the remaining 20 rows will be from the second execution but - and here it gets complicated - all parameters-columns of the statement will be set to the parameter-value set in the statement.