Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

We're seeing some odd behavior and bad results involving the number of prepared statements on a connection. This is a .net v3.5 application, using the ianywhere .net provider v. The results described below have been repro'd on win7 client and server, as well as win7 client, win2008 r2 server.

We set max_prepared_statements=0, launch our test app, we can monitor the prepared statement count from isql using connection_property('PrepStmt', number)

We see the count rise to the 900's (which is WAY too high) and stabilize. Eventually, after several hours of the test run, the engine crashes.

This looks like a classic failure to dispose of prepared statements. We thought so too, but have been unable to find bad code, despite extensive review and testing. AND, THIS:

We set max_prepared_statements=50, and run our test again. NOW, monitoring shows that the number of prepared statements don't rise above 40-something. We get no exceptions in the app, and the engine does not crash. If the app was really failing to dispose of statements, it should have seen an error when the limit was exceeded.

Obviously, this by itself is not really a problem for us. We just set the max to 50 and things run well. However, I'm posting this because a) it may indicate a defect in sqlanwhere that should be investigated. and b) we have some other issues that seem to suggest problems in the ianywhere provider, and those problems seem to involve the prepared statement management. I'll post that problem on a different thread, when I can.

asked 31 Aug '11, 15:33

Leo%20Tohill's gravatar image

Leo Tohill
accept rate: 0%

This issue was resolved in CR #687447 in builds, and later.

In rare, timing dependent cases the server could have incorrect behaviour if a connection had at least 20 prepared statements and cached prepared statements or at least 20 cursors open. The incorrect behaviour could vary, and the only known symptom is incorrectly returning the error "Resource governor for 'prepared statements' exceeded", but it may also be possible for server crashes or assertions to occur.

permanent link

answered 17 Nov '11, 12:40

Jeff%20Albion's gravatar image

Jeff Albion
accept rate: 25%

There has been an already-identified issue with the .NET provider 'leaking' prepared statements if an exception occurred while using SACommand.ExecuteReader(). See CR #625219 for more details regarding this issue (fixed in

Is it possible that you're seeing some evidence of this bug, perhaps? Can you try a later version of the .NET provider and see if you can get the same behaviour?

permanent link

answered 31 Aug '11, 16:42

Jeff%20Albion's gravatar image

Jeff Albion
accept rate: 25%

We tested with the latest EBF which I assume would have that fix. No diff. And if that problem description is complete, it doesn't apply to our situation, as there were no exceptions until the crash at the end.

Thanks for the mention, though.

(31 Aug '11, 21:46) Leo Tohill

In that case, it sounds like we would need to take a closer look at your application reproducible. By your description, it doesn't actually sound like prepared statements are truly 'leaking' in your scenario, otherwise you would see:

"[-685] "Resource governor for 'prepared statements' exceeded."

for any non-zero value of max_statement_count.

As documented, the default value for max_statement_count is "50", and setting it to "0" would indicate that you're trying to use an "unlimited" number of prepared statements (i.e. you do not wish for the resource governor to manage prepared statements).

This is generally not a recommended setting (as mentioned in the documentation) due to the fact that the database server could potentially run out memory in such a condition.

Do you have a technical support plan to open a case with us, or can you possibly log a non-priority bug report with us, with the included reproducible details?

(01 Sep '11, 13:56) Jeff Albion

I have an open case on a related issue. I'll try to work this into that. thanks.

(01 Sep '11, 15:17) Leo Tohill
Your answer
toggle preview

Follow this question

By Email:

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



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "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:


question asked: 31 Aug '11, 15:33

question was seen: 6,074 times

last updated: 17 Nov '11, 12:40