*****************************************************************
      *
      *  To compile:
      *
      *             CRTSQLRPGI PGM(XXX/SQL001RG) SRCFILE(XXX/QRPGLESRC)
      *
      *
      * COMPILING. In order to compile this program you will need
      *            to use options which allow it to work correctly
      *            between machines. These options are---
      *
      *               COMMIT = *NONE
      *               RDB    = Machine name that you will connect to.
      *               DLYPRP = *YES
      *               SQLPKG = The name & library that you want to
      *                        use for the package. This will put the
      *                        package on the RDB machine that you
      *                        specify.
      *****************************************************************
     FWebHits  cf   e             workstn
     F                                     sfile(sfl1:rrn1)
     F                                     infds(info)
      *
      * Information data structure to hold attention indicator byte.
      *
     Dinfo             ds
     D cfkey                 369    369
      *
      * Constants for attention indicator byte
      *
     D Exit            C                   const(X'33')
     D Prompt          C                   const(X'34')
     D Cancel          C                   const(X'3C')
     D Enter           C                   const(X'F1')
     D PageDown        C                   const(X'F5')

     D SelectOne       S            500A   INZ('SELECT requser, reqts,  -
     D                                     browser  -
     D                                     FROM webtemp2  -
     D                                     ORDER BY')

     D SelectTwo       S            500A   INZ(' ')

     D OrderBy         S             20A   INZ('requser')

     D SubfilePage     C                   Const(8)
     D Lastrrn         S                   Like(rrn1)
      *
      * Establish the connection to the remote machine. The -842 return
      * code indicates that the connection is already established. If
      * you want to connect to the local machine, use CONNECT RESET.

     C/EXEC SQL
     C+ CONNECT RESET
     C/END-EXEC
      *
     C                   ExSr      prep
     C                   ExSr      sflbld
      *
     C                   DoU       (cfkey = exit)
      *
     C                   Write     fkey1
     C                   ExFmt     sf1ctl
      *
     C                   Select
      *
      * prompt to selection sorting criteria
      *
     C                   When      cfkey = prompt
     C                   ExSr      sort
     C                   ExSr      clean
     C                   ExSr      prep
     C                   ExSr      sflbld
      *
     C                   When      cfkey = cancel
     C                   Leave
      *
     C                   When      cfkey = PageDown
     C                   ExSr      sflbld
      *
     C                   EndSl
      *
     C                   EndDo
      *
     C                   ExSr      clean
      *
     C                   Eval      *inlr = *on
      *
      *****************************************************************
      * Prepare SQL cursor
      *****************************************************************
      *
     C     prep          BegSr
      *
      * Clear the subfile
      *
     C                   Eval      *in31 = *on
     C                   Write     sf1ctl
     C                   Eval      *in31 = *off
     C                   Eval      rrn1 = 0
     C                   Eval      Lastrrn = 0
      *
      * Prepare the SQL statement for validation, since the program was
      * compiled with DLYPRP (*YES), it will wait until it is used before
      * it prepares th cursor.
      *
     C                   Eval      SelectTwo = %TRIMR(SelectOne) + ' '
     C                                         + OrderBy
      *
     C/EXEC SQL
     C+   PREPARE sel FROM :SelectTwo
     C/END-EXEC
      *
      * Declare the SQL cursor to hold the data retrieved from the SELECT
      *
     C/EXEC SQL
     C+ DECLARE MYCSR SCROLL CURSOR FOR SEL
     C/END-EXEC
      *
      * Open the SQL cursor.
      *
     C
     C/EXEC SQL
     C+ OPEN MYCSR
     C/END-EXEC
      *
     C                   EndSr
      *
      *****************************************************************
      * Clean up before exiting
      *****************************************************************
      *
     C     clean         BegSr
      *
      *  Close the SQL cursor after all processing is complete.
      *
     C/EXEC SQL
     C+   CLOSE mycsr
     C/END-EXEC
      *
     C                   EndSr
      *
      *****************************************************************
      * Build the subfile
      *****************************************************************
      *
     C     sflbld        BegSr
      *
     C                   Eval      rrn1 = Lastrrn
      *
      * Process the records in the SQL cursor until the return not = 0
      *
     C                   Do        SubfilePage
      *
      * Get the next row from the SQL cursor.
      *
     C/EXEC SQL
     C+   FETCH NEXT FROM mycsr
     C+      INTO :requser, :reqts, :browser
     C/END-EXEC
      *
     C                   If        sqlcod = 0
     C                   Eval      rrn1  = rrn1  + 1
     C                   Write     sfl1
     C                   Else
     C                   Leave
     C                   EndIf
      *
     C                   EndDo
      *
     C                   If        rrn1 = 0
     C                   Eval      *in32 = *on
     C                   Else
     C                   Eval      Lastrrn = rrn1
     C                   EndIf
      *
      * A code of 100 means end of file.
      *
     C                   If        sqlcod = 100
     C                   Eval      *in90 = *on
     C                   EndIf
      *
     C                   EndSr
      *
      *****************************************************************
      * SORT - prompt to select sort criteria
      *****************************************************************
      *
     C     sort          BegSr
      *
     C                   ExFmt     window1
      *
     C                   Select
      *
     C                   When      tab1 <> *blank
     C                   MoveL(p)  'requser'     OrderBy
     C                   Clear                   tab1
      *
     C                   When      tab2 <> *blank
     C                   MoveL(p)  'reqts'       OrderBy
     C                   Clear                   tab2
      *
     C                   When      tab3 <> *blank
     C                   MoveL(p)  'browser'     OrderBy
     C                   Clear                   tab3
      *
     C                   EndSl
      *
      *
     C                   EndSr
      *