Sorry for jumping in here without having really looked at the DBTags, but
I just thought I could offer what I believe is a different point of view
on how to deal with connections.
In the DB tags I developed for my JSP book, the tags that execute SQL
get a connection from a JDBC DataSource (that implements a connection
pool) available in the application scope, use it, and calls close()
(thereby returning it to the pool), all in the doEndTag() method (in the
default case). The DataSource is ideally placed in the application scope
by a servlet that's loaded at startup, but for a pure JSP application,
there's a useDataSource tag that can be used instead:
<ora:useDataSource id="example"
className="sun.jdbc.odbc.JdbcOdbcDriver"
url="jdbc:odbc:example"
/>
<ora:sqlUpdate>
UPDATE Account SET Balance = Balance - 1000
WHERE AccountNumber = 1234
</ora:sqlUpdate>
If multiple SQL statements must be executed through the same connection,
i.e. forming a transaction, a special transaction element is used
to enclose multiple DB tags. In this case the transaction tag gets
the connection from the DataSource and the nested DB tags get the
connection from the transaction tag. In this case they do *not* close
the connection; it's closed by the transaction's doEndTag() instead.
<ora:sqlTransaction dataSource="example">
<ora:sqlUpdate>
UPDATE Account SET Balance = Balance - 1000
WHERE AccountNumber = 1234
</ora:sqlUpdate>
<ora:sqlUpdate>
UPDATE Account SET Balance = Balance + 1000
WHERE AccountNumber = 5678
</ora:sqlUpdate>
</ora:sqlTransaction>
The DB tags catch exceptions to make sure the connection is always
closed. When a DB tag is nested within a transaction, it rollbacks the
transaction and close the connection in case of an exception, and then
rethrows the exception.
This approach seems to work in most cases, but it's not immune
to exceptions thrown by scripting code within the body. For
that, the JSP 1.2 TryCatchFinally is needed.
If you believe you can pick up some ideas from my design, feel free
to do so. All source code is available as part of the examples
bundle at <http://TheJSPBook.com/>
Hans
--
Hans Bergsten [EMAIL PROTECTED]
Gefion Software http://www.gefionsoftware.com
Author of JavaServer Pages (O'Reilly), http://TheJSPBook.com
