File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes error on statement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "error on statement" Watch "error on statement" New topic
Author

error on statement

Tom Joiner
Ranch Hand

Joined: Sep 19, 2006
Posts: 47
This code generates are error, having something to do with using the "Statement" twice in a row. If I utilized "Statement2" it has no error and works correctly. Do I need to close out the recordset or the statement to utilize the statement again in another query?

The error is posted at the bottom.






SCJP
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

Reusing/Closing a Statement will cause any resultset associated with to be released, so you can not use ResultSet once you close the statement or you execute any other query on same statement object.

you are getting the error because you are using resultset (roleSet), after you have executed another query on statement.

Shailesh


Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
Nidhi Singhal
Ranch Hand

Joined: Sep 19, 2004
Posts: 89

The error is coming because of using executeQuery() on the same statement object two times.

try if this works..

Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/database01", "SMART", "password");

String str1 = "SELECT * FROM account WHERE username = '" + userName + "'";

String str2 = "select * from role";

Statement statement1 = connection.createStatement();
ResultSet roleSet = statement1.executeQuery(str2);

Statement statement2 = connection.createStatement();
ResultSet rs = statement2.executeQuery(str1);
Tom Joiner
Ranch Hand

Joined: Sep 19, 2006
Posts: 47
So you are supposed to create a new statement for every SQL execution? That seems a bit out of control.. is there a way to release it and use it again?
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30537
    
150

Yes. You can reuse a prepared statement. However the SQL needs to be the same, so it wouldn't help you here.

String sql = "SELECT * FROM account WHERE username = ?";
PreparedStatement statement1 = connection.prepareStatement(sql);
statement1.setString(1, userName)l
ResultSet roleSet = statement1.executeQuery();


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
 
wood burning stoves
 
subject: error on statement