|
IDENTIFICATION DIVISION.
PROGRAM-ID. DML147.
ENVIRONMENT DIVISION.
CONFIGURATION SECTION.
SOURCE-COMPUTER. xyz.
OBJECT-COMPUTER. xyz.
DATA DIVISION.
WORKING-STORAGE SECTION.
* Standard COBOL (file "DML147.SCO") calling SQL
* procedures in file "DML147.MCO".
****************************************************************
*
* COMMENT SECTION
*
* DATE 1995/2/1 STANDARD COBOL LANGUAGE
* NIST SQL VALIDATION TEST SUITE V6.0
* DISCLAIMER:
* This program was written by employees of NIST to test SQL
* implementations for conformance to the SQL standards.
* NIST assumes no responsibility for any party's use of
* this program.
*
* DML147.SCO
* WRITTEN BY: Joan Sullivan (mostly)
* TRANSLATED AUTOMATICALLY FROM EMBEDDED COBOL BY CHRIS SCHANZLE
* Standard COBOL by David Flater
*
* This routine tests (1) the rolling back of schema
* manipulation, and (2) queries involving joins.
*
* REFERENCES
* FIPS PUB 127-2 14.1 Transitional SQL
* ANSI SQL-1992
*
****************************************************************
* EXEC SQL BEGIN DECLARE SECTION END-EXEC
01 SQLCODE PIC S9(9) COMP.
01 SQLSTATE PIC X(5).
01 uid PIC X(18).
01 uidx PIC X(18).
01 en PIC X(3).
01 ename PIC X(20).
01 ename2 PIC X(20).
01 ct PIC X(15).
01 sl PIC S9(9) DISPLAY SIGN LEADING SEPARATE.
01 gr PIC S9(9) DISPLAY SIGN LEADING SEPARATE.
01 indic1 PIC S9(4) DISPLAY SIGN LEADING SEPARATE.
01 indic2 PIC S9(4) DISPLAY SIGN LEADING SEPARATE.
01 int1 PIC S9(9) DISPLAY SIGN LEADING SEPARATE.
* EXEC SQL END DECLARE SECTION END-EXEC
01 norm1 PIC S9(9) DISPLAY SIGN LEADING SEPARATE.
01 norm2 PIC S9(9) DISPLAY SIGN LEADING SEPARATE.
01 ALPNUM-TABLE VALUE IS
"01234ABCDEFGH56789IJKLMNOPQRSTUVWXYZ".
05 ALPNUM PIC X OCCURS 36 TIMES.
01 NORMSQ.
05 NORMSQX PIC X OCCURS 5 TIMES.
01 errcnt PIC S9(9) DISPLAY SIGN LEADING SEPARATE.
*date_time declaration
01 TO-DAY PIC 9(6).
01 THE-TIME PIC 9(8).
01 flag PIC S9(9) DISPLAY SIGN LEADING SEPARATE.
01 SQL-COD PIC S9(9) DISPLAY SIGN LEADING SEPARATE.
PROCEDURE DIVISION.
P0.
MOVE "FLATER " TO uid
CALL "AUTHID" USING uid
MOVE "not logged in, not" TO uidx
* EXEC SQL SELECT USER INTO :uidx FROM HU.ECCO;
CALL "SUB1" USING SQLCODE SQLSTATE uidx
MOVE SQLCODE TO SQL-COD
* EXEC SQL ROLLBACK WORK;
CALL "SUB2" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
if (uid NOT = uidx) then
DISPLAY "ERROR: User ", uid " expected. User ", uidx "
- " connected"
STOP RUN
END-IF
MOVE 0 TO errcnt
DISPLAY
"SQL Test Suite, V6.0, Module COBOL, dml147.sco"
DISPLAY
"59-byte ID"
DISPLAY "TEd Version #"
*date_time print
ACCEPT TO-DAY FROM DATE
ACCEPT THE-TIME FROM TIME
DISPLAY "Date run YYMMDD: " TO-DAY " at hhmmssff: " THE-TIME
******************** BEGIN TEST0840 *******************
MOVE 1 TO flag
DISPLAY " TEST0840 "
DISPLAY " Roll back schema manipulation"
DISPLAY "References:"
DISPLAY " F# 3 -- Basic schema manipulation"
DISPLAY " - - - - - - - - - - - - - - - - - - -"
COMPUTE SQLCODE = -1
MOVE "xxxxx" TO SQLSTATE
DISPLAY "CREATE TABLE NOT_THERE (C1 CHAR (10));"
* EXEC SQL CREATE TABLE NOT_THERE (C1 CHAR (10));
CALL "SUB3" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
*14.4 GR.3: Any changes to schema are cancelled by a rollback
DISPLAY "ROLLBACK WORK;"
* EXEC SQL ROLLBACK WORK;
CALL "SUB4" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
*This is a syntax error. If it is rejected at compile time,
*this portion of the test is passed.
DISPLAY "INSERT INTO NOT_THERE VALUES ('1234567890');"
* EXEC SQL INSERT INTO NOT_THERE VALUES ('1234567890')
* ;
CALL "SUB5" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
DISPLAY "SQLCODE should be < 0; its value is ", SQL-COD
if (SQLCODE NOT < 0) then
MOVE 0 TO flag
END-IF
DISPLAY "SQLSTATE should be 42000; its value is ", SQLSTATE
PERFORM NOSUBCLASS THRU EXIT-NOSUBCLASS
if (NORMSQ NOT = "42000") then
MOVE 0 TO flag
END-IF
if (NORMSQ = "42000" AND NORMSQ NOT = SQLSTATE)
then
DISPLAY "Valid implementation-defined SQLSTATE accepted."
END-IF
DISPLAY "ROLLBACK WORK;"
* EXEC SQL ROLLBACK WORK;
CALL "SUB6" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "CREATE VIEW NOT_HERE AS"
DISPLAY " SELECT * FROM USIG;"
* EXEC SQL CREATE VIEW NOT_HERE AS
* SELECT * FROM USIG;
CALL "SUB7" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "ROLLBACK WORK;"
* EXEC SQL ROLLBACK WORK;
CALL "SUB8" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
*This is a syntax error. If it is rejected at compile time,
*this portion of the test is passed.
DISPLAY "SELECT COUNT (*) INTO :int1"
DISPLAY " FROM NOT_HERE;"
* EXEC SQL SELECT COUNT (*) INTO :int1
* FROM NOT_HERE;
CALL "SUB9" USING SQLCODE SQLSTATE int1
MOVE SQLCODE TO SQL-COD
DISPLAY "SQLCODE should be < 0; its value is ", SQL-COD
if (SQLCODE NOT < 0) then
MOVE 0 TO flag
END-IF
DISPLAY "SQLSTATE should be 42000; its value is ", SQLSTATE
PERFORM NOSUBCLASS THRU EXIT-NOSUBCLASS
if (NORMSQ NOT = "42000") then
MOVE 0 TO flag
END-IF
if (NORMSQ = "42000" AND NORMSQ NOT = SQLSTATE)
then
DISPLAY "Valid implementation-defined SQLSTATE accepted."
END-IF
DISPLAY "ROLLBACK WORK;"
* EXEC SQL ROLLBACK WORK;
CALL "SUB10" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "ALTER TABLE USIG"
DISPLAY " ADD COLUMN NUL INT;"
* EXEC SQL ALTER TABLE USIG
* ADD COLUMN NUL INT;
CALL "SUB11" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "ROLLBACK WORK;"
* EXEC SQL ROLLBACK WORK;
CALL "SUB12" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
*This is a syntax error. If it is rejected at compile time,
*this portion of the test is passed.
DISPLAY "SELECT COUNT (*) INTO :int1"
DISPLAY " FROM USIG WHERE NUL IS NULL;"
* EXEC SQL SELECT COUNT (*) INTO :int1
* FROM USIG WHERE NUL IS NULL;
CALL "SUB13" USING SQLCODE SQLSTATE int1
MOVE SQLCODE TO SQL-COD
DISPLAY "SQLCODE should be < 0; its value is ", SQL-COD
if (SQLCODE NOT < 0) then
MOVE 0 TO flag
END-IF
DISPLAY "SQLSTATE should be 42000; its value is ", SQLSTATE
PERFORM NOSUBCLASS THRU EXIT-NOSUBCLASS
if (NORMSQ NOT = "42000") then
MOVE 0 TO flag
END-IF
if (NORMSQ = "42000" AND NORMSQ NOT = SQLSTATE)
then
DISPLAY "Valid implementation-defined SQLSTATE accepted."
END-IF
DISPLAY "ROLLBACK WORK;"
* EXEC SQL ROLLBACK WORK;
CALL "SUB14" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "DROP TABLE USIG CASCADE;"
* EXEC SQL DROP TABLE USIG CASCADE;
CALL "SUB15" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "ROLLBACK WORK;"
* EXEC SQL ROLLBACK WORK;
CALL "SUB16" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
MOVE 50 TO int1
DISPLAY "SELECT COUNT(*) INTO :int1"
DISPLAY " FROM U_SIG;"
* EXEC SQL SELECT COUNT(*) INTO :int1
* FROM U_SIG;
CALL "SUB17" USING SQLCODE SQLSTATE int1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "int1 should be 2; its value is ", int1
if (int1 NOT = 2) then
MOVE 0 TO flag
END-IF
DISPLAY "ROLLBACK WORK;"
* EXEC SQL ROLLBACK WORK;
CALL "SUB18" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
MOVE 50 TO int1
DISPLAY "SELECT COUNT(*) INTO :int1"
DISPLAY " FROM USIG;"
* EXEC SQL SELECT COUNT(*) INTO :int1
* FROM USIG;
CALL "SUB19" USING SQLCODE SQLSTATE int1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "int1 should be 2; its value is ", int1
if (int1 NOT = 2) then
MOVE 0 TO flag
END-IF
DISPLAY "ROLLBACK WORK;"
* EXEC SQL ROLLBACK WORK;
CALL "SUB20" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
if ( flag = 1 ) then
DISPLAY " *** pass *** "
* EXEC SQL INSERT INTO HU.TESTREPORT
* VALUES('0840','pass','MCO');
CALL "SUB21" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
else
DISPLAY " dml147.sco *** fail *** "
* EXEC SQL INSERT INTO HU.TESTREPORT
* VALUES('0840','fail','MCO');
CALL "SUB22" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
COMPUTE errcnt = errcnt + 1
END-IF
DISPLAY "==============================================="
* EXEC SQL COMMIT WORK;
CALL "SUB23" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
******************** END TEST0840 ********************
******************** BEGIN TEST0841 *******************
MOVE 1 TO flag
DISPLAY " TEST0841 "
DISPLAY " Multiple-join and default order of joins"
DISPLAY "References:"
DISPLAY " F# 4 -- Joined table"
DISPLAY " 3.3.4.4 PP.2 -- Order of evaluation"
DISPLAY " 7.5 -- Format of allows parens"
DISPLAY " - - - - - - - - - - - - - - - - - - -"
COMPUTE SQLCODE = -1
MOVE "xxxxx" TO SQLSTATE
DISPLAY "DELETE FROM HU.STAFF4;"
* EXEC SQL DELETE FROM HU.STAFF4;
CALL "SUB24" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
DISPLAY "INSERT INTO HU.STAFF4"
DISPLAY " SELECT * FROM HU.STAFF3"
DISPLAY " WHERE EMPNUM > 'E3';"
* EXEC SQL INSERT INTO HU.STAFF4
* SELECT * FROM HU.STAFF3
* WHERE EMPNUM > 'E3';
CALL "SUB25" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
*testing that joins are done left to right
*same as next query where parentheses are explicit
DISPLAY "DECLARE C14721 CURSOR FOR"
DISPLAY " SELECT EMPNUM FROM HU.STAFF3"
DISPLAY " NATURAL LEFT JOIN HU.STAFF"
DISPLAY " NATURAL INNER JOIN HU.STAFF4"
DISPLAY " ORDER BY EMPNUM DESC;"
* EXEC SQL DECLARE C14721 CURSOR FOR
* SELECT EMPNUM FROM HU.STAFF3
* NATURAL LEFT JOIN HU.STAFF
* NATURAL INNER JOIN HU.STAFF4
* ORDER BY EMPNUM DESC END-EXEC
DISPLAY "OPEN C14721;"
* EXEC SQL OPEN C14721;
CALL "SUB26" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
MOVE "xxx" TO en
DISPLAY "FETCH C14721 INTO :en;"
* EXEC SQL FETCH C14721 INTO :en;
CALL "SUB27" USING SQLCODE SQLSTATE en
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "en should be 'E5 '; its value is '", en, "'"
if (en NOT = "E5 ") then
MOVE 0 TO flag
END-IF
MOVE "xxx" TO en
DISPLAY "FETCH C14721 INTO :en;"
* EXEC SQL FETCH C14721 INTO :en;
CALL "SUB28" USING SQLCODE SQLSTATE en
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "en should be 'E4 '; its value is '", en, "'"
if (en NOT = "E4 ") then
MOVE 0 TO flag
END-IF
DISPLAY "FETCH C14721 INTO :en;"
* EXEC SQL FETCH C14721 INTO :en;
CALL "SUB29" USING SQLCODE SQLSTATE en
MOVE SQLCODE TO SQL-COD
DISPLAY "SQLCODE should be 100; its value is ", SQL-COD
DISPLAY "SQLSTATE should be 02000; its value is ", SQLSTATE
PERFORM NOSUBCLASS THRU EXIT-NOSUBCLASS
if (SQLCODE NOT = 100 OR NORMSQ NOT = "02000") then
MOVE 0 TO flag
END-IF
if (NORMSQ = "02000" AND NORMSQ NOT = SQLSTATE)
then
DISPLAY "Valid implementation-defined SQLSTATE accepted."
END-IF
DISPLAY " "
DISPLAY "CLOSE C14721;"
* EXEC SQL CLOSE C14721;
CALL "SUB30" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "DECLARE C14722 CURSOR FOR"
DISPLAY " SELECT EMPNUM FROM"
DISPLAY " (HU.STAFF3 NATURAL LEFT JOIN HU.STAFF)"
DISPLAY " NATURAL INNER JOIN HU.STAFF4"
DISPLAY " ORDER BY EMPNUM ASC;"
* EXEC SQL DECLARE C14722 CURSOR FOR
* SELECT EMPNUM FROM
* (HU.STAFF3 NATURAL LEFT JOIN HU.STAFF)
* NATURAL INNER JOIN HU.STAFF4
* ORDER BY EMPNUM ASC END-EXEC
DISPLAY "OPEN C14722;"
* EXEC SQL OPEN C14722;
CALL "SUB31" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
MOVE "xxx" TO en
DISPLAY "FETCH C14722 INTO :en;"
* EXEC SQL FETCH C14722 INTO :en;
CALL "SUB32" USING SQLCODE SQLSTATE en
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "en should be 'E4 '; its value is '", en, "'"
if (en NOT = "E4 ") then
MOVE 0 TO flag
END-IF
MOVE "xxx" TO en
DISPLAY "FETCH C14722 INTO :en;"
* EXEC SQL FETCH C14722 INTO :en;
CALL "SUB33" USING SQLCODE SQLSTATE en
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "en should be 'E5 '; its value is '", en, "'"
if (en NOT = "E5 ") then
MOVE 0 TO flag
END-IF
DISPLAY "FETCH C14722 INTO :en;"
* EXEC SQL FETCH C14722 INTO :en;
CALL "SUB34" USING SQLCODE SQLSTATE en
MOVE SQLCODE TO SQL-COD
DISPLAY "SQLCODE should be 100; its value is ", SQL-COD
DISPLAY "SQLSTATE should be 02000; its value is ", SQLSTATE
PERFORM NOSUBCLASS THRU EXIT-NOSUBCLASS
if (SQLCODE NOT = 100 OR NORMSQ NOT = "02000") then
MOVE 0 TO flag
END-IF
if (NORMSQ = "02000" AND NORMSQ NOT = SQLSTATE)
then
DISPLAY "Valid implementation-defined SQLSTATE accepted."
END-IF
DISPLAY " "
DISPLAY "CLOSE C14722;"
* EXEC SQL CLOSE C14722;
CALL "SUB35" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "DECLARE C14723 CURSOR FOR"
DISPLAY " SELECT EMPNUM FROM"
DISPLAY " HU.STAFF3 NATURAL LEFT JOIN"
DISPLAY " (HU.STAFF NATURAL INNER JOIN HU.STAFF4)"
DISPLAY " ORDER BY EMPNUM;"
* EXEC SQL DECLARE C14723 CURSOR FOR
* SELECT EMPNUM FROM
* HU.STAFF3 NATURAL LEFT JOIN
* (HU.STAFF NATURAL INNER JOIN HU.STAFF4)
* ORDER BY EMPNUM END-EXEC
DISPLAY "OPEN C14723;"
* EXEC SQL OPEN C14723;
CALL "SUB36" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
MOVE "xxx" TO en
DISPLAY "FETCH C14723 INTO :en;"
* EXEC SQL FETCH C14723 INTO :en;
CALL "SUB37" USING SQLCODE SQLSTATE en
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "en should be 'E1 '; its value is '", en, "'"
if (en NOT = "E1 ") then
MOVE 0 TO flag
END-IF
MOVE "xxx" TO en
DISPLAY "FETCH C14723 INTO :en;"
* EXEC SQL FETCH C14723 INTO :en;
CALL "SUB38" USING SQLCODE SQLSTATE en
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "en should be 'E2 '; its value is '", en, "'"
if (en NOT = "E2 ") then
MOVE 0 TO flag
END-IF
MOVE "xxx" TO en
DISPLAY "FETCH C14723 INTO :en;"
* EXEC SQL FETCH C14723 INTO :en;
CALL "SUB39" USING SQLCODE SQLSTATE en
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "en should be 'E3 '; its value is '", en, "'"
if (en NOT = "E3 ") then
MOVE 0 TO flag
END-IF
MOVE "xxx" TO en
DISPLAY "FETCH C14723 INTO :en;"
* EXEC SQL FETCH C14723 INTO :en;
CALL "SUB40" USING SQLCODE SQLSTATE en
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "en should be 'E4 '; its value is '", en, "'"
if (en NOT = "E4 ") then
MOVE 0 TO flag
END-IF
MOVE "xxx" TO en
DISPLAY "FETCH C14723 INTO :en;"
* EXEC SQL FETCH C14723 INTO :en;
CALL "SUB41" USING SQLCODE SQLSTATE en
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "en should be 'E5 '; its value is '", en, "'"
if (en NOT = "E5 ") then
MOVE 0 TO flag
END-IF
DISPLAY "FETCH C14723 INTO :en;"
* EXEC SQL FETCH C14723 INTO :en;
CALL "SUB42" USING SQLCODE SQLSTATE en
MOVE SQLCODE TO SQL-COD
DISPLAY "SQLCODE should be 100; its value is ", SQL-COD
DISPLAY "SQLSTATE should be 02000; its value is ", SQLSTATE
PERFORM NOSUBCLASS THRU EXIT-NOSUBCLASS
if (SQLCODE NOT = 100 OR NORMSQ NOT = "02000") then
MOVE 0 TO flag
END-IF
if (NORMSQ = "02000" AND NORMSQ NOT = SQLSTATE)
then
DISPLAY "Valid implementation-defined SQLSTATE accepted."
END-IF
DISPLAY " "
DISPLAY "CLOSE C14723;"
* EXEC SQL CLOSE C14723;
CALL "SUB43" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "ROLLBACK WORK;"
* EXEC SQL ROLLBACK WORK;
CALL "SUB44" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
if ( flag = 1 ) then
DISPLAY " *** pass *** "
* EXEC SQL INSERT INTO HU.TESTREPORT
* VALUES('0841','pass','MCO');
CALL "SUB45" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
else
DISPLAY " dml147.sco *** fail *** "
* EXEC SQL INSERT INTO HU.TESTREPORT
* VALUES('0841','fail','MCO');
CALL "SUB46" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
COMPUTE errcnt = errcnt + 1
END-IF
DISPLAY "==============================================="
* EXEC SQL COMMIT WORK;
CALL "SUB47" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
******************** END TEST0841 ********************
******************** BEGIN TEST0842 *******************
MOVE 1 TO flag
DISPLAY " TEST0842 "
DISPLAY " Multi-column joins"
DISPLAY "References:"
DISPLAY " F# 4 -- Joined table"
DISPLAY " - - - - - - - - - - - - - - - - - - -"
COMPUTE SQLCODE = -1
MOVE "xxxxx" TO SQLSTATE
DISPLAY "CREATE TABLE STAFF66 ("
DISPLAY " SALARY INTEGER,"
DISPLAY " EMPNAME CHAR(20),"
DISPLAY " GRADE DECIMAL,"
DISPLAY " EMPNUM CHAR(3));"
* EXEC SQL CREATE TABLE STAFF66 (
* SALARY INTEGER,
* EMPNAME CHAR(20),
* GRADE DECIMAL,
* EMPNUM CHAR(3));
CALL "SUB48" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
* EXEC SQL COMMIT WORK;
CALL "SUB49" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO STAFF66"
DISPLAY " SELECT GRADE*1000, EMPNAME, GRADE, EMPNUM"
DISPLAY " FROM HU.STAFF3 WHERE EMPNUM > 'E2';"
* EXEC SQL INSERT INTO STAFF66
* SELECT GRADE*1000, EMPNAME, GRADE, EMPNUM
* FROM HU.STAFF3 WHERE EMPNUM > 'E2';
CALL "SUB50" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "UPDATE HU.STAFF3 SET EMPNUM = 'E6' WHERE EMPNUM =
- " 'E5';"
* EXEC SQL UPDATE HU.STAFF3 SET EMPNUM = 'E6' WHERE EMPNUM =
* 'E5';
CALL "SUB51" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "UPDATE HU.STAFF3 SET EMPNAME = 'Ali' WHERE GRADE =
- " 12;"
* EXEC SQL UPDATE HU.STAFF3 SET EMPNAME = 'Ali' WHERE GRADE =
* 12;
CALL "SUB52" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
*7.5 SR 6 d
*Table STAFF66 has 3 rows, only 1 matching on all columns
MOVE "xxxxxxxxxxxxxxxxxxxx" TO ename
MOVE 0 TO gr
MOVE "xxx" TO en
MOVE 0 TO sl
MOVE "xxxxxxxxxxxxxxx" TO ct
DISPLAY "SELECT * INTO :ename, :gr, :en, :sl, :ct FROM"
DISPLAY " STAFF66 NATURAL INNER JOIN HU.STAFF3;"
* EXEC SQL SELECT * INTO :ename, :gr, :en, :sl, :ct FROM
* STAFF66 NATURAL INNER JOIN HU.STAFF3;
CALL "SUB53" USING SQLCODE SQLSTATE ename gr en sl ct
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "ename should be 'Carmen '; its value
- " is '", ename, "'"
DISPLAY "gr should be 13; its value is ", gr
DISPLAY "en should be 'E3 '; its value is '", en, "'"
DISPLAY "sl should be 13000; its value is ", sl
DISPLAY "ct should be 'Vienna '; its value is '",
ct, "'"
if (gr NOT = 13 OR sl NOT = 13000) then
MOVE 0 TO flag
END-IF
if (ename NOT = "Carmen " OR en NOT =
"E3 ") then
MOVE 0 TO flag
END-IF
if (ct NOT = "Vienna ") then
MOVE 0 TO flag
END-IF
DISPLAY "DECLARE C14731 CURSOR FOR"
DISPLAY " SELECT EMPNUM, EMPNAME, SALARY FROM"
DISPLAY " HU.STAFF3 NATURAL LEFT OUTER JOIN STAFF66"
DISPLAY " WHERE EMPNUM > 'E1'"
DISPLAY " ORDER BY EMPNUM ASC;"
* EXEC SQL DECLARE C14731 CURSOR FOR
* SELECT EMPNUM, EMPNAME, SALARY FROM
* HU.STAFF3 NATURAL LEFT OUTER JOIN STAFF66
* WHERE EMPNUM > 'E1'
* ORDER BY EMPNUM ASC END-EXEC
DISPLAY "OPEN C14731;"
* EXEC SQL OPEN C14731;
CALL "SUB54" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
MOVE "xxx" TO en
MOVE "xxxxxxxxxxxxxxxxxxxx" TO ename
MOVE 0 TO indic1
DISPLAY "FETCH C14731 INTO :en, :ename, :sl:indic1;"
* EXEC SQL FETCH C14731 INTO :en, :ename, :sl:indic1;
CALL "SUB55" USING SQLCODE SQLSTATE en ename sl indic1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "en should be 'E2 '; its value is '", en, "'"
DISPLAY "ename should be 'Betty '; its value
- " is '", ename, "'"
DISPLAY "indic1 should be -1; its value is ", indic1
if (en NOT = "E2 " OR indic1 NOT = -1) then
MOVE 0 TO flag
END-IF
if (ename NOT = "Betty ") then
MOVE 0 TO flag
END-IF
MOVE "xxx" TO en
MOVE "xxxxxxxxxxxxxxxxxxxx" TO ename
COMPUTE indic1 = -1
MOVE 0 TO sl
DISPLAY "FETCH C14731 INTO :en, :ename, :sl:indic1;"
* EXEC SQL FETCH C14731 INTO :en, :ename, :sl:indic1;
CALL "SUB56" USING SQLCODE SQLSTATE en ename sl indic1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "en should be 'E3 '; its value is '", en, "'"
DISPLAY "ename should be 'Carmen '; its value
- " is '", ename, "'"
DISPLAY "sl should be 13000; its value is ", sl
DISPLAY "indic1 should be 0; its value is ", indic1
if (en NOT = "E3 " OR indic1 NOT = 0) then
MOVE 0 TO flag
END-IF
if (ename NOT = "Carmen " OR sl NOT =
13000) then
MOVE 0 TO flag
END-IF
MOVE "xxx" TO en
MOVE "xxxxxxxxxxxxxxxxxxxx" TO ename
MOVE 0 TO indic1
DISPLAY "FETCH C14731 INTO :en, :ename, :sl:indic1;"
* EXEC SQL FETCH C14731 INTO :en, :ename, :sl:indic1;
CALL "SUB57" USING SQLCODE SQLSTATE en ename sl indic1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "en should be 'E4 '; its value is '", en, "'"
DISPLAY "ename should be 'Ali '; its value
- " is '", ename, "'"
DISPLAY "indic1 should be -1; its value is ", indic1
if (en NOT = "E4 " OR indic1 NOT = -1) then
MOVE 0 TO flag
END-IF
if (ename NOT = "Ali ") then
MOVE 0 TO flag
END-IF
MOVE "xxx" TO en
MOVE "xxxxxxxxxxxxxxxxxxxx" TO ename
MOVE 0 TO indic1
DISPLAY "FETCH C14731 INTO :en, :ename, :sl:indic1;"
* EXEC SQL FETCH C14731 INTO :en, :ename, :sl:indic1;
CALL "SUB58" USING SQLCODE SQLSTATE en ename sl indic1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "en should be 'E6 '; its value is '", en, "'"
DISPLAY "ename should be 'Ed '; its value
- " is '", ename, "'"
DISPLAY "indic1 should be -1; its value is ", indic1
if (en NOT = "E6 " OR indic1 NOT = -1) then
MOVE 0 TO flag
END-IF
if (ename NOT = "Ed ") then
MOVE 0 TO flag
END-IF
DISPLAY "FETCH C14731 INTO :en, :ename, :sl:indic1;"
* EXEC SQL FETCH C14731 INTO :en, :ename, :sl:indic1;
CALL "SUB59" USING SQLCODE SQLSTATE en ename sl indic1
MOVE SQLCODE TO SQL-COD
DISPLAY "SQLCODE should be 100; its value is ", SQL-COD
DISPLAY "SQLSTATE should be 02000; its value is ", SQLSTATE
PERFORM NOSUBCLASS THRU EXIT-NOSUBCLASS
if (SQLCODE NOT = 100 OR NORMSQ NOT = "02000") then
MOVE 0 TO flag
END-IF
if (NORMSQ = "02000" AND NORMSQ NOT = SQLSTATE)
then
DISPLAY "Valid implementation-defined SQLSTATE accepted."
END-IF
DISPLAY " "
DISPLAY "CLOSE C14731;"
* EXEC SQL CLOSE C14731;
CALL "SUB60" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "DECLARE C14732 CURSOR FOR"
DISPLAY " SELECT EMPNUM, EMPNAME, SALARY FROM"
DISPLAY " STAFF66 NATURAL RIGHT OUTER JOIN HU.STAFF"
DISPLAY " WHERE EMPNUM > 'E1'"
DISPLAY " ORDER BY EMPNUM DESC;"
* EXEC SQL DECLARE C14732 CURSOR FOR
* SELECT EMPNUM, EMPNAME, SALARY FROM
* STAFF66 NATURAL RIGHT OUTER JOIN HU.STAFF
* WHERE EMPNUM > 'E1'
* ORDER BY EMPNUM DESC END-EXEC
DISPLAY "OPEN C14732;"
* EXEC SQL OPEN C14732;
CALL "SUB61" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
MOVE "xxx" TO en
MOVE "xxxxxxxxxxxxxxxxxxxx" TO ename
COMPUTE indic1 = -1
MOVE 0 TO sl
DISPLAY "FETCH C14732 INTO :en, :ename, :sl:indic1;"
* EXEC SQL FETCH C14732 INTO :en, :ename, :sl:indic1;
CALL "SUB62" USING SQLCODE SQLSTATE en ename sl indic1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "en should be 'E5 '; its value is '", en, "'"
DISPLAY "ename should be 'Ed '; its value
- " is '", ename, "'"
DISPLAY "sl should be 13000; its value is ", sl
DISPLAY "indic1 should be 0; its value is ", indic1
if (en NOT = "E5 " OR indic1 NOT = 0) then
MOVE 0 TO flag
END-IF
if (ename NOT = "Ed " OR sl NOT =
13000) then
MOVE 0 TO flag
END-IF
MOVE "xxx" TO en
MOVE "xxxxxxxxxxxxxxxxxxxx" TO ename
COMPUTE indic1 = -1
MOVE 0 TO sl
DISPLAY "FETCH C14732 INTO :en, :ename, :sl:indic1;"
* EXEC SQL FETCH C14732 INTO :en, :ename, :sl:indic1;
CALL "SUB63" USING SQLCODE SQLSTATE en ename sl indic1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "en should be 'E4 '; its value is '", en, "'"
DISPLAY "ename should be 'Don '; its value
- " is '", ename, "'"
DISPLAY "sl should be 12000; its value is ", sl
DISPLAY "indic1 should be 0; its value is ", indic1
if (en NOT = "E4 " OR indic1 NOT = 0) then
MOVE 0 TO flag
END-IF
if (ename NOT = "Don " OR sl NOT =
12000) then
MOVE 0 TO flag
END-IF
MOVE "xxx" TO en
MOVE "xxxxxxxxxxxxxxxxxxxx" TO ename
COMPUTE indic1 = -1
MOVE 0 TO sl
DISPLAY "FETCH C14732 INTO :en, :ename, :sl:indic1;"
* EXEC SQL FETCH C14732 INTO :en, :ename, :sl:indic1;
CALL "SUB64" USING SQLCODE SQLSTATE en ename sl indic1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "en should be 'E3 '; its value is '", en, "'"
DISPLAY "ename should be 'Carmen '; its value
- " is '", ename, "'"
DISPLAY "sl should be 13000; its value is ", sl
DISPLAY "indic1 should be 0; its value is ", indic1
if (en NOT = "E3 " OR indic1 NOT = 0) then
MOVE 0 TO flag
END-IF
if (ename NOT = "Carmen " OR sl NOT =
13000) then
MOVE 0 TO flag
END-IF
MOVE "xxx" TO en
MOVE "xxxxxxxxxxxxxxxxxxxx" TO ename
MOVE 0 TO indic1
MOVE 0 TO sl
DISPLAY "FETCH C14732 INTO :en, :ename, :sl:indic1;"
* EXEC SQL FETCH C14732 INTO :en, :ename, :sl:indic1;
CALL "SUB65" USING SQLCODE SQLSTATE en ename sl indic1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "en should be 'E2 '; its value is '", en, "'"
DISPLAY "ename should be 'Betty '; its value
- " is '", ename, "'"
DISPLAY "indic1 should be -1; its value is ", indic1
if (en NOT = "E2 " OR indic1 NOT = -1) then
MOVE 0 TO flag
END-IF
if (ename NOT = "Betty ") then
MOVE 0 TO flag
END-IF
DISPLAY "FETCH C14732 INTO :en, :ename, :sl:indic1;"
* EXEC SQL FETCH C14732 INTO :en, :ename, :sl:indic1;
CALL "SUB66" USING SQLCODE SQLSTATE en ename sl indic1
MOVE SQLCODE TO SQL-COD
DISPLAY "SQLCODE should be 100; its value is ", SQL-COD
DISPLAY "SQLSTATE should be 02000; its value is ", SQLSTATE
PERFORM NOSUBCLASS THRU EXIT-NOSUBCLASS
if (SQLCODE NOT = 100 OR NORMSQ NOT = "02000") then
MOVE 0 TO flag
END-IF
if (NORMSQ = "02000" AND NORMSQ NOT = SQLSTATE)
then
DISPLAY "Valid implementation-defined SQLSTATE accepted."
END-IF
DISPLAY " "
DISPLAY "CLOSE C14732;"
* EXEC SQL CLOSE C14732;
CALL "SUB67" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "DECLARE C14733 CURSOR FOR"
DISPLAY " SELECT * FROM STAFF66 RIGHT JOIN HU.STAFF"
DISPLAY " USING (GRADE, EMPNUM, EMPNAME)"
DISPLAY " WHERE EMPNUM > 'E1'"
DISPLAY " ORDER BY EMPNUM;"
* EXEC SQL DECLARE C14733 CURSOR FOR
* SELECT * FROM STAFF66 RIGHT JOIN HU.STAFF
* USING (GRADE, EMPNUM, EMPNAME)
* WHERE EMPNUM > 'E1'
* ORDER BY EMPNUM END-EXEC
DISPLAY "OPEN C14733;"
* EXEC SQL OPEN C14733;
CALL "SUB68" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
MOVE "xxxxxxxxxxxxxxxxxxxx" TO ename
MOVE 0 TO gr
MOVE "xxx" TO en
MOVE "xxxxxxxxxxxxxxx" TO ct
MOVE 0 TO indic1
DISPLAY "FETCH C14733 INTO :ename, :gr, :en, :sl:indic1,
- " :ct;"
* EXEC SQL FETCH C14733 INTO :ename, :gr, :en, :sl:indic1,
* :ct;
CALL "SUB69" USING SQLCODE SQLSTATE ename gr en sl
indic1 ct
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "ename should be 'Betty '; its value
- " is '", ename, "'"
DISPLAY "gr should be 10; its value is ", gr
DISPLAY "en should be 'E2 '; its value is '", en, "'"
DISPLAY "indic1 should be -1; its value is ", indic1
DISPLAY "ct should be 'Vienna '; its value is '",
ct, "'"
if (en NOT = "E2 " OR indic1 NOT = -1) then
MOVE 0 TO flag
END-IF
if (ename NOT = "Betty " OR gr NOT =
10) then
MOVE 0 TO flag
END-IF
if (ct NOT = "Vienna ") then
MOVE 0 TO flag
END-IF
MOVE "xxxxxxxxxxxxxxxxxxxx" TO ename
MOVE 0 TO gr
MOVE "xxx" TO en
MOVE "xxxxxxxxxxxxxxx" TO ct
MOVE 0 TO sl
DISPLAY "FETCH C14733 INTO :ename, :gr, :en, :sl:indic1,
- " :ct;"
* EXEC SQL FETCH C14733 INTO :ename, :gr, :en, :sl:indic1,
* :ct;
CALL "SUB70" USING SQLCODE SQLSTATE ename gr en sl
indic1 ct
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "ename should be 'Carmen '; its value
- " is '", ename, "'"
DISPLAY "gr should be 13; its value is ", gr
DISPLAY "en should be 'E3 '; its value is '", en, "'"
DISPLAY "sl should be 13000; its value is ", sl
DISPLAY "indic1 should be 0; its value is ", indic1
DISPLAY "ct should be 'Vienna '; its value is '",
ct, "'"
if (en NOT = "E3 " OR indic1 NOT = 0) then
MOVE 0 TO flag
END-IF
if (ename NOT = "Carmen " OR gr NOT =
13) then
MOVE 0 TO flag
END-IF
if (ct NOT = "Vienna " OR sl NOT = 13000)
then
MOVE 0 TO flag
END-IF
MOVE "xxxxxxxxxxxxxxxxxxxx" TO ename
MOVE 0 TO gr
MOVE "xxx" TO en
MOVE "xxxxxxxxxxxxxxx" TO ct
MOVE 0 TO sl
DISPLAY "FETCH C14733 INTO :ename, :gr, :en, :sl:indic1,
- " :ct;"
* EXEC SQL FETCH C14733 INTO :ename, :gr, :en, :sl:indic1,
* :ct;
CALL "SUB71" USING SQLCODE SQLSTATE ename gr en sl
indic1 ct
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "ename should be 'Don '; its value
- " is '", ename, "'"
DISPLAY "gr should be 12; its value is ", gr
DISPLAY "en should be 'E4 '; its value is '", en, "'"
DISPLAY "sl should be 12000; its value is ", sl
DISPLAY "indic1 should be 0; its value is ", indic1
DISPLAY "ct should be 'Deale '; its value is '",
ct, "'"
if (en NOT = "E4 " OR indic1 NOT = 0) then
MOVE 0 TO flag
END-IF
if (ename NOT = "Don " OR gr NOT =
12) then
MOVE 0 TO flag
END-IF
if (ct NOT = "Deale " OR sl NOT = 12000)
then
MOVE 0 TO flag
END-IF
MOVE "xxxxxxxxxxxxxxxxxxxx" TO ename
MOVE 0 TO gr
MOVE "xxx" TO en
MOVE "xxxxxxxxxxxxxxx" TO ct
MOVE 0 TO sl
DISPLAY "FETCH C14733 INTO :ename, :gr, :en, :sl:indic1,
- " :ct;"
* EXEC SQL FETCH C14733 INTO :ename, :gr, :en, :sl:indic1,
* :ct;
CALL "SUB72" USING SQLCODE SQLSTATE ename gr en sl
indic1 ct
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "ename should be 'Ed '; its value
- " is '", ename, "'"
DISPLAY "gr should be 13; its value is ", gr
DISPLAY "en should be 'E5 '; its value is '", en, "'"
DISPLAY "sl should be 13000; its value is ", sl
DISPLAY "indic1 should be 0; its value is ", indic1
DISPLAY "ct should be 'Akron '; its value is '",
ct, "'"
if (en NOT = "E5 " OR indic1 NOT = 0) then
MOVE 0 TO flag
END-IF
if (ename NOT = "Ed " OR gr NOT =
13) then
MOVE 0 TO flag
END-IF
if (ct NOT = "Akron " OR sl NOT = 13000)
then
MOVE 0 TO flag
END-IF
DISPLAY "FETCH C14733 INTO :ename, :gr, :en, :sl:indic1,
- " :ct;"
* EXEC SQL FETCH C14733 INTO :ename, :gr, :en, :sl:indic1,
* :ct;
CALL "SUB73" USING SQLCODE SQLSTATE ename gr en sl
indic1 ct
MOVE SQLCODE TO SQL-COD
DISPLAY "SQLCODE should be 100; its value is ", SQL-COD
DISPLAY "SQLSTATE should be 02000; its value is ", SQLSTATE
PERFORM NOSUBCLASS THRU EXIT-NOSUBCLASS
if (SQLCODE NOT = 100 OR NORMSQ NOT = "02000") then
MOVE 0 TO flag
END-IF
if (NORMSQ = "02000" AND NORMSQ NOT = SQLSTATE)
then
DISPLAY "Valid implementation-defined SQLSTATE accepted."
END-IF
DISPLAY " "
DISPLAY "CLOSE C14733;"
* EXEC SQL CLOSE C14733;
CALL "SUB74" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "DECLARE C14734 CURSOR FOR"
DISPLAY " SELECT * FROM HU.STAFF3 LEFT JOIN STAFF66"
DISPLAY " USING (GRADE, EMPNUM)"
DISPLAY " WHERE EMPNUM > 'E1'"
DISPLAY " ORDER BY EMPNUM ASC;"
* EXEC SQL DECLARE C14734 CURSOR FOR
* SELECT * FROM HU.STAFF3 LEFT JOIN STAFF66
* USING (GRADE, EMPNUM)
* WHERE EMPNUM > 'E1'
* ORDER BY EMPNUM ASC END-EXEC
DISPLAY "OPEN C14734;"
* EXEC SQL OPEN C14734;
CALL "SUB75" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
MOVE "xxxxxxxxxxxxxxxxxxxx" TO ename
MOVE "xxxxxxxxxxxxxxxxxxxx" TO ename2
MOVE 0 TO gr
MOVE "xxx" TO en
MOVE "xxxxxxxxxxxxxxx" TO ct
MOVE 0 TO sl
MOVE 2 TO indic1
MOVE 2 TO indic2
DISPLAY "FETCH C14734 INTO :en, :gr, :ename, :ct,"
DISPLAY " :sl:indic1, :ename2:indic2;"
* EXEC SQL FETCH C14734 INTO :en, :gr, :ename, :ct,
* :sl:indic1, :ename2:indic2;
CALL "SUB76" USING SQLCODE SQLSTATE en gr ename ct sl
indic1 ename2 indic2
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "en should be 'E2 '; its value is '", en, "'"
DISPLAY "gr should be 10; its value is ", gr
DISPLAY "ename should be 'Betty '; its value
- " is '", ename, "'"
DISPLAY "ct should be 'Vienna '; its value is '",
ct, "'"
DISPLAY "indic1 should be -1; its value is ", indic1
DISPLAY "indic2 should be -1; its value is ", indic2
if (en NOT = "E2 " OR gr NOT = 10) then
MOVE 0 TO flag
END-IF
if (ename NOT = "Betty ") then
MOVE 0 TO flag
END-IF
if (ct NOT = "Vienna ") then
MOVE 0 TO flag
END-IF
if (indic1 NOT = -1 OR indic2 NOT = -1) then
MOVE 0 TO flag
END-IF
MOVE "xxxxxxxxxxxxxxxxxxxx" TO ename
MOVE "xxxxxxxxxxxxxxxxxxxx" TO ename2
MOVE 0 TO gr
MOVE "xxx" TO en
MOVE "xxxxxxxxxxxxxxx" TO ct
MOVE 0 TO sl
MOVE 2 TO indic1
MOVE 2 TO indic2
DISPLAY "FETCH C14734 INTO :en, :gr, :ename, :ct,"
DISPLAY " :sl:indic1, :ename2:indic2;"
* EXEC SQL FETCH C14734 INTO :en, :gr, :ename, :ct,
* :sl:indic1, :ename2:indic2;
CALL "SUB77" USING SQLCODE SQLSTATE en gr ename ct
sl indic1 ename2 indic2
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "en should be 'E3 '; its value is '", en, "'"
DISPLAY "gr should be 13; its value is ", gr
DISPLAY "ename should be 'Carmen '; its value
- " is '", ename, "'"
DISPLAY "ct should be 'Vienna '; its value is '",
ct, "'"
DISPLAY "indic1 should be 0; its value is ", indic1
DISPLAY "sl should be 13000; its value is ", sl
DISPLAY "indic2 should be 0; its value is ", indic2
DISPLAY "ename2 should be 'Carmen '; its value
- " is '", ename2, "'"
if (en NOT = "E3 " OR gr NOT = 13) then
MOVE 0 TO flag
END-IF
if (ename NOT = "Carmen ") then
MOVE 0 TO flag
END-IF
if (ct NOT = "Vienna ") then
MOVE 0 TO flag
END-IF
if (indic1 NOT = 0 OR indic2 NOT = 0) then
MOVE 0 TO flag
END-IF
if (sl NOT = 13000 OR ename2 NOT = "Carmen
- " ") then
MOVE 0 TO flag
END-IF
MOVE "xxxxxxxxxxxxxxxxxxxx" TO ename
MOVE "xxxxxxxxxxxxxxxxxxxx" TO ename2
MOVE 0 TO gr
MOVE "xxx" TO en
MOVE "xxxxxxxxxxxxxxx" TO ct
MOVE 0 TO sl
MOVE 2 TO indic1
MOVE 2 TO indic2
DISPLAY "FETCH C14734 INTO :en, :gr, :ename, :ct,"
DISPLAY " :sl:indic1, :ename2:indic2;"
* EXEC SQL FETCH C14734 INTO :en, :gr, :ename, :ct,
* :sl:indic1, :ename2:indic2;
CALL "SUB78" USING SQLCODE SQLSTATE en gr ename ct sl
indic1 ename2 indic2
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "en should be 'E4 '; its value is '", en, "'"
DISPLAY "gr should be 12; its value is ", gr
DISPLAY "ename should be 'Ali '; its value
- " is '", ename, "'"
DISPLAY "ct should be 'Deale '; its value is '",
ct, "'"
DISPLAY "indic1 should be 0; its value is ", indic1
DISPLAY "sl should be 12000; its value is ", sl
DISPLAY "indic2 should be 0; its value is ", indic2
DISPLAY "ename2 should be 'Don '; its value
- " is '", ename2, "'"
if (en NOT = "E4 " OR gr NOT = 12) then
MOVE 0 TO flag
END-IF
if (ename NOT = "Ali ") then
MOVE 0 TO flag
END-IF
if (ct NOT = "Deale ") then
MOVE 0 TO flag
END-IF
if (indic1 NOT = 0 OR indic2 NOT = 0) then
MOVE 0 TO flag
END-IF
if (sl NOT = 12000 OR ename2 NOT = "Don
- " ") then
MOVE 0 TO flag
END-IF
MOVE "xxxxxxxxxxxxxxxxxxxx" TO ename
MOVE "xxxxxxxxxxxxxxxxxxxx" TO ename2
MOVE 0 TO gr
MOVE "xxx" TO en
MOVE "xxxxxxxxxxxxxxx" TO ct
MOVE 0 TO sl
MOVE 2 TO indic1
MOVE 2 TO indic2
DISPLAY "FETCH C14734 INTO :en, :gr, :ename, :ct,"
DISPLAY " :sl:indic1, :ename2:indic2;"
* EXEC SQL FETCH C14734 INTO :en, :gr, :ename, :ct,
* :sl:indic1, :ename2:indic2;
CALL "SUB79" USING SQLCODE SQLSTATE en gr ename ct sl
indic1 ename2 indic2
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "en should be 'E6 '; its value is '", en, "'"
DISPLAY "gr should be 13; its value is ", gr
DISPLAY "ename should be 'Ed '; its value
- " is '", ename, "'"
DISPLAY "ct should be 'Akron '; its value is '",
ct, "'"
DISPLAY "indic1 should be -1; its value is ", indic1
DISPLAY "indic2 should be -1; its value is ", indic2
if (en NOT = "E6 " OR gr NOT = 13) then
MOVE 0 TO flag
END-IF
if (ename NOT = "Ed ") then
MOVE 0 TO flag
END-IF
if (ct NOT = "Akron ") then
MOVE 0 TO flag
END-IF
if (indic1 NOT = -1 OR indic2 NOT = -1) then
MOVE 0 TO flag
END-IF
DISPLAY "FETCH C14734 INTO :en, :gr, :ename, :ct,"
DISPLAY " :sl:indic1, :ename2:indic2;"
* EXEC SQL FETCH C14734 INTO :en, :gr, :ename, :ct,
* :sl:indic1, :ename2:indic2;
CALL "SUB80" USING SQLCODE SQLSTATE en gr ename ct sl
indic1 ename2 indic2
MOVE SQLCODE TO SQL-COD
DISPLAY "SQLCODE should be 100; its value is ", SQL-COD
DISPLAY "SQLSTATE should be 02000; its value is ", SQLSTATE
PERFORM NOSUBCLASS THRU EXIT-NOSUBCLASS
if (SQLCODE NOT = 100 OR NORMSQ NOT = "02000") then
MOVE 0 TO flag
END-IF
if (NORMSQ = "02000" AND NORMSQ NOT = SQLSTATE)
then
DISPLAY "Valid implementation-defined SQLSTATE accepted."
END-IF
DISPLAY " "
DISPLAY "CLOSE C14734;"
* EXEC SQL CLOSE C14734;
CALL "SUB81" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
*Similar to above, except for explicit names of columns
MOVE "xxxxxxxxxxxxxxxxxxxx" TO ename
MOVE "xxxxxxxxxxxxxxxxxxxx" TO ename2
MOVE 0 TO gr
MOVE "xxx" TO en
MOVE "xxxxxxxxxxxxxxx" TO ct
MOVE 0 TO sl
DISPLAY "SELECT EMPNUM, GRADE, HU.STAFF3.EMPNAME, CITY,"
DISPLAY " SALARY, STAFF66.EMPNAME"
DISPLAY " INTO :en, :gr, :ename, :ct, :sl, :ename2"
DISPLAY " FROM HU.STAFF3 LEFT JOIN STAFF66"
DISPLAY " USING (GRADE, EMPNUM)"
DISPLAY " WHERE EMPNUM = 'E3';"
* EXEC SQL SELECT EMPNUM, GRADE, HU.STAFF3.EMPNAME, CITY,
* SALARY, STAFF66.EMPNAME
* INTO :en, :gr, :ename, :ct, :sl, :ename2
* FROM HU.STAFF3 LEFT JOIN STAFF66
* USING (GRADE, EMPNUM)
* WHERE EMPNUM = 'E3';
CALL "SUB82" USING SQLCODE SQLSTATE en gr ename ct sl ename2
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "en should be 'E3 '; its value is '", en, "'"
DISPLAY "gr should be 13; its value is ", gr
DISPLAY "ename should be 'Carmen '; its value
- " is '", ename, "'"
DISPLAY "ct should be 'Vienna '; its value is '",
ct, "'"
DISPLAY "sl should be 13000; its value is ", sl
DISPLAY "ename2 should be 'Carmen '; its value
- " is '", ename2, "'"
if (en NOT = "E3 " OR gr NOT = 13) then
MOVE 0 TO flag
END-IF
if (ename NOT = "Carmen ") then
MOVE 0 TO flag
END-IF
if (ct NOT = "Vienna ") then
MOVE 0 TO flag
END-IF
if (sl NOT = 13000 OR ename2 NOT = "Carmen
- " ") then
MOVE 0 TO flag
END-IF
DISPLAY "DECLARE C14735 CURSOR FOR"
DISPLAY " SELECT EMPNUM, GRADE, HU.STAFF3.EMPNAME, CITY,"
DISPLAY " SALARY, STAFF66.EMPNAME"
DISPLAY " FROM HU.STAFF3 LEFT JOIN STAFF66"
DISPLAY " USING (GRADE, EMPNUM)"
DISPLAY " WHERE EMPNUM = 'E3';"
* EXEC SQL DECLARE C14735 CURSOR FOR
* SELECT EMPNUM, GRADE, HU.STAFF3.EMPNAME, CITY,
* SALARY, STAFF66.EMPNAME
* FROM HU.STAFF3 LEFT JOIN STAFF66
* USING (GRADE, EMPNUM)
* WHERE EMPNUM = 'E3' END-EXEC
DISPLAY "OPEN C14735;"
* EXEC SQL OPEN C14735;
CALL "SUB83" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
MOVE "xxxxxxxxxxxxxxxxxxxx" TO ename
MOVE "xxxxxxxxxxxxxxxxxxxx" TO ename2
MOVE 0 TO gr
MOVE "xxx" TO en
MOVE "xxxxxxxxxxxxxxx" TO ct
MOVE 0 TO sl
DISPLAY "FETCH C14735 INTO :en, :gr, :ename, :ct, :sl,
- " :ename2;"
* EXEC SQL FETCH C14735 INTO :en, :gr, :ename, :ct, :sl,
* :ename2;
CALL "SUB84" USING SQLCODE SQLSTATE en gr ename ct sl ename2
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "en should be 'E3 '; its value is '", en, "'"
DISPLAY "gr should be 13; its value is ", gr
DISPLAY "ename should be 'Carmen '; its value
- " is '", ename, "'"
DISPLAY "ct should be 'Vienna '; its value is '",
ct, "'"
DISPLAY "sl should be 13000; its value is ", sl
DISPLAY "ename2 should be 'Carmen '; its value
- " is '", ename2, "'"
if (en NOT = "E3 " OR gr NOT = 13) then
MOVE 0 TO flag
END-IF
if (ename NOT = "Carmen ") then
MOVE 0 TO flag
END-IF
if (ct NOT = "Vienna ") then
MOVE 0 TO flag
END-IF
if (sl NOT = 13000 OR ename2 NOT = "Carmen
- " ") then
MOVE 0 TO flag
END-IF
DISPLAY "CLOSE C14735;"
* EXEC SQL CLOSE C14735;
CALL "SUB85" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
*REF: 7.5 GR 1 d ii This is a cartesian product
MOVE 0 TO int1
DISPLAY "SELECT COUNT (*) INTO :int1"
DISPLAY " FROM STAFF66 NATURAL RIGHT JOIN HU.PROJ;"
* EXEC SQL SELECT COUNT (*) INTO :int1
* FROM STAFF66 NATURAL RIGHT JOIN HU.PROJ;
CALL "SUB86" USING SQLCODE SQLSTATE int1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "int1 should be 18; its value is ", int1
if (int1 NOT = 18) then
MOVE 0 TO flag
END-IF
*FULL OUTER JOIN of tables with unique data in the joined column
DISPLAY "DECLARE C14736 CURSOR FOR"
DISPLAY " SELECT EMPNUM, CITY, SALARY"
DISPLAY " FROM HU.STAFF3 LEFT JOIN STAFF66 USING
- " (EMPNUM)"
DISPLAY " UNION"
DISPLAY " SELECT EMPNUM, CITY, SALARY"
DISPLAY " FROM HU.STAFF3 RIGHT JOIN STAFF66 USING
- " (EMPNUM)"
DISPLAY " ORDER BY EMPNUM;"
* EXEC SQL DECLARE C14736 CURSOR FOR
* SELECT EMPNUM, CITY, SALARY
* FROM HU.STAFF3 LEFT JOIN STAFF66 USING (EMPNUM)
* UNION
* SELECT EMPNUM, CITY, SALARY
* FROM HU.STAFF3 RIGHT JOIN STAFF66 USING (EMPNUM)
* ORDER BY EMPNUM END-EXEC
DISPLAY "OPEN C14736;"
* EXEC SQL OPEN C14736;
CALL "SUB87" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
MOVE "xxx" TO en
MOVE "xxxxxxxxxxxxxxx" TO ct
MOVE 0 TO indic1
DISPLAY "FETCH C14736 INTO :en, :ct, :sl:indic1;"
* EXEC SQL FETCH C14736 INTO :en, :ct, :sl:indic1;
CALL "SUB88" USING SQLCODE SQLSTATE en ct sl indic1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "en should be 'E1 '; its value is '", en, "'"
DISPLAY "ct should be 'Deale '; its value is '",
ct, "'"
DISPLAY "indic1 should be -1; its value is ", indic1
if (en NOT = "E1 " OR ct NOT = "Deale ")
then
MOVE 0 TO flag
END-IF
if (indic1 NOT = -1) then
MOVE 0 TO flag
END-IF
MOVE "xxx" TO en
MOVE "xxxxxxxxxxxxxxx" TO ct
MOVE 0 TO indic1
DISPLAY "FETCH C14736 INTO :en, :ct, :sl:indic1;"
* EXEC SQL FETCH C14736 INTO :en, :ct, :sl:indic1;
CALL "SUB89" USING SQLCODE SQLSTATE en ct sl indic1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "en should be 'E2 '; its value is '", en, "'"
DISPLAY "ct should be 'Vienna '; its value is '",
ct, "'"
DISPLAY "indic1 should be -1; its value is ", indic1
if (en NOT = "E2 " OR ct NOT = "Vienna ")
then
MOVE 0 TO flag
END-IF
if (indic1 NOT = -1) then
MOVE 0 TO flag
END-IF
MOVE "xxx" TO en
MOVE "xxxxxxxxxxxxxxx" TO ct
MOVE 0 TO sl
COMPUTE indic1 = -1
DISPLAY "FETCH C14736 INTO :en, :ct, :sl:indic1;"
* EXEC SQL FETCH C14736 INTO :en, :ct, :sl:indic1;
CALL "SUB90" USING SQLCODE SQLSTATE en ct sl indic1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "en should be 'E3 '; its value is '", en, "'"
DISPLAY "ct should be 'Vienna '; its value is '",
ct, "'"
DISPLAY "sl should be 13000; its value is ", sl
DISPLAY "indic1 should be 0; its value is ", indic1
if (en NOT = "E3 " OR ct NOT = "Vienna ")
then
MOVE 0 TO flag
END-IF
if (sl NOT = 13000 OR indic1 NOT = 0) then
MOVE 0 TO flag
END-IF
MOVE "xxx" TO en
MOVE "xxxxxxxxxxxxxxx" TO ct
MOVE 0 TO sl
COMPUTE indic1 = -1
DISPLAY "FETCH C14736 INTO :en, :ct, :sl:indic1;"
* EXEC SQL FETCH C14736 INTO :en, :ct, :sl:indic1;
CALL "SUB91" USING SQLCODE SQLSTATE en ct sl indic1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "en should be 'E4 '; its value is '", en, "'"
DISPLAY "ct should be 'Deale '; its value is '",
ct, "'"
DISPLAY "sl should be 12000; its value is ", sl
DISPLAY "indic1 should be 0; its value is ", indic1
if (en NOT = "E4 " OR ct NOT = "Deale ")
then
MOVE 0 TO flag
END-IF
if (sl NOT = 12000 OR indic1 NOT = 0) then
MOVE 0 TO flag
END-IF
MOVE "xxx" TO en
MOVE "xxxxxxxxxxxxxxx" TO ct
MOVE 0 TO sl
MOVE 0 TO indic1
DISPLAY "FETCH C14736 INTO :en, :ct:indic1, :sl;"
* EXEC SQL FETCH C14736 INTO :en, :ct:indic1, :sl;
CALL "SUB92" USING SQLCODE SQLSTATE en ct indic1 sl
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "en should be 'E5 '; its value is '", en, "'"
DISPLAY "sl should be 13000; its value is ", sl
DISPLAY "indic1 should be -1; its value is ", indic1
if (en NOT = "E5 ") then
MOVE 0 TO flag
END-IF
if (sl NOT = 13000 OR indic1 NOT = -1) then
MOVE 0 TO flag
END-IF
MOVE "xxx" TO en
MOVE "xxxxxxxxxxxxxxx" TO ct
MOVE 0 TO indic1
DISPLAY "FETCH C14736 INTO :en, :ct, :sl:indic1;"
* EXEC SQL FETCH C14736 INTO :en, :ct, :sl:indic1;
CALL "SUB93" USING SQLCODE SQLSTATE en ct sl indic1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "en should be 'E6 '; its value is '", en, "'"
DISPLAY "ct should be 'Akron '; its value is '",
ct, "'"
DISPLAY "indic1 should be -1; its value is ", indic1
if (en NOT = "E6 " OR ct NOT = "Akron ")
then
MOVE 0 TO flag
END-IF
if (indic1 NOT = -1) then
MOVE 0 TO flag
END-IF
DISPLAY "FETCH C14736 INTO :en, :ct, :sl:indic1;"
* EXEC SQL FETCH C14736 INTO :en, :ct, :sl:indic1;
CALL "SUB94" USING SQLCODE SQLSTATE en ct sl indic1
MOVE SQLCODE TO SQL-COD
DISPLAY "SQLCODE should be 100; its value is ", SQL-COD
DISPLAY "SQLSTATE should be 02000; its value is ", SQLSTATE
PERFORM NOSUBCLASS THRU EXIT-NOSUBCLASS
if (SQLCODE NOT = 100 OR NORMSQ NOT = "02000") then
MOVE 0 TO flag
END-IF
if (NORMSQ = "02000" AND NORMSQ NOT = SQLSTATE)
then
DISPLAY "Valid implementation-defined SQLSTATE accepted."
END-IF
DISPLAY " "
DISPLAY "ROLLBACK WORK;"
* EXEC SQL ROLLBACK WORK;
CALL "SUB95" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "DROP TABLE STAFF66 CASCADE;"
* EXEC SQL DROP TABLE STAFF66 CASCADE;
CALL "SUB96" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
* EXEC SQL COMMIT WORK;
CALL "SUB97" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
if ( flag = 1 ) then
DISPLAY " *** pass *** "
* EXEC SQL INSERT INTO HU.TESTREPORT
* VALUES('0842','pass','MCO');
CALL "SUB98" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
else
DISPLAY " dml147.sco *** fail *** "
* EXEC SQL INSERT INTO HU.TESTREPORT
* VALUES('0842','fail','MCO');
CALL "SUB99" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
COMPUTE errcnt = errcnt + 1
END-IF
DISPLAY "==============================================="
--> --------------------
--> maximum size reached
--> --------------------
¤ Dauer der Verarbeitung: 0.99 Sekunden
(vorverarbeitet)
¤
|
Haftungshinweis
Die Informationen auf dieser Webseite wurden
nach bestem Wissen sorgfältig zusammengestellt. Es wird jedoch weder Vollständigkeit, noch Richtigkeit,
noch Qualität der bereit gestellten Informationen zugesichert.
Bemerkung:
Die farbliche Syntaxdarstellung ist noch experimentell.
|
