473,902 Members | 3,411 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

SQL status '02000' too early!

Hi everyone,

I'm facing a really wierd issue here. I can't figure out what in the
world could be going on.

I have this open cursor with some records on it. I loop on the records
until I get a status code of '02000'. The problem is, I get this
status code too early, meaning, I get out of the loop before I process
all the records in the cursor.

How can this be explained?! Is there a work around for that?

To keep it simple, I'll only list relevant code from the stored
procedure.

Start code snippet:

DECLARE NOT_END NUMERIC(1) DEFAULT 1; -- used to check for end of
data
DECLARE AT_END CONDITION FOR '02000'; -- condition for end of data
DECLARE CONTINUE HANDLER FOR AT_END SET NOT_END = 0; -- to get out of
loop

OPEN cursorHoldings;

WHILE (NOT_END <> 0) DO
FETCH cursorHoldings INTO var1, var2, (...and so on)
IF (NOT_END <> 0) THEN
/*
Do things here
*/
END IF;
END WHILE;

CLOSE cursorHoldings;

:End code snippet

By the way, I tried different approaches, like changing the continue
handler declaration to

DECLARE CONTINUE HANDLER FOR NOT FOUND SET NOT_END = 0; -- to get out
of loop

It didn't work.

I thought I'd get the number of rows in the result and loop a certain
number of times, but "GET DIAGNOSTICS varName = ROW_COUNT" returned 0.

I searched for something to tell me the row number so that I can store
the current row number and in the next iteration check if I'm in the
same row number I would exit, but I can't find anything to tell me
which row I'm on now. Besides, I don't need to do that, it should work
the way I'm doing it now. At least, this is how the reference does it.

Any help is appreciated.
Nov 12 '05
12 11304
Ta*********@itw orx.com (Tarek M. Nabil) wrote in message news:<c8******* *************** ****@posting.go ogle.com>...
That doesn't work for me :(

Actually, I was surprised when I read it the first time because
(although I'm a newbie to both the DB2 and AS400 worlds) I remember
reading something in the SQL reference that really upset me at the
time, so it stuck in my mind.
It says:
"Notes
Compound statements cannot be nested."


The nested compounds definitely work on DB2 UDB for LUW, and IMHO are
an important feature of the language - but I was forgetting you were
on AS/400. I know very little about that flavour of DB2.

As a workaround I suppose you could try setting a flag to indicate
when you are doing a FETCH versus when you are doing anything else,
and change your original condition handler to only set AT_END = 'Y'
when in "fetch mode". Hope you see what I mean.

There may be better solutions that others can suggest.
Jeremy Rickard
Nov 12 '05 #11
This is a really good idea.

Unfortunately, data changes have caused the issue to stop happening.
I'm trying to replicate it but not with any success yet :((

Nevertheless, I have implemented the idea and it works syntactically.
I also think that if there was a chance this condition should happen
again, this would prevent it.

I would like to thank you for your continuous help and your brilliant
ideas :)

jr******@unisys tems.biz (Jeremy Rickard) wrote in message news:<d3******* *************** ****@posting.go ogle.com>...
Ta*********@itw orx.com (Tarek M. Nabil) wrote in message news:<c8******* *************** ****@posting.go ogle.com>...
That doesn't work for me :(

Actually, I was surprised when I read it the first time because
(although I'm a newbie to both the DB2 and AS400 worlds) I remember
reading something in the SQL reference that really upset me at the
time, so it stuck in my mind.
It says:
"Notes
Compound statements cannot be nested."


The nested compounds definitely work on DB2 UDB for LUW, and IMHO are
an important feature of the language - but I was forgetting you were
on AS/400. I know very little about that flavour of DB2.

As a workaround I suppose you could try setting a flag to indicate
when you are doing a FETCH versus when you are doing anything else,
and change your original condition handler to only set AT_END = 'Y'
when in "fetch mode". Hope you see what I mean.

There may be better solutions that others can suggest.
Jeremy Rickard

Nov 12 '05 #12
Jeremy Rickard wrote:
Ta*********@itw orx.com (Tarek M. Nabil) wrote in message news:<c8******* *************** ****@posting.go ogle.com>...
That doesn't work for me :(

Actually, I was surprised when I read it the first time because
(although I'm a newbie to both the DB2 and AS400 worlds) I remember
reading something in the SQL reference that really upset me at the
time, so it stuck in my mind.
It says:
"Notes
Compound statements cannot be nested."

The nested compounds definitely work on DB2 UDB for LUW, and IMHO are
an important feature of the language - but I was forgetting you were
on AS/400. I know very little about that flavour of DB2.


<snip>

Jeremy Rickard


I am informed that nested compound statements are supported by DB2 UDB
for iSeries (AS/400 follow-on) in V5R2. Reference:
http://publib.boulder.ibm.com/iserie...tm#HDRWHATSNEW

--
Karl Hanson

Nov 12 '05 #13

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

27
2623
by: John Roth | last post by:
PEP 263 is marked finished in the PEP index, however I haven't seen the specified Phase 2 in the list of changes for 2.4 which is when I expected it. Did phase 2 get cancelled, or is it just not in the changes document? John Roth
63
2726
by: Martin Johansen | last post by:
Hey guys Since C is my language of choice for almost any kind of application, I'd like to know what the status of C is around the world today. And I am ofcourse not talking about C++ or C#. I think the lack of object orientation and garbage collection causes better programming discipline needed for harder projects anyway. Is C still the language of choice for serious programming?
2
1740
by: mark | last post by:
I understand that writing programs with option strict on is the best way to obtain stable applications. I have also found the applications to run much faster. Option strict on disallows late binding, so if I have an array with class level scope and I am operating on that array at procedure level I fabricate a "dummy array" and copy the class level array into it. Then after performing the task I can copy the "dummy array" back into the...
8
14111
by: Tim Reynolds | last post by:
Our .Net application calls a web method of aplpication 2 that resides on their Apache server. When I as a developer C#, Studios 2003, make the call to their web method from my desktop, I receive no exceptions - completes fine. Their web service url was added as a web reference to our project with no problem. The following was generated as part of the proxy class: /// <remarks/> public DueDateAvailabilityResponse...
7
2167
by: Marek Zawadzki | last post by:
Hi all, In your opinion: what is current status of frameworks for PHP and which one would you choose? I am looking for an all-purpose, MVC-based framework I could learn and use for all the applications I'll be working on in the future. Something that is currently stable and documented enough to be used in a commercial world (but it doesn't have to drive space shuttles yet;-)), and has no limitations whatsoever on the view...
12
1346
by: Stef Mientki | last post by:
In the example below, "pin" is an object with a number of properties. Now I want 1- an easy way to create objects that contains a number of these "pin" 2- an multiple way to access these "pin", i.e. device.pin device.some_logical_name ad 1: a dictionary (as "pinlist" in the example) seems a very convenient way (from a viewpoint of the device creator). As you can see in the "__init__" section this dictionary can easily be
3
16082
ADezii
by: ADezii | last post by:
The process of verifying that an Object exists and that a specified Property or Method is valid is called Binding. There are two times when this verification process can take place: during compile time (Early Binding) or run time (Late Binding). When you declare an Object Variable as a specific Data Type, you are using Early Binding so the verification can take place during compile time. When you declare a Variable of the generic Object Data...
0
822
by: Chris Calloway | last post by:
Just a reminder, we're at the two week warning on early bird registration for PyCamp: http://trizpug.org/boot-camp/2008/ Registration is now open for: PyCamp: Python Boot Camp, August 4 - 8 Plone Boot Camp: Customizing Plone, July 28 - August 1
0
908
by: ZitoMD | last post by:
I have an application where the user selects appropriate files from a OpenFileDialog box which then sends the file to the PDF printer. My problem is that I want to wait until there are no more files in the Printer Queue prior to moving on to the next step which is combining the PDFs. IE If I combine too early the files still in the queue do not get added to the PDF. Now I have searched and seen alot of examples of WMI scripts or Mgmt...
0
9845
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
10978
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10497
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9672
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7204
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5892
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6084
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4724
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3323
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.