Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

how do you cycle trough a java result set

Posted on 2006-11-03
9
Medium Priority
?
287 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
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.
Viewers learn about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:
Suggested Courses
Course of the Month15 days, 8 hours left to enroll

575 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