Java

SQL-JAVA ResultSet problem

Please support our Java advertiser: Programming Forums - DaniWeb Sister Site

•

Join Date: May 2005

Posts: 30

Reputation: yassar is an unknown quantity at this point

Solved Threads: 0

yassar

Offline

Light Poster

SQL-JAVA ResultSet problem

Apr 23rd, 2008

Hi,

I get the following exception when i try to run the following code segment.

 Help with Code Tags 
 JAVA Syntax (Toggle Plain Text) 
stmt = (Statement) conn.createStatement();
 
rs = stmt.executeQuery( "SHOW TABLES");
 
if(rs.first()) {
	stmt.execute( "DROP TABLE " + rs.getString(1));
	while (rs.next()){
		stmt.execute( "DROP TABLE " + rs.getString(1));
	}
}
stmt = (Statement) conn.createStatement();
			
rs = stmt.executeQuery( "SHOW TABLES");
						
if(rs.first()) {
	stmt.execute( "DROP TABLE " + rs.getString(1));
	while (rs.next()){
		stmt.execute( "DROP TABLE " + rs.getString(1));
	}
}

And the exception i get is:

SQLException: Operation not allowed after ResultSet closed
SQLState: S1000
VendorError: 0

Last edited by yassar; Apr 23rd, 2008 at 4:45 am.

•

Join Date: May 2007

Posts: 4,505

Reputation: Ezzaral has a brilliant future

Solved Threads: 521

Ezzaral

Offline

Industrious Poster

Re: SQL-JAVA ResultSet problem

Apr 23rd, 2008

You have executed a new query here through your statement so the result set you obtained with that statement is no longer valid and you cannot retrieve field values from it.

 Help with Code Tags 
 java Syntax (Toggle Plain Text) 
stmt.execute( "DROP TABLE " + rs.getString(1));
stmt.execute( "DROP TABLE " + rs.getString(1));

Create a separate statement object to make your "DROP TABLE" calls with.

 Help with Code Tags 
 java Syntax (Toggle Plain Text) 
PreparedStatement updateStmt = conn.prepareStatement("DROP TABLE ?");
 
stmt = conn.createStatement();
 
rs = stmt.executeQuery( "SHOW TABLES");
 
while (rs.next()) {
    updateStmt.setString(1, rs.getString(1));
    updateStmt.executeUpdate();
}
updateStmt.close();
stmt.close();
PreparedStatement updateStmt = conn.prepareStatement("DROP TABLE ?");

stmt = conn.createStatement();
			
rs = stmt.executeQuery( "SHOW TABLES");
						
while (rs.next()) {
    updateStmt.setString(1, rs.getString(1));
    updateStmt.executeUpdate();
}
updateStmt.close();
stmt.close();