connection object reuse

[Lidia Cyc]

i have a question about the right architecture.
i have an application which uses connection pool. there are different methods at my "model" tier which has to work with db: f.e. a method which makes update and method which returns some values taken from resultset.
i know that in many cases i'll have to use these "update" and "select" methods one right after another, and in the future may be even a number of such methods together . the question is:
should each of these methods take a connection from the connection pool inside the method body (which will take 2 or more connections from the pool but keep the mvc architecture clean), or should they recieve a connection as a value passed from outside and just use different statements (which will use only one connection from the pool, but will move the call to the pool to the view tier) ?
thanks

[Nagendra Prasad]

There are a couple of situations you need to consider-
1. what is the autocommit settings on the connection?
if this is set to false
This will influence if u need to send the connection object as a parameter.
i.e of the 'select' needs to read what the 'update' as done, then they
should either have been done using the same connection or the 'select'
should be reading committed data.
2. from a puristic viewpoint
it is practise to set commit to true. therefore each transaction with the
database must be granular and complete in itself. from this viewpoint, it
is best to obtain a new connection for each 'sql' activity.
hope this helps.

[Lidia Cyc]

thanks. well, autocommit settings can be changed. but as i understand you'd pick the second possibility.

[Ryan Bailey]

Hi Cyc,
I would also go with the second option. It is the container's responsibility to manage the pool. I would leave it up to the container. I believe it would be just as efficient to use the container as it would be to pass the connection object to different SQL statements depending on how you had your container configured.
Just my $.02
Regards,
Ryan