Recursive DB queries in a Stateless Session Bean

[Phil Thomas]

Hello,
I have a Stateless Session bean that gets its database connection inthe setSessionContext(context) method using the DataSource object's getConnection() method. My queries work fine, except when I do a recursive query like the following:
private void loadChildren(int parentGroupID) throws SQLException
{
int id = -1;
String name = "";
String selectStatement = "SELECT GROUP_ID, GROUP WHERE PARENT_GROUP_ID="+parentGroupID;
PreparedStatement prepStmt = con.prepareStatemen(selectStatement);
ResultSet rs = prepStmt.executeQuery();
while (rs.next())
{
id =rs.getInt("GROUP_ID");
//recurse and call ourself again.
loadChildren(id);
}
prepStmt.close();
return;
}
The following error occurs on the prepStmt.executeQuery() call after one recursive call:
nested exception is: javax.ejb.EJBException: getAllGroupAndSubjectNames: [Microsoft][ODBC SQL
Server Driver]Connection is busy with results for another hstmt
p.s. This works fine in Entity beans. Am I missing something about session beans?

[Kiran Karupalli]

Hi ,
I am guessing some thing here... It may be because you are not closing result set and statement before loadChildren() recursive call. Close them before the loadChildren() recursive call and check..
Kiran
MindTree Consulting