Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Do we need to check sqlcode while declaring a cursor.

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
COUNT

New User


Joined: 15 Jan 2008
Posts: 6
Location: kolkata

PostPosted: Wed Apr 16, 2008 11:28 am    Post subject: Do we need to check sqlcode while declaring a cursor.
Reply with quote

hi, i am writing a PL1 program in which i am decaring a cursor. while running the program it is giving sqlcode 100. is it poasible??
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Apr 16, 2008 11:41 am    Post subject:
Reply with quote

Hello,

Quote:
running the program it is giving sqlcode 100. is it poasible??
As i was once told - if it happens it must be possible. . .

The 100 is not an error, this is the indicator for "none found" or "end of set". From the manual:
Quote:
+100 ROW NOT FOUND FOR FETCH, UPDATE OR DELETE, OR THE RESULT OF A QUERY
IS AN EMPTY TABLE
Explanation: One of the following conditions occurred:

No row met the search conditions specified in an UPDATE or DELETE statement.

The result of a SELECT INTO statement was an empty table.

A FETCH statement was executed when the cursor was positioned after the last row of the result table.

The result of the subselect of an INSERT statement is empty.

When a SELECT statement is executed using SPUFI, this SQLCODE indicates normal completion.


I'd suggest you look in the DB2 Messages & Codes documentation available via the IBM Manuals link at the top of the page.
Back to top
View user's profile Send private message
sri_mf

Active User


Joined: 31 Aug 2006
Posts: 216
Location: India

PostPosted: Wed Apr 16, 2008 12:07 pm    Post subject: Re: Do we need to check sqlcode while declaring a cursor.
Reply with quote

COUNT wrote:
hi, i am writing a PL1 program in which i am decaring a cursor. while running the program it is giving sqlcode 100. is it poasible??


Before executing the program just check ur query in SPUFI/QMF,then u can come know the records it is retrieving.
Back to top
View user's profile Send private message
COUNT

New User


Joined: 15 Jan 2008
Posts: 6
Location: kolkata

PostPosted: Wed Apr 16, 2008 12:57 pm    Post subject:
Reply with quote

As for the delete, upadate or fetch operation is concerned we can get an SQLCODE 100 if the matching records are not found. But my query is that , do we get SQLCODE 100 while declaring a cursor. afterall we are not going for search, upadate or fetch operation. it is just a decalartion of the cursor.


also i have one more query regarding insert operation.
while doing a query like.

insert into ..... select ....

i am getting an sqlcode 100 so while checking the select query fo the above operation i found that thee were no matching records. so its perfect for select query to give sqlcode 100. But is it OK with "INSERT" operartion to give same SQLCODE 100 . while using ERROR descroptor i found mantioned that the sqlcode 100 can be given for select, fetch and update operation.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Wed Apr 16, 2008 1:13 pm    Post subject:
Reply with quote

if you look at the compiler listing, there is no code generated for the declare cursor, so no need to check the sqlcode. actually the declare cursor (in addition to other things) is part of the DSNSQL SECTION which is executed as soon as your program is invoked (the first time).

So, if you declare a cursor and then check the sqlcode that has not been initialized, you are using either garbage from the module load or the sqlcode of a previous db2 call.

so, NO. no sqlcode check after a declare cursor - a mistake that many make.
Back to top
View user's profile Send private message
COUNT

New User


Joined: 15 Jan 2008
Posts: 6
Location: kolkata

PostPosted: Wed Apr 16, 2008 1:59 pm    Post subject:
Reply with quote

dbzTHEdinosauer wrote:
if you look at the compiler listing, there is no code generated for the declare cursor, so no need to check the sqlcode. actually the declare cursor (in addition to other things) is part of the DSNSQL SECTION which is executed as soon as your program is invoked (the first time).

So, if you declare a cursor and then check the sqlcode that has not been initialized, you are using either garbage from the module load or the sqlcode of a previous db2 call.

so, NO. no sqlcode check after a declare cursor - a mistake that many make.


thanks for your input!!! i was doing the same. mistake....

can you also suggesst a solution for the second question in my post...
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Wed Apr 16, 2008 2:48 pm    Post subject:
Reply with quote

COUNT,

second question - why a +100 for INSERT?
Anytime you have a WHERE clause, if it is not satisfied, you will receive an SQLCODE of +100.

yes, a +100 will be returned for a select, fetch or update operation. If you have a subselect in an INSERT, you have a SELECT which can return a +100.

did I answer you question? If not, please restate and someone will provide you with an answer - this board has a lot of knowledgeable DB2ers.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Testing rerad cursor for status with ... John F Dutcher DB2 8 Fri May 19, 2017 9:35 pm
No new posts Check if any Detail records and extra... V S Amarendra Reddy SYNCSORT 19 Mon May 08, 2017 8:54 pm
No new posts Check Binary Values for symbols gandikk CA Products 3 Tue Mar 21, 2017 5:11 am
No new posts Getting -504 Cursor Name GTT-ARTS-CUR... Robin Sulsona DB2 2 Fri Mar 17, 2017 1:43 am
No new posts DB2 SQL Error: SQLCODE=-440, SQLSTATE... kishpra DB2 1 Sat Feb 18, 2017 2:31 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us