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!

asked 08 Dec '10, 20:16

RobertDD's gravatar image

RobertDD
489161719
accept rate: 42%

edited 14 Dec '10, 10:05

Daz%20Liquid's gravatar image

Daz Liquid
861182338

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?

(10 Dec '10, 11:47) Volker Barth

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.

(10 Dec '10, 19:18) RobertDD

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.

(10 Dec '10, 19:19) RobertDD

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?

(11 Dec '10, 14:01) Volker Barth

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.

permanent link

answered 04 Jan '11, 18:00

RobertDD's gravatar image

RobertDD
489161719
accept rate: 42%

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%20Barth's gravatar image

Volker Barth
30.8k308456665
accept rate: 32%

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

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.

permanent link

answered 14 Dec '10, 15:55

Karim%20Khamis's gravatar image

Karim Khamis
5.6k53870
accept rate: 40%

edited 15 Dec '10, 21:50

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).

(15 Dec '10, 18:34) RobertDD

Hm, good point, see my edit above.

(15 Dec '10, 20:00) Karim Khamis
1

My edit #2 above gives you a different approach for the problem which does not use request logging.

(15 Dec '10, 21:51) Karim Khamis

I would prefare an ODBC trace, as well:)

(16 Dec '10, 20:33) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×13
×9
×2

question asked: 08 Dec '10, 20:16

question was seen: 3,631 times

last updated: 04 Jan '11, 18:00