IDENTIFICATION DIVISION.

       PROGRAM-ID.  EXSQRY01.

      *

      * InstantSQL Simple Query Example 01.

      *

      *This example connects to the data source named

      *Payroll.  It then executes a result set query that

      *obtains the Number, Surname, and Initials columns from the

      *Employees table of the Payroll data source and displays

      *the values from these columns for all employees.

      *The example then sets the value of the PayPeriod column

      *for all employees to the value in the ws-update-period

      *data item.  Finally, the example disconnects from the

      *data source and terminates InstantSQL.

 

      *Note:  To simplify the example, error handling is incomplete.

 

       DATA DIVISION.

       WORKING-STORAGE SECTION.

 

       COPY "lisqlall.cpy".

 

       01 ws-update-sql.

          05 FILLER            PIC X(33) value

                "UPDATE Employees SET PayPeriod = ".

          05 ws-update-period  PIC 99.

 

       01 ws-emp-data.

          05 ws-number         PIC 9(09).  *> employee number

          05 ws-surname        PIC X(30).  *> employee surname

          05 ws-initials       PIC X(03).  *> employee initials

 

       PROCEDURE DIVISION.

       A.

 

      *Connect to data source named Payroll.

           SQL CONNECT DATASOURCE sql-ConnectionHandle

               "Payroll"

               "MyName"

               "MyPassword".

           IF NOT sql-OK

             DISPLAY "<Error connecting to Payroll data source.>"

             STOP RUN

           END-IF.

 

      *Display Number, Surname and Initials for all Employees.

           SQL PREPARE QUERY sql-QueryHandle

                sql-ConnectionHandle

                "SELECT Number, Surname, Initials FROM Employees".

           SQL START QUERY sql-QueryHandle.

           PERFORM WITH TEST AFTER UNTIL NOT sql-OK

             SQL FETCH ROW sql-QueryHandle

             IF sql-OK

               SQL GET DATA sql-QueryHandle

                   "Number"   ws-number   OMITTED

                   "Surname"  ws-surname  OMITTED

                   "Initials" ws-initials OMITTED

               DISPLAY "Employee #" ws-number

                   "   Surname: " ws-surname

                   "   Initials: " ws-initials

             ELSE IF sql-EndOfData

               DISPLAY "<End of data.>"

             ELSE

               DISPLAY "<Error fetching row.>"

               STOP RUN

             END-IF END-IF

           END-PERFORM.

           SQL END QUERY sql-QueryHandle.

 

      *Update PayPeriod column value for all employees.

           SQL PREPARE QUERY sql-QueryHandle

               sql-ConnectionHandle

               ws-update-sql.

           SQL START QUERY sql-QueryHandle

           SQL DESCRIBE QUERY sql-QueryHandle

               sql-Query-Description.

           DISPLAY "<PayPeriod updated for "

               sql-QryRowCount OF sql-Query-Description

               " employees.>"

           SQL END QUERY sql-QueryHandle.

 

      *Disconnect from Payroll data source.

           SQL DISCONNECT DATASOURCE sql-ConnectionHandle.

 

      *Terminate InstantSQL.

           SQL SHUTDOWN.

 

           STOP RUN.

 

       END PROGRAM EXSQRY01.