OutOfMemoryError

[Blikkies Marais]

Hi
I am using JDBC to query a database using stmt.execute(), getting a resultset. The result set may be thousands of rows, but I only want to show a batch of 20 at a time.
The result set is a java object and therefore takes up memory, and if there are alot of users, alot of memory. - I get a java.lang.OutOfMemoryError Exception.
What is the best way to reduce the size of my result set just to the relevant rows? Do I need to declare a cursor? (How do I do that?) or are there better techniques?
Also we still need to know the number of rows in the entire resultset. (eg. 21 to 40 of 1000)
Thanks
[ July 11, 2003: Message edited by: Blikkies Marais ]

[Les Hayden]

I'm not sure what is causing your out of memory condition.
However, you can try to adjust the fetch size of a record set by using the statement method setFetchSize(int numRows). However, that only gives a *suggestion* to the driver and different dbs react to that method differently.
The default fetch size is only 25. If you use 0, that frees the db to choose what it think is optimal(which it may do anyway)
Hope this helps.
Les

[Lu Battist]

If your just looking for a quick way to limit the result set then I suggest digging into your specific database's sql syntax to see what phase to use to limit the reset set. For example in db2, the phrase is something like "select ... from ... where ... fetch first 20 rows only".
If you need to do batches of sql, then it depends on the table and primary key structure. Lets say the primary key was an integer field and was sequential as in 1, 2, 3, ... and so on. Then your sql could be "select ... from ... where key <= 20 order by key". Then the next group would have a where clause of "where key > 20 and <= 40" and so on.
You'd have to programmetically keep track of where you are and piece together the sql accordingly. The two methods can be combined together for dealing with more complicated keys.

[Nagendra Prasad]

I need to check to give you the exact syntax, but there are scrollable result sets avbl in JDBC2.0.

For this we need to create the preparedStatement with TYPE_SCROLL_INSENSITIVE set to false.
I shall check the actual syntax and post again.