Solved

how do you cycle trough a java result set

Posted on 2006-11-03
9
266 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);
}
0
Comment
Question by:claghorn
  • 5
  • 4
9 Comments
 
LVL 14

Expert Comment

by:hoomanv
ID: 17867947
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
0
 
LVL 14

Expert Comment

by:hoomanv
ID: 17867957
Try using two statements
0
 

Author Comment

by:claghorn
ID: 17868160
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.
0
 
LVL 14

Expert Comment

by:hoomanv
ID: 17868242
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.
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:claghorn
ID: 17868544
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).
0
 
LVL 14

Expert Comment

by:hoomanv
ID: 17868548
Please post your modified code
0
 

Author Comment

by:claghorn
ID: 17868778
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);
}
0
 
LVL 14

Accepted Solution

by:
hoomanv earned 25 total points
ID: 17868799
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();
0
 

Author Comment

by:claghorn
ID: 17869675
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.

0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

An old method to applying the Singleton pattern in your Java code is to check if a static instance, defined in the same class that needs to be instantiated once and only once, is null and then create a new instance; otherwise, the pre-existing insta…
Java Flight Recorder and Java Mission Control together create a complete tool chain to continuously collect low level and detailed runtime information enabling after-the-fact incident analysis. Java Flight Recorder is a profiling and event collectio…
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:
This video teaches viewers about errors in exception handling.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now