Skip to Main Content

Java Database Connectivity (JDBC)

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Streaming multiple result sets from long stored procedure

843859Jan 3 2008 — edited Jan 3 2008
I have a stored procedure in MySQL that can take a long time to run... nearly 2 minutes. It spits out multiple result sets at regular intervals during its run. I need to be able to stream these result sets to Java as they become available. I have made a little progress but am now stuck. Here is some source code with explanation:
stmt = conn.prepareCall("{call veryLongAndComplexProcedure}", 
		              java.sql.ResultSet.TYPE_FORWARD_ONLY,
		              java.sql.ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(Integer.MIN_VALUE);
/*
    The above tells the driver to stream the result instead of waiting 
    for the procedure to finish and subsequently storing the entire result.
*/
stmt.execute();
ResultSet rs = stmt.getResultSet();
rs.next();
int thisIntegerGetsSetCorrectly = rs.getInt("aValue");

/*
    Try to fetch the next result set.  If its not available retry every second 
    for the next minute.
*/
int i = 0;
while(stmt.getMoreResults() == false && stmt.getResultSet() == null && ++i < 60){				
	Thread.sleep(1000);
}

if(i == 60)
    System.out.println("Failed to fetch the next result set");
The problem is that after the first result set I cannot get any subsequent result sets. The stored procedure spits out a result set approximately every 1-2 seconds so It should be available.

Is there something I am missing? How do I get Java to retrieve the next result set?

This should work.... When I run the query in MySQL Query Browser it spits out the result sets as they come in... just as I would like to happen in Java.

Thank you for your help.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 31 2008
Added on Jan 3 2008
5 comments
841 views