How Many Rows Did SQL Fetch?

Published: March 29, 2006

Hey, Ted:

I am using SQL to load a subfile. Since there are 16 subfile records to a page, I fetch 16 database records into a data structure. However, the fetch does not always return 16 rows. Sometimes it returns fewer. How can I tell how many data structure occurrences were fetched?

--Lynn

You will need to check the SQLER3 field of the SQL Communications Area (SQLCA). I wrote about this field earlier in Four Hundred Guru. See "How Many Records Did SQL Delete?"

Here's an example I threw together that you can start from. It fetches seven records at a time from file QCUSTCDT, which you should be able to find in library QIWS on your system.

Fqsysprt   o    f  132        printer                                 
                                                                      
D CustRec       e ds                  extname(QCUSTCDT) occurs(7) inz 
D RecCt           s              3p 0                                 
D Index           s             10i 0                                 
D FetchCt         s              3p 0                                 
                                                                      
C                   eval      *inlr = *on                             
C/exec sql                                                            
C+   declare c1 cursor for                                            
C+      select *                                                      
C+        from qcustcdt                                               
C/end-exec                                                            
C/exec sql                                                            
C+   open c1                                                          
C/end-exec                                                            
C                   dow       '1'                                     
C/exec sql                                                            
C+   fetch c1                                                         
C+     for 7 rows                                                     
C+    into :CustRec                                                   
C/end-exec                                                            
C                   if        sqlstt <> *zeros
C                   leave                        
C                   endif                        
C                   eval      FetchCt += 1       
C                   for       index = 1 to SqlEr3
C                   eval      RecCt += 1         
C     index         occur     CustRec            
C                   except    pline              
C                   endfor                       
C                   enddo                        
C/exec sql                                       
C+   close c1                                    
C/end-exec                                       
C                   return                       
Oqsysprt   e            pline       1            
O                       FetchCt       4          
O                       RecCt         4  +0001   
O                       SQLEr3           +0001   
O                       cusnum           +0001   
O                       lstnam           +0001   
O                       init             +0001

The program has two loops. The outer loop fetches up to seven rows into a multiple-occurrence data structure. The inner loop processes the data that was placed into the data structure. Notice that the SQLER3 field serves to limit the number of iterations of the inner loop.

Here's the output I got when I ran the program. The first column counts the fetches. Notice that the program had to fetch twice to access 12 rows. The second column numbers the records. The third column is SQLER3. It shows that the first fetch retrieved seven rows, whereas the second fetch retrieved five. The remaining columns come from the database file.

1   1 000000007 938472 Henning  G K
1   2 000000007 839283 Jones    B D
1   3 000000007 392859 Vine     S S
1   4 000000007 938485 Johnson  J A
1   5 000000007 397267 Tyron    W E
1   6 000000007 389572 Stevens  K L
1   7 000000007 846283 Alison   J S
2   8 000000005 475938 Doe      J W
2   9 000000005 693829 Thomas   A N
2  10 000000005 593029 Williams E D
2  11 000000005 192837 Lee      F L
2  12 000000005 583990 Abraham  M T

--Ted

RELATED STORY

How Many Records Did SQL Delete?