DB2 - L

I'm working on a Native SQL SP that is using result set handling to return multiple rows. I'm having an issue with returning a +100 for when nothing is found. After a successful execution, regardless of if something was found or not, it will return a +466.  This SP is only grabbing data from tables and is not updating/modifying the tables at all. I'm wondering how you would go about returning a +100. Thanks

------------------------------
Phillip
------------------------------

Original Message:
Sent: 1/26/2023 2:35:00 PM
From: Phillip Victor
Subject: DB2 SQL PL SP Handling a Not Found

I'm working on a Native SQL SP that is using result set handling to return multiple rows. I'm having an issue with returning a +100 for when nothing is found. After a successful execution, regardless of if something was found or not, it will return a +466.  This SP is only grabbing data from tables and is not updating/modifying the tables at all. I'm wondering how you would go about returning a +100. Thanks

------------------------------
Phillip
------------------------------

Hey Renu,
Below is a basic outline of my code
Declares
Declare cursor
Declare Exit handler for sqlstate '02000'
Declare Exit handler for sqlexception 
Open Cursor
It's still returning a zero and not a +100. I also tried it with the "handler for not found". I have code set up for each handler to move the sqlcode to the output. 

Thanks

------------------------------
Phillip
------------------------------

Original Message:
Sent: Jan 26, 2023 02:46 PM
From: Renu Sharma
Subject: DB2 SQL PL SP Handling a Not Found

Hi Phillip

Please do the Sql error code checking after you get the result and put your logic for rows not found. I.e 100.  You have to declare handler and cursor to process the data. Please go to IBM site and look for cursors in SQL Procedures

Thanks

Renu Sharma


Sent from the all new AOL app for iOS

Original Message:
Sent: 1/26/2023 2:35:00 PM
From: Phillip Victor
Subject: DB2 SQL PL SP Handling a Not Found

I'm working on a Native SQL SP that is using result set handling to return multiple rows. I'm having an issue with returning a +100 for when nothing is found. After a successful execution, regardless of if something was found or not, it will return a +466.  This SP is only grabbing data from tables and is not updating/modifying the tables at all. I'm wondering how you would go about returning a +100. Thanks

------------------------------
Phillip
------------------------------

Is it just me, or are you really expecting sqlstate '02000' (sqlcode +100) on the Open cursor -
rather than the first fetch?

If you want something other than sqlcode +466 to be returned, you could try setting the
procedure's return sqlstate to '01Hxx' - and get sqlcode +462.

The only way I could think of getting sqlcode +100 in the calling program (with a null result
set) would be to return a dynamic result set - and the first fetch in the caller would get +100.

James Campbell


On 26 Jan 2023 at 20:37, Phillip Victor via International DB2 User wrote:

> Hey Renu,
> Below is a basic outline of my code
> Declares
> Declare cursor
> Declare Exit handler for sqlstate '02000'
> Declare Exit handler for sqlexception
> Open Cursor
> It's still returning a zero and not a +100. I also tried it with the "handler for not found". I have code set up for each handler to move the sqlcode to the output.
>
> Thanks
>
> ------------------------------
> Phillip
> ------------------------------
> -------------------------------------------
> Original Message:
> Sent: Jan 26, 2023 02:46 PM
> From: Renu Sharma
> Subject: DB2 SQL PL SP Handling a Not Found
>
> Hi PhillipPlease do the Sql error code checking after you get the result and put your logic for rows not found. I.e 100. You have to declare handler and cursor to process the data. Please go to IBM site and look for cursors in SQL Procedures
> Thanks
> Renu Sharma
>
>
> Sent from the all new AOL app for iOS

I've had a ponder. If I've got this correct you want the SP to identify that the result set is empty and do something other than return a result set? Nope, it's going to be the calling code that get the sqlcode+100.

What you could do is do a one row fetch in the SP. If it succeeds, then open the cursor and return. If it fails, then do whatever alternative processing you desire. Just a word of warning - if you don't return a result set, your caller gets sqlcode=0 and the sqlstate you've set.

------------------------------
James Campbell
------------------------------

Original Message:
Sent: Jan 26, 2023 09:58 PM
From: James Campbell
Subject: DB2 SQL PL SP Handling a Not Found

Is it just me, or are you really expecting sqlstate '02000' (sqlcode +100) on the Open cursor -
rather than the first fetch?

If you want something other than sqlcode +466 to be returned, you could try setting the
procedure's return sqlstate to '01Hxx' - and get sqlcode +462.

The only way I could think of getting sqlcode +100 in the calling program (with a null result
set) would be to return a dynamic result set - and the first fetch in the caller would get +100.

James Campbell


On 26 Jan 2023 at 20:37, Phillip Victor via International DB2 User wrote:

> Hey Renu,
> Below is a basic outline of my code
> Declares
> Declare cursor
> Declare Exit handler for sqlstate '02000'
> Declare Exit handler for sqlexception
> Open Cursor
> It's still returning a zero and not a +100. I also tried it with the "handler for not found". I have code set up for each handler to move the sqlcode to the output.
>
> Thanks
>
> ------------------------------
> Phillip
> ------------------------------
> -------------------------------------------
> Original Message:
> Sent: Jan 26, 2023 02:46 PM
> From: Renu Sharma
> Subject: DB2 SQL PL SP Handling a Not Found
>
> Hi PhillipPlease do the Sql error code checking after you get the result and put your logic for rows not found. I.e 100. You have to declare handler and cursor to process the data. Please go to IBM site and look for cursors in SQL Procedures
> Thanks
> Renu Sharma
>
>
> Sent from the all new AOL app for iOS

Original Message:
Sent: 1/27/2023 12:48:00 AM
From: James Campbell
Subject: RE: DB2 SQL PL SP Handling a Not Found

I've had a ponder. If I've got this correct you want the SP to identify that the result set is empty and do something other than return a result set? Nope, it's going to be the calling code that get the sqlcode+100.

What you could do is do a one row fetch in the SP. If it succeeds, then open the cursor and return. If it fails, then do whatever alternative processing you desire. Just a word of warning - if you don't return a result set, your caller gets sqlcode=0 and the sqlstate you've set.

------------------------------
James Campbell
------------------------------

Original Message:
Sent: Jan 26, 2023 09:58 PM
From: James Campbell
Subject: DB2 SQL PL SP Handling a Not Found

Is it just me, or are you really expecting sqlstate '02000' (sqlcode +100) on the Open cursor -
rather than the first fetch?

If you want something other than sqlcode +466 to be returned, you could try setting the
procedure's return sqlstate to '01Hxx' - and get sqlcode +462.

The only way I could think of getting sqlcode +100 in the calling program (with a null result
set) would be to return a dynamic result set - and the first fetch in the caller would get +100.

James Campbell


On 26 Jan 2023 at 20:37, Phillip Victor via International DB2 User wrote:

> Hey Renu,
> Below is a basic outline of my code
> Declares
> Declare cursor
> Declare Exit handler for sqlstate '02000'
> Declare Exit handler for sqlexception
> Open Cursor
> It's still returning a zero and not a +100. I also tried it with the "handler for not found". I have code set up for each handler to move the sqlcode to the output.
>
> Thanks
>
> ------------------------------
> Phillip
> ------------------------------
> -------------------------------------------
> Original Message:
> Sent: Jan 26, 2023 02:46 PM
> From: Renu Sharma
> Subject: DB2 SQL PL SP Handling a Not Found
>
> Hi PhillipPlease do the Sql error code checking after you get the result and put your logic for rows not found. I.e 100. You have to declare handler and cursor to process the data. Please go to IBM site and look for cursors in SQL Procedures
> Thanks
> Renu Sharma
>
>
> Sent from the all new AOL app for iOS