Re: JDBC techy pooling questions

Both the statement and the resultset returned should be closed after each query.

--Sanjay

Kevin McMurtrie wrote:

> I have some questions about pooling JDBC connections with the Oracle
> thin driver. I'm fairly new to JDBC and I'm not familiar with its
> performance details. My code will be running on a high traffic server
> with little maintenance. Unfortunately I don't have access to the
> database to monitor it's load.
>
> I have a wrapper class in my pooling that keeps a Connection instance
> and a Statement instance together. I am calling
> Connection.createStatement() once then reusing the Statement. Is it OK
> to keep reusing one instance of a Statement?
>
> I am disabling auto-commit as recommended by Oracle. Some queries make
> changes and perform a commit or rollback, but it's possible to execute
> several thousand read-only queries on various tables without commiting,
> closing a statement, or closing a connection. Docs imply that
> auto-commit is for all statements. With auto-commit off, should I call
> commit() after a batch of read-only queries?
>
> Oracle docs warn that failing to close a connection can cause a
> permanent resource leak. Is this true in practice? The JDBC docs
> specify that a Connection will be garbage collected. My code can loose
> its reference to a Connection instance if an unexpected exception is
> thrown, such as a RuntimeException or Error.
>
> What part of JDBC holds a cursor? Is is the Connection, the Statement,
> or just the execution of a query? Quick & dirty Perl code from someone
> else's project is eating up all the cursors. I'd like to be somewhat
> immune to temporary shortages while at the same time not being too much
> of a hog myself.
>
> I have noticed that it's possible for a query to hang forever on
> Socket.read(). Can I set the Oracle driver to use a timeout? I cache
> and share data so a stuck query will block any thread asking for the
> same stuck item.
>
> Thanks!
>
> - Kevin McMurtrie
Received on Sat May 06 2000 - 00:00:00 CEST