Results 1 to 4 of 4
  1. #1
    statichack is offline Member
    Join Date
    Jun 2011
    Posts
    2
    Rep Power
    0

    Default Problem with mysql query in for loop

    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

  2. #2
    Junky's Avatar
    Junky is offline Grand Poobah
    Join Date
    Jan 2011
    Location
    Dystopia
    Posts
    3,800
    Rep Power
    7

    Default

    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.

  3. #3
    doWhile is offline Moderator
    Join Date
    Jul 2010
    Location
    California
    Posts
    1,641
    Rep Power
    7

    Default

    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

  4. #4
    statichack is offline Member
    Join Date
    Jun 2011
    Posts
    2
    Rep Power
    0

    Default awesome

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

Similar Threads

  1. Eclipse MySQL query
    By kenmat514 in forum Eclipse
    Replies: 2
    Last Post: 06-23-2011, 11:39 PM
  2. Query MySQL
    By boss in forum Advanced Java
    Replies: 3
    Last Post: 01-04-2010, 10:36 AM
  3. Query MySQL
    By boss in forum Advanced Java
    Replies: 3
    Last Post: 01-04-2010, 10:35 AM
  4. MySQL/JDBC Mysql query output
    By thelinuxguy in forum Advanced Java
    Replies: 4
    Last Post: 02-13-2009, 02:57 AM
  5. Mysql/JDBC update query problem
    By thelinuxguy in forum Advanced Java
    Replies: 3
    Last Post: 02-11-2009, 10:56 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •