We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

how do you cycle trough a java result set

claghorn
claghorn asked
on
Medium Priority
318 Views
Last Modified: 2011-10-03
Why does the result set keep closing after just one insert? There are many more rows to insert for this result set.

sqlText = "select * from table;";

results1 = sql.executeQuery(sqlText);

while (results1.next()) //error occurrs here "this result set is closed"
{
    CACTD_FileNumber = results1.getString("FileNumber");
    sqlText = "INSERT INTO another_table VALUES(CACTD_FileNumber)";
    sql.executeUpdate(sqlText);
}
Comment
Watch Question

Top Expert 2006

Commented:
According to http://java.sun.com/j2se/1.5.0/docs/api/java/sql/ResultSet.html#close()

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

Commented:
Try using two statements

Author

Commented:
So are you saying I need a new resultset object for every row returned by the first resultset object? I would have to find out how many rows are returned by the first, and then create that many resultset objects. But wait, it won't let me cycle past the first result because I'd be using it to "movenext". So again, I do not know how to do this operation because I cannot access anything beyond the first result. I guess I need to see some examples of how to this.
Top Expert 2006

Commented:
I'm saying that you should use another statement object to insert new rows, not the same statement that you get the ResultSet from. As I said the ResultSet will be automatically closed when you try to execute another query through the same statement object.

Author

Commented:
I tried using a new sql statement object to perform the insert, my problem is still on the line:
while (results1.next()) //error occurrs here "this result set is closed"
I still don't understand how I must use a new object to move to the next row of the original object (result set).
Top Expert 2006

Commented:
Please post your modified code

Author

Commented:
sqlText = "select * from table;";

results1 = sql.executeQuery(sqlText);
String sqlText2
while (results1.next()) //error occurrs here "this result set is closed"
{
    CACTD_FileNumber = results1.getString("FileNumber");
    sqlText2 = "INSERT INTO another_table VALUES(CACTD_FileNumber)";
    sql.executeUpdate(sqlText2);
}
Top Expert 2006
Commented:
You are still executing both queries from the same statement object --> sql
sql.executeQuery(sqlText);
sql.executeUpdate(sqlText2);

Create a new Statement for inserts
Statement stmt2 = con.createStatement();

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
A ResultSet object is automatically closed by the Statement object that generated it when:
a. that Statement object is closed
b. re-executed
c. used to retrieve the next result from a sequence of multiple results

I guess I violated b but I thought I was violating c. Even though your suggestion worked and I no longer violate b, I still continue to utilize c but there is no more error. Java documentation is a little misleading.

Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.