Connection busy with results for another hstmt...

Hi mates,

I am getting this strange error while working on SQL server through JDBC.
I am using a connection-pooling mechanism. Have written my own connection pool. The pool holds a number of connections and has methods for requesting and returning connections from the clients. The issue is - when I get a connection from the pool and execute a select query, it works fine. Then I return the connection to the pool (at the top). On next request for connection, this same connection object is provided. When another select query is fired on this connection, I get an error - "connection is busy with results for another hstmt"... The error occurs at the time of executing the query and not while obtaining the statement object from the connection object.
If I don't use the connection pool and create new connection every time, everything works fine (except the performance!)...
I'll be grateful if anyone can shed some light on this....
Cheers!That's because each connection can only have 1 active (pending results) statement per connection.

It sounds like you are not closing/dropping the statements, or the driver is not. It might be your connection handling code. You might also try a different jdbc driver. Are you using an sql specific driver? It you use the default jdbc-odbc bridge, you can get bad behavior. That driver doen't work well. There are 3 kinds of replacements:

1) Free Jdbc drivers for SQL Server. MS provides one, and there is an open source one out there.

2) A ready for deployment JDBC-ODBC Bridge from a vendor. There are several.

3) A commercial JDBC Driver.

What you need is dependant on your budget, time and architecture.

Jay Grubb
Technical Consultant
OpenLink Software
Web: http://www.openlinksw.com:
Product Weblogs:
Virtuoso: http://www.openlinksw.com/weblogs/virtuoso
UDA: http://www.openlinksw.com/weblogs/uda
Universal Data Access & Virtual Database Technology Providers