select and update using the same statement object

[vidhi sanghavi]

Hi ,



I am trying to access cetain data from the database.
once I retrieve the data I have to process dat data and then update a particular column using the modified data.
I used the following code.

Statement stmt=null;

ResultSet rs=null;
rs = stmt.executeQuery("select data from table where id = 2");
while(rs.next()){
String description= rs.getString("data");
String overview=description.substring(description.indexOf("<Overview>")+10,description.indexOf("</Overview"));

String updateString = "update table " +
"set data= '"+overview+"' " +
"where id = 2 ";

int x= stmt.executeUpdate(updateString);
System.out.println("query updated");
}
I am getting the followng exception.........


java.sql.SQLException: ResultSet is closed


Please help me in solving the problem......


Thank you in advance

Vidhi

[Leonardo Carreira]

Hi...

i think you should use 2 statements for execute your queries..
first statement is used to execute "executeQuery" method...
second statement is used to execute "executeUpdate" method..

or you can use only 1 statement with define it as private...

private Statement stmt;

hope that help...

Correct Me if i'm wrong please..
Thanks..

[Balu Sadhasivam]

Vidhi,

Executing SQL Statements with same Statemenet object without closing the resultSet opened will yeild uncharacteristic results. The solution should be to store the resultset values to a local objects (variables) , open a new resultset and use it execute the second Statement ( statement object can be same).

[vidhi sanghavi]

Hi,


Thank you for the previous reply!!!11

Could you please include the statements that are mssing in the code.
I am nt able to understand what am I supposed to da



Thank you in advance,
Vidhi

[Balu Sadhasivam]

vidhi sanghavi wrote:
Could you please include the statements that are mssing in the code.
I am nt able to understand what am I supposed to da

You understood my reply ?

 while(rs.next()){
String description= rs.getString("data");
String overview=description.substring(description.indexOf("<Overview>")+10,description.indexOf("</Overview"));

String updateString = "update table " +
"set data= '"+overview+"' " +
"where id = 2 ";

int x= stmt.executeUpdate(updateString);
System.out.println("query updated");
} 

Why do you require to execute update for each time the loop iterates , your where condition "id = 2" which is always same for all iterations , so you can move that code out. like this.

 
String overview = "";

while(rs.next()){
String description= rs.getString("data");
overview=description.substring(description.indexOf("<Overview>")+10,description.indexOf("</Overview"));
} 

// Note no need to create a new statement.
String updateString = "update table " +
"set data= '"+overview+"' " +
"where id = 2 ";

int x= stmt.executeUpdate(updateString);
System.out.println("query updated");

[Balu Sadhasivam]

Leonardo Carreira wrote:

Or you can use only 1 statement with define it as private...
private Statement stmt;
Correct Me if i'm wrong please..

How "private" would help here ?

[Jan Cumps]

A remark:
Your example:

Statement stmt=null; 

ResultSet rs=null; 
rs = stmt.executeQuery("select data from table where id = 2"); 

would throw an exception, because stmt is null when you call it's executeQuery() method.



In your initial example, you change the original stmt inside of your loop.
Maybe this corrupts your loop, because the ResultSet you associated with the Statement is closed when you call a new operation (executeOpdate()) on that same statement?

Can you replace

int x=	stmt.executeUpdate(updateString); 

by

Statement updateStatement = yourconnection.createStatement();
int x=	updateStatement .executeUpdate(updateString); 
updateStatement .close();

, and tell us what happens?

[Leonardo Carreira]

Balu Sadhasivam wrote:
How "private" would help here ?

previously, i guessed the Statement stmt object is out of scope..
because stmt object accessed in the loop..

yeah i think the problem is stmt object set by null value..

Correct me if i'm wrong..
Thanks