RE: Looping through an SQL query - End of Recordset?

Cool.  That helps! Thanks Billy.

Brian.

-----Original Message-----
From: Billy Rowe [mailto:billyrowe@xxxxxxx] 
Sent: Monday, May 29, 2006 4:56 PM
To: RPG programming on the AS400 / iSeries
Subject: Re: Looping through an SQL query - End of Recordset?

   This is some simple code( fixed format :) ) for checking the cursor
state
   and if last row has been reached,
   used a Host Structure, this example processes 300 rows at a time:

          *  Constant for number of columns and elements in Host
structure
   array
        D CusColCnt       C                   Const(  12 )
        D CusAryElem      C                   Const( 300 )

          * Host Structure Array to hold columns retreived via SQL.
        D CusTaxR       E DS                  ExtName( CusTaxWrkF )
        D                                     Prefix( Ctx         )
        D                                     Occurs( CusAryElem  )
          D CurMoreRows     S              1a   Inz( @False )

        
 
*-----------------------------------------------------------------------
-
         *  Named Constants
        
 
*-----------------------------------------------------------------------
-
        D @True           C                   Const( '1'     )
        D @False          C                   Const( '0'     )
        D SqlStateOk      C                   Const( '00000' )
        D SqlStateNoRow   C                   Const( '02000' )
        D SqlErr5LastRow  C                   Const( 100     )

        
   *--------------------------------------------------------------------
         *
         * ProcessRows - Process Cursor Rows
         *
        C     ProcessRows   Begsr

         *   Loop through rows, fetching and processing blocks of data.
         *
        C                   Eval      CurMoreRows    =    @True
        C                   Dow       CurMoreRows    =    @True

        C                   Exsr      FetchCurRows

         * Save number of rows retreived
        C                   Eval      CurTopRow   =  SqlErrD( 3)

        C                   Select
                 <<<<<<   After you do your FETCH you can check the
cursor
   state  AND if last row for selection
                                                criteria has been
reached and
   then designate for reference in other processing......     >>>>>>
        C                   When      SqlStt       = SqlStateOk
        C                   If        SqlErrD( 5 ) = SqlErr5LastRow
        C                   Eval      CurMoreRows  = @False
        C                   EndIf
        C                   Exsr      ProCurRows

        C                   When      SqlStt       = SqlStateNoRow
        C                   Eval      CurMoreRows  = @False

        C                   Other
        C                   Eval      CurMoreRows  = @False
        C                   EndSl
        C                   EndDo

        C                   Endsr
   HTH,
   Billy

   Brian Piotrowski wrote:

 Can someone please tell me how I can loop through a returned result set
 of an SQL query until there are no more records left?

 Pseudo code-wise, I need something like this:

 SQL: Select * from myfile where myvalue = something

 If SQLCOD = 0 then
   Dow not end_of_query()
     (Do some processing)
     (Go to the next record)
   Loop
 End if

 I usually read a file until there are no more records, but I haven't
 done it yet with the returned recordset from an SQL statement.