The following program performs a simple ESQL CONNECT, DROP, CREATE, INSERT, SELECT, UPDATE, DELETE and DISCONNECT statement.
IDENTIFICATION DIVISION.
PROGRAM-ID. t006146.
*****************************************************************
ENVIRONMENT DIVISION.
INPUT-OUTPUT SECTION.
FILE-CONTROL.
SELECT CUSTFILE
ASSIGN TO "customer"
ORGANIZATION IS LINE SEQUENTIAL
FILE STATUS IS CUSTFILE-STATUS.
DATA DIVISION.
FILE SECTION.
FD CUSTFILE.
01 CUST-RECORD.
05 CUST-NUMBER PIC 9(3).
05 CUST-FIRST-NAME PIC X(20).
05 CUST-LAST-NAME PIC X(20).
05 CUST-BIRTHDAY.
07 SYEAR PIC X(4).
07 SMONTH PIC X(2).
07 SDAY PIC X(2).
WORKING-STORAGE SECTION.
01 CUSTFILE-STATUS PIC XX.
EXEC SQL INCLUDE SQLCA END-EXEC.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 C-RECORD.
05 C-NUMBER PIC S9(3) COMP-5.
05 C-FIRST-NAME PIC X(20).
05 C-LAST-NAME PIC X(20).
05 C-BIRTHDAY PIC X(10).
05 C-INFO PIC X(10).
01 servername pic x(30).
01 userid pic x(8).
01 passwd.
49 passwd-length PIC s9(4) comp-5 value 0.
49 passwd-name PIC x(18).
EXEC SQL END DECLARE SECTION END-EXEC.
01 pyyyymmdd.
03 syear pic 9(4).
03 smonth pic 9(2).
03 sday pic 9(2).
01 e-yyyymmdd.
03 syear pic 9(4).
03 filler pic x value "-".
03 smonth pic 9(2).
03 filler pic x value "-".
03 sday pic 9(2).
******************************************************************
PROCEDURE DIVISION.
Main Section.
EXEC SQL WHENEVER SQLERROR GO TO Error-Exit END-EXEC.
PERFORM initialization-routine.
PERFORM make-connection.
PERFORM drop-table.
PERFORM create-table.
PERFORM load-table
DISPLAY "Table created with 15 rows."
DISPLAY "Press Enter to retrieve and display these rows."
ACCEPT OMITTED.
PERFORM list-rows.
DISPLAY "Press Enter to perform a UPDATE on table row."
ACCEPT OMITTED.
PERFORM update-row.
DISPLAY "Press Enter to perform to DISPLAY updated list."
ACCEPT OMITTED.
PERFORM list-rows.
DISPLAY "Press Enter to DELETE row 15."
ACCEPT OMITTED.
PERFORM delete-row.
DISPLAY "Press Enter to display all remaining rows."
ACCEPT OMITTED.
PERFORM list-rows.
DISPLAY "Press Enter to DISCONNECT and Exit program."
PERFORM disconnect-connection.
ACCEPT OMITTED.
STOP RUN.
drop-table.
DISPLAY "Dropping existing table if it exists...".
EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC.
EXEC SQL
DROP TABLE CUSTOMER
END-EXEC.
* Ignore SQLCODE as the table likely does not exist at this point
EXEC SQL WHENEVER SQLERROR GO TO Error-Exit END-EXEC.
create-table.
DISPLAY "Creating CUSTOMER table...".
EXEC SQL
CREATE TABLE CUSTOMER (
C_NUMBER INTEGER NOT NULL,
C_FIRST_NAME CHAR(20),
C_LAST_NAME CHAR(20),
C_BIRTHDAY DATETIME,
C_INFO CHAR(10),
PRIMARY KEY (C_NUMBER))
END-EXEC.
IF SQLCODE < 0 PERFORM error-exit.
load-table.
DISPLAY "Loading CUSTOMER table with data...".
OPEN INPUT CUSTFILE.
PERFORM UNTIL CUSTFILE-STATUS = "10"
READ CUSTFILE NEXT RECORD
AT END
CONTINUE
NOT AT END
PERFORM insert-record
END-READ
END-PERFORM.
CLOSE CUSTFILE.
insert-record.
MOVE CUST-NUMBER TO C-NUMBER.
MOVE CUST-FIRST-NAME TO C-FIRST-NAME.
MOVE CUST-LAST-NAME TO C-LAST-NAME.
MOVE "New" TO C-INFO.
MOVE CORRESPONDING CUST-BIRTHDAY TO E-YYYYMMDD.
MOVE E-YYYYMMDD TO C-BIRTHDAY.
EXEC SQL
INSERT INTO CUSTOMER VALUES
(:C-NUMBER, :C-FIRST-NAME,
:C-LAST-NAME, :C-BIRTHDAY, :C-INFO)
END-EXEC.
MOVE LOW-VALUES TO CUST-RECORD.
MOVE LOW-VALUES TO C-RECORD.
initialization-routine.
DISPLAY "Enter MS SQL Servername or ODBC DSN:", no.
ACCEPT servername.
DISPLAY "Enter your user id (default none): ", no.
ACCEPT userid.
DISPLAY "Enter your password : ", no.
ACCEPT passwd-name.
IF userid = spaces then
DISPLAY "Using NT Authentication...".
INSPECT passwd-name TALLYING passwd-length FOR CHARACTERS
BEFORE INITIAL " ".
make-connection.
EXEC SQL CONNECT TO :servername as C1
USER :userid USING :passwd
END-EXEC.
IF SQLCODE < 0 PERFORM error-exit.
IF SQLCODE = 0 DISPLAY "Connection successful".
disconnect-connection.
EXEC SQL COMMIT END-EXEC.
EXEC SQL DISCONNECT ALL END-EXEC.
IF SQLCODE < 0 PERFORM error-exit.
Error-Exit.
EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC.
DISPLAY "SQL Error: SQLCODE " SQLCODE of SQLCA.
DISPLAY " SQLSTATE " SQLSTATE of SQLCA.
DISPLAY SQLERRMC OF SQLCA.
ACCEPT OMITTED.
EXEC SQL DISCONNECT ALL END-EXEC.
STOP RUN.
list-rows.
EXEC SQL
DECLARE COBCUR1 CURSOR FOR
SELECT
C_NUMBER,
C_FIRST_NAME,
C_LAST_NAME,
C_BIRTHDAY,
C_INFO
FROM CUSTOMER
WHERE C_NUMBER > 0
END-EXEC.
EXEC SQL
OPEN COBCUR1
END-EXEC.
PERFORM UNTIL SQLCODE NOT EQUAL 0
EXEC SQL
FETCH COBCUR1 INTO :C-RECORD
END-EXEC
IF SQLCODE EQUAL 0
DISPLAY c-number, ": ",c-first-name,
c-last-name, c-info
END-IF
END-PERFORM.
EXEC SQL
CLOSE COBCUR1
END-EXEC.
update-row.
EXEC SQL
DECLARE COBCUR2 CURSOR FOR
SELECT C_FIRST_NAME,
C_LAST_NAME
FROM CUSTOMER
WHERE C_NUMBER = 002
FOR UPDATE
END-EXEC.
EXEC SQL
OPEN COBCUR2
END-EXEC.
PERFORM UNTIL SQLCODE NOT EQUAL 0
EXEC SQL
FETCH COBCUR2
INTO :C-FIRST-NAME,
:C-LAST-NAME
END-EXEC
IF SQLCODE EQUAL 0
DISPLAY "Updating " C-FIRST-NAME , C-LAST-NAME
EXEC SQL
UPDATE CUSTOMER SET C_INFO = 'Revised'
WHERE CURRENT OF COBCUR2
END-EXEC
END-IF
END-PERFORM.
EXEC SQL
CLOSE COBCUR2
END-EXEC.
delete-row.
DISPLAY "Deleting row 15..."
EXEC SQL
DELETE FROM CUSTOMER
WHERE C_FIRST_NAME = 'OOOOO'
END-EXEC
.