Homework answers / question archive / BCI433 Lab 9 Lab objectives:    - Embed SQL statements in two RPGLE programs - Provide related RPGLE code for the embedded SQL statements   Requirements to pass the lab:   Successfully run the PhoneSQL program after copying supplied code and then adding the embedded SQL code and related RPGLE code that was given out in class

BCI433 Lab 9 Lab objectives:    - Embed SQL statements in two RPGLE programs - Provide related RPGLE code for the embedded SQL statements   Requirements to pass the lab:   Successfully run the PhoneSQL program after copying supplied code and then adding the embedded SQL code and related RPGLE code that was given out in class

Computer Science

Share With

---

BCI433 Lab 9

Lab objectives: 

 

- Embed SQL statements in two RPGLE programs

- Provide related RPGLE code for the embedded SQL statements

 

Requirements to pass the lab:

 

Successfully run the PhoneSQL program after copying supplied code and then adding the embedded SQL code and related RPGLE code that was given out in class. Be able to set the overflow line at 30 before running your program.

 

Successfully run the ProvSQL SQL cursor program after copying supplied code and then adding the embedded SQL code and related RPGLE code that was given out in class.

PhoneSQL

Input: CUSTOMER20 (accessed by RPGLE read statement)

 

 

CONTACTS20 (accessed through embedded SQL statements)

 

 

 

Output:  Two Pages

 

Long and short field names available to use with CONTACTS20

 

 

 

 

 

    

            // **************************************************************************

     //  CUSTOMER20 READ BY NATIVE LANGUAGE (ILE RPG)

     //  CONTACTS20 ROW RETRIEVED WITH EMBEDDED SQL

     //  DETAIL REPORT LINE INCLUDES INFORMATION FROM CUSTOMER20 AND CONTACTS20

     //  SUMMARRY REPORT INFORMATION RETRIEVED WITH EMBEDDED SQL STATEMENTS

     // **************************************************************************

    

        DCL-F PHNREPORT PRINTER OFLIND(*IN01) ;

 

        DCL-F CUSTOMER20 DISK(*EXT) KEYED USAGE(*INPUT)

              RENAME(CUSTOMER20:CUSTR);

        DCL-S Dummy  Zoned(1);

 

         // data structure for host variables from CONTACTS20

 

        ___________________________________________________________

 

       

        // Standalone fields for indicator variables

   

        ________________________________________________________

 

        ________________________________________________________

    

 

 

 

        //**************************************************************************

        //*                        ***   M A I N   R O U T I N E   ***

        //**************************************************************************    

 

       EXSR SummaryInfo;

       WRITE NEWPAGE;

       READ CUSTOMER20;

 

       DOW NOT %EOF;

           EXSR SQLSelect;

           IF *INOF = *ON;

               WRITE NEWPAGE;

               *INOF = *OFF;

           ENDIF;

           WRITE RPTLINE;

           READ CUSTOMER20;

        ENDDO;

 

        WRITE SUMMARY;

 

        *INLR = *ON;

        RETURN;

 

 

 //*************************************************************************

 //   S Q L S E L E C T   S U B R O U T I N E

 //********************************************************************

 // A row from the contacts table has the same customer number found in the record  

 // read from the CUSTOMER20 file. That row is retrieved to find out the last date

 // called, phone number, comments and the salesperson number.                             

                                                                                    

 //  The call back interval is added to the last date called to determine the      

 //  next date to call.  Since null values may be stored in the last date called   

 // indicator variables are used.                                                  

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

        //**********************************************************************

        // S U M M A R Y I N F O   S U B R O U T I N E

        //**********************************************************************

                         BEGSR  SummaryInfo;

        //  D E T E R M I N E   T O T A L S   F O R   CONTACTS20 & CUSTOMER20

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 // D E T E R M I N E   N U M B E R   O F   U N K N O W N   L A S T   D A T E 

 //  C A L L E D   R E C O R D S                                               

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 //  D E T E R M I N E   O L D E S T   &  M O S T   R E C E N T  L A S T

 //   C A L L B A C K   D A T E S                                        

                                                                         

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

// D E T E R M I N E   T H E   U S E R,  S E R V E R,  &   T I M E S T A M P

 

 

 

 

 

 

 

 

PHNREPORT (code available in BCI433LIB/LAB92020)

     A          R NEWPAGE                                                                          

     A                                  2  6'User:'                                                

     A                                  1  1'Timestamp:'                                           

     A                                  3  3'Server:'                                              

     A                                  1105'Page:'                                                

     A                                  1112PAGNBR                                                 

     A            TIMESTAMP       Z     1 12                                                       

     A            USER          10      2 12                                                        

     A            SERVER        10      3 12                                                       

     A                                  2 48'P H O N E   L O G   R E P O R T'                      

     A                                  5  3'Sales'                                                

     A                                  6  4'ID'                                                   

     A                                  5 11'Customer'                                              

     A                                  5 27'Customer Name'                                        

     A                                  6 11'Number'                                               

     A                                  6 78'Called'                                               

     A                                  5 59'Telephone'                                            

     A                                  5 77'Last Date'                                             

     A                                  5 90'Oldest Call'                                          

     A                                  6 94'Date'                                                 

     A                                  5103'Next Call'                                            

     A                                  6105'Date'                                                 

     A                                  7131'*'                                                     

     A          R RPTLINE                   SPACEB(1)                                              

     A            CSTSLN    R              3REFFLD(CONTACTS20/CSTSLN +                             

     A                                      BCI433LIB/CONTACTS20)                                  

     A            CSTNUM    R             11                                                       

     A                                      REFFLD(CUSTOMER20/CSTNUM +                              

     A                                      BCI433LIB/CUSTOMER20)                                  

     A            CSTNAM    R             22REFFLD(CUSTOMER20/CSTNAM +                             

     A                                      BCI433LIB/CUSTOMER20)                                  

     A            CSTPHN    R             57REFFLD(CONTACTS20/CSTPHN +                             

     A                                      BCI433LIB/CONTACTS20)                                   

     A            PHNLDC    R             77REFFLD(CONTACTS20/PHNLDC +                             

     A                                      BCI433LIB/CONTACTS20)                                  

     A            HIGHLIGHT      8        91                                                       

     A            NEXTCDATE       L      103                                                       

     A          R SUMMARY                   SPACEB(3)                                               

     A                                    12'S U M M A R Y   I N F O R M A T I -                   

     A                                      O N'                                                   

     A                                     2'CONTACTS20 Total Records:'                            

     A                                      SPACEB(2)                                              

     A            CONTACTT       5  0     27EDTCDE(1)                                               

     A                                    44'Oldest Last Callback Date:'                           

     A            OLDESTDATE      L       71                                                       

     A                                     2'CUSTOMER20 Total Records:'                            

     A                                      SPACEB(1)                                              

     A            CUSTOMERT      5  0     27EDTCDE(1)                                               

     A                                    39'Most Recent Last Callback Date:'                      

     A            MOSTRECENT      L       71                                                       

     A                                     2'Total Records with an Unknown Last-                   

     A                                       Callback Date:'                                       

     A                                      SPACEB(2)                                              

     A            UNKNOWNT       5  0     52EDTCDE(1)                                              

                                                                                                   

                                                                      

ProvSQL

 

INPUT (from three tables)

 

Manitoba Customers

 

 

 

Ontario Customers

 

 

 

 

Quebec Customers

CUSTOMER ID  FIRST NAME       LAST             CITY                   PURCHASE   PURCHASE 

                              NAME                                    AMOUNT     DATE     

  111111     Jacques          Trembley         Montreal                 122.33   01/01/20 

  122222     Sarah            Singh            Dorval                   233.66   12/31/19 

  133333     Rene             Lemieux          Quebec City              336.66   01/16/20 

  222222     Sandy            Thomas           Lachine                  445.54   12/14/19 

 

CALL PROVSQL 400

OUTPUT:

 

 

 

PROGRAM:

       //**************************************************************************

       //* THIS PROGRAM USES A CURSOR TO LOAD A TEMPORARY RESULT TABLE FROM 3

       //* SEPARATE TABLES, ONTARIO, QUEBEC AND MANITOBA. A NUMBER IS PASSED  

       //* TO THE PROGRAM TO DETERMINE WHICH RECORDS ARE INCLUDED FROM THE 3 TABLES

       //**************************************************************************  

           DCL-F PROVREPORT PRINTER OFLIND(*IN01) ;

           DCL-S ProvinceH    Char(10);

           DCL-S EndOfFile    IND;

           DCL-S TotalRecords PACKED(5:0) ;

          // LowLimit is passed to the program       

 

 

 

 

 

          // All Host Variables available under a single name   

 

 

 

 

 

 

 

 

 

 

     C/EJECT    

C*                        ***   M A I N   R O U T I N E   ***                       *

      /FREE

 

          ______________________________________________

          EXSR PrepareFiles;

          Write ReportHdg;

          Write RColumns;

          EXSR GetRow;

          Write NewProv;

          ProvinceH = Province;

          DOW NOT EndOfFile;

              IF *IN01 = *ON;

                  Write Title;

                  Write RColumns;

                  *IN01 = *OFF;

              ENDIF;

              // Province may change

 

 

 

 

 

 

 

 

 

              TotalPurch     = TotalPurch     + Purchase;

              TotalRecords= TotalRecords + 1;

              EXSR GetRow;

          ENDDO;

          Write Totals;

          EXSR   WRAPUP;

          Write UnderLimit;

          *INLR = *ON;

          RETURN;

        // O P E N F I L E S   S U B R O U T I N E     

         BEGSR  PrepareFiles;

        // S E T   U P   T H E   T E M P O R A R Y   R E S U L T  S T R U C T U R E

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

        // A   T E M P O R A R Y   R E S U L T   T A B L E   I S   C R E A T E D

 

 

 

 

 

 

 

 

 

            ENDSR;

 

        //   G E T     R O W    S U B R O U T I N E

           BEGSR     GETROW;

 

 

 

 

 

 

 

             ENDSR;

 

 

       // W R A P U P     S U B R O U T I N E

       

           BEGSR WRAPUP;

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

          ENDSR;

 

PROVREPORT (code available in BCI433LIB/LAB92020)

 

     A          R REPORTHDG

     A                                  2 39'C u s t o m e r s   >'

     A            LOWLIMIT       7  2   2 62EDTCDE(1 $)

     A          R RCOLUMNS                  SPACEB(2)

     A                                     4'Province'

     A                                    19'Customer ID'

     A                                    36' Full Name'

     A                                    62'City'

     A                                    83'Purchase'

     A                                   100'Purchase'

     A                                    84'Amount'

     A                                      SPACEB(1)

     A                                   102'Date'

     A          R NEWPROV                   SPACEB(1)

     A            PROVINCE      10         3

     A          R DETAIL

     A            CUSTID    R             20

     A                                      REFFLD(ONTARIO/CUSTID BCI433LIB/ONT-

     A                                      ARIO)

     A                                      SPACEB(1)

     A            FULLNAME      31        28

     A            CITY      R             60REFFLD(ONTARIO/CITY BCI433LIB/ONTAR-

     A                                      IO)

     A            PURCHASE  R             83REFFLD(ONTARIO/PURCHASE BCI433LIB/O-

     A                                      NTARIO)

     A                                      EDTCDE(1)

     A            PDATE     R             99

     A                                      REFFLD(ONTARIO/PDATE BCI433LIB/ONTA-

     A                                      RIO)

     A          R UNDERLIMIT                SPACEB(2)

     A                                    46'C u s t o m e r s   <='

     A            LOWLIMIT       7  2     70EDTCDE(1 $)

     A                                     6'Ontario'

     A                                      SPACEB(2)

     A                                    23'Quebec'

     A                                    39'Manitoba'

     A            ONTTOTAL       3  0      8SPACEB(2)

     A                                      EDTCDE(1)

     A            QUETOTAL       3  0     24EDTCDE(1)

     A            MANTOTAL       3  0     40EDTCDE(1)

     A          R TOTALS                    SPACEB(2)

     A                                    67'Total Amount:'

     A            TOTALPURCH     8  2     82EDTCDE(1 $)