Problem with mysql query in for loop

[statichack]

Hello all, i have been making a tool for work which stores data such as account attributes in a database, then allows you to search through the database using simple sql queries. All has been fine with being able to add a new row to a table in a database, delete a row, count the number of rows and make a dynamic html table to hold the info using jsp, but now ive come to the statistics page which takes data from one table, and uses it to get data in another.

Basically one table holds an index number, and a reason for the account to be in the database (2 columns) and the other table contains the accounts. I want to take each index and put them all into a table in 1 column, and in the other show the amount of accounts in the database with that index.


Whats odd is the page actually prints the first row of the table as it should, but then does the catch instructions for sqlexception


heres the code:



<%

String indexcount = "";
String indexcheck = "";
String reason = "";
String acccount = "";

try {

conn = DriverManager.getConnection("jdbc:mysql:1.1.1.1/database","xxxxxx","xxxxxx")

stmnt = conn.createStatement();

rs = stmnt.executeQuery("select count(*) from cindex");

rs.next();

indexcount = rs.getString(1);


int a = Integer.parseInt(indexcount);




rs = stmnt.executeQuery("select * from cindex");


%>

<table border=1>
<tr><th>Index</th><th>Reason</th><th>Number of Accounts</th></tr>

<%

for (int i = 1; i <= a; i++) {


rs.next();
indexcheck = rs.getString(1);
reason = rs.getString(2);
rs2 = stmnt.executeQuery("select count(*) from accountbase where rindex=" + indexcheck);
rs2.next();
acccount = rs2.getString(1);

%>
<tr><td> <%= indexcheck %> </td> <td> <%= reason %> </td> </td>
<td> <%= acccount %> </td></tr>

<%

}

} catch (SQLException e) {

out.println("ERRS");
e.printStackTrace();
}
%>




Whats even odder, is that the tomcat logs dont show ANYTHING for this error as if they would if the page code had errors in it, which leads me to believe its something to do with putting an SQL query in a for loop, maybe some sort of java security mechanism? the loop completes the first way through as an entire row of the table is generated with the first row of my index/reason/count and the count is even correct

[Junky]

In future it helps if you copy and paste the exact and full error message you get. However I assume you problem is related to (from the API):

"A ResultSet object is automatically closed when the Statement object that generated it is closed, re-executed, or used to retrieve the next result from a sequence of multiple results."

You can only have one ResultSet open at a time.

[doWhile]

Enclosing your code in the formatting code tags makes it that much more readable (and typically results in people actually trying to read your code rather then moving on to another post).

Read the API for Statement (Java Platform SE 6) - only a single ResultSet can be associated with a statement. Hence doing what you have coded - looping over a ResultSet and trying to query again using the same Statement - will result in this sort of behavior. Create multiple Statements or (better yet) use a PreparedStatement defined outside the loop and called from with the loop.

Edit: Too slow again

[statichack]

Great! got it thanks to your anwers! just had to create a seperate statement.