I have a Delphi 2010 application that uses ADO to query a SQL Anywhere 11 DB. I do not use prepared statements at all (TADOQuery.prepared = False) yet somehow I get prepared statements anyways. I know this because "select connection_property(''PrepStmt'')" returns a number higher than 0. It seems that either Delphi, ADO or SQL Anywhere is making the decision for me. The problem is that the number of prepared statements keeps rising (even though I am very tidy with cleaning up after myself) and once it hits 50 I get an error: Resource governor for 'prepared statements' exceeded" (which makes sense) I have no use for prepared statements at all. Is there any way I can prevent them from appearing (i.e. what setting am I missing?) or is there any way I can get rid of them once they are there? Something like a "Connection.RemoveAllPreparedStatements", or maybe a SQL statement that drops them for the current connection? Thanks in advance for helping! |
I seem to have solved the case of the mysterious prepared statements, although the solution is far from satisfying. Originally I used new TADOQuery components for every query, instantiating and freeing them as I went along. I suspected that there might be a problem with that so I opted to go for a pool instead. Every TADOQuery used was now, after use, reset and released back into the pool. The problem did not go away. Another unrelated problem we had encountered was an error message popping up when using a TADOQuery component twice in a row with a different query. I googled for the error (sorry, I do not remember the error message text) and found an easy solution: set the Connection property of the TADOQuery to nil, then reassign the TADOConnection and the error goes away. It occurred to me to try the same thing for my mysterious prepared statements, and it worked. So here's the lesson: If you use ADO in Delphi (TADOConnection and TADOQuery) to connect to SQL Anywhere 11, make sure you "nil" the connection property of the TADOQuery before either freeing, re-opening or re-executing the TADOQuery. There seems to be a resource leak if you don't. I hope this will help someone running into the same issue I did. |
While our small "comment discussion" has not yet lead to a solution, I guess you could find out why requests stay open with the help of request logging. This question gives some more details. AFAIK, the log should include the statement ID for typical statements, and that should show both
Yes, this is apparently more a hint than an answer... I am working on this, also with Sybase directly, to see what is going on. As a side note, if I start a local db from Sybase Central, then open interactive SQL (brand new connection) and run "select connection_property('PrepStmt')" it returns 4. Seems it should return 0? Might be unrelated, but what's up with that? |
Being no Delphi programmer, just some wild guesses: May it be that your TADOQuery objects stay open longer as needed? - AFAIK, at least closing a query/result set/statement handle or the like should close prepared statements. Does sa_conn_info show more open connections than expected?
At first, every TADOQuery object that was created was freed right after (a call to Free should, in theory, release all resources.) I then implemented a call to Close before I made the call to Free on all TADOQuery objects (this explicitly closes the query). This did not help. I then implemented a pool in which I keep TADOQuery objects alive and reuse them, which also did not alleviate the issue.
I use only one connection in the program. It is on that one connection that I see the prepared statement count rise. I checked and I don't see anything weird in sa_conn_info, but I am not sure I understand what I am looking for.
AFAIK, the ADO RecordSet.Close should free the statement handle and as such, free resources used for prepared statement. According to your comments, you do so by closing the TADOQuery object. So I'm puzzled. - Maybe changing the option "max_statement_count" to a small value (1 or 2) could help in identifying the problem. Or to test with the latest EBF?