Fill This Form To Receive Instant Help
Fill This Form To Receive Instant Help
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. 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 $)
Please download the answer file using this link
https://drive.google.com/file/d/19sp77HAJWLiWZ2S7ksVLKhNCBN-cZ4Tj/view?usp=sharing
