ERROR [HY000] [Sybase][ODBC Driver][Adaptive Server Anywhere]Resource governor for 'prepared statements' exceeded ??? What means this, what can be the reason ??

[Peter Stojkovic]

I am using ASA 9.01

What can be the reason for the following error:

ERROR [HY000] [Sybase][ODBC Driver][Adaptive Server Anywhere]Resource
governor for 'prepared statements' exceeded ???

Thanks
for any help
Peter Stojkovic

[Peter Stojkovic]

There is an Option MAX_STATEMENT_COUNT , but what it is for ???
When is a prepared statement destroyed ??

The applictaion is working for days and then the error occures !!

Post by Peter Stojkovic
I am using ASA 9.01
ERROR [HY000] [Sybase][ODBC Driver][Adaptive Server Anywhere]Resource
governor for 'prepared statements' exceeded ???
Thanks
for any help
Peter Stojkovic

[Glenn Paulley]

When you PREPARE a statement the ASA engine allocates some resources
(mostly memory) to represent that statement. There is no way of knowing
how long the application might require that prepared statement, so the
engine maintains it within the server until either the prepared statement
is freed (by an explicit application call) or a disconnect.

I often see this with Java applications, because programmers often assume
that Java's garbage collection will clean up all unused resources - but
Java itself doesn't issue the close() method on a PreparedStatement, and
hence one gets "leaks".

MAX_STATEMENT_COUNT is a limit of the number of prepared statements the
engine will handle before giving errors - it prevents incorrectly coded
applications from causing the ASA engine to leak memory until all
available memory is exhausted. You can turn off the prepared statement
governor by setting MAX_STATEMENT_COUNT to 0, but I don't at all
recommend doing that.

Glenn

Post by Peter Stojkovic
I am using ASA 9.01
ERROR [HY000] [Sybase][ODBC Driver][Adaptive Server Anywhere]Resource
governor for 'prepared statements' exceeded ???
Thanks
for any help
Peter Stojkovic

--
Glenn Paulley
Research and Development Manager, Query Processing
iAnywhere Solutions Engineering

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

[Peter Stojkovic]

Hello Glenn,

I dont know, when a Visual Basic.NET -Program does
a prepare-statement.
I have programmed only SELECT, UPDATES and so on.
May be the ODBC-API or compiler generates a prepare-statement.

But I CAN NOT close the connection, to free the resources.
The Application must run for a longer time ( several days ) !!

I do not understand, what I have done wrong in my program ???

Any help ??

Peter

Post by Glenn Paulley
When you PREPARE a statement the ASA engine allocates some resources
(mostly memory) to represent that statement. There is no way of knowing
how long the application might require that prepared statement, so the
engine maintains it within the server until either the prepared statement
is freed (by an explicit application call) or a disconnect.
I often see this with Java applications, because programmers often assume
that Java's garbage collection will clean up all unused resources - but
Java itself doesn't issue the close() method on a PreparedStatement, and
hence one gets "leaks".
MAX_STATEMENT_COUNT is a limit of the number of prepared statements the
engine will handle before giving errors - it prevents incorrectly coded
applications from causing the ASA engine to leak memory until all
available memory is exhausted. You can turn off the prepared statement
governor by setting MAX_STATEMENT_COUNT to 0, but I don't at all
recommend doing that.
Glenn

Post by Peter Stojkovic
I am using ASA 9.01
ERROR [HY000] [Sybase][ODBC Driver][Adaptive Server Anywhere]Resource
governor for 'prepared statements' exceeded ???
Thanks
for any help
Peter Stojkovic

--
Glenn Paulley
Research and Development Manager, Query Processing
iAnywhere Solutions Engineering
EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all
To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm
SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

[Chris Keating (iAnywhere Solutions)]

Yes, the objects that interact with the database are using prepared
statements in most cases. IME you must close the object and set the object
to nothing to free the resources that are used i.e., the allocated prepared
statement.

object.close
Set object = nothing
--
Chris Keating
Sybase Adaptive Server Anywhere Professional Version 8

****************************************************************************
*
Sign up today for your copy of the SQL Anywhere Studio 9 Developer Edition
and try out the market-leading database for mobile, embedded and small to
medium sized business environments for free!

http://www.ianywhere.com/promos/deved/index.html

****************************************************************************
*

iAnywhere Solutions http://www.iAnywhere.com

** Please only post to the newsgroup

** Whitepapers can be found at http://www.iAnywhere.com/developer
** EBFs can be found at http://downloads.sybase.com/swx/sdmain.stm
** Use CaseXpress to report bugs http://casexpress.sybase.com

****************************************************************************
*

Post by Peter Stojkovic
Hello Glenn,
I dont know, when a Visual Basic.NET -Program does
a prepare-statement.
I have programmed only SELECT, UPDATES and so on.
May be the ODBC-API or compiler generates a prepare-statement.
But I CAN NOT close the connection, to free the resources.
The Application must run for a longer time ( several days ) !!
I do not understand, what I have done wrong in my program ???
Any help ??
Peter

Post by Glenn Paulley
When you PREPARE a statement the ASA engine allocates some resources
(mostly memory) to represent that statement. There is no way of knowing
how long the application might require that prepared statement, so the
engine maintains it within the server until either the prepared statement
is freed (by an explicit application call) or a disconnect.
I often see this with Java applications, because programmers often assume
that Java's garbage collection will clean up all unused resources - but
Java itself doesn't issue the close() method on a PreparedStatement, and
hence one gets "leaks".
MAX_STATEMENT_COUNT is a limit of the number of prepared statements the
engine will handle before giving errors - it prevents incorrectly coded
applications from causing the ASA engine to leak memory until all
available memory is exhausted. You can turn off the prepared statement
governor by setting MAX_STATEMENT_COUNT to 0, but I don't at all
recommend doing that.
Glenn

Post by Peter Stojkovic
I am using ASA 9.01
ERROR [HY000] [Sybase][ODBC Driver][Adaptive Server Anywhere]Resource
governor for 'prepared statements' exceeded ???
Thanks
for any help
Peter Stojkovic

--
Glenn Paulley
Research and Development Manager, Query Processing
iAnywhere Solutions Engineering
EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all
To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm
SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288