Re: odd oracle error [MAX cursors]

That makes sense. There have been discussions on the list that a particular
'design pattern' be used to ensure connections are closed everywhere.
----- Original Message -----
From: "Chris Pratt" <[email protected]>
To: <[email protected]>
Sent: Tuesday, February 04, 2003 8:07 PM
Subject: Re: odd oracle error [MAX cursors]


> I normally centralize all data access in the Data Model using an MVC
> architecture.  I also like to add an extra layer of abstraction and
usually
> use a pluggable data model to allow flexibility.
>   (*Chris*)
>
> ----- Original Message -----
> From: "Manavendra Gupta" <[email protected]>
> To: <[email protected]>
> Sent: Wednesday, February 05, 2003 9:09 AM
> Subject: Re: [SERVLET-INTEREST] odd oracle error [MAX cursors]
>
>
> > Would you recommend using this pattern across the entire code base where
> > there are several entities (or java classes) interact with the
persistence
> > layer? Would you recommend a copy and paste of this code?
> >
> > When I set out to suggest that design strategy, I did not go against
this
> > accepted pattern - use this, by all means, but also, to reduce the copy
> > paste of the code, and the possibilty of forgetting something somewhere,
I
> > wanted a strategy that would have all this code centralized.
> >
> > ~Manav.
> > ----- Original Message -----
> > From: "Chris Pratt" <[email protected]>
> > To: <[email protected]>
> > Sent: Tuesday, February 04, 2003 5:35 PM
> > Subject: Re: odd oracle error [MAX cursors]
> >
> >
> > > I have used this pattern over and over with all manner of JDBC drivers
> and
> > > have not found one that it does not work with.  There were issues with
> > > performance in the initial 1.1 JVM's since try/catch blocks had a
fairly
> > > severe overhead, but with the newer 1.2+ JVM's it's negligible.  I do
> > > recommend a Connection Pool for actually handling the Connections as
> > shown:
> > >
> > > try {
> > >   Connection con = DbConnectionPool.getConnection();
> > >   try {
> > >     PreparedStatement stm = con.prepareStatement("SELECT SOMETHING
FROM
> > > SOMEWHERE");
> > >     try {
> > >       stm.setString(1,"Someone");
> > >       ResultSet res = stm.executeQuery();
> > >       try {
> > >         while(res.hasNext()) {
> > >           // Do something with the Result Set values
> > >         }
> > >       } finally {
> > >         res.close();
> > >       }
> > >     } finally {
> > >       stm.close();
> > >     }
> > >   } finally {
> > >     DbConnectionPool.releaseConnection(con);
> > >   }
> > > } catch(SQLException x) {
> > >   x.printStackTrace();
> > > }
> > >
> > > There is no way to strand any database resources with this model.
> > >   (*Chris*)
> > >
> > >
> > > ----- Original Message -----
> > > From: "Manavendra Gupta" <[email protected]>
> > > To: <[email protected]>
> > > Sent: Tuesday, February 04, 2003 5:53 AM
> > > Subject: Re: [SERVLET-INTEREST] odd oracle error [MAX cursors]
> > >
> > >
> > > > I'd like to re-open this thread for discussion.
> > > >
> > > > IMO, following is what has been proposed so far:
> > > >
> > > > 1. Use connection pooling.
> > > > 2. Use a singleton class as connection manager
> > > > OR (if your app server supports it)
> > > > 1. Configure JNDI datasource
> > > > 2. Configure connection pool manager
> > > >
> > > > There have been repeated discussions on the best way to close
> connection
> > > to ensure all resources are freed up properly. There is also that
issue
> > with
> > > Oracle implicit cursors not being closed unless you explicitly close
the
> > > statement.
> > > >
> > > > Instead of 'making sure' to close connection in the finally block
> (which
> > I
> > > believe is a repitition of code anyway), can we not have a design
> strategy
> > > that does this automatically for us? Here's what I suggest (apart from
> the
> > > steps listed above):
> > > > 1. Create a 'gateway' or 'adapter' class that interfaces with the
> > > connection manager (read: calls the getConnection(), freeConnection()
> and
> > > other DB interaction methods) with member variables for statement and
> > > resultset.
> > > > 2. This 'gateway' or 'adapter' exposes methods to perform generic
> > database
> > > methods (select, insert, update, delete) - you'd as it always use a
> > standard
> > > gateway for all your entities to use instead of all entities talking
to
> > the
> > > database, thereby splattering around the persistence code all over the
> > > application.
> > > > 3. The destructor of this gateway closes the statement as well as
the
> > > resultset.
> > > >
> > > > So, you have a domain model (or table gateway, row gateway or
whatever
> > > object-relational mapping you chose), your database code is at one
> single
> > > place, your connections are being managed and best, you don't have to
> > write
> > > a single additional line to ensure you don't exceed the MAX_CURSORS
> > (unless
> > > of course your methods take too long to complete and the load on your
> > > application is extremely high - in which case you'd have to increase
the
> > > cursors anyway, no matter what you choose).
> > > >
> > > > One of the pitfalls I see of this design is higher object
> > > creation/destruction, but your statements/resultsets had to be closed
> > anyway
> > > so you just have 4 additional bytes being used on the stack.
> > > >
> > > > Comments?
> > > >
> > > > Cheers,
> > > > Manav.
> > > >
> > > >
> > >
> >
>
___________________________________________________________________________
> > > > 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
> >
>
>
___________________________________________________________________________
> 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