Statement is closed?

[Jennifer Sohl]

I have numerous statement objects and result sets that are defined in different methods in one class. I am closing each statement object in a finally block at the end of each method. Now I keep getting "Statement is closed" error when I try to run my app. What is going on? If I am creating a new Statement object whenever that method is run, why is it telling me that it is closed? It doesn't get closed until it's done running through the method, right?
Here's some code...

public void addCustomerValues() {
addItem(" ");
ResultSet rs1 = null;
Statement stmt1 = con.getStatement();
try {
rs1 = stmt1.executeQuery("SELECT INWCNM FROM INCUST ORDER BY INWCNM");
while(rs1.next()) {
addItem(rs1.getString(1));
}
}
catch(SQLException sqle) {
JOptionPane.showMessageDialog(this, "SKComboBoxE01-" + sqle, "Error", JOptionPane.ERROR_MESSAGE);
}
finally {
try {
if(stmt1 != null)stmt1.close();
}
catch(SQLException sqle) {
JOptionPane.showMessageDialog(this, "SKComboBoxE11-" + sqle,"Error",JOptionPane.ERROR_MESSAGE);
}
}
}  

public void addMarketIDValues() {
addItem("");
ResultSet rs2 = null;
Statement stmt2 = con.getStatement();
try {
rs2 = stmt2.executeQuery("SELECT DESCRIPTION FROM MARKETINGUNITS order by description");
while(rs2.next()) {
addItem(rs2.getString(1));
}
}
catch(SQLException sqle) {
JOptionPane.showMessageDialog(this, "SKComboBoxE02-" + sqle, "Error", JOptionPane.ERROR_MESSAGE);
}
finally {
try {
if(stmt2 != null) stmt2.close();
}
catch(SQLException sqle) {
JOptionPane.showMessageDialog(this, "SKComboBoxE12-" + sqle,"Error",JOptionPane.ERROR_MESSAGE);
}
}
}  

Any ideas?
Thanks!

[Blake Minghelli]

From you code sample:

 Statement stmt1 = con.getStatement(); 

Is con a java.sql.Connection object? If so, is that just a typo in your post, do you mean con.createStatement();??? If con is not a java.sql.Connection object, then I think I need to see the getStatement() method code.
Other than the con.getStatement(), I don't see anything wrong.

[Jennifer Sohl]

Sorry! I have an SQLConnection class that is creating the statement. Here it is:

package com.storekraft.util;
import java.sql.*;
import com.jnetdirect.jsql.*;
import a.*;
import javax.swing.*;
public class SQLConnection {
private Connection con;
private String userName;
private String password;
private StringBuffer sb = new StringBuffer();
private Statement stmt;
private PreparedStatement ps1;
private PreparedStatement ps2;
private PreparedStatement ps3;
private PreparedStatement ps4;
private PreparedStatement ps5;
private PreparedStatement ps6;
private PreparedStatement ps7;
private String sourceURL;
public SQLConnection(String sourceURL, String userName, String password) {
this.userName = userName;
this.password = password;
this.sourceURL = sourceURL;
try {
Class.forName("com.jnetdirect.jsql.JSQLDriver");
//String sourceURL = "jdbc:JSQLConnect://207.91.37.42/database=northwind";
con = DriverManager.getConnection(sourceURL, userName, password);
stmt = con.createStatement();
ps1 = con.prepareStatement("UPDATE PROJECTMASTER SET Description = ?, MarketingUnit = ?, Type = ?, Name = ?, Address1 = ?, Address2 = ?, City = ?, StateProvince = ?, PostalCode = ?, Country = ?,ProjectCode = ?, CompletionDate = ?, StatusFlag = ? WHERE CustomerCode = ? AND ID# = ?");
ps2 = con.prepareStatement("INSERT INTO LOCATIONMASTER (CustomerCode, SiteID, Name, Address1, Address2, City, StateProvince, PostalCode, Country, Comments) VALUES( ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )");
ps3 = con.prepareStatement("INSERT INTO PROJECTMASTER (CustomerCode, ID#, Description, MarketingUnit, Type, Name, Address1, Address2, City, StateProvince, PostalCode, Country, ProjectCode, CompletionDate, StatusFlag)" +
   "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
ps4 = con.prepareStatement("UPDATE LOCATIONMASTER SET Customercode = ?, SiteID = ?, Name = ?, Address1 = ?, Address2 = ?, City = ?, StateProvince = ?, PostalCode = ?, Country = ?, Comments = ? WHERE CustomerCode = ? AND SiteID = ?");
ps5 = con.prepareStatement("INSERT INTO ORDERMASTER (OrderNumber,OrderType,OrderCode,CustomerCode,OperationalDivision,MarketingUnit,ProjectID,ShipToName,ShipToAddr1,ShipToAddr2,ShipToCity,ShipToState,ShipToPostal,ShipToCountry,OrderAssigned,SalesRelease,AnalystRelease,MaterialPurchased,ReleasedToOpDiv,DistributionDate,PreliminaryDistFlag,PreliminaryDist,ProductionSchedule,HoldCancelFlag,SchedShip,ActualShip,BackorderFlag,Packaging,Freight,ThirdPartyAR,Routing,ProgressPymtFlag,Terms,PurchaseOrder,InvoiceWithOrder,InvoiceFreightFlag,InvoiceErrorEdi tFlag,BillToAR,StateTax,CountyTax,LocalTax,CityTax,SpecialTax,County,SalesmanNumber,AccountSpec,DateInvoiced,DeleteCode,LastRefUsed,LastBillRef,PurchItemsFlag,ProductionComplete,HardwareTicketsFlag,WarehouseTicketsFlag,InspectionTicketsFlag,GlassTicketsFlag,Pieces,Weight,SchedValue,HoursType,LastUserChgHours,SVTypeChgFromEst,OrigProdDate,WoodStdHours,MetalStdHours,Dept2With,Dept2Without,Dept3With,Dept3Without,Dept4With,Dept4Without,Dept5With,Dept5Without,Dept6With,Dept6Without,Dept7With,Dept7Wit hout,Dept9With,Dept9Without,TotalWoodHrsWO,TotalMetalHrsWO,QCICode,OrderDesc) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
ps6 = con.prepareStatement("SELECT INWCCD FROM INCUST WHERE INWCNM = ?");
ps7 = con.prepareStatement("SELECT * FROM PROJECTMASTER WHERE CUSTOMERCODE = ? AND ID# = ?");
}
catch(ClassNotFoundException cnfe) {
JOptionPane.showMessageDialog(null, "SQLConnectionE01-" + cnfe, "Error!", JOptionPane.ERROR_MESSAGE);
}
catch(SQLException sqle) {
JOptionPane.showMessageDialog(null, "SQLConnectionE02-" + sqle, "Error!", JOptionPane.ERROR_MESSAGE);
}
}
public Connection getCon() {
return con;
}
public Statement getStatement() {
return stmt;
}
public PreparedStatement getPs1() {
return ps1;
}
public PreparedStatement getPs2() {
return ps2;
}
public PreparedStatement getPs3() {
return ps3;
}
public PreparedStatement getPs4() {
return ps4;
}
public PreparedStatement getPs5() {
return ps5;
}
public PreparedStatement getPs6() {
return ps6;
}
public PreparedStatement getPs7() {
return ps7;
}
// Method to close the database connection
public void closeConnection() {
try {
if(stmt != null) stmt.close();
if(con != null) con.close();
}
catch(SQLException sqle) {
JOptionPane.showMessageDialog(null, "SQLConnectionE03-" + sqle, "Error!", JOptionPane.ERROR_MESSAGE);
}
}
}  

Does that help?
Thanks again!

[Blake Minghelli]

Argh.... for some reason I can't see your initial post now. Can you repost the original offending method that was causing the exception and specifically which line? I don't see anything wrong with your SQLConnection class.