Cobol application using ODBC or File processing

1
Cobol application using ODBC orFile processing

• Vandana Janeja
• CIS 365
• With COBOL
• OR PowerCobol

2
ODBC from Control panel\administrative tools
ODBC from control panel\administrative tools
When you create a database in MS.Access be
careful not to use COBOL keywords as you will
need to access the fields by their name and COBOL
will give you an error if its a reserved word.
3
Creating an ODBC DSN name
4
Bringing out the control box from power Cobol
Creating a form in Power Cobol
5
Selecting controls to place on the form
Pulling out properties by double clicking the
control
6
Changing properties of the DBAccess control
specifically the DSN name specification
7
Data Control with the specifications of ODBC(DSN
name)
Pulling out the procedure for a control
currently no control is selected so the procedure
of the sheet/form will be opened so that we can
add global variables
8
Trapping the click event of the button
9
Adding the relevant code for database connection
button Similarly other code can be added to other
buttons.
10
DBAccess Control

• OPENDB Opens a data source.
• CLOSEDB Closes a data source.
• SELECTRECORDS Select the record group you want to
  process.
• READNEXTRECORD Reads records one by one from the
  record group selected by SELECTRECORDS.
• WRITERECORD Adds one record to the table.
• REWRITERECORD Updates a record in the table.
• DELETERECORD Deletes a record in the table.
• COMMITDB Performs commit processing in manual
  commit mode.
• ROLLBACKDB Performs roll-back processing in
  manual commit mode.

11
PUSH1 CLICK event procedure is DATA DIVISION.
WORKING-STORAGE SECTION. PROCEDURE DIVISION.
CALL OPENDB OF ODBC1. CALL
SELECTRECORDS OF ODBC1. .
. CALL CLOSEDB OF ODBC1.
12
PUSH1 CLICK event procedure for writing a record
DATA DIVISION. WORKING-STORAGE SECTION. 77
WORKNO PIC 9(4). 77 WORKNAME PIC X(32). 77
WORKPHONENO PIC X(64). PROCEDURE DIVISION.
. . . MOVE WORKNO TO
EMPLOYNO OF ODBC1.(OR Move POW-TEXT of Edit1 to
Empno of ODBC1) MOVE WORKNAME TO EMPLOYNAME
OF ODBC1. MOVE WORKPHONENO TO PHONENO OF
ODBC1. CALL WRITERECORD OF ODBC1.
Insert Record Button
13
For insert the ODBC should not be readOnly check
this in the DBAccess as ff
V.IMPORTANT ReadOnly option should be unchecked
if you have to insert records
14
PUSH1 CLICK event procedure is DATA DIVISION.
WORKING-STORAGE SECTION. 77 WORKNO PIC
9(4). 77 TEMPNO PIC 9(4) PROCEDURE
DIVISION. PERFORM LOOPCALL READNEXTRECORD OF
ODBC1. MOVE POW-TEXT OF EDIT1 TO TEMPNO.
MOVE EMPLOYNO OF ODBC1 TO WORKNO. IF
WORKNO TEMPNO THEN MOVE "Smith" TO
EMPLOYNAME OF ODBC1 MOVE "497-3497" TO
PHONENO OF ODBC1 END-IF. CALL
REWRITERECORD OF ODBC1.
Update Record Button
15
PUSH1 CLICK event procedure is DATA DIVISION.
PROCEDURE DIVISION. . IF PHONENO OF
ODBC1 "497-3497" THEN MOVE 1234 TO
EMPLOYNO OF ODBC1 MOVE "Brown" TO
EMPLOYNAME OF ODBC1 END-IF. CALL
DELETERECORD OF ODBC1. All records having
EMPLOYNO "1234" and EMPLOYNAME "Brown" are
deleted.
Delete Record button
16
Click on run and it will automatically compile
and build and link and basically create an exe
for you
17
Click ok to all messages and if there are no
errors you should see the output application
18
Cobol pseudocode / Flowchart for Cobol application
Accept choice from User Insert/Update/Delete
While Choice Not Exit
Update Process
Delete Process
Insert Process
Accept choice from User Insert/Update/Delete
19
IF choice I INSERTION IF
choiceD DELETION IF choiceU
UPDATING IF choiceP PRINTMODULE
Else Exit.
20
INSERTION
Accept Working or temporary variables
Check If record exists
If not Insert
File opening mode I-O , Read file first for
searching the record. Once not found use the
Write verb Else display a message that record
already exists
21
UPDATING
Accept Working or temporary variables
Check If record exists
If yes Update Old record with New Record
Use REWRITE EMPLOYEE-RECORD
22
DELETION
Accept Working or temporary variables
Check If record exists
If yes Delete

• Create a New file without the deleted record
• Have an additional field which can be set to D
  for a deleted record, and this file can be
  periodically renewed.

23
PRINTMODULE
24
Printing a Report.

• The report, will be based on the Data File
• The report will show the details of the records
  of students

• A report is made up of groups of printed lines of
  different types.
• What types of line are required for the Student
  Details Report?

25
Report Print Lines.

• Page Heading.
• UL Student Details Report
• Page Footing.
• Page PageNum
• Column Headings.
• Student Id. Student Name Gender Course
• Student Detail Line.
• StudentId. StudentName Gender CourseCode
• Report Footing.
• End of Student Details Report

26
Describing Print Lines.

• 01 PageHeading. 02 FILLER PIC X(7)
  VALUE SPACES. 02 FILLER PIC X(25)
  VALUE "UL Student Details Report".
• 01 PageFooting. 02 FILLER PIC X(19)
  VALUE SPACES. 02 FILLER PIC X(7) VALUE
  "Page ". 02 FILLER PIC 99.
• 01 ColumnHeadings PIC X(36) VALUE "
  StudentId StudentName Gender Course".
• 01 StudentDetailLine. 02 PrnStudId PIC
  BB9(7). 02 PrnStudName PIC BBX(10). 02
  PrnGender PIC BBBBX. 02 PrnCourse PIC
  BBBBX(4).
• 01 ReportFooting PIC X(38) VALUE "
  End of Student Details Report ".
• The Print Lines are all different record types!

27
The File Buffer

• All data coming from, or going to, the
  peripherals must pass through a file buffer
  declared in the File Section.
• The file buffer is associated with the physical
  device by means of a Select and Assign clause.
• In previous lectures we saw that the file
  bufferis represented by a record description (01
  level).
• But the different types of line that must appear
  on our report are declared as different record
  types.
• How can we declare these different record types
  in the File Section?

28
No VALUE clause in the FILE SECTION.

• Defining a file buffer which is used by different
  record types is easy (as we have seen).
• But !!
• These record types all map on to the same area of
  storage and print line records cannot share the
  same area of storage.
• Why? Because most of the print line record
  values are assigned using the VALUE clause and
  these values are assigned as soon as the program
  starts.
• To prevent us trying to use the VALUE clause to
  assign values to a File buffer COBOL has a rule
  which states that In the FILE SECTION, the
  VALUE clause must be used in condition-name
  entries only (i.e. it cannot be used to give an
  initial value to an item).

29
A Solution
We get round the problem as follows

• We define the print records in the
  WORKING-STORAGE SECTION.
• We create a file buffer in the FILE SECTION which
  is the size of the largest print record.
• We print a line by moving the appropriate print
  record to the file buffer and then WRITEing the
  contents of the file buffer to the device.

30
ENVIRONMENT DIVISION.INPUT-OUTPUT
SECTION.FILE-CONTROL. SELECT ReportFile
ASSIGN TO STUDENTS.RPT. DATA DIVISION.FILE
SECTION.FD ReportFile.01 PrintLine PIC
X(38). WORKING-STORAGE SECTION.01 PageHeading.
02 FILLER PIC X(7) VALUE SPACES. 02
FILLER PIC X(25) VALUE "UL Student
Details Report". 01 PageFooting. 02 FILLER
PIC X(19) VALUE SPACES. 02 FILLER
PIC X(7) VALUE "Page ". 02 FILLER
PIC 99. 01 ColumnHeadings PIC X(36) VALUE "
StudentId StudentName Gender Course". 01
StudentDetailLine. 02 PrnStudId PIC
BB9(7). 02 PrnStudName PIC BBX(10). 02
PrnGender PIC BBBBX. 02 PrnCourse PIC
BBBBX(4). 01 ReportFooting PIC X(38) VALUE
" End of Student Details Report ".
31
WRITE Syntax revisited.

• When we are writing to a printer or a print file
  we use a form of the WRITE command different from
  that we use when writing to a sequential file.

32
ENVIRONMENT DIVISION. INPUT-OUTPUT SECTION.
FILE-CONTROL. SELECT ReportFile ASSIGN TO
"STUDENTS.RPT" ORGANIZATION IS LINE
SEQUENTIAL. DATA DIVISION. FILE SECTION. FD
ReportFile. 01 PrintLine PIC X(40).
WORKING-STORAGE SECTION. 01 HeadingLine
PIC X(21) VALUE " Record Count Report". 01
StudentTotalLine. 02 FILLER PIC
X(17) VALUE "Total Students ". 02
PrnStudentCount PIC Z,ZZ9. 01 MaleTotalLine.
02 FILLER PIC X(17) VALUE "Total Males
". 02 PrnMaleCount PIC Z,ZZ9. 01
FemaleTotalLine. 02 FILLER PIC
X(17) VALUE "Total Females ". 02
PrnFemaleCount PIC Z,ZZ9.
MOVE StudentCount TO PrnStudentCountMOVE
MaleCount TO PrnMaleCountMOVE FemaleCount TO
PrnFemaleCount WRITE PrintLine FROM HeadingLine
AFTER ADVANCING PAGEWRITE PrintLine FROM
StudentTotalLine AFTER ADVANCING 2 LINESWRITE
PrintLine FROM MaleTotalLine AFTER ADVANCING 2
LINESWRITE PrintLine FROM FemaleTotalLine AFTER
ADVANCING 2 LINES.