Iam getting java.sql.SQLException: Closed Statement in the following code:
CallableStatement cs = null;
long noteID = -1;
try
{
if (noteText != null)
{
cs = mCreateTaskNoteCall.getCall();
cs.setLong (1, taskID); // p_task_id
cs.setString(2, noteText); // p_note_text
cs.registerOutParameter(3, java.sql.Types.NUMERIC); // x_note_id
cs.registerOutParameter(4, java.sql.Types.VARCHAR); // x_return_status
cs.registerOutParameter(5, java.sql.Types.NUMERIC); // x_msg_count
cs.registerOutParameter(6, java.sql.Types.VARCHAR); // x_msg_data
cs.execute();
mCreateTaskNoteCall.checkAPIReturn(
"csr_scheduler_pvt.create_task_note"
, cs.getString(4) // x_return_status
, cs.getInt (5) // x_msg_count
, cs.getString(6) // x_msg_data
);
noteID = cs.getLong(3);
}
}
catch(APIException apiex)
{
throw new SchedulerException( SchedulerException.ERROR_API_ERROR
, apiex.getAPIName()
, apiex.getReturnStatus()
, apiex.getMsgCount()
, apiex.getMsgData()
, new MessageToken("TASK", new Long(taskID))
, mTbx.getConnection()
);
}
catch(SQLException sqlex)
{
throw new SchedulerException(sqlex, SchedulerException.UNSPECIFIED_ERROR);
}
finally
{
if (cs != null)
{
try
{
cs.close();
}
catch(SQLException x){}
}
}
David Harper - 06 Jul 2004 20:51 GMT
> Iam getting java.sql.SQLException: Closed Statement in the following
> code:
[SNIP]
Hi Ramesh,
It would help us a *lot* if you could tell us
* which is the line where the exception is being thrown?
* does it happen *every* time you run the code, or only intermittently?
* if it's intermittent, is there any kind of pattern?
* did you check that the return value from mCreateTaskNoteCall.getCall()
is a valid CallableStatement object associated with a real Connection
object?
As it stands, your query doesn't give us enough information to help you.
David Harper
Cambridge, England
ramesh - 07 Jul 2004 06:54 GMT
> > Iam getting java.sql.SQLException: Closed Statement in the following
> > code:
[quoted text clipped - 5 lines]
>
> * which is the line where the exception is being thrown?
The call cs.execute() is throwing the java.sql.SQLException Closed Statement.
> * does it happen *every* time you run the code, or only intermittently?
It happens every time.
> * if it's intermittent, is there any kind of pattern?
>
> * did you check that the return value from mCreateTaskNoteCall.getCall()
> is a valid CallableStatement object associated with a real Connection
> object?
Yes
> As it stands, your query doesn't give us enough information to help you.
>
> David Harper
> Cambridge, England
David Harper - 07 Jul 2004 22:31 GMT
Hi Ramesh,
Thanks for those clues.
You may want to try testing the boolean value of
cs.getConnection().isClosed()
after each of the Java statements involving cs, to see where the
connection is being lost i.e.
cs = mCreateTaskNoteCall.getCall();
Connection conn = cs.getConnection();
if (conn == null)
System.err.println("conn is null after getCall");
if (conn.isClosed())
System.err.println("conn is closed after getCall");
cs.setLong (1, taskID);
if (conn.isClosed())
System.err.println("conn is closed after setLong #1");
cs.setString(2, noteText);
if (conn.isClosed())
System.err.println("conn is closed after setString #2");
and so forth.
I guess it depends on your JDBC driver whether any of the calls
before cs.execute() actually involve network traffic with the
server, but it may help to narrow down where the connection is
being dropped by the server.
What type of database server is your code talking to, anyway?
Hope this helps.
David Harper
Cambridge, England
sunilthumma - 11 Aug 2004 16:27 GMT
sunilthumma - 11 Aug 2004 16:24 GMT
Hi,
I am getting this Closed Statement exception intermittently.
I am using prepared statement for multiple inserts in a loop.
Using Weblogic 7.4 App server & Oracle 9i db.
Here is the sample code i am using
Connection con = null;
PreparedStatement ps = null;
int result = 0;
try {
con = getConnection();
con.setAutoCommit(false);
ps = con.prepareStatement("INSERT INTO
AUDIT(USER_ID,FIRST_NAME,LAST_NAME,CODE) VALUES(?,?,?,?)");
if(eCodes != null) {
for(int i=0;i < eCodes.length;i++) {
try {
ps.setString(1,user.getUserId());
ps.setString(2,user.getFirstName());
ps.setString(3,user.getLastName());
ps.setString(5,eCodes[i].getCode());
result = executeUpdate(ps);
} catch(SQLException e) {
// here the Closed Statement exception is coming
logger.error("[logUserActivity] error : "+ e.toString());
} finally {
// if (ps != null)
// closeStatement(ps);
}
}
con.commit();
}
} catch(SQLException e) {
logger.error("[logUserActivity] error : "+ e.toString());
} finally {
if (ps != null)
closeStatement(ps);
if (con != null)
closeConnection(con);
}
Am I doing any mistake here ?
Any help will be appreciated !!
Thanks
-Sunil
Robert Klemme - 12 Aug 2004 09:01 GMT
> Hi,
> I am getting this Closed Statement exception intermittently.
[quoted text clipped - 3 lines]
>
> Here is the sample code i am using
Remove the finally block in the loop. You must not close the PS as long
as you use it. I prefer a proper nesting of try finally for such
scenarios:
if ( eCodes == null ) return;
Connection con = getConnection();
try {
PreparedStatement ps = con.prepareStatement( "INSERT INTO
AUDIT(USER_ID,FIRST_NAME,LAST_NAME,CODE) VALUES(?,?,?,?)" );
try {
for ( int i = 0; i < eCodes.length; i++ ) {
try {
ps.setString( 1, user.getUserId() );
ps.setString( 2, user.getFirstName() );
ps.setString( 3, user.getLastName() );
ps.setString( 5, eCodes[i].getCode() );
result = executeUpdate( ps );
} catch ( SQLException e ) {
// here the Closed Statement exception is coming
logger.error( "[logUserActivity] error : " +
e.toString() );
}
}
con.commit();
}
finally {
closeStatement( ps );
}
}
finally {
closeConnection(con);
}
Regards
robert
Joe Weinstein - 07 Jul 2004 05:39 GMT
> Iam getting java.sql.SQLException: Closed Statement
You're not showing enough code. Can you guarantee that no other thread
either got and closed the statement, or closed the connection that generated
the statement?
Joe Weinstein at BEA
in the following code:
> CallableStatement cs = null;
> long noteID = -1;
[quoted text clipped - 51 lines]
> }
> }