At 09:05 AM 2/02/2010, foofoo3636 wrote:

>I have an application with a middleware that maintains a database pool of connections to firebird. I get the following error, intermittently:
>
>"The prepare statement identifies a prepare statement with an open cursor"
>
>I'm guessing that I'm not handling my transactions well somewhere before I put it back in the pool. But, I don't really know what the error means. Can anyone tell me?

It's gdscode 335544688, dsql_open_cursor_request, that will be thrown when the statement handle is still in use. "Still in use" might mean the server still has records waiting in the output buffer for the application to fetch, or it is holding the statement in a "prepared" state, waiting for new values to be applied to input parameters.

>It might help me narrow down where I'm having the transaction issue. Or, if anyone knows how I can help narrow down what piece of sql has caused this issue, that would be awesome!

It's not the statement ("piece of SQL") that is causing this problem, but an untidy exit from an operation involving a prepared statement. This will be somehow related to how your programming interface cleans up a prepared query when its transaction commits or rolls back.

>I have a ton of code that leverages this pool, so it can be really hard to track down transaction handling issues.

All statements, including SELECTs, happen inside transactions. One transaction is isolated from all other transactions. Likewise, all transactions happen in a connection context (which may be a single connection or a multi-database connection but NOT multiple connections to a single database). If you are returning a connection to a "pool" by some mechanism that doesn't take care of the transaction contexts associated with the connection (including the statement contexts associated with those transactions) then your app is pooling dirty connections.

Is this application a D-I-Y implementation of the API, or are you using a recognised language driver? If the latter, your best bet is to go to the forum for that driver, where the local experts can help you with the idiosyncrasies of that implementation.

./heLen