CODEWITHSUNDEEP
javaoracle-databasejdbctransactionssqlexception
Ricardo
Ricardo

Reputation: 169

java.sql.SQLException Closed Statement

I have a problem, the below code runs fine if I run it without the autoCommit property, however I would prefer to run it as a transaction, the code basically inserts an article's header information and then the list each articles associated with it (so it's like a one-to-many relationship), so I could like to commit everything in one go rather than first the article information and then its items. The issue is that when I reach to the cn.commit() line, I get an exception that says "Closed Statement"

database insertion method

public static void addArticle(Article article) throws SQLException {
             Connection cn = null;
             PreparedStatement ps = null;
             StringBuffer insert = new StringBuffer();
             StringBuffer itemsSQL = new StringBuffer();

             try {
                    article.setArticleSortNum(getNextArticleNum(article.getShopId()));
                    article.setArticleId(DAOHelper.getNextId("article_id_sequence"));

                    cn = DBHelper.makeConnection();
                    cn.setAutoCommit(false);

                    insert.append("insert query for article goes here");
                    ps = cn.prepareStatement(insert.toString());
                    int i = 1;
                    ps.setLong(i, article.getArticleId()); i++;
                    ps.setLong(i, article.getShopId()); i++;
                    ps.setInt(i, article.getArticleNum()); i++;
                    // etcetera...
                    ps.executeUpdate(); 

                    itemsSQL.append("insert query for each line goes here");
                    itemStatement = cn.prepareStatement(itemsSQL.toString());
                    for(Article item : article.getArticlesList()) {

                        item.setArticleId(article.getArticleId());
                        i= 1;
                        itemStatement.setLong(i, item.getArticleId()); i++;
                        itemStatement.setInt(i,  item.getItemsOnStock()); i++;
                        itemStatement.setInt(i,  item.getQuantity()); i++;
                        // etcetera...
                        itemStatement.executeUpdate();

                    }
                    cn.commit();

             } catch (SQLException e) {
                    cn.rollback();
                    log.error(e.getMessage());
                    throw e;
             }
             finally {
                    DBHelper.releasePreparedStatement(ps);
                    DBHelper.releasePreparedStatement(itemStatement);
                    DBHelper.releaseConnection(cn);
             }
       }

I also had the items insertion where the For is running with addBatch() then executeBatch but also the same Closed Statement error upon reaching cn.commit()... I dont understand why its closing, all connections and everything is released in the finally clause, so I get the feeling I'm making some fundamental error I'm not aware of... Any ideas? Thanks in advance!

EDIT: Below is the stack trace:

java.sql.SQLException: Closed Statement at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:189) at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:231) at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:294) at oracle.jdbc.driver.OracleStatement.ensureOpen(OracleStatement.java:6226) at oracle.jdbc.driver.OraclePreparedStatement.sendBatch(OraclePreparedStatement.java:592) at oracle.jdbc.driver.OracleConnection.commit(OracleConnection.java:1376) at com.evermind.sql.FilterConnection.commit(FilterConnection.java:201) at com.evermind.sql.OrionCMTConnection.commit(OrionCMTConnection.java:461) at com.evermind.sql.FilterConnection.commit(FilterConnection.java:201) at com.dao.ArticlesDAO.addArticle(ArticlesDAO.java:571) at com.action.registry.CustomBaseAction.execute(CustomBaseAction.java:57) at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:431) at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:236) at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1196) at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:432) at javax.servlet.http.HttpServlet.service(HttpServlet.java:760) at javax.servlet.http.HttpServlet.service(HttpServlet.java:853) at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:765) at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:317) at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:790) at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:270) at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:112) at com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:192) at java.lang.Thread.run(Unknown Source)

EDIT 2:

These are the parameters in the driver's datasource config, I thought the debugging process might be making it time out, but even finishing in less than a second throws the closed statement exception

min-connections="20" 
max-connections="200" 
inactivity-timeout="20" 
stmt-cache-size="40"/>

Upvotes: 3

Views: 20232

Answers (1)

Nathan Hughes
Nathan Hughes

Reputation: 96444

It's usually best to create a statement, use it and close it as soon as possible, and it does no harm to do so before the transaction gets committed. From reading the Oracle tuturial about the batch model it's sounding like it could be a problem to have multiple statements open at one time. I would try closing the ps object before working with the itemStatement, then moving the initialization

itemStatement = cn.prepareStatement(itemsSQL.toString());

to directly above the for loop, and also move where you close the itemStatement to immediately after the for loop:

PreparedStatement itemStatement = cn.prepareStatement(itemsSQL.toString());
try {
    for(Article item : article.getArticlesList()) {
       item.setArticleId(article.getArticleId());
        i= 1;
        itemStatement.setLong(i, item.getArticleId()); i++;
        itemStatement.setInt(i,  item.getItemsOnStock()); i++;
        itemStatement.setInt(i,  item.getQuantity()); i++;
                    // etcetera...
        itemStatement.executeUpdate();
    }
} finally {
    DBHelper.releasePreparedStatement(itemStatement);
}

It looks like what is going on is you have some batching parameter set on the connection that is causing the connection to try to look for unfinished business in the statement to finish up; it's finding the statement is already closed and the connection is complaining about it. This is weird because at the point the commit blows up on you the code hasn't reached the finally where the statement gets closed.

Reading up on Oracle batching models may be helpful. Also check the JDBC driver version and make sure it's right for the version of Oracle you're using, and see if there are any updates available for it.

Upvotes: 1

Related Questions