I know this question has probably been asked a thousand times; but, I need to really understand this quickly.
When a resultSet is returned with a fetchsize = 1000, <i>for example</i>, where is the data that matched the query?
1. Is it in the JVM memory (all 1000 rows)?
2. Is it on the database server and retrieved one row at a time by the the next invocation on the ResultSet object?
3. some other mechanism?
Also, if a query produced 10,000 row matches against a specific table and the fetchsize = 1000, how is the next group of 1000 rows acquired by the resultSet...I have read the java docs and a number of other sources and frankly, I am more confused now than when I started.
I thought that the ResultSet was a cursor...and that it was an index into a row oriented collection of data that is in the database memory and that when next is invoked on the ResultSet in the jvm, it moves a row of data from the db server to the JVM memory. I have been told this is NOT true.
Your help is much appreciated and if you can state things simply then I can understand better.
The answer to your question depends on the database and JDBC driver you are using, since each database and JDBC driver is free to implement the JDBC standard as they please.
Excuse me but I could find no mention in your post of the database or JDBC driver you are using.
In ancient times, when I worked with Oracle, the Oracle documentation provided a detailed explanation that answers the exact same question you are asking here.
Hope the above has helped you.
Joined: Jun 25, 2009
I am using the Informix driver. I have looked through the IBM forums and documentation on this driver (such as it is) and could not find anything specific as to how the driver is managing the result set returned; that is wheather it is actually in the database cache and sent one row at a time to the client per invocations of next() on the RS or if the entire match set is returned to the clent.
I guess this is the crux of my question.
thanks for you information...if you can give me anything more, I'd appreciate it.
Wasn't Informix swallowed up by IBM?
I still didn't see what database you are using but if the driver is Informix, as you say, then I guess the database is DB2.
So my only suggestion is that you address your question to IBM.
Specifically regarding their implementation of the Informix JDBC driver for their DB2 database.