JDBC resultset issues with weblogic 8.1.6

[aravind gedela]

i'm getting a resultset exhausted code for one of my action classes in a simple struts j2ee app. the JDBC code is working in all other places except this one. i'm pasting the code and the error stack trace. any help would be appreciated. thank you. (this app works just fine in tomcat)!i'm using weblogic 8.1.6

Code:

import java.io.IOException;

import javax.servlet.*;

import javax.servlet.http.*;

import org.apache.struts.action.*;

import java.sql.*;

import java.util.ArrayList;
import java.util.PropertyResourceBundle;


public class mainPageAction extends Action {


public ActionForward execute(

ActionMapping mapping,

ActionForm form,

HttpServletRequest request,

HttpServletResponse response)

throws IOException, ServletException {




try
{
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
}
catch (ClassNotFoundException cnf){
System.err.println( "Oracle Driver Not Found ");
}




HttpSession session = request.getSession(true);
ArrayList states = new ArrayList();
ArrayList contractTypes = new ArrayList();
ArrayList owners = new ArrayList();

ArrayList contracts = new ArrayList();
PropertyResourceBundle myprops = (PropertyResourceBundle) PropertyResourceBundle.getBundle("ApplicationResources");
String URL = myprops.getString("URL");
String USERNAME = myprops.getString("USERNAME");
String PASSWORD = myprops.getString("PASSWORD");
Connection con = DriverManager.getConnection(URL,USERNAME,PASSWORD);
Statement st = con.createStatement();

ResultSet resultset = null;
ResultSet resultset1 = null;
ResultSet resultset2 = null;
ResultSet resultset3 = null;
ResultSet resultset4 = null;


loginBean lb = (loginBean) session.getAttribute("loginbean");
if (lb == null){
System.out.println("Not a valid session");
return (mapping.findForward("failure"));
}

String sid = null;
int count = 0;

String scount = null;

scount = String.valueOf(count);

statebean state;
siteBean site = null;
contract cont;
contractType ctype = null;
ownerBean OBean = null;






state = new statebean();
state.setMyLabel("");
state.setMyValue("0");
states.add(state);

for(resultset = st.executeQuery("SELECT ename,id FROM STATE"); resultset.next(); states.add(state))
{
state = new statebean();
state.setMyLabel(resultset.getString(1));
state.setMyValue(resultset.getString(2));
}

resultset.close();
session.setAttribute("states", states);


ctype = new contractType();
ctype.setMyLabel("");
ctype.setMyValue("0");
contractTypes.add(ctype);

for(resultset3 = st.executeQuery("SELECT ename,id FROM CONTRACT_TYPE"); resultset3.next(); contractTypes.add(ctype))
{
ctype = new contractType();
ctype.setMyLabel(resultset.getString(1));
ctype.setMyValue(resultset.getString(2));
}

resultset3.close();
session.setAttribute("contractTypes", contractTypes);


OBean = new ownerBean();
OBean.setMyLabel("");
OBean.setMyValue("0");
owners.add(OBean);


for(resultset4 = st.executeQuery("SELECT first_name,last_name,id FROM USERS WHERE spe******t_ind = '1' "); resultset4.next(); owners.add(OBean))
{
OBean = new ownerBean();
OBean.setMyLabel(resultset.getString(1)+" "+resultset.getString(2));
OBean.setMyValue(resultset.getString(3));
}

resultset4.close();
session.setAttribute("owners", owners);

String m = null;
String cm = request.getParameter("cmode");
m = request.getParameter("mode");
session.setAttribute("mode", m);
session.setAttribute("cmode", cm);
System.out.println(m);
if (!m.equals("Add"))
{
if (cm == null){
cm = "View";}
session.setAttribute("cmode",cm);
sid = request.getParameter("sid");
if (sid == null){
sid = (String)session.getAttribute("sid");
}
System.err.println("in main page action"+sid);
resultset1 = st.executeQuery("SELECT id,ename,state_id FROM SITE WHERE id = "+sid+"");
while (resultset1.next())
{
site = new siteBean();
site.setSiteID(resultset1.getString(1));
site.setSiteName(resultset1.getString(2));
site.setStateID(resultset1.getInt(3));
sid = site.getSiteID();
session.setAttribute("sitename", site.getSiteName());
}
resultset1.close();


/*site.setConts(contracts);*/





for(resultset2 = st.executeQuery("SELECT contract.id, contract.created_by, site.ename, users.first_name, users.last_name, contract_type.ename,contract.enumber FROM (users right outer join (site right outer join (contract_type right outer join contract on contract_type.id = contract.contract_type_id) on site.id = contract.site_id) on users.id = contract.users_id_spe******t) where contract.site_id = "+ sid +""); resultset2.next(); contracts.add(cont))
{
cont = new contract();
cont.setContractID(resultset2.getString(1));
cont.setCreatedBy(resultset2.getString(2));
cont.setSite(resultset2.getString(3));
cont.setOwner(resultset2.getString(4)+" "+resultset2.getString(5));
cont.setContractType(resultset2.getString(6));
cont.setContractNumber(resultset2.getString(7));

}

resultset2.close();

}
else{
site = new siteBean();
site.setSiteID("");
site.setSiteName("");
site.setStateID(0);


}





session.setAttribute("site", site);
session.setAttribute("scount", scount);
request.setAttribute("contracts", contracts);




st.close();

con.close();

}
catch (SQLException se) {
se.printStackTrace();
}



return (mapping.findForward("VFiles"));

}

}

Stack Trace:

<Dec 4, 2006 2:22:11 PM EST> <Notice> <WebLogicServer> <BEA-000360> <Server star
ted in RUNNING mode>
user present!
java.sql.SQLException: Exhausted Resultset
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java
:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java
:146)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java
:208)
at oracle.jdbc.driver.OracleResultSetImpl.getString(OracleResultSetImpl.
java:382)
at mainPageAction.execute(mainPageAction.java:112)
at org.apache.struts.action.RequestProcessor.processActionPerform(Reques
tProcessor.java:431)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.ja
va:236)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:119
6)
at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:414)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:740)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at weblogic.servlet.internal.ServletStubImpl$ServletInvocationAction.run
(ServletStubImpl.java:1077)
at weblogic.servlet.internal.ServletStubImpl.invokeServlet(ServletStubIm
pl.java:465)
at weblogic.servlet.internal.ServletStubImpl.invokeServlet(ServletStubIm
pl.java:348)
at weblogic.servlet.internal.WebAppServletContext$ServletInvocationActio
n.run(WebAppServletContext.java:7047)
at weblogic.security.acl.internal.AuthenticatedSubject.doAs(Authenticate
dSubject.java:321)
at weblogic.security.service.SecurityManager.runAs(SecurityManager.java:
121)
at weblogic.servlet.internal.WebAppServletContext.invokeServlet(WebAppSe
rvletContext.java:3902)
at weblogic.servlet.internal.ServletRequestImpl.execute(ServletRequestIm
pl.java:2773)
at weblogic.kernel.ExecuteThread.execute(ExecuteThread.java:224)
at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:183)

Message was edited by:
agajoji

[Carol Enderlin]

Wow! That could use some refactoring.

Which line is 112 in mainPageAction?

java.sql.SQLException: Exhausted Resultset
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java
:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java
:146)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java
:208)
at oracle.jdbc.driver.OracleResultSetImpl.getString(OracleResultSetImpl.
java:382)
at mainPageAction.execute(mainPageAction.java:112)

[Carol Enderlin]

This looks like a problem...

Inside this "resultset3" for loop you are using "resultset"

for(resultset3 = st.executeQuery("SELECT ename,id FROM CONTRACT_TYPE"); resultset3.next(); contractTypes.add(ctype))
{
ctype = new contractType();
ctype.setMyLabel(resultset.getString(1));
ctype.setMyValue(resultset.getString(2));
}

Also doing that in the resultset4 for loop.
[ December 05, 2006: Message edited by: Carol Enderlin ]

[aravind gedela]

Thanks a lot! totally missed it! funny how it worked fine in tomcat!