Page 1 of 1

END OF CURSOR AND NO ROWS FOUND IN CURSOR.

PostPosted: Fri Oct 04, 2013 5:28 pm
by sinmani
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??

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

PostPosted: Fri Oct 04, 2013 7:45 pm
by dick scherrer
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.

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

PostPosted: Fri Oct 04, 2013 7:47 pm
by Akatsukami
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?

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

PostPosted: Sat Oct 05, 2013 9:37 am
by sinmani
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.

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

PostPosted: Mon Oct 07, 2013 6:40 am
by dick scherrer
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.

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

PostPosted: Mon Oct 07, 2013 10:36 pm
by Akatsukami
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.