1.

How Can I Get Data From Multiple Resultsets?

Answer»

With certain database systems, a STORED procedure can return multiple result sets, multiple update COUNTS, or some combination of both. Also, if you are providing a user with the ability to enter any SQL statement, you don't know if you are going to get a ResultSet or an update count back from each statement, without analyzing the contents. The Statement.execute() method helps in these cases.

Method Statement.execute() returns a boolean to tell you the type of response:

  • true indicates NEXT result is a ResultSet

Use Statement.getResultSet to get the ResultSet

  • false indicates next result is an update count

Use Statement.getUpdateCount to get the update count

  • false also indicates no more results

Update count is -1 when no more results (usually 0 or positive)

After processing each response, you use Statement.getMoreResults to CHECK for more results, again returning a boolean. The following demonstrates the processing of multiple result sets:

boolean result = stmt.execute(" ... ");
int updateCount = stmt.getUpdateCount();
while (result || (updateCount != -1)) {
if(result) {
ResultSet r = stmt.getResultSet();
// process result SET
} else if(updateCount != -1) {
// process update count
}
result = stmt.getMoreResults();
updateCount = stmt.getUpdateCount();
}

With certain database systems, a stored procedure can return multiple result sets, multiple update counts, or some combination of both. Also, if you are providing a user with the ability to enter any SQL statement, you don't know if you are going to get a ResultSet or an update count back from each statement, without analyzing the contents. The Statement.execute() method helps in these cases.

Method Statement.execute() returns a boolean to tell you the type of response:

Use Statement.getResultSet to get the ResultSet

Use Statement.getUpdateCount to get the update count

Update count is -1 when no more results (usually 0 or positive)

After processing each response, you use Statement.getMoreResults to check for more results, again returning a boolean. The following demonstrates the processing of multiple result sets:


Discussion

No Comment Found

Related InterviewSolutions