Systems Analysis and Design Home
|
The database was implemented on the VAX computer system using the Rdb/VMS database management software. Rdb allows database maintenance using SQL and also supports the use of 3GL programming languages such as Cobol. We have used SQL to create the database and have written a Cobol data entry application to load records into it. Reporting may be done using either interactive SQL queries or Cobol applications. SQL commands to create the payroll database:create database filename 'payroll'; create table payroll ( ssn char(9) not null unique, fname char(20) not null, lname char(20) not null, address char(25) not null, city char(20) not null, state char(2) not null, zip char(5) not null, area_code char(3) not null, phone char(7) not null, ext char(4), dept char(4), salary integer ); create unique index payroll_index on payroll(ssn); Adding records to the database (2 options)
Sample: Using Interactive SQL to load records into the database
$ DEFINE SQL$DATABASE PAYROLL
$ SQL
SQL> insert into payroll values
cont> ('300670001','Gary','Theis','401 N. Beidler','Paw Paw',
cont> 'MI','49079','616','4251008','4001','3600',25000);
SQL> commit;
SQL> insert into payroll values
cont> ('300670002','Donna','Lee','3844 Brockton', 'Muskegon', 'OH',
cont> '45207','513','7226709','4002','0917',35000);
SQL> commit;
SQL> exit;
This type of SQL interface is probably not appropriate for most end-users! Instead a data entry program can be written. Sample 3GL Data Entry ApplicationOnce the database has been created records can be loaded into the payroll table using the data entry program. The data entry program is written in Cobol using embedded SQL statements. The following simple Cobol example creates a crude but functional data entry screen on a standard character-cell terminal.
identification division.
program-id. pr-data-entry.
author. Bill Rogers.
environment division.
configuration section.
source-computer. vax.
object-computer. vax.
special-names.
symbolic characters
ctrl-key-z
27.
data division.
working-storage section.
*
* host variables:
exec sql begin declare section end-exec.
01 scrn-ssn pic x(9).
01 scrn-fname pic x(20).
01 scrn-lname pic x(20).
01 scrn-address pic x(25).
01 scrn-city pic x(20).
01 scrn-state pic x(2).
01 scrn-zip pic x(5).
01 scrn-area-code pic x(3).
01 scrn-phone pic x(7).
01 scrn-ext pic x(4).
01 scrn-dept pic x(4).
01 scrn-salary pic s9(9).
exec sql end declare section end-exec.
*
* table declaration:
exec sql
declare payroll table (
ssn char(9) not null unique,
fname char(20) not null,
lname char(20) not null,
address char(25) not null,
city char(20) not null,
state char(2) not null,
zip char(5) not null,
area_code char(3) not null,
phone char(7) not null,
ext char(4),
dept char(4),
salary integer)
end-exec.
*
* required communication area:
exec sql include sqlca end-exec.
*
* program variables:
*
01 result pic s9(9) comp.
01 kbd-input pic 9.
88 valid-menu-response values 1 thru 2.
88 user-quits value 2.
01 ctrl-key pic x.
88 ctrl-z value ctrl-key-z.
01 ok-flag pic x.
88 valid-yes-no-response values 'Y' 'y' 'N' 'n'.
88 user-says-ok values 'Y' 'y'.
*
procedure division.
main-program section.
main-line.
perform display-menu.
perform data-entry
until user-quits.
stop run.
*
*
data-entry.
perform display-form.
perform get-data.
perform display-menu.
*
*
display-form.
perform clear-scrn.
display 'Payroll File' underlined line 2 column 33.
display 'Social Security Number:' line 5 column 5.
display ' ' reversed line 5 column 29.
display 'First Name:' line 7 column 5.
display ' ' reversed line 7 column 17.
display 'Last Name:' line 7 column 38.
display ' ' reversed line 7 column 49.
display 'Address:' line 9 column 5.
display ' ' reversed
line 9 column 17.
display 'City:' line 11 column 5.
display ' ' reversed line 11 column 17.
display 'State:' line 11 column 38.
display ' ' reversed line 11 column 45.
display 'Zip Code:' line 11 column 48.
display ' ' reversed line 11 column 58.
display 'Area Code:' line 13 column 5.
display ' ' reversed line 13 column 17.
display 'Telephone:' line 13 column 21.
display ' ' reversed line 13 column 32.
display 'Extension:' line 13 column 40.
display ' ' reversed line 13 column 51.
display 'Department:' line 15 column 5.
display ' ' reversed line 15 column 17.
display 'Salary:' line 15 column 30.
display ' ' reversed line 15 column 38.
*
*
get-data.
accept scrn-ssn reversed protected line 5 column 29.
accept scrn-fname reversed protected line 7 column 17.
accept scrn-lname reversed protected line 7 column 49.
accept scrn-address reversed protected line 9 column 17.
accept scrn-city reversed protected line 11 column 17.
accept scrn-state reversed protected line 11 column 45.
accept scrn-zip reversed protected line 11 column 58.
accept scrn-area-code reversed protected line 13 column 17.
accept scrn-phone reversed protected line 13 column 32.
accept scrn-ext reversed protected line 13 column 51.
accept scrn-dept reversed protected line 15 column 17.
accept scrn-salary protected with conversion
reversed line 15 column 38.
move space to ok-flag.
perform get-confirmation
until valid-yes-no-response.
*
*
get-confirmation.
display 'Add this record to the database? (Y or N) '
line 20 column 5 bold no advancing.
accept ok-flag.
if user-says-ok
perform do-sql.
*
*
do-sql.
exec sql
insert into payroll (ssn, fname, lname, address, city,
state, zip, area_code, phone, ext, dept, salary)
values (:scrn-ssn, :scrn-fname, :scrn-lname, :scrn-address,
:scrn-city, :scrn-state, :scrn-zip,
:scrn-area-code, :scrn-phone,
:scrn-ext, :scrn-dept, :scrn-salary)
end-exec.
*
*
display-menu.
move zero to kbd-input.
perform do-menu
until valid-menu-response.
*
*
do-menu.
perform clear-scrn.
display '1. Add a record' line 15 column 1.
display '2. Exit program' line 16 column 1.
display 'Enter menu number (1 or 2): '
line 18 column 1.
accept kbd-input protected with conversion
from line 18 column 30 reversed
control key in ctrl-key
on exception perform test-for-control-z.
*
*
test-for-control-z.
* allow control-z to be used to exit.
if ctrl-z
move 2 to kbd-input.
*
*
clear-scrn.
display ' ' line 1 column 1 erase to end of screen.
There are also two options for retrieving information from the database: SQL or a 3GL-based interface. SQL QuerySQL can now be used to query the database. Reports can be restricted to specific fields of interest or sorted as needed. SQL> select fname, lname, dept, ssn, salary from payroll; SQL> select fname, lname, dept, ssn, salary from payroll cont> order by lname; SQL> select fname, lname, dept, ssn, salary from payroll cont> order by ssn; 3GL Report WriterHowever, instead of simply using interactive SQL, the SQL commands can be embedded into an application written in a 3GL programming language such as Cobol. This allows the addition of column headings, control breaks, and formatting of numeric fields.
identification division.
program-id. pr-report.
author. Bill Rogers.
environment division.
configuration section.
source-computer. vax.
object-computer. vax.
special-names.
symbolic characters
ctrl-key-z
27.
input-output section.
file-control.
select print-file
assign to rms.
*
data division.
file section.
fd print-file
value of id 'report.lis'.
01 print-item pic x(80).
*
working-storage section.
*
* host variables:
exec sql begin declare section end-exec.
01 report-ssn pic x(9).
01 report-fname pic x(20).
01 report-lname pic x(20).
01 report-address pic x(25).
01 report-city pic x(20).
01 report-state pic x(2).
01 report-zip pic x(5).
01 report-area-code pic x(3).
01 report-phone pic x(7).
01 report-ext pic x(4).
01 report-dept pic x(4).
01 report-salary pic s9(9).
exec sql end declare section end-exec.
*
* table declaration:
exec sql
declare payroll table (
ssn char(9) not null unique,
fname char(20) not null,
lname char(20) not null,
address char(25) not null,
city char(20) not null,
state char(2) not null,
zip char(5) not null,
area_code char(3) not null,
phone char(7) not null,
ext char(4),
dept char(4),
salary integer)
end-exec.
*
* required communication area:
exec sql include sqlca end-exec.
*
* program variables:
*
01 result pic s9(9) comp.
01 ctrl-key pic x.
88 ctrl-z value ctrl-key-z.
01 records-flag pic x.
88 no-more-records value 'Y'.
01 line-count pic 9(4) comp.
*
01 header-1.
02 filler picture x(30) value spaces.
02 filler picture x(40) value 'Payroll Report by Name'.
*
01 header-2.
02 filler pic x(42) value
' --- First Name ----- --- Last Name ------'.
02 filler pic x(36) value
' Dept --- SSN --- --- Salary ---'.
*
01 detail-line.
02 filler pic x value space.
02 print-fname pic x(20).
02 filler pic x value space.
02 print-lname pic x(20).
02 filler pic x value space.
02 print-dept pic x(4).
02 filler pic x(3) value space.
02 print-ssn pic xxxbxxbxxxx.
02 filler pic x(3) value space.
02 print-salary pic zzz,zzz,zz9.99-.
*
* cursor definition: assigns a name to the result of sql query.
exec sql
declare employees cursor for
select fname, lname, dept, ssn, salary
from payroll
order by lname
end-exec.
*
procedure division.
main-program section.
main-line.
perform initialization-routine.
perform process-report
until no-more-records.
perform eoj.
stop run.
*
*
initialization-routine.
exec sql
open employees
end-exec.
open output print-file.
move space to records-flag.
perform print-headers.
perform fetch-routine.
*
*
process-report.
if line-count > 50
perform print-headers.
move report-fname to print-fname.
move report-lname to print-lname.
move report-ssn to print-ssn.
inspect print-ssn replacing all space by '-'.
move report-dept to print-dept.
move report-salary to print-salary.
write print-item from detail-line.
add 1 to line-count.
perform fetch-routine.
*
*
fetch-routine.
exec sql
fetch employees
into :report-fname, :report-lname, :report-dept,
:report-ssn, :report-salary
end-exec.
if sqlcode is not equal to zero
move 'Y' to records-flag.
*
*
eoj.
close print-file.
exec sql
close employees
end-exec.
*
*
print-headers.
write print-item from header-1 after advancing page.
write print-item from header-2 after 3.
move spaces to print-item.
write print-item.
move zero to line-count.
Sample command file to compile a Cobol program (PROGR1.COB) that does not include SQL:$ cobol/ansi progr1 $ link progr1
Command file to compile a Cobol program (PROGR2.SCO) with embedded SQL:$ define sql$database labdisk:[stu.rogers.sys]payroll.rdb $ define lnk$library sys$library:sql$user.olb $ run sys$system:sql$pre PROGR2 /COBOL/ANSI $ link progr2 The first DEFINE command specifies what database is to be used by assigning the logical name SQL$DATABASE to the physical database file PAYROLL.RDB. The second DEFINE command specifies that the LINK command will resolve external references in the object code by using the library modules in the file SYS$LIBRARY:SQL$USER.OLB. The RUN command executes the SQL Precompiler. The following line is an instruction to the Precompiler that specifies PR-DATA-ENTRY as the program to be precompiled and then compiled using the ANSI Cobol compiler. |