|
IDENTIFICATION DIVISION.
PROGRAM-ID. DML062.
ENVIRONMENT DIVISION.
CONFIGURATION SECTION.
SOURCE-COMPUTER. xyz.
OBJECT-COMPUTER. xyz.
DATA DIVISION.
WORKING-STORAGE SECTION.
* EMBEDDED COBOL (file "DML062.PCO")
****************************************************************
*
* COMMENT SECTION
*
* DATE 1989/07/21 EMBEDDED 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.
*
* DML062.PCO
* WRITTEN BY: SUN DAJUN
* TRANSLATED AUTOMATICALLY FROM EMBEDDED C BY CHRIS SCHANZLE
*
* THIS ROUTINE TESTS COMMIT AND VIEW
*
* REFERENCES
* AMERICAN NATIONAL STANDARD database language - SQL
* X3.135-1989
*
* SECTION 8.2 <commit statement>
* SECTION 6.9 <view definition>
*
****************************************************************
EXEC SQL BEGIN DECLARE SECTION END-EXEC
01 test1 PIC X(20).
01 HOURS1 PIC S9(9) DISPLAY SIGN LEADING SEPARATE.
01 count2 PIC S9(9) DISPLAY SIGN LEADING SEPARATE.
01 count3 PIC S9(9) DISPLAY SIGN LEADING SEPARATE.
01 count4 PIC S9(9) DISPLAY SIGN LEADING SEPARATE.
01 count5 PIC S9(9) DISPLAY SIGN LEADING SEPARATE.
01 count1 PIC S9(9) DISPLAY SIGN LEADING SEPARATE.
01 SECND1 PIC X(1).
01 SECND2 PIC X(1).
01 PNUM1 PIC X(3).
01 EMPN1 PIC X(3).
01 EMPN2 PIC X(3).
01 uid PIC X(18).
01 uidx PIC X(18).
EXEC SQL END DECLARE SECTION END-EXEC
01 SQLCODE PIC S9(9) COMP.
01 errcnt PIC S9(4) DISPLAY SIGN LEADING SEPARATE.
01 i PIC S9(9) DISPLAY SIGN LEADING SEPARATE.
01 flag PIC S9(9) DISPLAY SIGN LEADING SEPARATE.
01 SQL-COD PIC S9(9) DISPLAY SIGN LEADING SEPARATE.
* date_time declaration *
01 TO-DAY PIC 9(6).
01 THE-TIME PIC 9(8).
PROCEDURE DIVISION.
P0.
MOVE "HU" TO uid
CALL "AUTHID" USING uid
MOVE "not logged in, not" TO uidx
EXEC SQL SELECT
USER INTO :uidx FROM HU.ECCO END-EXEC
if (uid NOT = uidx) then
DISPLAY "ERROR: User " uid " expected."
DISPLAY "User " uidx " connected."
DISPLAY " "
STOP RUN
END-IF
MOVE 0 TO errcnt
DISPLAY
"SQL Test Suite, V6.0, Embedded COBOL, dml062.pco"
DISPLAY " "
DISPLAY
"59-byte ID"
DISPLAY "TEd Version #"
DISPLAY " "
* date_time print *
ACCEPT TO-DAY FROM DATE
ACCEPT THE-TIME FROM TIME
DISPLAY "Date run YYMMDD: " TO-DAY " at hhmmssff: " THE-TIME
******************** BEGIN TEST0274 *******************
*COMMIT AND ROLLBACK across schemas
DISPLAY " COMMIT & ROLLBACK across schemas "
DISPLAY "Reference X3.135-1989 section 8.2 General Rules "
DISPLAY " ------------------------------------------ "
DISPLAY " TEST0274 "
DISPLAY " Reference 8.2 General Rules )"
DISPLAY " - - - - - - - - - - - - - - - - - - - - - "
DISPLAY " "
DISPLAY " DELETE FROM SULLIVAN1.AUTH_TABLE;"
DISPLAY " "
DISPLAY " INSERT INTO SULLIVAN1.AUTH_TABLE"
DISPLAY " VALUES (10,'A');"
DISPLAY " "
DISPLAY " INSERT INTO SULLIVAN1.AUTH_TABLE"
DISPLAY " VALUES (100,'B');"
DISPLAY " "
DISPLAY " DELETE FROM AA;"
DISPLAY " "
DISPLAY " INSERT INTO AA"
DISPLAY " VALUES ('In God we trust');"
DISPLAY " "
DISPLAY " COMMIT WORK;"
DISPLAY " "
DISPLAY " DELETE FROM SULLIVAN1.AUTH_TABLE;"
DISPLAY " "
DISPLAY " DELETE FROM AA;"
DISPLAY " ROLLBACK WORK;"
DISPLAY " "
DISPLAY " SELECT COUNT(*) INTO :count1 FROM
- " SULLIVAN1.AUTH_TABLE;"
DISPLAY " "
COMPUTE count1 = -1
EXEC SQL DELETE FROM SULLIVAN1.AUTH_TABLE END-EXEC
MOVE SQLCODE TO SQL-COD
EXEC SQL INSERT INTO SULLIVAN1.AUTH_TABLE
VALUES (10,'A') END-EXEC
MOVE SQLCODE TO SQL-COD
EXEC SQL INSERT INTO SULLIVAN1.AUTH_TABLE
VALUES (100,'B') END-EXEC
MOVE SQLCODE TO SQL-COD
EXEC SQL DELETE FROM AA END-EXEC
MOVE SQLCODE TO SQL-COD
EXEC SQL INSERT INTO AA
VALUES ('In God we trust') END-EXEC
MOVE SQLCODE TO SQL-COD
EXEC SQL COMMIT WORK END-EXEC
MOVE SQLCODE TO SQL-COD
EXEC SQL DELETE FROM SULLIVAN1.AUTH_TABLE END-EXEC
MOVE SQLCODE TO SQL-COD
EXEC SQL DELETE FROM AA END-EXEC
MOVE SQLCODE TO SQL-COD
EXEC SQL ROLLBACK WORK END-EXEC
MOVE SQLCODE TO SQL-COD
EXEC SQL SELECT COUNT(*) INTO :count1 FROM
SULLIVAN1.AUTH_TABLE END-EXEC
MOVE SQLCODE TO SQL-COD
MOVE "NV" TO test1
EXEC SQL SELECT CHARTEST INTO :test1 FROM AA END-EXEC
MOVE SQLCODE TO SQL-COD
EXEC SQL DELETE FROM AA END-EXEC
MOVE SQLCODE TO SQL-COD
EXEC SQL DELETE FROM SULLIVAN1.AUTH_TABLE END-EXEC
MOVE SQLCODE TO SQL-COD
DISPLAY " The correct answer is :"
DISPLAY " count1 = 2"
DISPLAY " test1 = In God we trust"
DISPLAY " Your answer is :"
DISPLAY " count1 = ", count1
DISPLAY " test1 =", test1
if (count1 = 2 AND test1 = "In God we trust") then
DISPLAY " *** pass *** "
EXEC SQL INSERT INTO TESTREPORT
VALUES('0274','pass','PCO') END-EXEC
MOVE SQLCODE TO SQL-COD
else
DISPLAY " dml062.pco *** fail *** "
EXEC SQL INSERT INTO TESTREPORT
VALUES('0274','fail','PCO') END-EXEC
ADD 1 TO errcnt
MOVE SQLCODE TO SQL-COD
END-IF
DISPLAY "==============================================="
EXEC SQL COMMIT WORK END-EXEC
MOVE SQLCODE TO SQL-COD
******************** END TEST0274 *******************
******************** BEGIN TEST0275 *******************
*COMMIT and ROLLBACK of multiple cursors
DISPLAY " COMMIT & ROLLBACK of multiple cursors "
DISPLAY "Reference X3.135-1989 section 8.2 General Rules "
DISPLAY " ------------------------------------------ "
DISPLAY " TEST0275 "
DISPLAY " Reference 8.2 General Rules )"
DISPLAY " - - - - - - - - - - - - - - - - - - - - - "
DISPLAY " "
DISPLAY " DECLARE CHICHI CURSOR FOR SELECT EMPNUM FROM
- " STAFF1"
DISPLAY " WHERE EMPNUM = 'E3';"
DISPLAY " "
DISPLAY " DECLARE MAC CURSOR FOR SELECT EMPNUM FROM
- " WORKS1"
DISPLAY " WHERE EMPNUM = 'E3';"
DISPLAY " "
DISPLAY " DECLARE KING CURSOR FOR SELECT PNUM FROM
- " PROJ1"
DISPLAY " WHERE PNUM = 'P2';"
DISPLAY " "
DISPLAY " UPDATE STAFF1"
DISPLAY " SET EMPNUM = 'E9'"
DISPLAY " WHERE CURRENT OF CHICHI;"
DISPLAY " "
DISPLAY " UPDATE WORKS1"
DISPLAY " SET EMPNUM = 'E9', PNUM = 'P9'"
DISPLAY " WHERE CURRENT OF MAC;"
DISPLAY " "
DISPLAY " UPDATE PROJ1"
DISPLAY " SET PNUM = 'P9'"
DISPLAY " WHERE CURRENT OF KING;"
DISPLAY " "
DISPLAY " COMMIT WORK;"
DISPLAY " "
DISPLAY " SELECT COUNT(*) INTO :count1 FROM "
DISPLAY " STAFF1,WORKS1,PROJ1"
DISPLAY " WHERE STAFF1.EMPNUM = 'E9' AND"
DISPLAY " STAFF1.EMPNUM = WORKS1.EMPNUM AND"
DISPLAY " PROJ1.PNUM = WORKS1.PNUM;"
EXEC SQL DELETE FROM STAFF1 END-EXEC
MOVE SQLCODE TO SQL-COD
DISPLAY " "
COMPUTE count1 = -1
EXEC SQL INSERT INTO STAFF1
SELECT * FROM STAFF END-EXEC
MOVE SQLCODE TO SQL-COD
EXEC SQL INSERT INTO WORKS1
SELECT * FROM WORKS END-EXEC
MOVE SQLCODE TO SQL-COD
EXEC SQL INSERT INTO PROJ1
SELECT * FROM PROJ END-EXEC
MOVE SQLCODE TO SQL-COD
EXEC SQL DECLARE CHICHI CURSOR FOR SELECT EMPNUM FROM STAFF1
WHERE EMPNUM = 'E3' END-EXEC
*FOR UPDATE OF EMPNUM;
EXEC SQL DECLARE MAC CURSOR FOR SELECT EMPNUM FROM WORKS1
WHERE EMPNUM = 'E3' END-EXEC
*FOR UPDATE OF EMPNUM, PNUM;
EXEC SQL DECLARE KING CURSOR FOR SELECT PNUM FROM PROJ1
WHERE PNUM = 'P2' END-EXEC
*FOR UPDATE OF PNUM;
EXEC SQL OPEN CHICHI END-EXEC
MOVE SQLCODE TO SQL-COD
EXEC SQL OPEN MAC END-EXEC
MOVE SQLCODE TO SQL-COD
EXEC SQL OPEN KING END-EXEC
MOVE SQLCODE TO SQL-COD
EXEC SQL FETCH CHICHI INTO :EMPN1 END-EXEC
MOVE SQLCODE TO SQL-COD
EXEC SQL FETCH MAC INTO :EMPN2 END-EXEC
MOVE SQLCODE TO SQL-COD
EXEC SQL FETCH KING INTO :PNUM1 END-EXEC
MOVE SQLCODE TO SQL-COD
EXEC SQL UPDATE STAFF1
SET EMPNUM = 'E9'
WHERE CURRENT OF CHICHI END-EXEC
MOVE SQLCODE TO SQL-COD
EXEC SQL UPDATE WORKS1
SET EMPNUM = 'E9', PNUM = 'P9'
WHERE CURRENT OF MAC END-EXEC
MOVE SQLCODE TO SQL-COD
EXEC SQL UPDATE PROJ1
SET PNUM = 'P9'
WHERE CURRENT OF KING END-EXEC
MOVE SQLCODE TO SQL-COD
EXEC SQL COMMIT WORK END-EXEC
MOVE SQLCODE TO SQL-COD
EXEC SQL SELECT COUNT(*) INTO :count1 FROM
STAFF1,WORKS1,PROJ1
WHERE STAFF1.EMPNUM = 'E9' AND
STAFF1.EMPNUM = WORKS1.EMPNUM AND
PROJ1.PNUM = WORKS1.PNUM END-EXEC
MOVE SQLCODE TO SQL-COD
EXEC SQL DELETE FROM STAFF1 END-EXEC
MOVE SQLCODE TO SQL-COD
EXEC SQL DELETE FROM PROJ1 END-EXEC
MOVE SQLCODE TO SQL-COD
EXEC SQL DELETE FROM WORKS1 END-EXEC
MOVE SQLCODE TO SQL-COD
DISPLAY " The correct answer is :"
DISPLAY " count1 = 1"
DISPLAY " Your answer is :"
DISPLAY " count1 = ", count1
if (count1 = 1) then
DISPLAY " *** pass *** "
EXEC SQL INSERT INTO TESTREPORT
VALUES('0275','pass','PCO') END-EXEC
MOVE SQLCODE TO SQL-COD
else
DISPLAY " dml062.pco *** fail *** "
EXEC SQL INSERT INTO TESTREPORT
VALUES('0275','fail','PCO') END-EXEC
ADD 1 TO errcnt
MOVE SQLCODE TO SQL-COD
END-IF
DISPLAY "==============================================="
EXEC SQL COMMIT WORK END-EXEC
MOVE SQLCODE TO SQL-COD
******************** END TEST0275 *******************
******************** BEGIN TEST0276 *******************
*View across schemas
DISPLAY " VIEW across schemas "
DISPLAY "Reference X3.135-1989 section 6.9 General Rules "
DISPLAY " ------------------------------------------ "
DISPLAY " TEST0276 "
DISPLAY " Reference 6.9 General Rules )"
DISPLAY " - - - - - - - - - - - - - - - - - - - - - "
DISPLAY " "
DISPLAY " DELETE FROM SULLIVAN1.AUTH_TABLE;"
DISPLAY " "
DISPLAY " INSERT INTO SULLIVAN1.AUTH_TABLE"
DISPLAY " VALUES (12,'A');"
DISPLAY " "
DISPLAY " DECLARE PIGGY CURSOR FOR"
DISPLAY " SELECT EMPNUM,SECOND2 FROM
- " SULLIVAN1.MUL_SCH"
DISPLAY " ORDER BY EMPNUM;"
DISPLAY " "
EXEC SQL DELETE FROM SULLIVAN1.AUTH_TABLE END-EXEC
MOVE SQLCODE TO SQL-COD
EXEC SQL INSERT INTO SULLIVAN1.AUTH_TABLE
VALUES (12,'A') END-EXEC
MOVE SQLCODE TO SQL-COD
EXEC SQL DECLARE PIGGY CURSOR FOR
SELECT EMPNUM,SECOND2 FROM SULLIVAN1.MUL_SCH
ORDER BY EMPNUM END-EXEC
EXEC SQL OPEN PIGGY END-EXEC
MOVE SQLCODE TO SQL-COD
EXEC SQL FETCH PIGGY INTO :EMPN1, :SECND1 END-EXEC
MOVE SQLCODE TO SQL-COD
EXEC SQL FETCH PIGGY INTO :EMPN2, :SECND2 END-EXEC
MOVE SQLCODE TO SQL-COD
EXEC SQL CLOSE PIGGY END-EXEC
MOVE SQLCODE TO SQL-COD
EXEC SQL ROLLBACK WORK END-EXEC
MOVE SQLCODE TO SQL-COD
DISPLAY " The correct answer is :"
DISPLAY " EMPN1 = E1"
DISPLAY " SECND1 = A"
DISPLAY " EMPN2 = E4"
DISPLAY " SECND2 = A"
DISPLAY " Your answer is :"
DISPLAY " EMPN1 = ", EMPN1
DISPLAY " SECND1 = ", SECND1
DISPLAY " EMPN2 = ", EMPN2
DISPLAY " SECND2 = ", SECND2
MOVE 0 TO flag
if (EMPN1 NOT = "E1") then
COMPUTE flag = flag + 1
END-IF
if (EMPN2 NOT = "E4") then
COMPUTE flag = flag + 1
END-IF
if (SECND1 = "A" AND SECND2 = "A" AND flag =
0) then
DISPLAY " *** pass *** "
EXEC SQL INSERT INTO TESTREPORT
VALUES('0276','pass','PCO') END-EXEC
MOVE SQLCODE TO SQL-COD
else
DISPLAY " dml062.pco *** fail *** "
EXEC SQL INSERT INTO TESTREPORT
VALUES('0276','fail','PCO') END-EXEC
ADD 1 TO errcnt
MOVE SQLCODE TO SQL-COD
END-IF
DISPLAY "==============================================="
EXEC SQL COMMIT WORK END-EXEC
MOVE SQLCODE TO SQL-COD
******************** END TEST0276 *******************
******************** BEGIN TEST0279 *******************
*IN is a 3-valued predicate, EXISTS is 2-valued
DISPLAY " IN is a 3-valued predicate, EXISTS is 2-valued"
DISPLAY " Reference X3.135-1989 :"
DISPLAY " section 5.13 General Rules "
DISPLAY " section 5.17 General Rules "
DISPLAY " ------------------------------------------ "
DISPLAY " TEST0279 "
DISPLAY " Reference 5.13 5.17 General Rules )"
DISPLAY " - - - - - - - - - - - - - - - - - - - - - "
DISPLAY " "
DISPLAY " UPDATE WORKS"
DISPLAY " SET HOURS = NULL"
DISPLAY " WHERE PNUM = 'P5' OR EMPNUM = 'E4';"
DISPLAY " "
DISPLAY " SELECT COUNT(*) INTO :count1 FROM STAFF;"
DISPLAY " "
DISPLAY " SELECT COUNT(*) INTO :count2 FROM STAFF"
DISPLAY " WHERE 40 IN (SELECT HOURS FROM WORKS"
DISPLAY " WHERE STAFF.EMPNUM =
- " WORKS.EMPNUM);"
DISPLAY " "
DISPLAY " SELECT COUNT(*) INTO :count3 FROM STAFF"
DISPLAY " WHERE 40 NOT IN (SELECT HOURS FROM WORKS"
DISPLAY " WHERE STAFF.EMPNUM =
- " WORKS.EMPNUM);"
DISPLAY " "
DISPLAY " SELECT COUNT(*) INTO :count4 FROM STAFF"
DISPLAY " WHERE EXISTS (SELECT * FROM WORKS"
DISPLAY " WHERE HOURS = 40 AND STAFF.EMPNUM =
- " WORKS.EMPNUM);"
DISPLAY " "
DISPLAY " SELECT COUNT(*) INTO :count5 FROM STAFF"
DISPLAY " WHERE NOT EXISTS (SELECT * FROM WORKS"
DISPLAY " WHERE HOURS = 40 AND STAFF.EMPNUM =
- " WORKS.EMPNUM);"
DISPLAY " "
COMPUTE count1 = -1
COMPUTE count2 = -1
COMPUTE count3 = -1
COMPUTE count4 = -1
COMPUTE count5 = -1
EXEC SQL UPDATE WORKS
SET HOURS = NULL
WHERE PNUM = 'P5' OR EMPNUM = 'E4' END-EXEC
MOVE SQLCODE TO SQL-COD
EXEC SQL SELECT COUNT(*) INTO :count1 FROM STAFF END-EXEC
MOVE SQLCODE TO SQL-COD
EXEC SQL SELECT COUNT(*) INTO :count2 FROM STAFF
WHERE 40 IN (SELECT HOURS FROM WORKS
WHERE STAFF.EMPNUM = WORKS.EMPNUM) END-EXEC
MOVE SQLCODE TO SQL-COD
EXEC SQL SELECT COUNT(*) INTO :count3 FROM STAFF
WHERE 40 NOT IN (SELECT HOURS FROM WORKS
WHERE STAFF.EMPNUM = WORKS.EMPNUM) END-EXEC
MOVE SQLCODE TO SQL-COD
EXEC SQL SELECT COUNT(*) INTO :count4 FROM STAFF
WHERE EXISTS (SELECT * FROM WORKS
WHERE HOURS = 40 AND STAFF.EMPNUM = WORKS.EMPNUM) END-EXEC
MOVE SQLCODE TO SQL-COD
EXEC SQL SELECT COUNT(*) INTO :count5 FROM STAFF
WHERE NOT EXISTS (SELECT * FROM WORKS
WHERE HOURS = 40 AND STAFF.EMPNUM = WORKS.EMPNUM) END-EXEC
MOVE SQLCODE TO SQL-COD
EXEC SQL ROLLBACK WORK END-EXEC
MOVE SQLCODE TO SQL-COD
DISPLAY " The correct answer is :"
DISPLAY " count1 = 5"
DISPLAY " count2 = 2"
DISPLAY " count3 = 2"
DISPLAY " count4 = 2"
DISPLAY " count5 = 3"
DISPLAY " Your answer is :"
DISPLAY " count1 = ", count1
DISPLAY " count2 = ", count2
DISPLAY " count3 = ", count3
DISPLAY " count4 = ", count4
DISPLAY " count5 = ", count5
MOVE 0 TO flag
if (count1 NOT = 5 OR count2 NOT = 2 OR count3 NOT =
2) then
MOVE 1 TO flag
END-IF
if (count4 NOT = 2 OR count5 NOT = 3) then
MOVE 1 TO flag
END-IF
if (flag = 0) then
DISPLAY " *** pass *** "
EXEC SQL INSERT INTO TESTREPORT
VALUES('0279','pass','PCO') END-EXEC
MOVE SQLCODE TO SQL-COD
else
DISPLAY " dml062.pco *** fail *** "
EXEC SQL INSERT INTO TESTREPORT
VALUES('0279','fail','PCO') END-EXEC
ADD 1 TO errcnt
MOVE SQLCODE TO SQL-COD
END-IF
DISPLAY "==============================================="
EXEC SQL COMMIT WORK END-EXEC
MOVE SQLCODE TO SQL-COD
******************** END TEST0279 *******************
**** TESTER MAY CHOOSE TO INSERT CODE FOR errcnt > 0
STOP RUN.
* **** Procedures for PERFORM statements
¤ Dauer der Verarbeitung: 0.9 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.
|
