The forum will be down for maintenance over the weekend of August 18-20, 2017. The forum will be shut down on the evening (EDT) of Friday, August 18. Downtime is unknown but may be up to two days. The forum will be restarted as soon as maintenance is complete.

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?

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?

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 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?

I believe you will have to start with Volker's suggestion and run your test with request logging turned on. That will tell you which query is being prepared. Once you have that, we can dig further to see which component is actually preparing the statement. SA will not prepare any statements on its own, and according to our ADO expert, neither will the ADO/OLEDB driver.

---- EDIT ---

I believe a real prepared statement will always follow with a direct OPEN rather than an EXEC followed by OPEN.

Can you switch your application to use the OLEDB to ODBC bridge? If so, then try switching to the OLEDB to ODBC bridge (temporarily) with the SA ODBC driver underneath. See if that reproduces the same behaviour. If you do get the same behaviour with the OLEDB to ODBC bridge, then quit your app, turn on ODBC tracing, and rerun your test. You should then be able to look at the ODBC trace and search for any SQLPrepare calls. If, after switching to the OLEDB to ODBC bridge the problem does not reproduce, then we may be looking at a problem within the OLEDB driver after all.

What exactly am I looking for to determine if a query is being prepared or not? If I look in the request log it looks as if the database prepares EVERY query, whether I run it from my app or directly from Interactive SQL. I suspect I might be looking for the wrong thing, though (request logs are not the most reader friendly things).