claghorn
asked on
how do you cycle trough a java result set
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("FileNu mber");
sqlText = "INSERT INTO another_table VALUES(CACTD_FileNumber)";
sql.executeUpdate(sqlText) ;
}
sqlText = "select * from table;";
results1 = sql.executeQuery(sqlText);
while (results1.next()) //error occurrs here "this result set is closed"
{
CACTD_FileNumber = results1.getString("FileNu
sqlText = "INSERT INTO another_table VALUES(CACTD_FileNumber)";
sql.executeUpdate(sqlText)
}
Try using two statements
ASKER
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.
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.
ASKER
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).
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).
Please post your modified code
ASKER
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("FileNu mber");
sqlText2 = "INSERT INTO another_table VALUES(CACTD_FileNumber)";
sql.executeUpdate(sqlText2 );
}
results1 = sql.executeQuery(sqlText);
String sqlText2
while (results1.next()) //error occurrs here "this result set is closed"
{
CACTD_FileNumber = results1.getString("FileNu
sqlText2 = "INSERT INTO another_table VALUES(CACTD_FileNumber)";
sql.executeUpdate(sqlText2
}
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
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