Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Resource governor for 'prepared statements' exceeded Options

5 posts in General Discussion Last posting was on 2007-08-09 17:34:27.0Z
Geoffrey Chambers Posted on 2007-08-06 20:22:53.0Z
Sender: 35cd.46b78114.1804289383@sybase.com
From: Geoffrey Chambers
Newsgroups: ianywhere.public.general
Subject: Resource governor for 'prepared statements' exceeded Options
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <46b7831d.35fb.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 6 Aug 2007 13:22:53 -0700
X-Trace: forums-1-dub 1186431773 10.22.241.41 (6 Aug 2007 13:22:53 -0700)
X-Original-Trace: 6 Aug 2007 13:22:53 -0700, 10.22.241.41
Lines: 34
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6166
Article PK: 4634

I have created a procedure that checks to see if a records
exists and if it doesn't then It's added. It works by
stepping through a master table passing the record to my
stored procedure and updating another table. I get the
following error after 93 calls to my procedure.

Here's the code
==============================================
ALTER PROCEDURE "DBA"."AddToCostCodes"(in cCostCode
char(6),in cDesc char(30))
--===============================================================
-- DBA.AddToCostCodes - Adds new unique row to costcodes
table
--================================================================
begin
declare nDummy char(6);
declare cErrNotFound exception for sqlstate value '02000';
declare curCodes dynamic scroll cursor for select costcode
from
DBA.COSTCODES where
costcode = cCostCode;
open curCodes;
fetch next curCodes into nDummy;
if sqlstate = cErrNotFound then
insert into DBA.COSTCODES(costcode,description)
values(cCostCode,cDesc)
end if;
close curCodes
end
============================================================

I tried using select statements in my code first checking if
a record exists and then inserting if it doesn't but I get
the same error.


Glenn Paulley Posted on 2007-08-06 21:51:15.0Z
From: Glenn Paulley <paulley@ianywhere.com>
Reply-To: paulley@ianywhere.com
Organization: Sybase iAnywhere
User-Agent: Thunderbird 2.0.0.5 (Windows/20070716)
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: Resource governor for 'prepared statements' exceeded Options
References: <46b7831d.35fb.1681692777@sybase.com>
In-Reply-To: <46b7831d.35fb.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: wsip-70-165-211-169.lv.lv.cox.net
X-Original-NNTP-Posting-Host: wsip-70-165-211-169.lv.lv.cox.net
Message-ID: <46b797d3$1@forums-1-dub>
Date: 6 Aug 2007 14:51:15 -0700
X-Trace: forums-1-dub 1186437075 70.165.211.169 (6 Aug 2007 14:51:15 -0700)
X-Original-Trace: 6 Aug 2007 14:51:15 -0700, wsip-70-165-211-169.lv.lv.cox.net
Lines: 57
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6167
Article PK: 2605

Take a look at the help for the MAX_STATEMENT_COUNT option. It is likely
that the error is caused by leaks of prepared statements within your
application, rather than with this particular procedure.

Glenn

Geoffrey Chambers wrote:
> I have created a procedure that checks to see if a records
> exists and if it doesn't then It's added. It works by
> stepping through a master table passing the record to my
> stored procedure and updating another table. I get the
> following error after 93 calls to my procedure.
>
> Here's the code
> ==============================================
> ALTER PROCEDURE "DBA"."AddToCostCodes"(in cCostCode
> char(6),in cDesc char(30))
> --===============================================================
> -- DBA.AddToCostCodes - Adds new unique row to costcodes
> table
> --================================================================
> begin
> declare nDummy char(6);
> declare cErrNotFound exception for sqlstate value '02000';
> declare curCodes dynamic scroll cursor for select costcode
> from
> DBA.COSTCODES where
> costcode = cCostCode;
> open curCodes;
> fetch next curCodes into nDummy;
> if sqlstate = cErrNotFound then
> insert into DBA.COSTCODES(costcode,description)
> values(cCostCode,cDesc)
> end if;
> close curCodes
> end
> ============================================================
>
> I tried using select statements in my code first checking if
> a record exists and then inserting if it doesn't but I get
> the same error.

--
Glenn Paulley
Director, Engineering (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://case-express.sybase.com

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

Whitepapers, TechDocs, and bug fixes are all available through the iAnywhere
Developer Community at www.ianywhere.com/developer


Geoffrey Chambers Posted on 2007-08-07 12:55:40.0Z
Sender: 35cd.46b78114.1804289383@sybase.com
From: Geoffrey Chambers
Newsgroups: ianywhere.public.general
Subject: Re: Resource governor for 'prepared statements' exceeded Options
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <46b86bcc.4a80.1681692777@sybase.com>
References: <46b797d3$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 7 Aug 2007 05:55:40 -0700
X-Trace: forums-1-dub 1186491340 10.22.241.41 (7 Aug 2007 05:55:40 -0700)
X-Original-Trace: 7 Aug 2007 05:55:40 -0700, 10.22.241.41
Lines: 15
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6168
Article PK: 4635

I am new to a 3rd party library within my application, and
not that you would neccessarily unerstand it ( or me for me
that matter) but the way I call the stored procedure is by
executing a ADOCommand:Execute() and I can write it as oRS
:= ADOCommand:Execute() (used for select statements) which
returns a RecordSet that I can call a oRS:Close(), but with
just the ADOCommand, I don't see anyway to explicitly close
the object/statement.

Can I add something to the code of the stored procedure that
would call the DROP STATEMENT? I was also thinking If I
write the call to the stored procedure in a seperate method
the variables created would be destroyed on the return from
the method. But I still probably would be relying on the
garbage collector to clean things up.


Shuchit Posted on 2007-08-08 15:49:03.0Z
Newsgroups: ianywhere.public.general
Subject: Re: Resource governor for 'prepared statements' exceeded Options
From: Shuchit <me@privacy.net>
References: <46b7831d.35fb.1681692777@sybase.com>
Message-ID: <Xns99867830BBFA2svelkarprivacynet@127.0.0.1>
User-Agent: Xnews/2006.08.24 Hamster/2.1.0.11
NNTP-Posting-Host: svelkar-pc.sybase.com
X-Original-NNTP-Posting-Host: svelkar-pc.sybase.com
Date: 8 Aug 2007 08:49:03 -0700
X-Trace: forums-1-dub 1186588143 10.25.98.144 (8 Aug 2007 08:49:03 -0700)
X-Original-Trace: 8 Aug 2007 08:49:03 -0700, svelkar-pc.sybase.com
Lines: 42
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6172
Article PK: 4637


Geoffrey Chambers wrote in news:46b7831d.35fb.1681692777@sybase.com:

> I have created a procedure that checks to see if a records
> exists and if it doesn't then It's added. It works by
> stepping through a master table passing the record to my
> stored procedure and updating another table. I get the
> following error after 93 calls to my procedure.
>
> Here's the code
>==============================================
> ALTER PROCEDURE "DBA"."AddToCostCodes"(in cCostCode
> char(6),in cDesc char(30))
> --===============================================================
> -- DBA.AddToCostCodes - Adds new unique row to costcodes
> table
> --================================================================
> begin
> declare nDummy char(6);
> declare cErrNotFound exception for sqlstate value '02000';
> declare curCodes dynamic scroll cursor for select costcode
> from
> DBA.COSTCODES where
> costcode = cCostCode;
> open curCodes;
> fetch next curCodes into nDummy;
> if sqlstate = cErrNotFound then
> insert into DBA.COSTCODES(costcode,description)
> values(cCostCode,cDesc)
> end if;
> close curCodes
> end
>============================================================
>

What version of SQL Anywhere are you using ? For 8.0.2 and above
you could use the "insert .... on existing skip ...." syntax
and not have to bother with the cursor. Depending on how your code is setup
you might be able to eliminate this stored procedure and write a single
insert statement that does a select from your master table.

Shuchit