• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

PreparedStatement

 
Anonymous
Ranch Hand
Posts: 18944
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have used PreparedStatement in my JDBC program. But it only works once after I run the program. The second time run the SQL I got an error. I am using JDBC:ODBC bridge and Sqlserver from MS. Can any one tell me this problem is due to my programing or the bridge or the Sqlserver? Thanks.
[This message has been edited by what10468 (edited June 22, 2000).]
 
Milind Kulkarni
Ranch Hand
Posts: 146
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
Can you post the the exact error and a part of code for our analysis.
Regards,
Milind
 
Anonymous
Ranch Hand
Posts: 18944
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Milind Kulkarni:
Hi,
Can you post the the exact error and a part of code for our analysis.
Regards,
Milind

Thanks Milind.
Here is the code
public void actionPerformed(ActionEvent evt){
String arg = evt.getActionCommand();
if (arg.equals("Query")){
ResultSet rs = null;
try{
String employee = (String)employees.getSelectedItem();
String category = (String)categories.getSelectedItem();
if (!employee.equals("All")&&!category.equals("Any")){
if (employeeCategoryQueryStmt == null){
String employeeCategoryQuery = "SELECT Products.ProductName, " +
"[Order Details].Discount FROM Employees, Orders, [Order Details], " +
"Products, Categories WHERE Employees.EmployeeID = " +
"Orders.EmployeeID AND Orders.OrderID = [Order Details].OrderID "+
"AND [Order Details].ProductID = Products.ProductID " +
"AND Products.CategoryID = Categories.CategoryID " +
"AND Employees.LastName = ? AND Categories.CategoryName = ?";
employeeCategoryQueryStmt = con.prepareStatement(
employeeCategoryQuery);
}
employeeCategoryQueryStmt.setString(1, employee);
employeeCategoryQueryStmt.setString(2, category);
rs = employeeCategoryQueryStmt.executeQuery();
result.setText("");
while (rs.next())
result.append(rs.getString(1) + " | " + rs.getFloat(2) + "\n");
rs.close();
}
else {
result.setText("Please try it later");
}
}
catch(Exception e){
result.setText("Error 2 " + e);
}
}
}
After click on the "Query" button second time, I got the following error:
Error 2 java.sql.SQLException: Invalid state for getResultSet
After the third time click, I got the following:
Error 2 java.sql.SQLException: [Microsoft][ODBC SQL Server Driver]Invalid cursor state
Thank you for the help!
 
Carl Trusiak
Sheriff
Posts: 3341
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by what10468:
Thanks Milind.
Here is the code
public void actionPerformed(ActionEvent evt){
String arg = evt.getActionCommand();
if (arg.equals("Query")){
ResultSet rs = null;
try{
String employee = (String)employees.getSelectedItem();
String category = (String)categories.getSelectedItem();
if (!employee.equals("All")&&!category.equals("Any")){
if (employeeCategoryQueryStmt == null){
String employeeCategoryQuery = "SELECT Products.ProductName, " +
"[Order Details].Discount FROM Employees, Orders, [Order Details], " +
"Products, Categories WHERE Employees.EmployeeID = " +
"Orders.EmployeeID AND Orders.OrderID = [Order Details].OrderID "+
"AND [Order Details].ProductID = Products.ProductID " +
"AND Products.CategoryID = Categories.CategoryID " +
"AND Employees.LastName = ? AND Categories.CategoryName = ?";
employeeCategoryQueryStmt = con.prepareStatement(
employeeCategoryQuery);
}
employeeCategoryQueryStmt.setString(1, employee);
employeeCategoryQueryStmt.setString(2, category);
rs = employeeCategoryQueryStmt.executeQuery();
result.setText("");
while (rs.next())
result.append(rs.getString(1) + " | " + rs.getFloat(2) + "\n");
rs.close();
}
else {
result.setText("Please try it later");
}
}
catch(Exception e){
result.setText("Error 2 " + e);
}
}
}
After click on the "Query" button second time, I got the following error:
Error 2 java.sql.SQLException: Invalid state for getResultSet
After the third time click, I got the following:
Error 2 java.sql.SQLException: [Microsoft][ODBC SQL Server Driver]Invalid cursor state
Thank you for the help!

Something similar can happen in Oracle, you arn't closing the Statement Object and some databases (Oracle is one of them) dosen't release the cursor when you close the ResultSet and Statement. After you close them you need to set them to null.
This should release the cursor in the database and prevent the Invalid state errors.
 
Milind Kulkarni
Ranch Hand
Posts: 146
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
As is clear from the code you do not seem to be closing ResultSet and Statement objects. These kinds of problems can normally be avoided if you release the resources by invoking their respective close() methods.
Regards,
Milind
 
Anonymous
Ranch Hand
Posts: 18944
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Milind Kulkarni:
Hi,
As is clear from the code you do not seem to be closing ResultSet and Statement objects. These kinds of problems can normally be avoided if you release the resources by invoking their respective close() methods.
Regards,
Milind

Thanks. You meant that I have to close the statement not just the
ResultSet, which I did close it. If I close the Statement objects, will the PreparedStatement be compiled again?

[This message has been edited by what10468 (edited June 23, 2000).]
 
Anonymous
Ranch Hand
Posts: 18944
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Something similar can happen in Oracle, you arn't closing the Statement Object and some databases (Oracle is one of them) dosen't release the cursor when you close the ResultSet and Statement. After you close them you need to set them to null.
This should release the cursor in the database and prevent the Invalid state errors
Thank you, Carl.
Should I close it in finally{} and if I do so, will the PreparedStatement be compiled again?
 
Carl Trusiak
Sheriff
Posts: 3341
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by what10468:

Something similar can happen in Oracle, you arn't closing the Statement Object and some databases (Oracle is one of them) dosen't release the cursor when you close the ResultSet and Statement. After you close them you need to set them to null.
This should release the cursor in the database and prevent the Invalid state errors
Thank you, Carl.
Should I close it in finally{} and if I do so, will the PreparedStatement be compiled again?

Yes, that's the perfect place to do this. It actually depends on yoour Database, some recompile it each time but, I believe that most cache this and when it gets a new one, sees that it already exists and uses the already compiled cached version.
Also after you close the statement and resultset set the reference to null. Oracle at least doesn't always release the cusor, this causes an 'Exceeds the max number of open curosrs allowed' error if you don't.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic