• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQLException: The connection was closed

 
Michael Schwartz
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Has anybody encountered the message on the subject line?

Details:
We are upgrading from Oracle 9iAS to 10gAS. The 9iAS jdbc drivers were very forgiving, it allowed us to keep connections open that had no business being open. 10gAS forced us to explicitly close our connections. I've managed to do 99.9% of them. Except, when I perform this one workflow, if it's big enough, I get the error.

I appretiate any help you guys give. Thanks.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Michael,
Welcome to JavaRanch!

If the code is written correctly, it could be a connection timeout. Can you post the code that is failing so we can see if everything is closed correctly? In particular, the connection should not be closed before the statement and the statement should not be closed before the resultset.
 
Michael Schwartz
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Jeanne. It's a pleasure to be here... I included the exception and the code with markers to the exception. I think this is more of a mem leak issue than anything else only because it happens when the data set is large. I'm open to the idea that it's a performance thing as well.

I had to explicitly close and null the rs/stmt/connections and I thought that would have helped. I realize that it's a bit of a mess but you can't always control what you inherit :-) Thanks again!


EXCEPTION **********************************
06/05/30 10:52:02 java.sql.SQLException: The connection was closed
06/05/30 10:52:02 at com.evermind.sql.OrionCMTConnection.intercept(OrionCMTConnection.java:259)
06/05/30 10:52:02 at com.evermind.sql.FilterConnection.createStatement(FilterConnection.java:276)
06/05/30 10:52:02 at com.evermind.sql.ConnectionBCELProxy.createStatement(ConnectionBCELProxy.java:40)
06/05/30 10:52:02 at com.experio.app.util.DAOUtil.generateSequenceNumber(DAOUtil.java:110)
06/05/30 10:52:02 at com.experio.app.util.DAOUtil.generateSequenceNumber(DAOUtil.java:73)
06/05/30 10:52:02 at com.rpg.ncamp.app.session.fixture.FixtureControllerDAOORCL.createFixture(FixtureControllerDAOORCL.java:550)
06/05/30 10:52:02 at com.rpg.ncamp.app.session.fixture.FixtureControllerBean.createFixture(FixtureControllerBean.java:441)
06/05/30 10:52:02 at com.rpg.ncamp.app.session.fixture.FixtureControllerBean.copyFixtures(FixtureControllerBean.java:719)
06/05/30 10:52:02 at com.rpg.ncamp.app.session.fixture.FixtureControllerBean.copyFixtures(FixtureControllerBean.java:599)
06/05/30 10:52:02 at FixtureController_StatelessSessionBeanWrapper26.copyFixtures(FixtureController_StatelessSessionBeanWrapper26.java:866)
06/05/30 10:52:02 at com.rpg.ncamp.app.session.planogram.PlanogramControllerBean.resetPlanogram(PlanogramControllerBean.java:325)
06/05/30 10:52:02 at
END EXCEPTION ***********************************************************



START OF CODE ***********************************************************
Object: com.rpg.ncamp.app.session.fixture.FixtureControllerBean
Method: createFixture
STACKTRACE: 441
/**
* Changes the given fixtures' assortments. Uses the planogram data object
* to ensure that the changes will occur on a current planogram and will not
* overwrite others' changes.
*
* @param Fixture the fixture to be added
* @param Pockets the pockets that needs be added to this fixture
* @return new Fixture PK
* @throws CorruptPlanogramVersionException
* @throws CreateException indicates that the inserts didn't happen
*
*
*/
public int createFixture(PlanogramDO pdo, FixtureDO fdo, FixturePocketDO[] pockets)
throws CorruptPlanogramVersionException, CreateException
{
if (VERBOSE)
Debug.println(" &&&&&& &&&&&&& Entering FixtureControllerBean.createFixture()");

PlanogramController pog =
(PlanogramController) AppUtil.createEJB("PlanogramController");
FixturePocketController pock =
(FixturePocketController) AppUtil.createEJB("FixturePocketController");
int newFixtureId = 0;

try
{
pdo.setVersion(pog.incrementPlanogramVersion(pdo));
pog.updatePlanogramVersion(pdo);
//now set the pog's id within the Fixdo
fdo.setPlanogramId(pdo.getPlanogramId());
SimpleDateFormat formatter = new SimpleDateFormat("hh:mm:ss:SS a zzz");

if (VERBOSE)
Debug.println("A fixture was added at " +
formatter.format(new java.util.Date(System.currentTimeMillis()))
+ " to POG '" + pdo.getPlanogramId() + "' starting to add pockets");

if (VERBOSE)
Debug.println("POG " + pdo.getPlanogramId() + " will be getting a new fixture.");

fdo.setCreatedBy(pdo.getCreatedBy());
// !!!
// STACKTRACE: 441
newFixtureId = FixtureControllerDAO.getDAO().createFixture(fdo);

if (VERBOSE)
Debug.println("POG " + pdo.getPlanogramId() +
" String modified " + newFixtureId);

if (pockets != null)
{
for (int i = 0; i < pockets.length; i++)
{
//now we need to take that new id and
//place it in each of the pockets to correctly
//tie it to the fixture
pockets.setPlanogramFixtureId(newFixtureId);
pockets.setFixturePocketId(0);
/* When resetting a store this informatino needs to be identical to the Completed
when using copy Planogram it will need to blank out this information
pockets.setDsDesignCode(null);
pockets.setDsDesignStatus("");
pockets.setFeDesignCode(null);
*/
pockets.setCreatedBy(pdo.getCreatedBy());
}
}

pock.createPockets(pockets);

if (VERBOSE)
Debug.println(" &&&&&& &&&&&&& FixtureControllerBean.createFixture()");

return newFixtureId;
}
catch (Exception e)
{
throw new EJBException(e);
}
}





Object: com.rpg.ncamp.app.session.fixture.FixtureControllerDAOORCL
Method: createFixture
STACKTRACE: 550
/**
* Creates a new fixture for the planogram.
*
* @param fdo a data object representing the fixture to create
* @returns int Primary Key
*/
public int createFixture(FixtureDO fdo)
{
if (VERBOSE)
Debug.println("Entering FixtureControllerDAOORCL.createFixture()");

Connection conn = null;
Statement stmt = null;

StringBuffer sql = new StringBuffer();
int newFixtureId;
DAOUtil dao = new DAOUtil();

try
{
// !!!
// STACKTRACE: 550
newFixtureId = dao.generateSequenceNumber("NCamp", "NCAMP_POG_FXT", "POG_FXT_ID");
conn = DbUtil.getConnection("NCamp");

sql.append("INSERT INTO NCAMP_POG_FXT (POG_FXT_ID, POG_ID, " +
"FXT_NUM, ORIG_FXT_NUM, " +
"NO_ROWS, NO_COLS, X_COORDINATE, Y_COORDINATE, " +
"TEMPLATE_CODE, TEMPLATE_NAME, STYLE_ID, " +
"NBR_SCHEME_ID) values (" +
newFixtureId + ", " +
fdo.getPlanogramId() + ", " +
DAOUtil.formatStringForSQL(fdo.getFixtureNumber()) + ", " +
DAOUtil.formatStringForSQL(fdo.getOrigFixtureNumber()) + ", " +
fdo.getNumRows() + ", " +
fdo.getNumColumns() + ", " +
fdo.getXCoord() + ", " +
fdo.getYCoord() + ", " +
DAOUtil.formatStringForSQL(fdo.getTemplateCode()) + ", " +
DAOUtil.formatStringForSQL(fdo.getTemplateName()) + ", " +
fdo.getStyle().getStyleId() + ", " +
fdo.getScheme().getSchemeId() + ")");

if (VERBOSE)
Debug.println("FixtureControllerDAOORCL.createFixture(): sql="
+ sql.toString());

stmt = conn.createStatement();
stmt.executeUpdate(sql.toString());

DbUtil.close(stmt, conn);
stmt = null;
conn = null;

DAOUtil.setCreatedBy("NCamp", "NCAMP_POG_FXT", "POG_FXT_ID", newFixtureId, fdo.getCreatedBy());

DAOUtil.setModifiedBy("NCamp", "NCAMP_POG_FXT", "POG_FXT_ID", newFixtureId, fdo.getCreatedBy());

return newFixtureId;
}
catch (Exception e)
{
if (VERBOSE)
Debug.println("FixtureControllerDAOORCL.createFixture(): exception:"
+ e.toString());
e.printStackTrace();
throw new GeneralRuntimeException(e);
}
finally
{
DbUtil.close(stmt, conn);

stmt = null;
conn = null;
}
}





Object: com.experio.app.util.DAOUtil
Method: generateSequenceNumber
STACKTRACE: 73
/**
* Gets the next sequence number so that it may be used as the key for a new row.
* This method simply generates a sequence name using the table name and simply
* calls its overloaded method to do the operation.
*
* @param conn Connection The database connection to be used for SQL operation
* @param tableName String The tableName where the sequenceNumber created will be used.
* @param columnName String The columnName where the sequenceNumber will be inserted.
*
* @return int Generated Sequence Number
*
* @throws SQLException
*/
public static int generateSequenceNumber(String connName, String tableName, String columnName) throws SQLException
{
String sSeqName = "SEQ_" + tableName;

return generateSequenceNumber(connName, sSeqName, tableName, columnName);
}






Object: com.experio.app.util.DAOUtil
Method: generateSequenceNumber
STACKTRACE: 110
/**
* Gets the next sequence number so that it may be used as the key for a new row.
* After we get the sequenceNumber, a check is done to ensuere that the number
* is not used in the columnName of the table.
*
* @param conn Connection The database connection to be used for SQL operation
* @param sequenceName String The sequenceName which will be used to
* create sequenceNumber.
* @param tableName String The tableName where the sequenceNumber created
* will be used.
* @param columnName String The columnName where the sequenceNumber will
* be inserted.
*
* @return int Generated Sequence Number
*
* @throws SQLException
*
* @return The newly generated id, or -1 if there is problem getting nextval
* for the indicated sequence.
*
* @author
* @date 04/16/2002
*/
public static int generateSequenceNumber(String connName, String sequenceName,
String tableName, String columnName) throws SQLException
{
Connection conn = null;
int sequenceNumber = 0;

conn = DbUtil.getConnection(connName);

if (conn.isClosed()) System.out.println("conn is closed!");
// !!!
STACKTRACE: 110
Statement stmt = conn.createStatement();
ResultSet rs = null;
try
{
rs = stmt.executeQuery("SELECT " + sequenceName + ".NEXTVAL FROM DUAL");

if (!rs.next())
{
sequenceNumber = -1;
}
else
{
sequenceNumber = rs.getInt("NEXTVAL");
}

if (rs != null) rs.close();
if (stmt != null) stmt.close();
if (conn != null) conn.close();
}
finally
{
rs = null;
stmt = null;
conn = null;
}

return sequenceNumber;
}
END OF CODE ***************************************************
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic