Re: AS/400 JDBC Connection

Thanks all for the responses.

Connection pooling seems to be the way to go.  Now... let me see if I can 
fill that pool with water.

Ron Power
Programmer
Information Services
City Of St. John's, NL
P.O. Box 908
St. John's, NL
A1C 5M2
709-576-8132
rpower@xxxxxxxxxx
http://www.stjohns.ca/
___________________________________________________________________________
Success is going from failure to failure without a loss of enthusiasm. - 
Sir Winston Churchill




"Joe Sam Shirah" <jshirah@xxxxxxxxxxxxx> 
Sent by: java400-l-bounces@xxxxxxxxxxxx
2006/03/24 02:42 PM
Please respond to
Java Programming on and around the iSeries / AS400 
<java400-l@xxxxxxxxxxxx>


To
"Java Programming on and around the iSeries / AS400" 
<java400-l@xxxxxxxxxxxx>
cc

Subject
Re: AS/400 JDBC Connection







    Hi Bruce,

    If I understand your response correctly, that is, using one connection
for all instances of a servlet, you could run into some serious problems.

    The primary one is that commit() and rollback() are Connection 
methods.
So, even if only two users share the connection, an unexpected commit or
rollback could occur.

    The other thing I can think of off the top of my head is that while
compliant drivers are required to handle multiple Statements on a single
Connection, some do so by handling them sequentially, causing really bad
response time.  I believe the JTOpen driver does it properly but you 
should
always be  careful.

    To Ron:  The others are correct that you should use a DataSource and
pooling in a multithreaded context.  Some would say use them all the time.
Normally I would point you to some articles, but I'm in a rush today. It's
well worth your time to google, read and understandsome articles on the
benefits of DataSources and pooling.


                                                         Joe Sam

Joe Sam Shirah -        http://www.conceptgo.com
conceptGO       -        Consulting/Development/Outsourcing
Java Filter Forum:       http://www.ibm.com/developerworks/java/
Just the JDBC FAQs: http://www.jguru.com/faq/JDBC
Going International?    http://www.jguru.com/faq/I18N
Que Java400?            http://www.jguru.com/faq/Java400


----- Original Message ----- 
From: "Bruce Jin" <brucej@xxxxxxxxxxxxxxxxxxxx>
To: "Java Programming on and around the iSeries / AS400"
<java400-l@xxxxxxxxxxxx>
Sent: Friday, March 24, 2006 10:34 AM
Subject: Re: AS/400 JDBC Connection


> If you only have a few servlets and the traffic is not that heavy you
> can open/close connection in your servlet. However you should open
> connection in init method and close it in destroy method so that they
> are done only once in the life time of the servlet. Make sure the method
> signatures are right so that they really override the ones in super 
class.
>
> If you have many servlets and have heavy traffic it is a good idea to
> setup a connection pool.
>
>
> RPower@xxxxxxxxxx wrote:
> > I'm trying to figure out if I should be creating and closing my
> > connections to the JDBC each time a servlet is called or should I just
> > open it once and leave it open?  ie:
> >         public void performTask(HttpServletRequest req,
> > HttpServletResponse resp) {
> >                 sess = req.getSession(true);
> >                 customer = (Customer) sess.getAttribute("customer");
> >                 createAuditTrail = false;
> >                 try {
> >                         //!---New code put in Jun 2, 2005
> >                         if (ds == null)
> >                                 getDS();
> >                         conn = ds.getConnection();
> >                         dmd = conn.getMetaData();
> >
> > code here.....
> >
> >
> >                 conn.close();
> >
> > is this efficient or am I wasting valuable resources?  Thanks,
> >
> > Ron Power
> > Programmer
> > Information Services
> > City Of St. John's, NL
> > P.O. Box 908
> > St. John's, NL
> > A1C 5M2
> > 709-576-8132
> > rpower@xxxxxxxxxx
> > http://www.stjohns.ca/
> >