Exahusted Resultset error when data is present

[Becker]

I am attempting to pull data from a database via a servlet. I have
verified that my SQL works and that there is data in the database
(exactly one row for each query that I'm interested in). When I
restart the webserver my servlet works wonderfully. However when I go
back and click on a second item I get a ExahustedResultset error. I
have renamed my conn/stmt/rs variable names in this servlet to make
sure they are unique. I have verifed with cut-n-paste that my SQL is
fine. There is one row for each query in the database. I have tried
rs.first() rs.last() rs.absolute(1) but the second time I use the
servlet, it fails.

Any ideas?

Code is below. Some name have been changed to protect the guilty.




--------------

public class Foo extends HttpServlet {

public void init() throws ServletException {


super.init();
username="username";
password="password";
query1 = "select stuff from lotsofstuff"
jdbc_class = "oracle.jdbc.driver.OracleDriver";
jdbc_url = "jdbcracle:thinasysadm10:1521:MACHINENAME";

}

public synchronized void service(HttpServletRequest req,
HttpServletResponse res)
throws ServletException, IOException
{

try {
Connection conA = null;
Statement stmtA = null;
ResultSet rsA = null;
String variableName = null;

Class.forName(jdbc_class);
conA = DriverManager.getConnection(jdbc_url, username,
password);

DatabaseMetaData dma = conA.getMetaData();

rsA = stmtA.executeQuery(query1);

if ( rsA.next() ) {
variableName = rsA.getString(1);
}


/********* I have attempted to add in the following, but it doesn't
work
else {
rsA.first();
variableName = rsA.getString(1);
}
**********/
stmtA.clearBatch();

rsA.close();
stmtA.close();
conA.close();

rsA = null;
stmtA = null;
conA = null;

}

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

 

[Bryce (Work)]

I am attempting to pull data from a database via a servlet. I have
verified that my SQL works and that there is data in the database
(exactly one row for each query that I'm interested in). When I
restart the webserver my servlet works wonderfully. However when I go
back and click on a second item I get a ExahustedResultset error. I
have renamed my conn/stmt/rs variable names in this servlet to make
sure they are unique. I have verifed with cut-n-paste that my SQL is
fine. There is one row for each query in the database. I have tried
rs.first() rs.last() rs.absolute(1) but the second time I use the
servlet, it fails.

Any ideas?

Code is below. Some name have been changed to protect the guilty.




--------------

public class Foo extends HttpServlet {

public void init() throws ServletException {


super.init();
username="username";
password="password";
query1 = "select stuff from lotsofstuff"
jdbc_class = "oracle.jdbc.driver.OracleDriver";
jdbc_url = "jdbcracle:thinasysadm10:1521:MACHINENAME";

}

Just a suggestion, you may want to look at configuring your container
for datasources, then retrieving a connection is as easy as:
Context initialContext = new InitialContext()
DataSource ds =
(DataSource)initialContext.lookup("java:comp/env/jdbc/myds");

Connection conn = ds.getConnection();

and then you can setup the configuration in your config files...

public synchronized void service(HttpServletRequest req,
HttpServletResponse res)
throws ServletException, IOException
{

try {
Connection conA = null;
Statement stmtA = null;
ResultSet rsA = null;
String variableName = null;

Class.forName(jdbc_class);
conA = DriverManager.getConnection(jdbc_url, username,
password);

DatabaseMetaData dma = conA.getMetaData();

rsA = stmtA.executeQuery(query1);

I don't see you setting stmtA.... Did you leave part of your code out?

if ( rsA.next() ) {
variableName = rsA.getString(1);
}

Shouldn't need the next part...