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.

exeption in loop because of lost cursor - help please

3 posts in General Discussion Last posting was on 2011-01-11 08:55:08.0Z
Ralf Krzyzaniak Posted on 2011-01-10 14:28:21.0Z
Content-Type: text/plain; charset="UTF-8"
From: Ralf Krzyzaniak <RalfK@RalfK.de>
Reply-To: RalfK@RalfK.de
Organization: Krzyzaniak IT-Beratung
User-Agent: KNode/4.4.5
Content-Transfer-Encoding: 8Bit
Newsgroups: ianywhere.public.general
Subject: exeption in loop because of lost cursor - help please
Followup-To: ianywhere.public.general
Lines: 123
MIME-Version: 1.0
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4d2b1785@forums-1-dub>
Date: 10 Jan 2011 06:28:21 -0800
X-Trace: forums-1-dub 1294669701 10.22.241.152 (10 Jan 2011 06:28:21 -0800)
X-Original-Trace: 10 Jan 2011 06:28:21 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:8111
Article PK: 7131

Hi folks,
I have a strange problem for me. I'm running on client a Win 7, with
Anywhere 12.0.0.2483 and on the server side on a SUSE Linux a server
12.0.0.2566. Everything runns so far and good.
I whant to delete old Auftrags-stuff and collect first all UniqueKeys (UID)
in a table. Then I run thrugh the table (see loop) and set first only all
positions to the deleting status. For the first record this works fine. But
on the next fetch of the cursor I got an exeption, the cursor is gone. I
don't know why. BTW it is not interesting if triggers are allowed or off.
Can someone please help me?
Regards
Ralf

The dbms output :

2011-01-10 15:20:49.395296 FETCH
2011-01-10 15:20:49.395444 IF
2011-01-10 15:20:49.395519 UPDATING : 00100000000000103100
2011-01-10 15:20:49.398824 Trigger starting (tub_aufpos) of table aufpos
2011-01-10 15:20:49.534913 Trigger ending (tub_aufpos) of table aufpos
2011-01-10 15:20:49.535077 Trigger starting (tub_aufpos) of table aufpos
2011-01-10 15:20:49.558447 Trigger ending (tub_aufpos) of table aufpos
2011-01-10 15:20:49.55861 Trigger starting (tub_aufpos) of table aufpos
2011-01-10 15:20:49.573129 Trigger ending (tub_aufpos) of table aufpos
2011-01-10 15:20:49.573375 FETCH
2011-01-10 15:20:49.573435
##########################################################################
2011-01-10 15:20:49.573477 EXCEPTION in dbpDeleteAllAuftragNow sAuUID:
00100000000000103100
2011-01-10 15:20:49.573505
##########################################################################


The procedure:

create procedure dbpDeleteAllAuftragNow()
//
// New 14.02.2002 RK Es werden alle Aufträge gelöscht, die zur Löschung
markiert sind mit allen Positionen und
// die X-Tage (CnwSys Tabelle) älter sind als das current date.
// Update RK 22.07.2004 Aufräumen im System, lösche alle AU Sätze, zu denen
keine Positionen mehr existieren
// Update RK 08.01.2011 Löschen alter Aufträge, neue logik
//
begin
DECLARE err_not_found EXCEPTION FOR SQLSTATE '02000';
//
DECLARE sAuUID CHAR(20);
DECLARE nAuDelTage NUMERIC(3);
DECLARE nCountAll NUMERIC(5);
DECLARE nCountDel NUMERIC(5);
//
DECLARE curAU CURSOR FOR
SELECT tmpAuUid FROM dba.tmpAuDeleting;
DELETE FROM dba.tmpAuDeleting;
COMMIT;
//
select FIRST cs_audeltage INTO nAuDelTage from DBA.CnwSys order by
cs_buero;
//
// RK 01.2004 evt. Fehler von nicht gelöschten Lagerzuordnungen
korregieren, kam in 5 Jahren nur einmal vor
//
delete from dba.lagerzu from
dba.aufpos key join dba.lagerzu
where ap_limenge <> lz_menge and ap_posstat = 9;
//
INSERT INTO dba.tmpAuDeleting
SELECT au_uid AS AuUid FROM AUFKOPF WHERE
(select count(ap_auuid) AS nCountDel from aufpos where ap_posstat =
9 AND ap_kenn = 'A' AND ap_auuid = AuUID AND ap_belredat < current date -
nAuDelTage) =
(select count(ap_auuid) AS nCountAll from aufpos where ap_auuid =
AuUID AND ap_kenn = 'A');
//
OPEN curAU;
ThisAuLoop:
LOOP
FETCH NEXT curAU INTO sAuUID;
IF SQLSTATE = err_not_found THEN
LEAVE ThisAuLoop;
END IF;
//
message ' DELETING: dbpDeleteAllAuftragNow AU UID: ', sAuUID;
// delete position first then the main
BEGIN
// Alles was noch nicht geliefert wurde in der Liefermenge auf 0
UPDATE dba.aufpos SET ap_limenge = 0 WHERE ap_auuid = sAuUID AND
ap_posstat < 3;
// mark all for delete - see befor delete trigger of AufPos
UPDATE dba.aufpos SET ap_posstat = 9 WHERE ap_auuid = sAuUID;
// delete position first then the main
DELETE FROM dba.lagerzu
FROM dba.lagerzu KEY JOIN dba.aufpos
WHERE ap_auuid = sAuUID
AND ap_posstat > 3
AND ap_abbuchen = 'N';
//DELETE FROM dba.aufpos WHERE ap_auuid = cUid;
//DELETE FROM dba.aufkopf WHERE au_uid = cUid;
COMMIT;
END
//
END LOOP ThisAuLoop;
CLOSE curAU;
//
// RK 22.07.2004 Aufräumen im System, lösche alle AU Sätze, zu denen
keine Positionen mehr existieren
delete from aufkopf where
( select count(ap_uid) from aufpos where aufpos.ap_auuid =
aufkopf.au_uid) = 0;
//
COMMIT;
exception
when others then
message
'##########################################################################';
message ' EXCEPTION in dbpDeleteAllAuftragNow sAuUID: ', sAuUID;
message
'##########################################################################';
rollback;
resignal;
end
;//;


Jeff Albion [Sybase iAnywhere] Posted on 2011-01-10 16:50:28.0Z
From: "Jeff Albion [Sybase iAnywhere]" <firstname.lastname@sybase.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: exeption in loop because of lost cursor - help please
References: <4d2b1785@forums-1-dub>
In-Reply-To: <4d2b1785@forums-1-dub>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4d2b38d4$1@forums-1-dub>
Date: 10 Jan 2011 08:50:28 -0800
X-Trace: forums-1-dub 1294678228 10.22.241.152 (10 Jan 2011 08:50:28 -0800)
X-Original-Trace: 10 Jan 2011 08:50:28 -0800, vip152.sybase.com
Lines: 31
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:8112
Article PK: 7133

Hello Ralf,

On 10/01/2011 9:28 AM, Ralf Krzyzaniak wrote:
> // ...
> DECLARE curAU CURSOR FOR
> SELECT tmpAuUid FROM dba.tmpAuDeleting;
> // ...
> OPEN curAU;
> ThisAuLoop:
> LOOP
> // ...
> COMMIT;

Your cursor will be closed here. See:
http://dcx.sybase.com/index.html?r=1#1200en/dbprogramming/pg-sqlapp-sectb-3817092.html

Try this line instead:

OPEN curAU WITH HOLD;

Cheers,

--
Jeff Albion, Sybase iAnywhere, an SAP Company

iAnywhere Developer Community :
http://www.sybase.com/developer/library/sql-anywhere-techcorner
iAnywhere Documentation : http://www.ianywhere.com/developer/product_manuals
SQL Anywhere Patches and EBFs :
http://downloads.sybase.com/swd/summary.do?baseprod=144&client=ianywhere&timeframe=0
Report a Bug/Open a Case : http://case-express.sybase.com/cx/


Ralf Krzyzaniak Posted on 2011-01-11 08:55:08.0Z
Content-Type: text/plain; charset="ISO-8859-1"
From: Ralf Krzyzaniak <RalfK@RalfK.de>
Reply-To: RalfK@RalfK.de
Organization: Krzyzaniak IT-Beratung
User-Agent: KNode/4.4.5
Content-Transfer-Encoding: 7Bit
Subject: Re: exeption in loop because of lost cursor - help please
Newsgroups: ianywhere.public.general
References: <4d2b1785@forums-1-dub> <4d2b38d4$1@forums-1-dub>
Followup-To: ianywhere.public.general
Lines: 30
MIME-Version: 1.0
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4d2c1aec@forums-1-dub>
Date: 11 Jan 2011 00:55:08 -0800
X-Trace: forums-1-dub 1294736108 10.22.241.152 (11 Jan 2011 00:55:08 -0800)
X-Original-Trace: 11 Jan 2011 00:55:08 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:8113
Article PK: 7135

Thanks Jeff, this was it. Now I have an ill head of the banging down on the
table SMILE
Have a good day,
Ralf

Jeff Albion [Sybase iAnywhere] wrote:

> Hello Ralf,
>
> On 10/01/2011 9:28 AM, Ralf Krzyzaniak wrote:
>> // ...
>> DECLARE curAU CURSOR FOR
>> SELECT tmpAuUid FROM dba.tmpAuDeleting;
>> // ...
>> OPEN curAU;
>> ThisAuLoop:
>> LOOP
> > // ...
>> COMMIT;
>
> Your cursor will be closed here. See:
> http://dcx.sybase.com/index.html?r=1#1200en/dbprogramming/pg-sqlapp-
sectb-3817092.html
>
> Try this line instead:
>
> OPEN curAU WITH HOLD;
>
> Cheers,
>