File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Exhausted Resultset Error Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Exhausted Resultset Error" Watch "Exhausted Resultset Error" New topic
Author

Exhausted Resultset Error

shweta kavungal
Greenhorn

Joined: Feb 12, 2004
Posts: 3
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
internet detective
Marshal

Joined: May 26, 2003
Posts: 30309
    
150

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Seetesh Hindlekar
Ranch Hand

Joined: Feb 13, 2004
Posts: 244
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
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
 
subject: Exhausted Resultset Error