ResultSet problem

[Felix Li]

Hi all,

After I used a ResultSet object to store data from the database. I use the following code to print the rows one by one:

while(rs.next()){
out.print(rs.getString(0));
out.print(rs.getString(1));
...
}

The problem is, I will get an invalide cursor error. Then comes my investigation. The ResultSet should contain one row as validated using actual SQL against the database. rs.next() returns true the first time of the loop and things are fine without the while(). BUT if rs.next() is called again, it returns true again! But this time, any calls to getString() will cause an invalid cursor state error.

No doubt I can still get my app going without the while(). But I am really interested in knowing why this is happening.

*In case if this has anything to do with it, the same object for connection and ResultSet were used in issuing a previous query earlier.

Thanks in advance

[Scott Selikoff]

while(rs.next()) is an extremely common pattern so could you post the code before and after this loop as I suspect there might be something there?

Actually from what I understand of problems like this it usually arises when you issue multiple queries. Can you post the code you are using to execute and close result sets and statements for both queries? That should help figure out where the issue is.
[ August 06, 2008: Message edited by: Scott Selikoff ]

[Paul Clapham]

out.print(rs.getString(0));

Column numbering starts at 1 in JDBC.

[Felix Li]

Oh, sorry about the rs.getString(0). It is supposed to be rs.getString(1). That's not the root of the problem. (I was just trying to think of an example :P)

The overall design has a ServletContextListener and initialize a DBConnection object and put it as a context attribute. Then whenever a jsp needs to query the database, I get the DBConnection object out from the context attribute and use the doSelect() method in DBConnection class. Here is some of the more important code previous to the call:

public class DBConnection {

private Connection connection;
private Statement statement;
private ResultSet resultSet;

public DBConnection(){
connect();
}
private boolean connect(){
boolean status = false;
try{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}catch(ClassNotFoundException ex){
}
try{
connection = DriverManager.getConnection("jdbc:odbc:ODBC_MSACCESS_LOCAL");
connection.setAutoCommit(true);
status = true;
}catch(SQLException ex){
System.out.println(ex);
}
return status;
}
public ResultSet doSelect(String query){

try{
statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY,ResultSet.CLOSE_CURSORS_AT_COMMIT);
statement.execute(query);
resultSet = statement.getResultSet();
}catch(SQLException ex){
System.out.println(ex);
resultSet = null;
}
return resultSet;

}
}
******************************
Report.jsp:

<%@ page import="db.*,java.sql.*" %>
<%
DBConnection db = (DBConnection)application.getAttribute("conn");
String query1 = "Select * from EXPENSE";
ResultSet rs = db.doSelect(query1);

out.print("<table>");

while(rs.next()){
out.print("<tr>");
out.print("<td>" + rs.getString("Date").split(" ")[0] + "</td>");
out.print("<td>" + rs.getString("Description") + "</td>");
out.print("<td>$" + rs.getDouble("Subtotal") + "</td>");
out.print("<td>$" + rs.getDouble("Total") + "</td>");
out.print("</tr>");
}

String query2 = "select sum(subtotal) AS SUMSUB", sum(total) AS SUMTOT from EXPENSE";

rs = db.doSelect(query2);

while(rs.next()){
out.print("<td><b>$" + rs.getDouble("SUMSUB") + "</b></td>");
out.print("<td><b>$" + rs.getDouble("SUMTOT") + "</b></td>");
}

out.print("</table>");
%>
...

So basically I am trying to print a list of expenses from the database, and add a last <tr></tr> to display the total expenses. If I remove the 2nd while loop and only do a rs.next() once, then this app will work (given there is at least one record)

Thanks for the effort in helping.

[Paul Clapham]

There's a few problems I can see there, which may or may not be causing your problem.

1. I would use forward-only result sets. There's no point in using scrollable result sets for this sort of application.

2. You only have one connection for the entire application. That's going to lead to obscure problems when you have multiple users accessing it at the same time.

3. You don't ever close your result sets. Always close them when you are finished with them.

4. Your Statement and ResultSet objects are instance-level variables in your DBConnection class. This is unnecessary, they could be local variables in the method where they are used. Making them instance-level variables opens the door to synchronization problems.

And then there's the design issues, which aren't causing your problem:

5. When you find yourself using "out.print()" in a JSP scriptlet, that's a sure sign you shouldn't have used a JSP. That code should be in a servlet. Or better still, use a servlet to extract the data from the database into a list of bean objects, then forward to a JSP which reads that list and produces HTML.

6. You have locked yourself into using a String for your queries. This makes it impossible to use a PreparedStatement. Now if you have variable information in your query, you're going to have to construct your query using string concatenation. This leaves you open to SQL injection attacks and requires you to deal with escaping of quotes and date formatting yourself.

[Paul Clapham]

Also, this code is rather strange:

 statement.execute(query);
resultSet = statement.getResultSet();

A more usual way to do it is like this:

resultSet = statement.executeQuery(query);

[Felix Li]

Awewome advice from Paul. Thanks. Problem solved with using forward only for statement and closing all those ResultSets.

From point 5, I am interested in better design. So when would it be a good situation to use servlet and when is it good to use jsp? I am still in the middle of picking up jsp. I know that all jsp are translated to servlet, so I tried to keep things straight forward thus have a list of jsp and no servlet java file. :P

Would you mind giving me a simple example on how to use the PreparedStatement?

Thanks.

[Paul Clapham]

Originally posted by Chun Lee:
From point 5, I am interested in better design. So when would it be a good situation to use servlet and when is it good to use jsp? I am still in the middle of picking up jsp. I know that all jsp are translated to servlet, so I tried to keep things straight forward thus have a list of jsp and no servlet java file.

Yes, it seems easier to just write JSP when you're starting out. Although I found it took me about two days before I got really, really tired of trying to get the Java bits matched up with the Java bits and the HTML bits matched up with the HTML bits.

The current professional style is to use a servlet to get the parameters from the request and to prepare any data for the response. This would include connecting to the database and extracting data into bean-style objects. You should close all your JDBC resources here. Then you forward to a JSP which simply goes through the bean-style objects (which you stored as request attributes) and produces HTML. Also, using JSTL in these JSPs simplifies things compared to scriptlets.

I would go so far as to say it's not worth learning how to use scriptlets in JSPs any more. Learn JSTL instead for new developments. But there's an awful lot of scriptlet-packed JSPs in production which need maintenance, so there is a reason to learn about scriptlets.

Would you mind giving me a simple example on how to use the PreparedStatement?

Here's a link to part of the JDBC tutorial. It would be worth your while to have a look at the rest of it, too.

[Felix Li]

Thanks for the help. The link looks good and should give me enough info about PreparedStatement.

See you around the ranch again