Retrieving multiple resultsets objects in JDBC from Stored procedure

Hi All,

The below example will demonstrate the way we can access the multiple result set from a procedure call. If you want the procedure to returned multiple result set then you have to use CallableStatement object and have to invoke execute() method.In this case, an internal pointer will be maintained inside the CallableStatement object. This pointer will maintain(or pointing to) the result sets(current).

// Create CallableStatement Object
CallableStatement cst = con.prepareCall(“CALL my_proc_name(?)”);

// Register OUT parameters
cst.registerOutParameter(1, java.sql.Types.INTEGER);

boolean isValidRS=cst.execute();

// First ReulstSet object
if (!isValidRS) {
System.out.println(“The first result is not a ResultSet.”);
return;
}

// Below steps will start reading the ResultSet, We can write a recursive method to handle the same, but for now it better to keep the flow very simple.

// Read First ReulstSet object
ResultSet res = cs.getResultSet();
while (res.next()) {
System.out.println(”  “+res.getInt(“ID”)
+”, “+res.getString(“CustName”)
+”, “+res.getString(“CustLastName”);
}
res.close();

// Check if other resulet sets are available
isMoreResultSets = cs.getMoreResults();
if (!isMoreResultSets) {
System.out.println(“There are no result sets found, Taking the control back.”);
return;
}

// Read the next ReulstSet object
res = cs.getResultSet();
while (res.next()) {
System.out.println(”  “+res.getInt(“ID”)
+”, “+res.getString(“CustName”)
+”, “+res.getString(“CustLastName”));

}
res.close();


Thanks
R Vashi

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s