or as you say we could use a TIBCO adapter which talks to Oracle database, and handles all the connection pooling for us. There are many ways of doing this, but arent we making things more complex by making use of adapter, rather using a class or finally block. Putting code in finally block is not code redundancy but this is to check that the connection is not open, if open then close it and let the gc do the rest.
vikram. -----Original Message----- From: A mailing list for discussion about Sun Microsystem's Java Servlet API Technology. [mailto:[EMAIL PROTECTED]]On Behalf Of Manavendra Gupta Sent: Tuesday, February 04, 2003 7:23 PM To: [EMAIL PROTECTED] Subject: Re: 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
