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