Hi Roman

Thanks for the answer.

Roman Rokytskyy wrote:

> Mark,
>
> > In FBConnection active statements are held in a HashSet.
> >
> > I was wondering if anyone had thought of changing this to a
> > WeakHashMap?
>
> Short answer would be - "no". Long answer will follow.
>

Short response, would be why not change? :-). long answer to also
follow :-).

> According to specs if somebody forgets to call stmt.close(), it will
> be called automatically when connection is closed. There's nothing
> about garbage collection.
>
> > Not that one would admit to using such poor coding style as to
> > forget to add the close() :-). But in case one did you would get
> > a :
> >
> > Exception in thread "main" java.lang.RuntimeException: Unable to
> > close BLOB input stream."
> >
> > or similar message, which is not very clear.
>
> Well... this is a bug, and it should be fixed in driver and not
> worked around by not closing the statements.
>

I did think that it might be nice to have something that would explictly
display what resources jayBird did have allocated. The error eventually
came from firebird server failed to allocate resources, but jaybird (or
the server) didn't have an easy way to indicate it already held lots of
stmt resources.

> > From there it took a fair amount of fiddling, eventually arriving
> > at the (old) Heap Analysis Tool,
> >
> > http://java.sun.com/people/billf/heap/
> >
> > (I had to hack the code to get it to load profiles from jdk1.4.1)
> > but then when I realised I had well over 20,000 FBCallableStatment
> > instances, which then pointed to the correct source of the error.
>
> What about closing the connection?
>

Well that worked, but it took a long time to figure out that was the
problem, abstraction and layering made it fairly dificult to precicely
pin down what resource was not being released. (everyting was being
released according to automatic java rules - except that FBStatement
needed an explicit close() rather than having an automatic close on
loosing scope - which is what i think should be considered).

>
> No, otherwise it makes no sense to track statements at all. Each open
> statement handle eats some resources on server (probably there's even
> a limit on max. number of open statements). So, forgetting to call
> stmt.close() is a bug in application, same as memory leaks.
>

Well yes, I agree sort off, it's nice to explicitly know about open
statements, but when it's this reference that is the only link that
holds them open, wouldn't it be nice to then close them.

It would be nice if when they went out of scope these stmts cleaned
themselves up and I think weak references would help do that.

loosing scope and automatically disposing is meant to be an advantage of
java I might add :-).

And assuming most programmers are stupid (as is the general case - and
certainly in my case) why not handle these as weak references. Is there
real downside to doing it this way, and to using weak references - as it
would avoid that class of error completely?

> To solve this issue at work I have created a dynamic proxy for
> Connection and PreparedStatement that track statement creation and
> closing and print list of not closed statements with their allocation
> point on connection.commit(), connection.rollback() and
> connection.close(). This works perfectly.
>

I understand a layer over the top may help.

But my motivation is more because just using the standard jdbc and my
own layer the problem was hard to diagnose. And that I got caught, in
what I cosndier a fairly simple problem in a fairly simple case, one
server with one connection looping arround doing a similar prepared stmt
lots of times.

In my case (to help explain why I get such a simple error) it's hidden
within an instance of an Iterator,object which didn't explicitly call
the .close() object when scope was lost, and that depended upon the
whim of the user of reading until the end of the result set.

But I can see this occuring in a program if say Exceptions occur
occasionally, that then forget to call close(), and it will only be
after running for 4 days the program will crash after gradually slowing
down (since they end up having 20,000 open stmts to the server).


So to make it easier for the novice user, is there any inherent problem
with having connection.activeStatements as a weak references, then even
of the users is not real bright, they will work ok, even if they do not
explicitly have to close them.

(BTW: this generate code worked fine with MSSQL, Oracle 817, and the
JDBCODBC, so Im assuming they work this way)


BTW: Sorry to be a bit repedative and otherwise faulted, this answer is
produced while under the influence :-).


Cheers (in the best sense of the word :-)


Mark




slightly off topic:

Im very much reminded of a similar problem where the MS java runtime,
which would hang if you tried to read three http:// files in an applet
using java.util.httpURLConnection. It would hang indefinately unless
you called System.gc(), after each read, which then happened to close
the first two socket connections. (MS JAVA you see was being so
standards compilant that it would only allow a maximum of two concurrent
connections to a remote server at one time, and it kept any previous
ones open in case you wanted the file again, so it required a
System.gc() after a connection, to close the connection and allow you to
get another file, otherwise the JVM just sat there waiting forever for
the JVM to release one of the other two connections).