This is a comprehensive example showing the use of CM and PM objects to develop a simple program to list each of the author’s associated with a particular publisher. Figure 21 below depicts the normal display of this screen. All references to Oracle tables are taken from the sample database Books which is listed in its entirety in Appendix A of this manual. PM and PM SQL_WINDOW effectively demonstrate the following capabilities in ACCENT R and Open SQL:
- The Joining of 4 Oracle Tables together
- An object-oriented Fill Screen
- ACCENT R’s Utility Routine Library
- Modular Code Libraries through Code Segments
- Modular Routines through the Process Section
- Modular Code Libraries with Arguments
- Scrolling Region Support
- Hot Key Functions
- Structured Programming Language (SPL) Code
- Interactive Command Language (ICL)
Figure 21 Comprehensive example
!-------------------------------------------------------------------------
! Title: CM SQL_WINDOW
! Description: Invoke the PM which lists the authors associated with a
! particular publisher.
!-------------------------------------------------------------------------
!
Clear the screen, establish the Oracle Server, invoke the PM objects
SQL_WINDOW
SCREEN KILL_SCREEN
USE ORACLE SERVER AS WAGNER
USE PM SQL_WINDOW
The CM object SQL_WINDOW clears the SMF screen buffers and declares the Oracle Server for use before invoking PM SQL_WINDOW.
!-------------------------------------------------------------------------
! Title: PM SQL_WINDOW
! Description: List the authors associated with a particular publisher using
! the sample database BOOKS.
!-------------------------------------------------------------------------
!===============
CONTROL SECTION
!===============
RELATE DS PUBLISHERS_ORA IN DBL NISDATA:BOOKS AS PUBLISHERS_ORA FOR GET RELATE DS BOOKAUTHOR_ORA IN DBL NISDATA:BOOKS AS BOOKAUTHOR_ORA FOR GET RELATE DS BOOKS_ORA IN DBL NISDATA:BOOKS AS BOOKS_ORA FOR GET RELATE DS AUTHORS_ORA IN DBL NISDATA:BOOKS AS AUTHORS_ORA FOR GET
The Control Section of the PM object SQL_WINDOW defines how the PM accesses other DBL objects such as DS objects. The Control Section effectively opens the tables and readies them for access. Oracle, RMS, and DB-MACH2 tables can be accessed simultaneously in this version of ACCENT R Open SQL.
!===============
DECLARE SECTION
!===============
ROW_CNT, INT, MAX
PUB_AUTH_ID, INT, MAX
NAME_ARRAY, CHAR, 60, OCCURS 99
MAX_ROWS, INT, 2, DATA 99
PUB_CODE, CHAR, 6
! Include the working variables for these generalized routine
INCLUDE SCROLL_LIST_DECLARE FROM CS SCROLL_LIST IN DBL NISDATA:BOOKS_ORA
INCLUDE ACCSTB_DECLARE FROM CS ACCSTB IN DBL NIS:ACCLIB
INCLUDE ACCADD_DECLARE FROM CS ACCADD IN DBL NIS:ACCUTI
LAYOUT SCREEN PUB_AUTH
/ FILL 2, 2, 14, 72, @SCN_NORMAL
/ SELECTION @SCN_REVERSE
/ FUNCTION HOT_KEYS
/ BORDER @SCN_BOLD, " Publisher’s Authors "
/ MESSAGE 2, 5, @SCN_NORMAL, "Publisher's Code:"
/ FIELD 2, 23, 6, @SCN_REVERSE, PUB_CODE:D, ENTER
/ HELP 4, 28, 5, 50, @SCN_NORMAL
/ TEXT "Enter the name of the Publisher Code. A list "
/ TEXT "of the authors associated with this publisher "
/ TEXT "will be listed on the window. Enter ‘/’ to get "
/ TEXT "a list of possible publisher codes "
/ PERFORM PUB_AUTH ALWAYS AFTER
/ MESSAGE 5, 5, @SCN_NORMAL, "Authors:"
/ MESSAGE 6, 5, @SCN_NORMAL + @SCN_UNDERLINE, "Last Name"
/ MESSAGE 6, 30, @SCN_NORMAL + @SCN_UNDERLINE, "First Name"
/ MESSAGE 14, 2, @SCN_NORMAL, &
"/to List,PF1 for Calculator,? for Help, ^R to Refresh, PF2 to Exit"
LAYOUT END
The Declare Section of the Process Module defines any working storage used by the Process Module. You’ll notice that the working storage associated with the SCROLL_LIST routine is stored in the ACCENT R Examples Library NISDTA:BOOKS_ORA. The working storage for the scrolling table ACCSTB and the pop-up calculator ACCADD is also included.
The Layout Screen statement for the PUB_AUTH window allows input of the publishers code to be looked up. This value is stored in the field PUB_CODE. Regardless of what the user enters, the routine PUB_AUTH is executed. The table join is performed in this routine.
!====================
PROCESS SECTION
!====================
! Include the procedural code for the Scrolling table (ACCSTB) and
! the pop-up calculator (ACCADD).
INCLUDE ACCSTB_PROCESS FROM CS ACCSTB IN DBL NIS:ACCLIB
INCLUDE ACCADD_PROCESS FROM CS ACCADD IN DBL NIS:ACCLIB
!---------------------------
ROUTINE AUTHOR_SCROLL_LIST
!---------------------------
! Include a special version of the scrolling table with arguments for
! displaying the authors associated with a particular publisher.
INCLUDE SCROLL_LIST_PROCESS FROM CS SCROLL_LIST & IN DBL NISEXM:ACCEXAMPLES
WITH 'NAME_ARRAY:D', '5', '40', &
'"Authors"', '9', '3', 'SCROLL_LIST_POS:D'
!------------------------------
ROUTINE PUBLISHER_SCROLL_LIST
!------------------------------
! Call the scrolling table routine in order to display a list of ! publishers
! when a "/" is typed in the publisher field.
25 TO ACCSTB_COL:D; 15 TO ACCSTB_COL_NUM:D
"Publishers" TO ACCSTB_HEADER:D
4 TO ACCSTB_ROW:D; 1 TO ACCSTB_ROW_NUM:D
PERFORM ACCSTB
!--------------------
ROUTINE HOT_KEYS
!--------------------
! This routine is called directly from the FILL window layout.
! This routine displays the pop-up calculator whenever the PF1
! key is entered.
PERFORM ACCADD IF @SCN_FUNCTION_CODE = @SCN_FUNCTION_LIST(1)
The Process Section of the PM contains modules of code which perform individual functions. The procedural code for the standardized scrolling table and pop-up calculator from the ACCENT R libraries is included in this section. A version of the scrolling table is called with arguments to display a list of authors. The standard scrolling table routine is called to display a list of publisher codes for selection.
The HOT_KEYS routine is invoked automatically when the PF1 key field is entered. The key is changeable through the system fields @SCN_FUNCTION_LIST(n) in the Detail Section of the PM.
!--------------------
ROUTINE PUB_AUTH
!--------------------
! This routine is called directly from the FILL window layout and
! performs the SQL SELECT statement necessary to join the tables
! within the sample database BOOKS.
! They typed a "/" so look up the publisher codes for display
IF:5 @SCN_TERM_CODE = @SCN_TERM_LIST(3)
SQL SELECT PUB_CODE FROM PUBLISHERS_ORA ORDER BY "PUB_CODE"
START:10 FOR ACCSTB_POS:D = 1 TO MAX_ROWS:D
SQL FETCH NEXT ROW
LEAVE:10 IF @SQLCODE # 0
PUB_CODE:PUBLISHERS_ORA TO ACCSTB:D(ACCSTB_POS:D) HUSH
REPEAT:10
PERFORM PUBLISHER_SCROLL_LIST
ACCSTB:D(ACCSTB_POS:D) TO PUB_CODE:D UNLESS ACCSTB_POS:D = 0
! They typed a PF2 so let's leave
ORIF:5 @SCN_TERM_CODE = @SCN_END_CODE
! They typed a publisher code so see if it exists
ELSE:5
SQL SELECT PUB_CODE FROM PUBLISHERS_ORA &
WHERE "PUBLISHERS_ORA.PUB_CODE = '" + PUB_CODE:D + "'"
SQL FETCH NEXT ROW
! Look for authors if the publishers ID exists. This requires that we
! select on the publishers table in order to find the books which are
! handled by this publisher. The title codes of the books is then used
! to find the authors of the books in the book to author table. Finally,
! this author code is used to select the author’s name from the authors
! table.
IF:10 @SQLCODE = 0
SQL SELECT DISTINCT AUTHORS_ORA.AUTHOR_LNAME,
AUTHORS_ORA.AUTHOR_FNAME &
FROM PUBLISHERS_ORA, BOOKS_ORA, BOOKAUTHOR_ORA, AUTHORS_ORA &
WHERE "PUBLISHERS_ORA.PUB_CODE = '" + PUB_CODE:D + "'" +&
" AND PUBLISHERS_ORA.PUB_CODE = BOOKS_ORA.PUB_CODE" + &
" AND BOOKS_ORA.TITLE_CODE = BOOKAUTHOR_ORA.TITLE_CODE" + &
" AND BOOKAUTHOR_ORA.AUTHOR_CODE = AUTHORS_ORA.AUTHOR_CODE" &
ORDER BY "AUTHORS_ORA.AUTHOR_LNAME, AUTHORS_ORA.AUTHOR_FNAME"
SQL FETCH NEXT ROW
! Display the values on the screen if the select was successful. Each
! matching row in the set will be fetched and loaded into an array.
IF:20 @SQLCODE = 0
START:30 FOR ROW_CNT:D = 1 TO MAX_ROWS:D
LEAVE:30 IF @SQLCODE # 0
AUTHOR_LNAME:AUTHORS_ORA + ', ' + AUTHOR_FNAME:AUTHORS_ORA TO &
NAME_ARRAY:D(ROW_CNT:D) HUSH
SQL FETCH NEXT ROW
REPEAT:30
! Tell them that no authors were found
ELSE:20
"Publisher has no associated authors" TO NAME_ARRAY:D(1)
CONTINUE:20
! Tell that the publisher code was not found
ELSE:10
"Publisher does not exist" TO NAME_ARRAY:D(1)
CONTINUE:10
! Allow the user to move up and down through the authors names.
PERFORM AUTHOR_SCROLL_LIST
CONTINUE:5
Routine PUB_AUTH controls the actual joining of the tables. It calls the routines PUBLISHER_SCROLL_ LIST and AUTHOR_SCROLL_LIST which display the rows on the screen. Routine PUB_AUTH is called automatically from the FILL screen each time the cursor is moved to the publisher’s code field.
!====================
DETAIL SECTION
!====================
! Start the transaction
SQL BEGIN WORK READ ONLY
! Initialize the function keys
256 TO @SCN_FUNCTION_LIST(1) !PF1 key 257 TO @SCN_FUNCTION_LIST(2) !PF2 key 257 TO @SCN_END_CODE !PF2 key
47 TO @SCN_TERM_LIST(3) !"/" key
! Generate the window image and test for an error
0 TO @ERROR_NUMBER
GENERATE PUB_AUTH:D TO PUB_AUTH_ID:D
TYPE "Unable to open screen form PUB_AUTH " IF @ERROR_NUMBER # 0
! Display the window image and display the authors for each publisher
! specified.
FILL PUB_AUTH_ID:D FOR ENTER
! Commit the transaction just in case we didn't retrieve all
! of the results.
SQL COMMIT WORK
The Detail Section is the point where code execution within this example starts. After starting a read only transaction for Oracle, the function keys are initialized, and the screen is initialized through the Generate statement. The Fill statement is the only SPL statement necessary for the screen to be placed on the user’s terminal and the process to begin.
