SAP SQL Anywhere

Question

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!

Accepted Answer

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.

Answer 2

1

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

if there are any prepared statements, and
if there are statements that stay unclosed (for whatever reason).

Yes, this is apparently more a hint than an answer...

permanent link

answered 11 Dec '10, 18:36

Volker Barth
39.5k●355●539●810
accept rate: 34%

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?

(15 Dec '10, 18:31) RobertDD

flat view

Answer 3

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.

For example:

+1,<,1,PREPARE,select * from systable
=,>,1,PREPARE,65538
=,<,1,DESC_OUT,65538
+30,>,1,DESC_OUT
=,<,1,DESC_IN,65538
=,>,1,DESC_IN
=,<,1,EXEC,65538
=,W,1,111,Statement cannot be executed
=,>,1,EXEC
=,<,1,OPEN,65538

Is a non-prepared statement while:

=,<,1,PREPARE,select * from syscolumn
=,>,1,PREPARE,65540
=,<,1,DESC_OUT,65540
+3,>,1,DESC_OUT
=,<,1,DESC_IN,65540
=,>,1,DESC_IN
+2,<,1,OPEN,65540

Is a prepared statement.

---- EDIT #2 ---

Here is another approach...

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.

SAP SQL Anywhere

How do I get rid of prepared statements?

Follow this question

Related questions