Fetching multiple rows in SQLRPGLE

480 pts.
Tags:
AS/400
AS/400 commands
AS/400 Records
SQLRPGLE
Hi, When I try to fetch more records, I couldn't do it. Please see my code below and explain. Thanks.

Software/Hardware used:
As400 SQLRPGLE

Answer Wiki

Thanks. We'll let you know when a new response is added.
--------------------

d data          e ds                   extname(FILENAME1) occurs(10)        
D IFLDNAM         S             10a   inz('amtfld')                          
d Iactfld         s             12s 0 inz(31000000000)                       
d maxRows         s             10i 0 Inz(maxElem)                           
d maxElem         c                   200                                    
d I               s              3i 0 Inz(1)                                 
d TRNCNT          s              3i 0 Inz(0)                                 

d sqlString       s          32000a   varying                                
d                                     inz('Select * from FILENAME1 where +  
d                                      actfld = ? ' )                        

c/exec sql                                                              
c+ declare mainCursor Cursor                                            
c+     for mainStatement                                                
c/end-exec                                                              
c/exec sql                                                              
c+ prepare mainStatement                                                
c+    from :sqlString                                                   
c/end-exec                                                              
c/exec sql                                                              
c+ open mainCursor                                                      
c+ using :Iactfld                                                       
c/end-exec                                                              
c/exec sql                                                              
c+                  fetch mainCursor for :maxRows rows                  
c+                   into :data                                         
c/end-exec                                                              
C                   movel     sqlstring     sqlstring1       52         
C     sqlstring1    dsply                                               
C                   EVAL      TRNCNT = sqler3                           
 /free                                                                  
                    FOR       I = 1 to sqler3;                         
 /end-free                                                             
C                   EVAL      I = I +1                                 
C     NPDT8         dsply                                              
C     actfld        dsply                                              

c*exec sql                                                             
c*                    fetch next                                       
c*                     from mainCursor for :maxRows rows               
c*                     into :data                                      
c*end-exec                                                             
c                   end                                                

-----------------------------

Discuss This Question: 12  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta

    Are you asking about "getting more rows" or "getting multiple rows"? You use "multiple" in your question title and you show a multi-row FETCH; but your question says you can't get "more" rows.

    What happens? You didn't tell us what your code does that is wrong. How do you know it isn't working?

    I see things that don't make sense. For example, you have a FOR-loop indexed by I that has the number of FETCHed rows as a limit. But you increment I inside the loop which doesn't make sense. Then you do another multi-row FETCH inside the loop which really makes no sense. The loop doesn't seem to have any purpose.

    But I'd normally assume that it's not a complete program anyway, so some sense might be obscured.

    Tom

    125,585 pointsBadges:
    report
  • philpl1jb

    maxrows 200 but Data occurs 10???

    That might be an issue .

     

    49,830 pointsBadges:
    report
  • as400dev
    I need to search for the particular account number and need to load it into the Data structure. It may be 1 or many. So How can i modify the code.
    480 pointsBadges:
    report
  • TomLiotta

    I'm not sure how to modify your code. There are too many things that I would change. And Phil can find other things, too, I'm sure.

    Instead, I'll post a simple example that's similar:

         H  dftactgrp( *NO )
         H   actgrp( 'MULTFETCH' )
    
         DACCPTH         e ds                  occurs( 10 )
    
         DIND_ARRAY        DS                  occurs( 10 )
         D INDS                           5i 0 dim( 4 )
    
         D SQLErrorMsg     pr
    
         D fetchFile       s             10    inz( 'LICKEYS' )
         D sqlStmt         s            512
         D i               s             10i 0
          /free
           exec SQL  SET OPTION
                         Naming    = *Sys,
                         Commit    = *None,
                         UsrPrf    = *User,
                         DynUsrPrf = *User,
                         Datfmt    = *iso,
                         CloSqlCsr = *EndMod ;
    
           sqlStmt = 'SELECT * FROM ACCPTH WHERE APFILE = ?' ;
    
           exec SQL  DECLARE C1 CURSOR FOR stmt ;
           exec SQL  PREPARE stmt FROM :sqlStmt ;
    
           exec SQL  OPEN C1 USING :fetchFile ;
    
           if SQLCOD < *zero ;
              callp(E) SQLErrorMsg() ;
           endif ;
    
           dou SQLCOD > *zero ;
    
              exec SQL  FETCH C1 FOR 10 ROWS
                            INTO :ACCPTH:IND_ARRAY ;
    
              if SQLCOD < *zero ;
                 callp(E) SQLErrorMsg() ;
              else ;
                 for i = 1 to sqler3 ;
                    %occur( ACCPTH ) = i ;
                    dsply APLIB ;
                 endfor ;
              endif ;
    
           enddo ;
    
           exec SQL  CLOSE C1 ;
    
           *inlr = *on ;
           return ;
          /end-free
          *================================================================
         P SQLErrorMsg     b
          *
          *================================================================
          * Prototype/variables for sending a program message
          *================================================================
          *
         D QMHSNDPM        pr                  extpgm( 'QMHSNDPM' )
         D  pMsgId                             const like( MsgID )
         D  pMsgFile                     20a   const
         D  pMsgDta                     256    const
         D  pMsgDtaLen                   10i 0 const
         D  pMsgType                     10a   const
         D  pMsgStack                    10a   const
         D  pMsgStack#                   10i 0 const
         D  pMsgKey                            like( MsgKey )
         D  pMsgError                          like( dsErrCode )
          *
         D MsgId           s              7a
         D MsgKey          s              4a
          *==============================================================
          * Error Code Data Structure for API Calls
          *==============================================================
         DdsErrCode        ds
         D  BytesProvided                10i 0 inz( %size(MsgData) )
         D  BytesAvail                   10i 0
         D  ExceptionID                   7
         D  Reserved                      1
         D  MsgData                     128
          /free
            MsgID = 'SQL' + %Subst( %EditW( %Abs(SQLCOD) : '0         ' ) : 7 ) ;
    
           // 3 levels - subprocedure / program / calling program
            callp(E) QMHSNDPM( MsgId : 'QSQLMSG   *LIBL' : SQLERM : SQLERL
                             : '*ESCAPE' : '*' : 2 : MsgKey : dsErrCode ) ;
    
            return ;
          /end-free
         P SQLErrorMsg     e

    It reads a file named ACCPTH. I created it by running a DSPFD command for TYPE(*ACCPTH) to an *OUTFILE named ACCPTH. I have a number of files named LICKEYS, so the program is FETCHing those rows from the file 10 rows at a time.

    It uses DSPLY to display the library (APLIB) that each LICKEYS file is found in; but you can use any name you want, or you can change it to use your file instead of ACCPTH.

    I have a null-indicators array also coded. You probably don't need it, so you can delete those lines.

    Tom

    Tom

    125,585 pointsBadges:
    report
  • philpl1jb

    When you receive into an Occurs DS .. you will be seeing the value in the first occurance .. you need to step through the occurances with the Occurs command or function .. you also do a

    For I = 1 to sqler3; this increments I by adding 1 to it until it reaches sqler3

    followed by I = I + 1 the adds another 1 to I so you would be skipping looking at every other occurance .. if you were looking at occurances. 

    49,830 pointsBadges:
    report
  • as400dev
    Thanks Tom.
    I used the code you provided but I would like to know
    1. How to fetch the maximum no of records, instead of 10 mentioned in occurs.
    2. Also I need to use this program to load the particular field from the file based on the input parameter. For ex, If input parameter is field1, then I need to load the field details in to an array.

    Can you help me to modify accordingly.
    480 pointsBadges:
    report
  • philpl1jb

    Tom's code fetches 10 rows at a time (all fields) into the 10 occurances of the data structure DACCPTH.  Once he's checked those 10 his code loops and fetches the next 10. 

    If you wanted to get 100 at a time change the 2 occurs to 100 and the fetch row number to 100.

    Tom's program loads all fields from the file.  Subsequent code can use whichever columns you need.

     

    49,830 pointsBadges:
    report
  • as400dev
    Thanks Philip.

    But my requirement is something like , PGMA calls this program with 2 parameters 
    1.Input field (field1)
    2.Key field (LICKEYS)
    3.Output array. (need to load all the values of field1 from the result and pass it back)

    480 pointsBadges:
    report
  • TomLiotta
    1.Input field (field1)
    2.Key field (LICKEYS)

    What is "field1"? Is that a column name from FILENAME1? What is "Key field"? In my example, 'LICKEYS' is a value not a field name.

    Tom

    125,585 pointsBadges:
    report
  • as400dev
    But my requirement is something like , PGMA calls this program with 2 parameters 
    1.Input field (field1)
    2.Key field (LICKEYS)
    3.Output array. (need to load all the values of field1 from the result and pass it back)

    If a file1 has field1, field2 ,.. field10.
    FIELD1 FIELD2 FIELD3
    A TEXT1 NEW4
    A TEXT2 NEW6

    If i call the program with parameters ('FIELD1'  'A'  ' ')
    Then I need to retrieve back the values TEXT1 and TEXT2 in an array(as the 3rd parameter) 

    If i call the program with parameters ('FIELD3'  'A'  ' ')
    Then I need to retrieve back the values NEW4 and NEW6 in an array(as the 3rd parameter) 
    480 pointsBadges:
    report
  • TomLiotta

    You need to clarify that comment because there seems to be an error in it. It doesn't make sense.

    If (‘FIELD3′ ’A’ ’ ‘) matches with NEW4 and NEW6, then I don't see how (‘FIELD1′ ’A’ ’ ‘) matches TEXT1 and TEXT2.

    Tom

    125,585 pointsBadges:
    report
  • philpl1jb

    Assuming that all fields are the same type..

    Perhaps something like this ...

    for i = 1 to sqler3 ;  
    
       %occur( ACCPTH ) = i ;  
    
       Select;
         When InputField = "FIELD2";
    	myArray(i) = Field2; 
         When InputField = "FIELD3";
    	myArray(i) = Field3; 
    
      etc.
    
    endfor ;  
    49,830 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following