END OF CURSOR AND NO ROWS FOUND IN CURSOR.



IBM's flagship relational database management system

END OF CURSOR AND NO ROWS FOUND IN CURSOR.

Postby sinmani » Fri Oct 04, 2013 5:28 pm

Hi Forum,

I Am using a cursor for a DB2 table.

Now I have to read the values until END of CURSOR and load them in a Table until END of cursor.
However if NO ROWS are found then the program is to be abended.

How can I do this??

Are NO ROW FOUND and END OF CURSOR different types in Cursor processing??
-----------------------------------------
As long as you think you are a student, you are doing well.
The day you consider yourself as the master of the game..........well
sinmani
 
Posts: 93
Joined: Thu Mar 22, 2012 10:02 am
Has thanked: 14 times
Been thanked: 0 time

Re: END OF CURSOR AND NO ROWS FOUND IN CURSOR.

 

Re: END OF CURSOR AND NO ROWS FOUND IN CURSOR.

Postby dick scherrer » Fri Oct 04, 2013 7:45 pm

Hello,

One way to do what you want is to add to a counter each time a row is fetched.

When the sql code says there is no record found, check the count. If it is still zero, there were no rows.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times

Re: END OF CURSOR AND NO ROWS FOUND IN CURSOR.

Postby Akatsukami » Fri Oct 04, 2013 7:47 pm

Think about it: if the requirement were to read a file and store the records in a table, but to "abend" (whatever that means to you) the program if the file were empty, how would you do it?
"You have sat too long for any good you have been doing lately ... Depart, I say; and let us have done with you. In the name of God, go!" -- what I say to a junior programmer at least once a day
User avatar
Akatsukami
Global moderator
 
Posts: 1053
Joined: Sat Oct 16, 2010 2:31 am
Location: Bloomington, IL
Has thanked: 6 times
Been thanked: 51 times

Re: END OF CURSOR AND NO ROWS FOUND IN CURSOR.

Postby sinmani » Sat Oct 05, 2013 9:37 am

Counter is a simple option Which would work.
But I wanted to know if we have any such specific SQL code for NO ROWS FOUND.
What I knew that at the end of last fetch we would get a SQL code = 100. Similarly for No rows found too I will get SQL code = 100.
So I coded it that way. Then a very senior person told me that these two conditions can be made to work differently.
-----------------------------------------
As long as you think you are a student, you are doing well.
The day you consider yourself as the master of the game..........well
sinmani
 
Posts: 93
Joined: Thu Mar 22, 2012 10:02 am
Has thanked: 14 times
Been thanked: 0 time

Re: END OF CURSOR AND NO ROWS FOUND IN CURSOR.

Postby dick scherrer » Mon Oct 07, 2013 6:40 am

Hello,

It this "senior" knows these things, s/he should be able to clarify.

make sure they explain why testing for 100 would NOT work for your case.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times

Re: END OF CURSOR AND NO ROWS FOUND IN CURSOR.

Postby Akatsukami » Mon Oct 07, 2013 10:36 pm

sinmani wrote:Counter is a simple option Which would work.
But I wanted to know if we have any such specific SQL code for NO ROWS FOUND.

No.

I wrote a simple test program:
 foo46:  proc (parms) options (main) reorder;               
                                                             
 dcl desc                           char (255),             
     parms                          char (100) var,         
     freq                           fixed bin (31);         
                                                             
 EXEC SQL                                                   
   INCLUDE SQLCA;                                           
                                                             
 EXEC SQL                                                   
   DECLARE A CURSOR FOR                                     
     SELECT DESC FROM EAMAY                                 
       WHERE FREQUENCY = :FREQ;                             
                                                             
 freq = parms;                                               
                                                             
 EXEC SQL                                                   
   OPEN A;                                                   
                                                             
 put skip edit ('SQLCODE on open is ', sqlcode) (a, f(6));   
                                                             
 do while (sqlcode=0);                                       
   EXEC SQL                                                 
     FETCH A INTO :DESC;                                     
                                                             
   put skip edit ('SQLCODE on fetch is ', sqlcode) (a, f(6));
                                                             
 end;                                                       
                                                             
 end foo46;                                                 

and executed it with this JCL:
//hlqJ12   JOB  ,'This is a test',CLASS=S,MSGCLASS=1,REGION=0M,
//         SCHENV=DB2@HD0D                                     
//*MAIN FAILURE=RESTART,LINES=(999)                           
//STEP1    EXEC PGM=IKJEFT1B                                   
//STEPLIB  DD   DSN=hlq.WORK.LOAD,DISP=SHR                     
//SYSPRINT DD   SYSOUT=*                                       
//SYSTSPRT DD   SYSOUT=*                                       
//SYSTSIN  DD   *                                             
  DSN SYSTEM(HD0D)                                             
  RUN PROGRAM(POO46) PLAN(POO46) PARMS('/8')                   
/*                                                             

(8 being a value that I happen to know is not held by frequency on any row in that table). I got the following output:
SQLCODE on open is      0
SQLCODE on fetch is    100

So I believe it is time that your very senior person, as we say, put up or shut up.
"You have sat too long for any good you have been doing lately ... Depart, I say; and let us have done with you. In the name of God, go!" -- what I say to a junior programmer at least once a day

These users thanked the author Akatsukami for the post:
sinmani (Tue Oct 08, 2013 11:13 am)
User avatar
Akatsukami
Global moderator
 
Posts: 1053
Joined: Sat Oct 16, 2010 2:31 am
Location: Bloomington, IL
Has thanked: 6 times
Been thanked: 51 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post