Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

how do you cycle trough a java result set

Posted on 2006-11-03
9
Medium Priority
?
284 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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
 

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 75 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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
Video by: Michael
Viewers learn about how to reduce the potential repetitiveness of coding in main by developing methods to perform specific tasks for their program. Additionally, objects are introduced for the purpose of learning how to call methods in Java. Define …
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

916 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