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.