Re: JDBC-Oracle connections not disconnecting

[Dr. Dragomir D. Dimitrijevic]

It looks like a hot topic that tackles many fellow Javans so here
are my thauts for 2 cents:

I had to parse a file line by line, preprocess data and insert
some 200000 records in an Oracle db. It worked like:

conn = connect();
while ( notenough ) {
    // parse a line
    // preprocess data
    // insert a record
}
conn.close();

So I get an exception that too many cursors were opened. I
checked carefully and everything was closed properly.

I ended up with:

while ( notenough ) {
   conn = connect();
    // parse a line
    // preprocess data
    // insert a record
    conn.close();
}

Here is the kicker. I made experiments and in the first code added
artificial fixed delay in the loop. It worked. It makes me believe
that rs.close() does not mean "really close". It's more
likelly "marked to close" and the garbage collection will really
close resultset or statement. Since, due a large number of accesses to db (insert or 
update),
JVM did not get a chance to do cleanup and the db went nuts. Since
I prototype in MS-Access and deliver in Oracle, MS-Access goes
bannanas even earlier.

Cheers

Dragomir



----------
From:   Chris Pratt[SMTP:[EMAIL PROTECTED]]
Sent:   Friday, November 12, 1999 07:33 AM
To:     [EMAIL PROTECTED]
Subject:        Re: JDBC-Oracle connections not disconnecting

Make sure you close every ResultSet, every Statement and every
PreparedStatement after your done with it.  It's a very good idea to do this
in a finally block to make sure it always happens, something like this works
well and in the newer VM's isn't too big of a performance hit:

Connection con = ConnectionPool.get();
try {
  Statement stm = con.createStatement ();
  try {
    ResultSet res = stm.executeQuery("select * from users where
username='Mark'");
    try {
      while(res.next()) {
        // Do Something with the data
      }
    } finally {
      res.close();
    }
  } finally {
    stm.close();
  }
} finally {
  ConnectionPool.release(con);
}

    (*Chris*)

----- Original Message -----
From: Mark Foley <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, November 11, 1999 4:01 PM
Subject: JDBC-Oracle connections not disconnecting


> Hi All!
>
> Again a problem!  We are using Oracle thin JDBC drivers to talk to Oracle
> 8.0.5.0 on Solaris using a dynamic connection pool.  We are running out of
> connections (limit = 100) even though we have no more than 2 or 3 people
> using the servlet at any time, and we are being careful to shrink the pool
> when fewer users are 'logged in'.
>
> We have traced the connection pool and logged the connects and disconnects
> to a file, and there are never more than 5 connections open at the same
> time.  Our DBA tells us each time Oracle runs out of connections there are
> 100 connections registered to 'JDBC'.   Rebooting the servlet server
doesn't
> help either.
>
> We are using IBM WebSphere 2.02 with MS IIS 4 on WinNT 4 SP5, but have
kept
> our code generic for use (if necessary) on other platforms.
>
> Any help would be most appreciated - this is a reall worry (and in the
land
> of "No Worries!" that's not good).
>
> Many thanks,
>
> Mark Foley
> EDS (Australia)
> Tel: +61-2-6275 6494
> e-mail1: [EMAIL PROTECTED]
> e-mail2: [EMAIL PROTECTED]
>
>
___________________________________________________________________________
> To unsubscribe, send email to [EMAIL PROTECTED] and include in the
body
> of the message "signoff SERVLET-INTEREST".
>
> Archives: http://archives.java.sun.com/archives/servlet-interest.html
> Resources: http://java.sun.com/products/servlet/external-resources.html
> LISTSERV Help: http://www.lsoft.com/manuals/user/user.html
>

___________________________________________________________________________
To unsubscribe, send email to [EMAIL PROTECTED] and include in the body
of the message "signoff SERVLET-INTEREST".

Archives: http://archives.java.sun.com/archives/servlet-interest.html
Resources: http://java.sun.com/products/servlet/external-resources.html
LISTSERV Help: http://www.lsoft.com/manuals/user/user.html

___________________________________________________________________________
To unsubscribe, send email to [EMAIL PROTECTED] and include in the body
of the message "signoff SERVLET-INTEREST".

Archives: http://archives.java.sun.com/archives/servlet-interest.html
Resources: http://java.sun.com/products/servlet/external-resources.html
LISTSERV Help: http://www.lsoft.com/manuals/user/user.html