Java/MySQL loop... stuck?

Question

I don't really know what is going on so I'll just post part of the code

    int index2 = 0;

    while(rs.next()){
        System.out.println(rs.getInt("ItemID") + ", " + rs.getString("SocSecNum") + ", " + rs.getInt("Score"));

        if(rs.getInt("ItemID") == ID && rs.getString("SocSecNum").equals(socSecNum)){
            alreadySet = true;
            System.out.println("Flag 1");
            caq.executeUpdate("UPDATE ProjectScore SET Score = " + userScoreField.getText() +
                    " WHERE (ItemID = " + ID + ") AND (SocSecNum = '" + socSecNum + "')");
        }

        index2++;
        System.out.println(index2);
    }

    System.out.println("Flag 2");

Looks like it would work yeah? Here's the output:

1, 640730-7419, 3

Flag 1

1

This would imply that the while-loop is stuck somehow but there is no additional output (index2). Also, the database is updated exactly as it should but the program doesn't progress from here and "Flag 2" is never written out. Any ideas?

Edit:

catch (SQLException e1) {
                    System.out.println(e1.getMessage());
                    System.out.println(e1.getCause());
                }

Gives

Operation not allowed after ResultSet closed

null

Edit 2:

Here's the code used to make it work

    PreparedStatement statement = caq.getConnection().prepareStatement("SELECT * FROM ProjectScore WHERE SocSecNum = '"
    + socSecNum + "'", ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
    rs  = statement.executeQuery();

So,... you're updating the query while still going through its ResultSet? Solution: don't do that. — Hovercraft Full Of Eels
– Hovercraft Full Of Eels, Commented Jan 4, 2013 at 2:42
Mind explaining how I'm doing that? I don't really know how a ResultSet work. — Latedi
– Latedi, Commented Jan 4, 2013 at 2:47

Subin Sebastian · Accepted Answer · 2013-01-04 02:47:26Z

1

can you try instead of caq.executeUpdate connection.prepareStatement().executeUpdate() I am assuming caq.executeUpdate will cause the previous returned ResultSet object to close.

Also there seems to be a way to set concurrency level in Statement ResultSet.CONCUR_UPDATABLE
http://docs.oracle.com/javase/6/docs/api/java/sql/Connection.html#prepareStatement(java.lang.String,int,int)

edited Jan 4, 2013 at 2:47

answered Jan 4, 2013 at 2:37

Subin Sebastian

11k3 gold badges39 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Latedi Over a year ago

Hmm connection doesn't have a method called getSatement() however I tried rs = caq.getStatement().executeQuery("SELECT * FROM ProjectScore WHERE SocSecNum = '" + socSecNum + "'"); but it didn't work either. I think it might be worth mentioning that I used the same ResultSet previously. Although I made another one and still had the same result.

Subin Sebastian Over a year ago

edited the answer. it is not getStatement() is prepareStatement(), let me know know if that works. Sorry for the dump answer at first :-)

Subin Sebastian Over a year ago

basically the problem is you are sending an update query in the loop, which will close the resultset and throws exception

Latedi Over a year ago

Thanks man it worked perfectly :D much appreciated! It's going to take a while before I understand exactly why but I'll try to read up on it.

Collectives™ on Stack Overflow

Java/MySQL loop... stuck?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related