Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
HomeAnnouncementsWhite Papers
Discussion GroupsFirst AidDatabasesJavaBeansGUIJava 3DVirtual MachineCORBASecurityToolsGeneral
Java DirectoryOpen Source ProjectsSample Book ChaptersUser GroupsWeb Resources
Related Topics
Databases.NETMore Topics ...

Java Forum / Databases / August 2004

Tip: Looking for answers? Try searching our database.

java.sql.sqlException closed statement

Thread view: 
ramesh - 06 Jul 2004 17:56 GMT
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]
>       }
>     }


Free Magazines

Get these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...

Oracle MagazineNetwork ComputingComputer WorldBio-IT WorldeWeekInformation WeekInfosecurity
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.