* Standard COBOL (file "DML160.SCO") calling SQL * procedures in file "DML160.MCO".
**************************************************************** * * COMMENT SECTION * * DATE 1995/12/11 Module COBOL LANGUAGE * 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. * * DML160.SCO * WRITTEN BY: David Flater * TRANSLATED AUTOMATICALLY FROM EMBEDDED COBOL BY CHRIS SCHANZLE * * This routine tests joined tables. * * REFERENCES * FIPS PUB 127-2 14.2 Intermediate SQL * ANSI SQL-1992 * ****************************************************************
*Count the number of projects that each employee is working on *that meet certain budgetary criteria
DISPLAY"DECLARE C16011 CURSOR FOR" DISPLAY" SELECT EMPNUM, (SELECT COUNT(*) FROM HU.WORKS
- " JOIN HU.PROJ" DISPLAY" ON HU.WORKS.PNUM = HU.PROJ.PNUM" DISPLAY" AND BUDGET > AVG (OSTAFF.GRADE) * 1000" DISPLAY" WHERE HU.WORKS.EMPNUM = OSTAFF.EMPNUM) FROM
- " HU.STAFF AS OSTAFF" DISPLAY" ORDER BY 2, 1;" * EXEC SQL DECLARE C16011 CURSOR FOR * SELECT EMPNUM, (SELECT COUNT(*) FROM HU.WORKS JOIN * HU.PROJ * ON HU.WORKS.PNUM = HU.PROJ.PNUM * AND BUDGET > AVG (OSTAFF.GRADE) * 1000 * WHERE HU.WORKS.EMPNUM = OSTAFF.EMPNUM) FROM HU.STAFF AS * OSTAFF * ORDER BY 2, 1 END-EXEC
DISPLAY"OPEN C16011;" * EXEC SQL OPEN C16011; CALL"SUB3"USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY" "
MOVE"xxx"TO emnum COMPUTE cnth = -1 DISPLAY"FETCH C16011 INTO :emnum, :cnth;" * EXEC SQL FETCH C16011 INTO :emnum, :cnth; CALL"SUB4"USING SQLCODE SQLSTATE emnum cnth MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"emnum should be 'E5 '; its value is '", emnum, "'" DISPLAY"cnth should be 0; its value is ", cnth if (emnum NOT = "E5 "OR cnth NOT = 0) then MOVE 0 TO flag END-IF
MOVE"xxx"TO emnum COMPUTE cnth = -1 DISPLAY"FETCH C16011 INTO :emnum, :cnth;" * EXEC SQL FETCH C16011 INTO :emnum, :cnth; CALL"SUB5"USING SQLCODE SQLSTATE emnum cnth MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"emnum should be 'E2 '; its value is '", emnum, "'" DISPLAY"cnth should be 1; its value is ", cnth if (emnum NOT = "E2 "OR cnth NOT = 1) then MOVE 0 TO flag END-IF
MOVE"xxx"TO emnum COMPUTE cnth = -1 DISPLAY"FETCH C16011 INTO :emnum, :cnth;" * EXEC SQL FETCH C16011 INTO :emnum, :cnth; CALL"SUB6"USING SQLCODE SQLSTATE emnum cnth MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"emnum should be 'E3 '; its value is '", emnum, "'" DISPLAY"cnth should be 1; its value is ", cnth if (emnum NOT = "E3 "OR cnth NOT = 1) then MOVE 0 TO flag END-IF
MOVE"xxx"TO emnum COMPUTE cnth = -1 DISPLAY"FETCH C16011 INTO :emnum, :cnth;" * EXEC SQL FETCH C16011 INTO :emnum, :cnth; CALL"SUB7"USING SQLCODE SQLSTATE emnum cnth MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"emnum should be 'E4 '; its value is '", emnum, "'" DISPLAY"cnth should be 2; its value is ", cnth if (emnum NOT = "E4 "OR cnth NOT = 2) then MOVE 0 TO flag END-IF
MOVE"xxx"TO emnum COMPUTE cnth = -1 DISPLAY"FETCH C16011 INTO :emnum, :cnth;" * EXEC SQL FETCH C16011 INTO :emnum, :cnth; CALL"SUB8"USING SQLCODE SQLSTATE emnum cnth MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"emnum should be 'E1 '; its value is '", emnum, "'" DISPLAY"cnth should be 4; its value is ", cnth if (emnum NOT = "E1 "OR cnth NOT = 4) then MOVE 0 TO flag END-IF
* EXEC SQL COMMIT WORK; CALL"SUB12"USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD ******************** END TEST0859 ******************** ******************** BEGIN TEST0860 ******************* MOVE 1 TO flag
DISPLAY" TEST0860" DISPLAY" Domains over various data types" DISPLAY"References:" DISPLAY" F# 25 -- Domain definition" DISPLAY" F# 41 -- Time zone specification" DISPLAY" F# 5 -- DATETIME data types" DISPLAY" F# 6 -- VARCHAR data type" DISPLAY" F# 8 -- Union in views" DISPLAY" F# 17 -- Multiple schemas per user" DISPLAY" F# 20 -- CAST functions" DISPLAY" - - - - - - - - - - - - - - - - - - -"
COMPUTE SQLCODE = -1 MOVE"xxxxx"TO SQLSTATE
*Some people insist on using epochs outside of the traditional *0 to 360 range, so may as well use implementation-defined *precision too. DISPLAY"CREATE DOMAIN EPOCH_NOT_NORM AS DECIMAL (5, 2);" * EXEC SQL CREATE DOMAIN EPOCH_NOT_NORM AS DECIMAL (5, 2) * ; CALL"SUB13"USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY" "
*We only have data for 1994 through 2025 DISPLAY"CREATE DOMAIN TIDEDATE AS DATE" DISPLAY" CHECK (VALUE BETWEEN DATE '1994-01-01' AND DATE
- " '2025-12-31');" * EXEC SQL CREATE DOMAIN TIDEDATE AS DATE * CHECK (VALUE BETWEEN DATE '1994-01-01' AND DATE * '2025-12-31'); CALL"SUB19"USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY" "
DISPLAY"CREATE DOMAIN TIDETIMESTAMP AS TIMESTAMP WITH TIME
- " ZONE" DISPLAY" CHECK (VALUE BETWEEN TIMESTAMP '1994-01-01
- " 00:00:00+00:00'" DISPLAY" AND TIMESTAMP '2025-12-31 23:59:59+00:00');" * EXEC SQL CREATE DOMAIN TIDETIMESTAMP AS TIMESTAMP WITH TIME * ZONE * CHECK (VALUE BETWEEN TIMESTAMP '1994-01-01 * 00:00:00+00:00' * AND TIMESTAMP '2025-12-31 23:59:59+00:00'); CALL"SUB21"USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY" "
*...and furthermore, we have very specific notions about dinner. DISPLAY"CREATE DOMAIN DINNERTIME AS TIME" DISPLAY" CHECK (VALUE BETWEEN TIME '17:30:00' AND TIME
- " '19:00:00');" * EXEC SQL CREATE DOMAIN DINNERTIME AS TIME * CHECK (VALUE BETWEEN TIME '17:30:00' AND TIME '19:00:00') * ; CALL"SUB23"USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY" "
*Re-invent the wheel in our default schema because we have non- *normalized data and think in radians. Eventually the non- *normalized data will be normalized and moved into the main *schema (we hope).
DISPLAY"CREATE TABLE CONST_NOT_NORM (" DISPLAY" LOC_ID DEC (7) NOT NULL," DISPLAY" CONST_ID TIDES.CONST_ID_TYPE NOT NULL," DISPLAY" UNIQUE (LOC_ID, CONST_ID)," DISPLAY" AMPLITUDE TIDES.AMPLITUDE_TYPE," DISPLAY" EPOCH EPOCH_NOT_NORM);" * EXEC SQL CREATE TABLE CONST_NOT_NORM ( * LOC_ID DEC (7) NOT NULL, * CONST_ID TIDES.CONST_ID_TYPE NOT NULL, * UNIQUE (LOC_ID, CONST_ID), * AMPLITUDE TIDES.AMPLITUDE_TYPE, * EPOCH EPOCH_NOT_NORM); CALL"SUB25"USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY" "
*First try out the DOUBLE PRECISION and INTERVAL domains that ar *already in TIDES.
MOVE 500 TO xhour MOVE 500 TO xminit DISPLAY"SELECT EXTRACT (HOUR FROM MERIDIAN), EXTRACT" DISPLAY" (MINUTE FROM MERIDIAN) INTO :xhour, :xminit" DISPLAY" FROM TIDES.LOCATIONS WHERE LOC_NAME LIKE '", , "ewfound", ";" * EXEC SQL SELECT EXTRACT (HOUR FROM MERIDIAN), EXTRACT * (MINUTE FROM MERIDIAN) INTO :xhour, :xminit * FROM TIDES.LOCATIONS WHERE LOC_NAME LIKE '%Newfound%' * ; CALL"SUB37"USING SQLCODE SQLSTATE xhour xminit MOVE SQLCODE TO SQL-COD PERFORM CHCKOK *Sign of results specified by 6.6 GR.3.a.i DISPLAY"xhour should be -3; its value is ", xhour DISPLAY"xminit should be -30; its value is ", xminit if (xhour NOT = -3 OR xminit NOT = -30) then MOVE 0 TO flag END-IF
*There is no GMT-13. Violation of domain constraint 9.2 GR.4 *Integrity constraint violation
DISPLAY"INSERT INTO TIDES.LOCATIONS VALUES (" DISPLAY" 300, 'Atlantis', 160.0000, 3.0000, 0, 1.2E0," DISPLAY" INTERVAL -'13:00' HOUR TO MINUTE, 'GMT-13');" * EXEC SQL INSERT INTO TIDES.LOCATIONS VALUES ( * 300, 'Atlantis', 160.0000, 3.0000, 0, 1.2E0, * INTERVAL -'13:00' HOUR TO MINUTE, 'GMT-13'); CALL"SUB38"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 23000; its value is ", SQLSTATE PERFORM NOSUBCLASS THRU EXIT-NOSUBCLASS if (NORMSQ NOT = "23000") then MOVE 0 TO flag END-IF if (NORMSQ = "23000"AND NORMSQ NOT = SQLSTATE) then DISPLAY"Valid implementation-defined SQLSTATE accepted." END-IF DISPLAY" "
*Negative amplitudes also illegal
DISPLAY"UPDATE TIDES.CONSTITUENTS" DISPLAY" SET AMPLITUDE = - AMPLITUDE" DISPLAY" WHERE LOC_ID = 100" DISPLAY" AND CONST_ID = 0;" * EXEC SQL UPDATE TIDES.CONSTITUENTS * SET AMPLITUDE = - AMPLITUDE * WHERE LOC_ID = 100 * AND CONST_ID = 0; CALL"SUB39"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 23000; its value is ", SQLSTATE PERFORM NOSUBCLASS THRU EXIT-NOSUBCLASS if (NORMSQ NOT = "23000") then MOVE 0 TO flag END-IF if (NORMSQ = "23000"AND NORMSQ NOT = SQLSTATE) then DISPLAY"Valid implementation-defined SQLSTATE accepted." END-IF DISPLAY" "
DISPLAY"INSERT INTO TIDES.CONSTITUENTS VALUES (300, 2,
- " 0.134E0, 385.0);" * EXEC SQL INSERT INTO TIDES.CONSTITUENTS VALUES (300, 2, * 0.134E0, 385.0); CALL"SUB41"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 23000; its value is ", SQLSTATE PERFORM NOSUBCLASS THRU EXIT-NOSUBCLASS if (NORMSQ NOT = "23000") then MOVE 0 TO flag END-IF if (NORMSQ = "23000"AND NORMSQ NOT = SQLSTATE) then DISPLAY"Valid implementation-defined SQLSTATE accepted." END-IF DISPLAY" "
MOVE 0.0 TO flt1 DISPLAY"SELECT EPOCH INTO :flt1 FROM CONST_RAD" DISPLAY" WHERE LOC_ID = 100" DISPLAY" AND CONST_ID = 0;" * EXEC SQL SELECT EPOCH INTO :flt1 FROM CONST_RAD * WHERE LOC_ID = 100 * AND CONST_ID = 0; CALL"SUB79"USING SQLCODE SQLSTATE flt1 MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"flt1 should be 2.11 += 0.01; its value is ", flt1 if (flt1 < 2.10 OR flt1 > 2.12) then MOVE 0 TO flag END-IF
COMPUTE cnth = -1 DISPLAY"SELECT COUNT(*) INTO :cnth" DISPLAY" FROM CONST_RAD_NOT_NORM" DISPLAY" WHERE EPOCH > 6.2831853E0;" * EXEC SQL SELECT COUNT(*) INTO :cnth * FROM CONST_RAD_NOT_NORM * WHERE EPOCH > 6.2831853E0; CALL"SUB80"USING SQLCODE SQLSTATE cnth MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"cnth should be 4; its value is ", cnth if (cnth NOT = 4) then MOVE 0 TO flag END-IF
*Check that constraint. *7PM EST is 12AM GMT, which is outside the constraint
DISPLAY"INSERT INTO PENDING VALUES (" DISPLAY" 101, TIMESTAMP '2025-12-30 19:00:00-05:00'," DISPLAY" TIMESTAMP '2025-12-31 19:00:00-05:00', 1);" * EXEC SQL INSERT INTO PENDING VALUES ( * 101, TIMESTAMP '2025-12-30 19:00:00-05:00', * TIMESTAMP '2025-12-31 19:00:00-05:00', 1); CALL"SUB82"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 23000; its value is ", SQLSTATE PERFORM NOSUBCLASS THRU EXIT-NOSUBCLASS if (NORMSQ NOT = "23000") then MOVE 0 TO flag END-IF if (NORMSQ = "23000"AND NORMSQ NOT = SQLSTATE) then DISPLAY"Valid implementation-defined SQLSTATE accepted." END-IF DISPLAY" "
*1993-12-31 19:00:00-05:00 is within the constraint because it's *midnight GMT. Unfortunately, when we cast it to TIDEDATE, *it ends up being just 1993-12-31, which does not meet the *constraint. 6.10 GR.9.c
DISPLAY"SELECT EXTRACT (YEAR FROM CHECK_DATES)" DISPLAY" INTO :cnth" DISPLAY" FROM CHECK_PTS WHERE JOB_ID = 2 AND FLAG = 0;" * EXEC SQL SELECT EXTRACT (YEAR FROM CHECK_DATES) * INTO :cnth * FROM CHECK_PTS WHERE JOB_ID = 2 AND FLAG = 0; CALL"SUB85"USING SQLCODE SQLSTATE cnth 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 23000; its value is ", SQLSTATE PERFORM NOSUBCLASS THRU EXIT-NOSUBCLASS if (NORMSQ NOT = "23000") then MOVE 0 TO flag END-IF if (NORMSQ = "23000"AND NORMSQ NOT = SQLSTATE) then DISPLAY"Valid implementation-defined SQLSTATE accepted." END-IF DISPLAY" "
COMPUTE cnth = -1 DISPLAY"SELECT EXTRACT (YEAR FROM CHECK_DATES)" DISPLAY" INTO :cnth" DISPLAY" FROM CHECK_PTS WHERE JOB_ID = 2 AND FLAG = 1;" * EXEC SQL SELECT EXTRACT (YEAR FROM CHECK_DATES) * INTO :cnth * FROM CHECK_PTS WHERE JOB_ID = 2 AND FLAG = 1; CALL"SUB86"USING SQLCODE SQLSTATE cnth MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"cnth should be 1994; its value is ", cnth if (cnth NOT = 1994) then MOVE 0 TO flag END-IF
DISPLAY"INSERT INTO DINNER_CLUB VALUES" DISPLAY" (0, TIME '17:30:00');" * EXEC SQL INSERT INTO DINNER_CLUB VALUES * (0, TIME '17:30:00'); CALL"SUB87"USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY" "
DISPLAY"INSERT INTO DINNER_CLUB VALUES" DISPLAY" (1, CAST (TIME '18:00:00' AS DINNERTIME));" * EXEC SQL INSERT INTO DINNER_CLUB VALUES * (1, CAST (TIME '18:00:00' AS DINNERTIME)); CALL"SUB88"USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY" "
DISPLAY"INSERT INTO DINNER_CLUB VALUES" DISPLAY" (2, TIME '19:30:00');" * EXEC SQL INSERT INTO DINNER_CLUB VALUES * (2, TIME '19:30:00'); CALL"SUB89"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 23000; its value is ", SQLSTATE PERFORM NOSUBCLASS THRU EXIT-NOSUBCLASS if (NORMSQ NOT = "23000") then MOVE 0 TO flag END-IF if (NORMSQ = "23000"AND NORMSQ NOT = SQLSTATE) then DISPLAY"Valid implementation-defined SQLSTATE accepted." END-IF DISPLAY" "
* EXEC SQL COMMIT WORK; CALL"SUB115"USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD ******************** END TEST0860 ******************** **** TESTER MAY CHOOSE TO INSERT CODE FOR errcnt > 0 STOPRUN.
* **** Procedures for PERFORM statements
*Test SQLCODE and SQLSTATE for normal completion.
CHCKOK. DISPLAY"SQLCODE should be 0; its value is ", SQL-COD DISPLAY"SQLSTATE should be 00000; its value is ", SQLSTATE PERFORM NOSUBCLASS THRU EXIT-NOSUBCLASS if (SQLCODE NOT = 0 OR NORMSQ NOT = "00000") then MOVE 0 TO flag END-IF if (NORMSQ = "00000"AND NORMSQ NOT = SQLSTATE) then DISPLAY"Valid implementation-defined SQLSTATE accepted." END-IF
.
NOSUBCLASS.
*This routine replaces valid implementation-defined *subclasses with 000. This replacement equates valid *implementation-defined subclasses with the 000 value *expected by the test case; otherwise the test will fail. *After calling NOSUBCLASS, NORMSQ will be tested * SQLSTATE will be printed.
MOVE SQLSTATE TO NORMSQ
MOVE 3 TO norm1 *subclass begins in position 3 of char array NORMSQ *valid subclass begins with 5-9, I-Z, end of ALPNUM table PERFORMVARYING norm2 FROM 14 BY 1 UNTIL norm2 > 36 if (NORMSQX(norm1) = ALPNUM(norm2)) then MOVE"0"TO NORMSQX(norm1) END-IF END-PERFORM
*Quit if NORMSQ is unchanged. Subclass is not impl.-def. *Changed NORMSQ means implementation-defined subclass, *so proceed to zero it out, if valid (0-9,A-Z) if (NORMSQ = SQLSTATE) then GOTO EXIT-NOSUBCLASS END-IF
MOVE 4 TO norm1 *examining position 4 of char array NORMSQ *valid characters are 0-9, A-Z PERFORMVARYING norm2 FROM 1 BY 1 UNTIL norm2 > 36 if (NORMSQX(norm1) = ALPNUM(norm2)) then MOVE"0"TO NORMSQX(norm1) END-IF END-PERFORM
MOVE 5 TO norm1 *valid characters are 0-9, A-Z *examining position 5 of char array NORMSQ PERFORMVARYING norm2 FROM 1 BY 1 UNTIL norm2 > 36 if (NORMSQX(norm1) = ALPNUM(norm2)) then MOVE"0"TO NORMSQX(norm1) END-IF END-PERFORM
*implementation-defined subclasses are allowed for warnings *(class = 01). These equate to successful completion *SQLSTATE values of 00000. *Reference SQL-92 4.28 SQL-transactions, paragraph 2
if (NORMSQX(1) = "0"AND NORMSQX(2) = "1") then MOVE"0"TO NORMSQX(2) END-IF
.
EXIT-NOSUBCLASS. EXIT.
¤ Dauer der Verarbeitung: 0.44 Sekunden
(vorverarbeitet)
¤
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.
