ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

SQL in RPGLE

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • SQL in RPGLE

    Hello everyone.
    How can I use the SQL statement in RPGLE member?
    I have tried to enter very simple statement like:
    C/EXEC SQL
    C+ SELECT *
    C+ FROM F_PRAC
    C/END-EXEC
    but the compiler (when I try to create a module) shows error: "Compiler directive not recognized".

    In the member, there are subroutines that add, update, delete etc. records to/from F_PRAC pf. All are written using Free-Form. Now I want to use SQL to display records from the file (all records or expected ones). Can I do that inside Free-Form? Do I need to change RPGLE to SQLRPGLE? If I do that, will subroutines work correct?

  • #2
    Re: SQL in RPGLE

    make your source member type SQLRPGLE

    Yes subroutines will work there

    check out these examples




    Jamie
    All my answers were extracted from the "Big Dummy's Guide to the As400"
    and I take no responsibility for any of them.

    www.code400.com

    Comment


    • #3
      Re: SQL in RPGLE

      I've changed a member type to SQLRPGLE and tried to enter statement:
      C/EXEC SQL Set Option Commit = *None
      C/END-EXEC
      C/EXEC SQL
      C+ SELECT *
      C+ FROM F_PRAC
      C/END-EXEC
      but there is an error: "Position 9 INTO clause missing from embedded statement
      ".
      I don't want to get the result into variable (by the way, what kind of variable should it be, to keep the above statement result?). All I want is to display the result on the screen. How can I do that? Do I have to use DDS for that? But how?
      I am a beginner and have no idea for that

      Comment


      • #4
        Re: SQL in RPGLE

        rafi,

        You have to define a cursor.

        you can define a datastructure using DDS to the file you are reading

        then you will select into this data structure.

        use the FETCH procedure to do this.

        Please review some of the code on the link I posted earlier.

        you need only 4 subroutines

        Here is an example of exactly what u r trying to do
        Code:
             FRUSF072A  O  A E           K DISK
        
        
             D PRMDTA          DS
        
             D  @PRDG1                 1      5
             D  @PRDG2                 6     10
             D  @LOW_MI_DSM           11     13
             D  @HIGH_MI_DSM          14     16
             D  @PRIME1               17     22
             D  @PRIME2               23     28
             D  @PRIME3               29     34
             D  @PRIME4               35     40
             D  @THANDLER             41     41
             D  @TMREP1               42     44
             D  @TMREP2               45     47
        
             D SRLDA         E DS                  EXTNAME(SRDLDA)
             D  XXFDAT                        6  0 OVERLAY(LDUSR1:16)
             D  XXTDAT                        6  0 OVERLAY(LDUSR1:22)
        
             D                SDS
             D PGMNAME                 1     10
        
        
             DINVDETL        E DS                  EXTNAME(SROISDPL)
        
             D ISO             S               D
             D @FDATE          S              8  0
             D @TDATE          S              8  0
        
             C                   EXSR      SQLOPEN
        
             C                   EXSR      GETDETAIL
        
             C                   EXSR      SQLCLOSE
        
             C                   MOVE      *ON           *INLR
        
             C/EJECT
        
             C     GETDETAIL     BEGSR
        
              * Read selected invoice detail records
        
             C                   EXSR      GET
             C     SQLCOD        DOWEQ     0
        
             C                   IF        IDAMOU <> 0
        
             C                   CLEAR                   TYPE
        
             C                   SELECT
             C                   WHEN      IDCCA1 = @PRIME1 OR IDCCA1 = @PRIME2 OR
             C                             IDCCA1 = @PRIME3 OR IDCCA1 = @PRIME4
             C                   EVAL      TYPE = '2'
        
             C                   WHEN      %SUBST(IDHAND:1:1) <> @THANDLER AND
             C                             IDSALE >= @LOW_MI_DSM AND
             C                             %SUBST(IDSALE:1:1) <> %SUBST(@TMREP1:1:1)
             C                   EVAL      TYPE = '3'
        
             C                   WHEN      %SUBST(IDHAND:1:1) = @THANDLER AND
             C                             IDSALE >= @LOW_MI_DSM AND
             C                             %SUBST(IDSALE:1:1) <> %SUBST(@TMREP1:1:1)
             C                   EVAL      TYPE = '4'
        
             C                   WHEN      %SUBST(IDHAND:1:1) = @THANDLER AND
             C                             IDSALE >= @TMREP1 AND IDSALE <= @TMREP2
             C                   EVAL      TYPE = '5'
             C                   ENDSL
        
              * Reverse credit memo amount
        
             C                   IF        IDTYPP = 2
             C                   EVAL      IDQTY  = IDQTY  * -1
             C                   EVAL      IDAMOU = IDAMOU * -1
             C                   END
        
             C                   WRITE     R072A
             C                   ENDIF
        
             C                   EXSR      GET
             C                   ENDDO
             C                   ENDSR
        
             C/EJECT
             C     *INZSR        BEGSR
        
             C     *DTAARA       DEFINE    *LDA          SRLDA
             C                   IN        SRLDA
        
              * Convert entered date range to CCYYMMD and report headings
        
             C     *MDY          MOVE      XXFDAT        ISO
             C                   MOVE      ISO           @FDATE
             C     *MDY          MOVE      XXTDAT        ISO
             C                   MOVE      ISO           @TDATE
        
        
             C     KEY           KLIST
             C                   KFLD                    PRMTYP
             C                   KFLD                    PSARCH
        
             C                   EVAL      PRMTYP = 'RPGPGM'
             C                   EVAL      PSARCH = PGMNAME
        
              * Get parameter definition record
        
             C     KEY           CHAIN     XABCTLPM
        
        
             C                   ENDSR
             C/EJECT
        
             C     SQLOPEN       BEGSR
        
              * Execute SQL prepare and open statement
        
        
             C/EXEC SQL
             C+ DECLARE A CURSOR FOR
             C+  SELECT *
             C+  FROM SR3ISD
             C+  WHERE IDIDAT BETWEEN :@FDATE AND :@TDATE AND
             C+        IDPGRP BETWEEN :@PRDG1 AND :@PRDG2 AND
             C+        IDSALE <=      :@HIGH_MI_DSM AND
             C+        IDFOCC <> 'Y'
             C/END-EXEC
        
             C/EXEC SQL
             C+   OPEN A
             C/END-EXEC
        
             C                   ENDSR
        
             C/EJECT
        
             C     GET           BEGSR
        
              * Get invoice detail records using dealer cursor
        
             C/EXEC SQL
             C+   FETCH A INTO :INVDETL
             C/END-EXEC
        
             C                   ENDSR
        
        
             C/EJECT
             C     SQLCLOSE      BEGSR
        
              * Execute close of cursor
        
             C/EXEC SQL
             C+   CLOSE  A
             C/END-EXEC
        
             C                   ENDSR
             C/EJECT
        All my answers were extracted from the "Big Dummy's Guide to the As400"
        and I take no responsibility for any of them.

        www.code400.com

        Comment


        • #5
          Re: SQL in RPGLE

          It works.
          Thanks for tips. You're my life-saver .

          Comment


          • #6
            Re: SQL in RPGLE

            If you're only going to retrieve a single record, then rather than define a cursor, just use Select thsField into :thtField from thsFile--as the compiler error signified, you were missing the INTO clause, which means you didn't have a receiver field for the field(s) you were retrieving. But if you were going to be reading more than one record, then you'll need the cursor.

            Comment


            • #7
              Re: SQL in RPGLE

              I want to fetch a recordset into a ds that comes from ExtName of two files.

              Any suggestions with DS syntax?

              PHP Code:
              d dsInv         E Ds                  ExtName(InvInz           
              d dsSet         E Ds                  ExtName
              (SetInz           

              d ds1             ds                  qualified          
              d   dsInv                                             
              d   dsSet                             overlay
              (ds1:*next)                   

              c/EXEC SQL                                             
               
              Set Option Commit = *NONE                           
               
              + DECLARE MyCursor1 CURSOR FOR                        
               +    
              SELECT FROM INV join SET 
               
              +    on INV.INUMBR =  SET.ISETNO                
               
              +    WHERE INV.PPKTYP 'P'                        
               
              Open MyCursor1                                      
              c
              /End-Exec                                             

              c                   DoU       SQLCOD 
              <> 0        
              c
              /Exec Sql                                       
               
              Fetch Next From MyCursor1 Into :ds1 

              Comment


              • #8
                Re: SQL in RPGLE

                Hi,

                Why not simply doing a direct fetch into the 2 external data structures?

                PHP Code:
                Exec SQL  Fecth Next From MyCursor1 Into :DSInv, :DSSet
                Birgitta

                Comment

                Working...
                X