aspose file tools*
The moose likes JDBC and the fly likes Huge result sets Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Huge result sets" Watch "Huge result sets" New topic
Author

Huge result sets

M Burke
Ranch Hand

Joined: Jun 25, 2004
Posts: 388
Hi, I am using JDBC to query a database. The result set is huge, and often causes a stack overflow on the JVM (in Windows).

Is there a way JDBC can just get one row at a time rather than caching the whole result set? Or is there some other way to handle this problem?
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3710
    
    5

It sounds to me like this might be a coding issue rather than a JDBC/technology issue, can you post the code that you are working with and highlight the line that is throwing the error?

If it was a JDBC error though, you can use setFetchSize() to limit the size of the results.


My Blog: Down Home Country Coding with Scott Selikoff
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333
Originally posted by Scott Selikoff:
It sounds to me like this might be a coding issue rather than a JDBC/technology issue, can you post the code that you are working with and highlight the line that is throwing the error?

If it was a JDBC error though, you can use setFetchSize() to limit the size of the results.


To be nitpicky and precise, setFetchSize() doesn't limit the size of the results, it hints to the driver how many rows to transfer from the DB in one fetch, which in turn sometimes results in the driver only buffereing that many rows in Java memory at once.

Some databases (such as Postgres) will default to fetching all rows at once, so setting a fetch size for those databases can be very very important when working with large results.

However, some drivers, when working with scrollable ResultSets, will collect all rows of the ResultSet in memory as they see them, even if the driver is honoring the fetch size when fetching. I know that Oracle in particular does this; for that database and a huge ResultSet, you can't use scrollable, you have to be forward-only, (and it helps performance to set a fetch size larger than the default which for Oracle JDBC is 10 rows - I usually set between 100 and 1,000).
M Burke
Ranch Hand

Joined: Jun 25, 2004
Posts: 388
Originally posted by Scott Selikoff:
It sounds to me like this might be a coding issue rather than a JDBC/technology issue, can you post the code that you are working with and highlight the line that is throwing the error?

If it was a JDBC error though, you can use setFetchSize() to limit the size of the results.



One can use any run of the mill JDBC code on a huge result set. The error is not an SQLException. It's the JVM, it blows up after an uncertain but huge number of rows are retrieved.

I don�t see how setting the fetch size will help. I have to process all the records and breaking it up with where statements is not possible in this case.


package dbtest;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TigerTest
{


public static void main(String[] args)
{
final String dbURI = "jdbcracle:thin:@64.53.135.39:1521rcl";
final String oracleDvr = "oracle.jdbc.driver.OracleDriver";
final String userID = "lala";
final String pw = "lala";

Driver oDvr = null;
Connection conn = null;

oDvr = new oracle.jdbc.driver.OracleDriver();
try
{
DriverManager.registerDriver (oDvr);

conn = DriverManager.getConnection(dbURI,userID, pw);
conn.setAutoCommit(false);

Statement stmt = conn.createStatement();
ResultSet rset = stmt.executeQuery("Select * from customertrans");

int cc = rset.getMetaData().getColumnCount();
String temp = "";
while (rset.next()){
for(int z = 1; z < cc; z++){
temp = temp + rset.getString(z) + " ";
}
System.out.println(temp);
temp = "";
}

rset.close();
stmt.close();
conn.close();

}
catch (SQLException e)
{
e.printStackTrace();
}
finally
{

}

}
}
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3710
    
    5

What line does it crash on? Could you post the full stack trace from the error?
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Huge result sets