Docstoc

ODBC

Document Sample
ODBC Powered By Docstoc
					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
                        Pulling out the procedure for a
ODBC(DSN name)
                        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).           Insert Record Button
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.
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”    PRINTMODULE
Else Exit.
                       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                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!
                   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.
  with the physical device by            FILE-CONTROL.
                                              SELECT Printer
  means of a Select and                           ASSIGN TO “LPT1”.
  Assign clause.                         DATA DIVISION.
• In previous                            FILE SECTION.
                                         FD Printer.
  lectures we saw                        01 PrintLine.
                                                ????????????????
  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 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.
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.                           DISK
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 FROM Identifier 
                                        LINE        
                             AdvanceNum              
                                         LINES
                                                       
       BEFORE                                      
       AFTER 
                   ADVANCING MnemonicName           
                             PAGE                     
                                                      
                                                      
                                                      
  ENVIRONMENT DIVISION.
  INPUT-OUTPUT SECTION.
  FILE-CONTROL.
      SELECT ReportFile ASSIGN TO "STUDENTS.RPT"
             ORGANIZATION IS LINE SEQUENTIAL.
  DATA DIVISION.                                                         DISK
  FILE SECTION.
  FD ReportFile.
  01 PrintLine           PIC X(40).
                                                                       STUDENTS.RPT
  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     PrnStudentCount
MOVE MaleCount    TO     PrnMaleCount
MOVE FemaleCount TO      PrnFemaleCount
WRITE PrintLine FROM     HeadingLine           AFTER   ADVANCING      PAGE
WRITE PrintLine FROM     StudentTotalLine      AFTER   ADVANCING      2 LINES
WRITE PrintLine FROM     MaleTotalLine         AFTER   ADVANCING      2 LINES
WRITE PrintLine FROM     FemaleTotalLine       AFTER   ADVANCING      2 LINES.

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:5
posted:10/23/2011
language:English
pages:32