Exhausted Resultset Error

[shweta kavungal]

Hi
The problem is that i am getting "Exhausted REsultset error" sporadically.
from the below code.
try
{
ResultSet rsForRoles = SOADbAccess.retrieveData(qryForRoles);
//If the number of roles is greater than one a page has to be displayed
//to select the rolr required.
while(rsForRoles.next())
{
count++;
System.out.println(rsForRoles.getString("USER_ROLE"));
}
System.out.println("Role count:"+count);
if(count == 0)
{
mssgTxt = "You are not an authorised";
displayErrorMessage(out,mssgTxt);
}
}
catch(Exception e)
{
System.out.println("Exception in retrieving roles::"+e);
displayErrorMessage(out, e.getMessage());
}
try
{

query = "Select user_role,user_id,emp_id,AreaSalesMgr_Id,AreaServiceMgr_Id,DealerMgr_Id,ModalityMgr_Id,CreditMgr_Id,CountryFinMgr_Id,CountrySalesMgr_Id,OTR_Id,LegalMgr_Id,President_Id,PoleLegal_Id,PoleFinance_Id,PolePresident_Id,CountryId,Country,Region_ID,Region_Name,e_mail from SOA_Users where UPPER(E_Mail) like UPPER('%"+LDAPemailId.toUpperCase()+"%') ";
System.out.println("If count= 1 query:"+query);
rs = SOADbAccess.retrieveData(query);
if (rs.next())
{
userid = SOAUtility.Trim(rs.getString("user_id"));
role = SOAUtility.Trim(rs.getString("user_role"));
employeeId = SOAUtility.Trim(rs.getString("emp_id"));
asmid = SOAUtility.Trim(rs.getString("AreaSalesMgr_Id"));
asrmid = SOAUtility.Trim(rs.getString("AreaServiceMgr_Id"));
dmid = SOAUtility.Trim(rs.getString("DealerMgr_Id"));
mmid = SOAUtility.Trim(rs.getString("ModalityMgr_Id"));
cmid = SOAUtility.Trim(rs.getString("CreditMgr_Id"));
fmid = SOAUtility.Trim(rs.getString("CountryFinMgr_Id"));
csmid = SOAUtility.Trim(rs.getString("CountrySalesMgr_Id"));
otrid = SOAUtility.Trim(rs.getString("OTR_Id"));
legalid = SOAUtility.Trim(rs.getString("LegalMgr_Id"));
pid = SOAUtility.Trim(rs.getString("President_Id"));
plegalid = SOAUtility.Trim(rs.getString("PoleLegal_Id"));
pfinanceid = SOAUtility.Trim(rs.getString("PoleFinance_Id"));
ppid = SOAUtility.Trim(rs.getString("PolePresident_Id"));
country = SOAUtility.Trim(rs.getString("Country"));
countryId = SOAUtility.Trim(rs.getString("CountryID"));
regionId = SOAUtility.Trim(rs.getString("Region_ID"));
regionName = SOAUtility.Trim(rs.getString("Region_Name"));
e_mail = SOAUtility.Trim(rs.getString("e_mail"));
session.putValue("ssoid",ssoId);
session.putValue("userid",userid);
session.putValue("role",role);
session.putValue("e_mail",e_mail);
if(employeeId != null)
{
session.putValue("empid",employeeId);
}
if (asmid != null)
session.putValue("asmid",asmid);
if (asrmid != null)
session.putValue("asrmid",asrmid);
if (dmid != null)
session.putValue("dmid",dmid);
if (mmid != null)
session.putValue("mmid",mmid);
if (cmid != null)
session.putValue("cmid",cmid);
if (fmid != null)
session.putValue("fmid",fmid);
if (csmid != null)
session.putValue("csmid",csmid);
if (otrid != null)
session.putValue("otrid",otrid);
if (legalid != null)
session.putValue("legalid",legalid);
if (pid != null)
session.putValue("pid",pid);
if (plegalid != null)
session.putValue("plegalid",plegalid);
if (pfinanceid != null)
session.putValue("pfinanceid",pfinanceid);
if (ppid != null)
session.putValue("ppid",ppid);
if (country != null)
session.putValue("country",country);
if (countryId != null)
session.putValue("countryId",countryId);
if (regionId != null)
session.putValue("regionId",regionId);
if (regionName != null)
session.putValue("region",regionName);
if(role.equalsIgnoreCase("GroupOtr"))
{
session.putValue("gotruserid",userid);
session.putValue("gotrrole",role);
if (pid != null)
session.putValue("gotrpid",pid);
if (country != null)
session.putValue("gotrcountry",country);
if (countryId != null)
session.putValue("gotrcountryId",countryId);
if (regionId != null)
session.putValue("gotrregionId",regionId);
if (regionName != null)
session.putValue("gotrregion",regionName);
}
rs.close();
if(role.equalsIgnoreCase("admin"))
{
//response.sendRedirect("/servlet/SOAAdmin");
dispatch("/servlet/SOAAdmin",request,response);
return;
}
else if ((role.equalsIgnoreCase("superadmin")))
{
//response.sendRedirect("/servlet/SOASuperAdmin");
dispatch("/servlet/SOASuperAdmin",request,response);
return;
}
else
{
//response.sendRedirect("/servlet/SOANewMainPage");
dispatch("/servlet/SOANewMainPage",request,response);
return;
}
}
else
{
String qryForUserName = "SELECT USER_ID FROM SOA_USERS WHERE USER_ROLE='"+role+"' AND UPPER(E_Mail) like '%"+LDAPemailId.toUpperCase()+"%' ";
System.out.println("qryForUserName="+qryForUserName);
ResultSet rsUserName = SOADbAccess.retrieveData(qryForUserName);
if(!rsUserName.next())
{
displayErrorMessage(out,SOAMessage.Message_20);
rsUserName.close();
}
else
{
rsUserName.close();
displayErrorMessage(out,SOAMessage.Message_21);
}
session.invalidate();
}
}
catch (Exception e)
{
System.out.println(e);
displayErrorMessage(out, e.getMessage());
}
}
In the application i have a class DBAccess in which the connection and the statement are made.
The resultset object is passed to the servlets from this class and the resultsets are closed after use.
Also in the application there are no statement.close() or connection.close() statement.
I want to confirm if closing the connection would solve the problem.
would appreciate if Tony or Joe or Jamie can give me quick reponse.
there is no stataement as connection.close() or statement.close()

[Jeanne Boyarsky]

It is a good idea to close the statement and connection under any circumstances. I imagine it could lead to the problem you are seeing. Also, you forgot to close the result set after the first query.

[Seetesh Hindlekar]

Hi,
Cld u post the code for SOADbAccess.xxx method used to retrieve ur resultset object?
maybe ur opening a new connection/resultset/statement object there?
Seetesh