2

I am using the following code to create a prepared statement and a streaming result set. Turns out that in some cases the result set is fairly large but I would like to close it after consuming a couple of thousand rows. However, close seems to run through all remaining results -- see this link for the definition of close: http://love.isti.com/libs/MySQL/com/mysql/jdbc/RowDataDynamic.java

statement = db.conn().prepareStatement(query, java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY); db.setFetchSize(statement, kFetchSize);

I would like to close the result set (and/or statement) immediately without iterating through all the remaining results. Is that possible?

I also tried to move cursor to the end of the result set (afterLast) but that is not supported for streaming result sets. And, not using streaming result sets is not an option since result sets can sometimes be very large, and I don't want to get all of the results to the client.

thanks

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
swows
  • 1
  • 1
  • 3
  • 1
    You're aware that the version you posted is 9 years old? Incredible implementation, but is it really still there? – user207421 Nov 22 '13 at 08:34
  • At least, the behavior of the driver matches this implementation, as far as I tried. – swows Nov 27 '13 at 07:15
  • 1
    I've gotten stuck with the same problem. Sadly, I'm finding mounting evidence you can't do what you want. See [link](http://bugs.mysql.com/bug.php?id=42929), and [link](http://dev.mysql.com/doc/refman/5.5/en/connector-j-reference-implementation-notes.html) - "You must read all of the rows in the result set (or close it) before you can issue any other queries on the connection, or an exception will be thrown." – milletron Jan 02 '14 at 15:12

1 Answers1

4

So as posted in my comment to the question, the official answer from the MySQL connector is that you have to stream all of the result set in order for it to close (http://dev.mysql.com/doc/refman/5.5/en/connector-j-reference-implementation-notes.html). Additionally, you can't perform any more queries while a streaming result is taking place.

As a completely disgusting hack, I used reflection to go down into RowDataDynamic (ver. 5.1.24) and fake an interrupted exception, like so:

    final Class<?> rdClass = rd.getClass();
    final Field isInterruptedField = rdClass.getDeclaredField("isInterrupted");
    isInterruptedField.setAccessible(true);  // override 'protected' visibility
    isInterruptedField.set(rd, true);

Note, you'll have to walk down whatever object you have a handle on to get to the ResultSet. For me, I was using Hibernate's ScrollableResults class. This meant getting ResultSet reference from it (its super class, actually), then RowData from there.

This will allow the close operation to take place without streaming the rest of the results HOWEVER I get an exception due to mismatched packet size when I try to rollback the transaction (which I just catch and ignore). Using Atomikos as the connection pool, I will see warnings about the next few connections as things get cleaned up, but everything still works ok.

Clearly this approach may not work for everyone, but at least it's a workaround when doing the processing via the database query or writing more complicated logic to retrieve results in batches just won't work.

milletron
  • 370
  • 1
  • 5
  • 11