Transcript ODBC - Rutgers University

Cobol application using ODBC
or
File processing
Vandana Janeja
CIS 365
With COBOL
OR PowerCobol
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 it’s a reserved word.
Creating an ODBC DSN name
Bringing out the control box from power Cobol
Creating a form in Power Cobol
Selecting
controls to
place on the
form
Pulling out properties by double
clicking the control
Changing properties of the DBAccess control;
specifically the DSN name specification
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
Trapping the click event of the button
Adding the relevant code for database connection button
Similarly other code can be added to other buttons.
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.
PUSH1 CLICK event procedure is:
DATA DIVISION.
WORKING-STORAGE SECTION.
PROCEDURE DIVISION.
CALL OPENDB OF ODBC1.
CALL SELECTRECORDS OF ODBC1.
.
.
CALL CLOSEDB OF ODBC1.
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).
Insert Record Button
PROCEDURE DIVISION.
.
.
.
MOVE WORKNO TO EMPLOYNO OF ODBC1.(OR Move POW-TEXT of
Edit1 to Empno of ODBC1)
MOVE WORKNAMETO EMPLOYNAME OF ODBC1.
MOVE WORKPHONENO
TO PHONENO OF ODBC1.
CALL WRITERECORD OF ODBC1.
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
PUSH1 CLICK event procedure is:
Update Record Button
DATA DIVISION.
WORKING-STORAGE SECTION.
77 WORKNO
PIC 9(4).
77 TEMPNO PIC 9(4)
PROCEDURE DIVISION.
PERFORM LOOP…………CALL 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.
PUSH1 CLICK event procedure is:
DATA DIVISION.
Delete Record button
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.
Click on run and it will automatically
compile and build and link and
basically create an exe for you
Click ok to all messages and if there are no errors
you should see the output application
Cobol pseudocode / Flowchart for Cobol application
Accept choice from User Insert/Update/Delete
While Choice
Not Exit
Insert Process
Update Process
Delete Process
Accept choice from User Insert/Update/Delete
IF choice =“I”
INSERTION
IF choice=“D”
DELETION
IF choice=“U”
UPDATING
IF choice=“P”
Else Exit.
PRINTMODULE
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
UPDATING
Accept Working or temporary variables
Check If record exists
If yes Update Old record with New Record
Use REWRITE EMPLOYEE-RECORD
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.
PRINTMODULE
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?
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 ***
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
02 FILLER
02 FILLER
01
ColumnHeadings PIC X(36)
VALUE " StudentId StudentName Gender Course".
01
StudentDetailLine.
02 PrnStudId
PIC
02 PrnStudName PIC
02 PrnGender
PIC
02 PrnCourse
PIC
01
ReportFooting
PIC X(38)
VALUE "*** End of Student Details Report ***".
PIC X(19) VALUE SPACES.
PIC X(7) VALUE "Page : ".
PIC 99.
BB9(7).
BBX(10).
BBBBX.
BBBBX(4).
• The Print Lines are all different record types!
The File Buffer
• All data coming from, or going to, the peripherals must pass
through a file buffer declared in the File Section.
ENVIRONMENT DIVISION.
• The file buffer is associated
INPUT-OUTPUT SECTION.
FILE-CONTROL.
with the physical device by
SELECT Printer
means of a Select and
ASSIGN TO “LPT1”.
Assign clause.
DATA DIVISION.
FILE SECTION.
• In previous
FD Printer.
01 PrintLine.
lectures we saw
????????????????
that the file buffer
is 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?
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).
A Solution
We get round the problem as follows;
• We define the print records in the WORKINGSTORAGE 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.
ENVIRONMENT DIVISION.
INPUT-OUTPUT SECTION.
FILE-CONTROL.
SELECT ReportFile ASSIGN TO “STUDENTS.RPT”.
DATA DIVISION.
FILE SECTION.
FD ReportFile.
01 PrintLine
PIC X(38).
DISK
WORKING-STORAGE SECTION.
01 PageHeading.
02 FILLER
PIC X(7) VALUE SPACES.
STUDENTS.RPT
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 ***".
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.
WRITE
RecordName



BEFORE


  AFTER



FROM
Identifier

 ADVANCING



 AdvanceNum


 MnemonicNa
 PAGE



 LINE   

  
LINES

  
 
me







 
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
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
MOVE MaleCount
TO
MOVE FemaleCount TO
WRITE PrintLine FROM
WRITE PrintLine FROM
WRITE PrintLine FROM
WRITE PrintLine FROM
PrnStudentCount
PrnMaleCount
PrnFemaleCount
HeadingLine
StudentTotalLine
MaleTotalLine
FemaleTotalLine
AFTER
AFTER
AFTER
AFTER
DISK
STUDENTS.RPT
Report".
= ".
= ".
= ".
ADVANCING
ADVANCING
ADVANCING
ADVANCING
PAGE
2 LINES
2 LINES
2 LINES.