IDENTIFICATION DIVISION .
PROGRAM-ID . DML160.
ENVIRONMENT DIVISION .
CONFIGURATION SECTION .
SOURCE-COMPUTER . xyz.
OBJECT-COMPUTER . xyz.
DATA DIVISION .
WORKING-STORAGE SECTION .
* EMBEDDED COBOL (file "DML160.PCO")
****************************************************************
*
* COMMENT SECTION
*
* DATE 1995/12/11 EMBEDDED 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.PCO
* WRITTEN BY: David Flater
* TRANSLATED AUTOMATICALLY FROM EMBEDDED C BY CHRIS SCHANZLE
*
* This routine tests joined tables.
*
* REFERENCES
* FIPS PUB 127-2 14.2 Intermediate 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 emnum PIC X(3).
01 cnth PIC S9(9) DISPLAY SIGN LEADING SEPARATE .
01 xhour PIC S9(9) DISPLAY SIGN LEADING SEPARATE .
01 xminit PIC S9(9) DISPLAY SIGN LEADING SEPARATE .
01 flt1 PIC S9(4)V9(4) 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 END-EXEC
MOVE SQLCODE TO SQL-COD
EXEC SQL ROLLBACK WORK END-EXEC
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, Embedded COBOL, dml160.pco"
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 TEST0859 *******************
MOVE 1 TO flag
DISPLAY " TEST0859"
DISPLAY " contained in "
DISPLAY "References:"
DISPLAY " 7.5 SR.4.b -- set function in
- " condition>"
DISPLAY " F# 4 -- Joined table"
DISPLAY " F# 47 -- Scalar subquery values"
DISPLAY " 6.11 LR.2.e"
DISPLAY " - - - - - - - - - - - - - - - - - - -"
COMPUTE SQLCODE = -1
MOVE "xxxxx" TO SQLSTATE
*<join condition> ::= ON <search condition>
*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
-- 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 END-EXEC
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 END-EXEC
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 END-EXEC
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 END-EXEC
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 END-EXEC
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 END-EXEC
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
DISPLAY "COMMIT WORK;"
EXEC SQL COMMIT WORK END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
if ( flag = 1 ) then
DISPLAY " *** pass *** "
EXEC SQL INSERT INTO HU.TESTREPORT
VALUES('0859','pass','PCO') END-EXEC
MOVE SQLCODE TO SQL-COD
else
DISPLAY " dml160.pco *** fail *** "
EXEC SQL INSERT INTO HU.TESTREPORT
VALUES('0859','fail','PCO') END-EXEC
MOVE SQLCODE TO SQL-COD
COMPUTE errcnt = errcnt + 1
END-IF
DISPLAY "==============================================="
EXEC SQL COMMIT WORK END-EXEC
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)
* END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
EXEC SQL COMMIT WORK END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
*Similar, for people who think in radians
DISPLAY "CREATE DOMAIN RAD_EPOCH_TYPE FLOAT (20)"
DISPLAY " CHECK (VALUE BETWEEN 0E0 AND 2E0 * 3.1416E0);"
* EXEC SQL CREATE DOMAIN RAD_EPOCH_TYPE FLOAT (20)
* CHECK (VALUE BETWEEN 0E0 AND 2E0 * 3.1416E0) END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
EXEC SQL COMMIT WORK END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "CREATE DOMAIN RAD_EPOCH_NOT_NORM REAL;"
* EXEC SQL CREATE DOMAIN RAD_EPOCH_NOT_NORM REAL END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
EXEC SQL COMMIT WORK END-EXEC
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') END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
EXEC SQL COMMIT WORK END-EXEC
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') END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
EXEC SQL COMMIT WORK END-EXEC
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')
END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
EXEC SQL COMMIT WORK END-EXEC
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) END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
EXEC SQL COMMIT WORK END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "CREATE VIEW CONST_RAD (LOC_ID, CONST_ID,"
DISPLAY " AMPLITUDE, EPOCH) AS"
DISPLAY " SELECT LOC_ID, CONST_ID, AMPLITUDE,"
DISPLAY " CAST (EPOCH * 3.14159265358979E0 / 180E0 AS
- " RAD_EPOCH_TYPE)"
DISPLAY " FROM TIDES.CONSTITUENTS;"
EXEC SQL CREATE VIEW CONST_RAD (LOC_ID, CONST_ID,
AMPLITUDE, EPOCH) AS
SELECT LOC_ID, CONST_ID, AMPLITUDE,
CAST (EPOCH * 3.14159265358979E0 / 180E0 AS
RAD_EPOCH_TYPE)
FROM TIDES.CONSTITUENTS END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
EXEC SQL COMMIT WORK END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "CREATE VIEW CONST_RAD_NOT_NORM (LOC_ID, CONST_ID,"
DISPLAY " AMPLITUDE, EPOCH) AS"
DISPLAY " SELECT LOC_ID, CONST_ID, AMPLITUDE,"
DISPLAY " CAST (EPOCH * 3.14159265358979E0 / 180E0 AS
- " RAD_EPOCH_NOT_NORM)"
DISPLAY " FROM CONST_NOT_NORM;"
EXEC SQL CREATE VIEW CONST_RAD_NOT_NORM (LOC_ID, CONST_ID,
AMPLITUDE, EPOCH) AS
SELECT LOC_ID, CONST_ID, AMPLITUDE,
CAST (EPOCH * 3.14159265358979E0 / 180E0 AS
RAD_EPOCH_NOT_NORM)
FROM CONST_NOT_NORM END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
EXEC SQL COMMIT WORK END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
*List of tide predictions that are pending
DISPLAY "CREATE TABLE PENDING ("
DISPLAY " LOC_ID DEC (7) NOT NULL,"
DISPLAY " FROMTIME TIDETIMESTAMP NOT NULL,"
DISPLAY " TOTIME TIDETIMESTAMP NOT NULL,"
DISPLAY " CHECK (FROMTIME <= TOTIME),"
DISPLAY " JOB_ID INT PRIMARY KEY);"
EXEC SQL CREATE TABLE PENDING (
LOC_ID DEC (7) NOT NULL,
FROMTIME TIDETIMESTAMP NOT NULL,
TOTIME TIDETIMESTAMP NOT NULL,
CHECK (FROMTIME <= TOTIME),
JOB_ID INT PRIMARY KEY) END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
EXEC SQL COMMIT WORK END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
*The data for the starting and ending days are hand-verified
*6.10 GR.9.c Casting timestamp to date throws out the time
*part. Which is good, considering that you can't
*EXTRACT (DATE FROM TIDEDATE).
DISPLAY "CREATE VIEW CHECK_PTS (CHECK_DATES, JOB_ID, FLAG)
- " AS"
DISPLAY " SELECT CAST (FROMTIME AS TIDEDATE), JOB_ID,"
DISPLAY " CAST (0 AS INT) FROM PENDING"
DISPLAY " UNION"
DISPLAY " SELECT CAST (TOTIME AS TIDEDATE), JOB_ID,"
DISPLAY " CAST (1 AS INT) FROM PENDING;"
EXEC SQL CREATE VIEW CHECK_PTS (CHECK_DATES, JOB_ID, FLAG)
AS
SELECT CAST (FROMTIME AS TIDEDATE), JOB_ID,
CAST (0 AS INT) FROM PENDING
UNION
SELECT CAST (TOTIME AS TIDEDATE), JOB_ID,
CAST (1 AS INT) FROM PENDING END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
EXEC SQL COMMIT WORK END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "CREATE TABLE DINNER_CLUB ("
DISPLAY " LOC_ID DEC (7) NOT NULL,"
DISPLAY " DINNER DINNERTIME);"
EXEC SQL CREATE TABLE DINNER_CLUB (
LOC_ID DEC (7) NOT NULL,
DINNER DINNERTIME) END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
EXEC SQL COMMIT WORK END-EXEC
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%'
END-EXEC
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') END-EXEC
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 END-EXEC
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.LOCATIONS VALUES (300,"
DISPLAY " 'Bath, Maine', -69.8133, 43.9183,"
DISPLAY " 1, 3.422E0, INTERVAL '-05:00' HOUR TO MINUTE,
- " ':US/Eastern');"
EXEC SQL INSERT INTO TIDES.LOCATIONS VALUES (300,
'Bath, Maine', -69.8133, 43.9183,
1, 3.422E0, INTERVAL '-05:00' HOUR TO MINUTE,
':US/Eastern') END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
*Check constraint on normalized table
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) END-EXEC
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 " "
*Load up some non-normalized data
DISPLAY "INSERT INTO CONST_NOT_NORM VALUES (300, 0,
- " 0.021E0, 151.6);"
EXEC SQL INSERT INTO CONST_NOT_NORM VALUES (300, 0,
0.021E0, 151.6) END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO CONST_NOT_NORM VALUES (300, 1,
- " 0.324E0, 144.5);"
EXEC SQL INSERT INTO CONST_NOT_NORM VALUES (300, 1,
0.324E0, 144.5) END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO CONST_NOT_NORM VALUES (300, 2,
- " 0.134E0, 385.0);"
EXEC SQL INSERT INTO CONST_NOT_NORM VALUES (300, 2,
0.134E0, 385.0) END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO CONST_NOT_NORM VALUES (300, 3,
- " 0.181E0, 40.9);"
EXEC SQL INSERT INTO CONST_NOT_NORM VALUES (300, 3,
0.181E0, 40.9) END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO CONST_NOT_NORM VALUES (300, 4,
- " 0.037E0, 150.0);"
EXEC SQL INSERT INTO CONST_NOT_NORM VALUES (300, 4,
0.037E0, 150.0) END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO CONST_NOT_NORM VALUES (300, 5,
- " 3.143E0, 352.3);"
EXEC SQL INSERT INTO CONST_NOT_NORM VALUES (300, 5,
3.143E0, 352.3) END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO CONST_NOT_NORM VALUES (300, 6,
- " 0.000E0, 50.0);"
EXEC SQL INSERT INTO CONST_NOT_NORM VALUES (300, 6,
0.000E0, 50.0) END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO CONST_NOT_NORM VALUES (300, 7,
- " 0.104E0, 242.8);"
EXEC SQL INSERT INTO CONST_NOT_NORM VALUES (300, 7,
0.104E0, 242.8) END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO CONST_NOT_NORM VALUES (300, 8,
- " 0.031E0, 158.6);"
EXEC SQL INSERT INTO CONST_NOT_NORM VALUES (300, 8,
0.031E0, 158.6) END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO CONST_NOT_NORM VALUES (300, 9,
- " 0.000E0, 133.3);"
EXEC SQL INSERT INTO CONST_NOT_NORM VALUES (300, 9,
0.000E0, 133.3) END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO CONST_NOT_NORM VALUES (300, 10,
- " 0.744E0, 322.0);"
EXEC SQL INSERT INTO CONST_NOT_NORM VALUES (300, 10,
0.744E0, 322.0) END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO CONST_NOT_NORM VALUES (300, 11,
- " 0.087E0, 307.4);"
EXEC SQL INSERT INTO CONST_NOT_NORM VALUES (300, 11,
0.087E0, 307.4) END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO CONST_NOT_NORM VALUES (300, 12,
- " 0.260E0, 130.4);"
EXEC SQL INSERT INTO CONST_NOT_NORM VALUES (300, 12,
0.260E0, 130.4) END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO CONST_NOT_NORM VALUES (300, 13,
- " 0.011E0, 158.7);"
EXEC SQL INSERT INTO CONST_NOT_NORM VALUES (300, 13,
0.011E0, 158.7) END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO CONST_NOT_NORM VALUES (300, 14,
- " 0.107E0, 140.8);"
EXEC SQL INSERT INTO CONST_NOT_NORM VALUES (300, 14,
0.107E0, 140.8) END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO CONST_NOT_NORM VALUES (300, 15,
- " 0.043E0, 114.3);"
EXEC SQL INSERT INTO CONST_NOT_NORM VALUES (300, 15,
0.043E0, 114.3) END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO CONST_NOT_NORM VALUES (300, 16,
- " 0.007E0, 116.4);"
EXEC SQL INSERT INTO CONST_NOT_NORM VALUES (300, 16,
0.007E0, 116.4) END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO CONST_NOT_NORM VALUES (300, 17,
- " 0.004E0, 383.2);"
EXEC SQL INSERT INTO CONST_NOT_NORM VALUES (300, 17,
0.004E0, 383.2) END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO CONST_NOT_NORM VALUES (300, 18,
- " 0.000E0, 17.3);"
EXEC SQL INSERT INTO CONST_NOT_NORM VALUES (300, 18,
0.000E0, 17.3) END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO CONST_NOT_NORM VALUES (300, 19,
- " 0.488E0, 383.4);"
EXEC SQL INSERT INTO CONST_NOT_NORM VALUES (300, 19,
0.488E0, 383.4) END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO CONST_NOT_NORM VALUES (300, 20,
- " 0.000E0, 69.0);"
EXEC SQL INSERT INTO CONST_NOT_NORM VALUES (300, 20,
0.000E0, 69.0) END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO CONST_NOT_NORM VALUES (300, 21,
- " 0.000E0, 103.5);"
EXEC SQL INSERT INTO CONST_NOT_NORM VALUES (300, 21,
0.000E0, 103.5) END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO CONST_NOT_NORM VALUES (300, 22,
- " 0.053E0, 365.8);"
EXEC SQL INSERT INTO CONST_NOT_NORM VALUES (300, 22,
0.053E0, 365.8) END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO CONST_NOT_NORM VALUES (300, 23,
- " 0.053E0, 37.3);"
EXEC SQL INSERT INTO CONST_NOT_NORM VALUES (300, 23,
0.053E0, 37.3) END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO CONST_NOT_NORM VALUES (300, 24,
- " 0.023E0, 297.8);"
EXEC SQL INSERT INTO CONST_NOT_NORM VALUES (300, 24,
0.023E0, 297.8) END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO CONST_NOT_NORM VALUES (300, 25,
- " 0.138E0, 328.3);"
EXEC SQL INSERT INTO CONST_NOT_NORM VALUES (300, 25,
0.138E0, 328.3) END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO CONST_NOT_NORM VALUES (300, 26,
- " 0.010E0, 124.4);"
EXEC SQL INSERT INTO CONST_NOT_NORM VALUES (300, 26,
0.010E0, 124.4) END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO CONST_NOT_NORM VALUES (300, 27,
- " 0.000E0, 50.6);"
EXEC SQL INSERT INTO CONST_NOT_NORM VALUES (300, 27,
0.000E0, 50.6) END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO CONST_NOT_NORM VALUES (300, 28,
- " 0.000E0, 49.4);"
EXEC SQL INSERT INTO CONST_NOT_NORM VALUES (300, 28,
0.000E0, 49.4) END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO CONST_NOT_NORM VALUES (300, 29,
- " 0.000E0, 66.0);"
EXEC SQL INSERT INTO CONST_NOT_NORM VALUES (300, 29,
0.000E0, 66.0) END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO CONST_NOT_NORM VALUES (300, 30,
- " 0.000E0, 67.8);"
EXEC SQL INSERT INTO CONST_NOT_NORM VALUES (300, 30,
0.000E0, 67.8) END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO CONST_NOT_NORM VALUES (300, 31,
- " 0.000E0, 35.7);"
EXEC SQL INSERT INTO CONST_NOT_NORM VALUES (300, 31,
0.000E0, 35.7) END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO CONST_NOT_NORM VALUES (300, 32,
- " 0.073E0, 285.0);"
EXEC SQL INSERT INTO CONST_NOT_NORM VALUES (300, 32,
0.073E0, 285.0) END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO CONST_NOT_NORM VALUES (300, 33,
- " 0.033E0, 257.3);"
EXEC SQL INSERT INTO CONST_NOT_NORM VALUES (300, 33,
0.033E0, 257.3) END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO CONST_NOT_NORM VALUES (300, 34,
- " 0.000E0, 0.6);"
EXEC SQL INSERT INTO CONST_NOT_NORM VALUES (300, 34,
0.000E0, 0.6) END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO CONST_NOT_NORM VALUES (300, 35,
- " 0.056E0, 128.8);"
EXEC SQL INSERT INTO CONST_NOT_NORM VALUES (300, 35,
0.056E0, 128.8) END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO CONST_NOT_NORM VALUES (300, 36,
- " 0.038E0, 97.7);"
EXEC SQL INSERT INTO CONST_NOT_NORM VALUES (300, 36,
0.038E0, 97.7) END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
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 END-EXEC
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 END-EXEC
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
DISPLAY "INSERT INTO PENDING VALUES ("
DISPLAY " 300, TIMESTAMP '1995-12-15 00:00:00-05:00',"
DISPLAY " TIMESTAMP '1995-12-17 00:00:00-05:00', 0);"
EXEC SQL INSERT INTO PENDING VALUES (
300, TIMESTAMP '1995-12-15 00:00:00-05:00',
TIMESTAMP '1995-12-17 00:00:00-05:00', 0) END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
*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) END-EXEC
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 PENDING VALUES ("
DISPLAY " 101, TIMESTAMP '2025-12-30 19:00:00-05:00',"
DISPLAY " TIMESTAMP '2025-12-31 18:59:59-05:00', 1);"
EXEC SQL INSERT INTO PENDING VALUES (
101, TIMESTAMP '2025-12-30 19:00:00-05:00',
TIMESTAMP '2025-12-31 18:59:59-05:00', 1) END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO PENDING VALUES ("
DISPLAY " 102, TIMESTAMP '1993-12-31 19:00:00-05:00',"
DISPLAY " TIMESTAMP '1994-01-02 00:00:00-05:00', 2);"
EXEC SQL INSERT INTO PENDING VALUES (
102, TIMESTAMP '1993-12-31 19:00:00-05:00',
TIMESTAMP '1994-01-02 00:00:00-05:00', 2) END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
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 END-EXEC
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 END-EXEC
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') END-EXEC
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)) END-EXEC
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') END-EXEC
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 "COMMIT WORK;"
EXEC SQL COMMIT WORK END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
*Clean up the mess
DISPLAY "DROP DOMAIN EPOCH_NOT_NORM CASCADE;"
EXEC SQL DROP DOMAIN EPOCH_NOT_NORM CASCADE END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
EXEC SQL COMMIT WORK END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "DROP DOMAIN RAD_EPOCH_TYPE CASCADE;"
EXEC SQL DROP DOMAIN RAD_EPOCH_TYPE CASCADE END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
EXEC SQL COMMIT WORK END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "DROP DOMAIN RAD_EPOCH_NOT_NORM CASCADE;"
EXEC SQL DROP DOMAIN RAD_EPOCH_NOT_NORM CASCADE END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
EXEC SQL COMMIT WORK END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "DROP DOMAIN TIDEDATE CASCADE;"
EXEC SQL DROP DOMAIN TIDEDATE CASCADE END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
EXEC SQL COMMIT WORK END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "DROP DOMAIN TIDETIMESTAMP CASCADE;"
EXEC SQL DROP DOMAIN TIDETIMESTAMP CASCADE END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
EXEC SQL COMMIT WORK END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "DROP DOMAIN DINNERTIME CASCADE;"
EXEC SQL DROP DOMAIN DINNERTIME CASCADE END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
EXEC SQL COMMIT WORK END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "DROP TABLE CONST_NOT_NORM CASCADE;"
EXEC SQL DROP TABLE CONST_NOT_NORM CASCADE END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
EXEC SQL COMMIT WORK END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "DROP VIEW CONST_RAD CASCADE;"
EXEC SQL DROP VIEW CONST_RAD CASCADE END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
EXEC SQL COMMIT WORK END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "DROP TABLE PENDING CASCADE;"
EXEC SQL DROP TABLE PENDING CASCADE END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
EXEC SQL COMMIT WORK END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "DROP TABLE DINNER_CLUB CASCADE;"
EXEC SQL DROP TABLE DINNER_CLUB CASCADE END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
EXEC SQL COMMIT WORK END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "DELETE FROM TIDES.LOCATIONS"
DISPLAY " WHERE LOC_ID = 300;"
EXEC SQL DELETE FROM TIDES.LOCATIONS
WHERE LOC_ID = 300 END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
EXEC SQL COMMIT WORK END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
if ( flag = 1 ) then
DISPLAY " *** pass *** "
EXEC SQL INSERT INTO HU.TESTREPORT
VALUES('0860','pass','PCO') END-EXEC
MOVE SQLCODE TO SQL-COD
else
DISPLAY " dml160.pco *** fail *** "
EXEC SQL INSERT INTO HU.TESTREPORT
VALUES('0860','fail','PCO') END-EXEC
MOVE SQLCODE TO SQL-COD
COMPUTE errcnt = errcnt + 1
END-IF
DISPLAY "==============================================="
EXEC SQL COMMIT WORK END-EXEC
MOVE SQLCODE TO SQL-COD
******************** END TEST0860 ********************
**** TESTER MAY CHOOSE TO INSERT CODE FOR errcnt > 0
STOP RUN.
* **** 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
PERFORM VARYING 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
GO TO EXIT-NOSUBCLASS
END-IF
MOVE 4 TO norm1
*examining position 4 of char array NORMSQ
*valid characters are 0-9, A-Z
PERFORM VARYING 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
PERFORM VARYING 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.74 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.