Solved

how do you cycle trough a java result set

Posted on 2006-11-03
9
271 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
login jsp example 24 85
java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: NO) 1 59
Java: anonymous class 4 38
Java Eclipse Loop 3 30
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.

756 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