| Quellcodebibliothek Statistik Leitseite products/Sources/formale Sprachen/COBOL/Test-Suite/SQL P/dml100-186/ Datei vom 30.6.2008 mit Größe 117 kB |
|
Quelle dml113.cobSprache: Cobol |
|||||||||||||||
|
IDENTIFICATION DIVISION. PROGRAM-ID. DML113. ENVIRONMENT DIVISION. CONFIGURATION SECTION. SOURCE-COMPUTER. xyz. OBJECT-COMPUTER. xyz. DATA DIVISION. WORKING-STORAGE SECTION. * EMBEDDED COBOL (file "DML113.PCO") **************************************************************** * * COMMENT SECTION * * DATE 1993/11/10 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. * * DML113.PCO * WRITTEN BY: David W. Flater * TRANSLATED AUTOMATICALLY FROM EMBEDDED C BY CHRIS SCHANZLE * * This routine tests NULLs with DATETIME data types and in * outer joins, datetimes in a <default clause>, TRIM, and also * some schema manipulation statements. * This is the dynamic version of DML112.PC. * * 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 int1 PIC S9(9) DISPLAY SIGN LEADING SEPARATE. 01 int2 PIC S9(9) DISPLAY SIGN LEADING SEPARATE. 01 ind1 PIC S9(4) DISPLAY SIGN LEADING SEPARATE. 01 chtime PIC X(8). 01 ch1 PIC X(11). 01 ch2 PIC X(14). 01 ch3 PIC X(14). 01 dstmt PIC X(50). 01 longst PIC X(240). 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, dml113.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 TEST0622 ******************* MOVE 1 TO flag DISPLAY " TEST0622 " DISPLAY " DATETIME NULLs (dynamic)" DISPLAY "References:" DISPLAY " FIPS PUB 127-2 14.1 Transitional SQL features - " 5,20" DISPLAY " - - - - - - - - - - - - - - - - - - -" COMPUTE SQLCODE = -1 MOVE "xxxxx" TO SQLSTATE EXEC SQL DELETE FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL INSERT INTO CONCATBUF VALUES ( 'CREATE TABLE MERCH (' || ' ITEMKEY INT,' || ' ORDERED DATE,' || ' RDATE DATE,' || ' RTIME TIME,' || ' SOLD TIMESTAMP)' ) END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "COMMIT WORK;" EXEC SQL COMMIT WORK END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "EXECUTE IMMEDIATE :longst;" EXEC SQL EXECUTE IMMEDIATE :longst 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 " " EXEC SQL DELETE FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL INSERT INTO CONCATBUF VALUES ( 'CREATE TABLE TURNAROUND (' || ' ITEMKEY INT,' || ' MWAIT INTERVAL MONTH,' || ' DWAIT INTERVAL DAY TO HOUR)' ) END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "COMMIT WORK;" EXEC SQL COMMIT WORK END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "EXECUTE IMMEDIATE :longst;" EXEC SQL EXECUTE IMMEDIATE :longst 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 " " *Do this with an outer join in a combined test later. *For FSQL, use SELECT DISTINCT (all that stuff) *Statement too long for CONCATBUF; break into two views. *Even now, this statement has whitespace removed to make it fit EXEC SQL DELETE FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL INSERT INTO CONCATBUF VALUES ( 'CREATE VIEW NULLINV AS' || ' SELECT ITEMKEY,ORDERED,CAST' || ' (NULL AS INTERVAL MONTH) AS MWAIT,CAST' || ' (NULL AS INTERVAL DAY TO HOUR) AS DWAIT FROM' || ' MERCH WHERE RDATE IS NOT NULL AND SOLD IS NULL' || ' AND MERCH.ITEMKEY NOT IN(SELECT ITEMKEY' || ' FROM TURNAROUND)' ) END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "COMMIT WORK;" EXEC SQL COMMIT WORK END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "EXECUTE IMMEDIATE :longst;" EXEC SQL EXECUTE IMMEDIATE :longst 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 " " EXEC SQL DELETE FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL INSERT INTO CONCATBUF VALUES ( 'CREATE VIEW INVENTORY AS' || ' SELECT MERCH.ITEMKEY AS ITEMKEY, ORDERED,' || ' MWAIT, DWAIT FROM MERCH, TURNAROUND COR1 WHERE RDATE' || ' IS NOT NULL AND SOLD IS NULL AND' || ' MERCH.ITEMKEY = COR1.ITEMKEY' || ' UNION' || ' SELECT * FROM NULLINV' ) END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "COMMIT WORK;" EXEC SQL COMMIT WORK END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "EXECUTE IMMEDIATE :longst;" EXEC SQL EXECUTE IMMEDIATE :longst 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 " " *Something ordered but not received EXEC SQL DELETE FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL INSERT INTO CONCATBUF VALUES ( 'INSERT INTO MERCH VALUES (' || ' 0, DATE ''1993-11-23'',' || ' NULL, NULL, NULL)' ) END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "EXECUTE IMMEDIATE :longst;" EXEC SQL EXECUTE IMMEDIATE :longst END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " *Something we don't know what time it arrived MOVE "12:34:56" TO chtime DISPLAY "chtime is 12:34:56" COMPUTE ind1 = -1 DISPLAY "ind1 = -1;" EXEC SQL DELETE FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL INSERT INTO CONCATBUF VALUES ( 'INSERT INTO MERCH VALUES (' || ' 1, DATE ''1993-12-10'',' || ' DATE ''1994-01-03'',' || ' CAST (? AS TIME), NULL)' ) END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "PREPARE S11311 FROM :longst;" EXEC SQL PREPARE S11311 FROM :longst END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "EXECUTE S11311 USING :chtime:ind1;" EXEC SQL EXECUTE S11311 USING :chtime:ind1 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " *Something back-ordered EXEC SQL DELETE FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL INSERT INTO CONCATBUF VALUES ( 'INSERT INTO MERCH VALUES (' || ' 2, DATE ''1993-12-11'',' || ' NULL, NULL,' || '1993-12-11 13:00:00' * ' CAST (''TIMESTAMP ''''1993-12-11 13:00:00''''' AS * TIMESTAMP)) ) END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "EXECUTE IMMEDIATE :longst;" EXEC SQL EXECUTE IMMEDIATE :longst END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " EXEC SQL DELETE FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL INSERT INTO CONCATBUF VALUES ( 'INSERT INTO MERCH VALUES (' || ' 4, DATE ''1993-01-26'', DATE ''1993-01-27'',' || ' NULL, NULL)' ) END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "EXECUTE IMMEDIATE :longst;" EXEC SQL EXECUTE IMMEDIATE :longst END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " EXEC SQL DELETE FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL INSERT INTO CONCATBUF VALUES ( 'INSERT INTO TURNAROUND VALUES (' || ' 2, INTERVAL ''1'' MONTH,' || ' INTERVAL ''20:0'' DAY TO HOUR)' ) END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "EXECUTE IMMEDIATE :longst;" EXEC SQL EXECUTE IMMEDIATE :longst END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " *We have only a vague idea of the turnaround for this thing DISPLAY "chtime is 20:0" MOVE "20:0 " TO chtime DISPLAY "ind1 = -1;" COMPUTE ind1 = -1 EXEC SQL DELETE FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL INSERT INTO CONCATBUF VALUES ( 'INSERT INTO TURNAROUND VALUES (' || ' 5, INTERVAL ''5'' MONTH,' || ' CAST (? AS INTERVAL DAY TO HOUR))' ) END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "PREPARE S11312 FROM :longst;" EXEC SQL PREPARE S11312 FROM :longst END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "EXECUTE S11312 USING :chtime:ind1;" EXEC SQL EXECUTE S11312 USING :chtime:ind1 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " EXEC SQL DELETE FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL INSERT INTO CONCATBUF VALUES ( 'INSERT INTO TURNAROUND VALUES' || ' (6, INTERVAL ''2'' MONTH, NULL)' ) END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "EXECUTE IMMEDIATE :longst;" EXEC SQL EXECUTE IMMEDIATE :longst END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " EXEC SQL DELETE FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL INSERT INTO CONCATBUF VALUES ( 'SELECT COUNT(*) FROM' || ' MERCH A, MERCH B WHERE A.SOLD = B.SOLD' ) END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "PREPARE S11313 FROM :longst;" EXEC SQL PREPARE S11313 FROM :longst END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C11313 CURSOR FOR S11313;" EXEC SQL DECLARE C11313 CURSOR FOR S11313 END-EXEC DISPLAY " " DISPLAY "OPEN C11313;" EXEC SQL OPEN C11313 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 DISPLAY "FETCH C11313 INTO :int1;" EXEC SQL FETCH C11313 INTO :int1 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "int1 should be 1; its value is ", int1 if (int1 NOT = 1) then MOVE 0 TO flag END-IF DISPLAY "CLOSE C11313;" EXEC SQL CLOSE C11313 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " EXEC SQL DELETE FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL INSERT INTO CONCATBUF VALUES ( 'SELECT COUNT(*) FROM' || ' MERCH A, MERCH B WHERE A.RTIME = B.RTIME' ) END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "PREPARE S11314 FROM :longst;" EXEC SQL PREPARE S11314 FROM :longst END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C11314 CURSOR FOR S11314;" EXEC SQL DECLARE C11314 CURSOR FOR S11314 END-EXEC DISPLAY " " DISPLAY "OPEN C11314;" EXEC SQL OPEN C11314 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 DISPLAY "FETCH C11314 INTO :int1;" EXEC SQL FETCH C11314 INTO :int1 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "int1 should be 0; its value is ", int1 if (int1 NOT = 0) then MOVE 0 TO flag END-IF DISPLAY "CLOSE C11314;" EXEC SQL CLOSE C11314 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "dstmt=""SELECT COUNT(*) FROM MERCH WHERE RDATE IS - " NULL""" MOVE "SELECT COUNT(*) FROM MERCH WHERE RDATE IS - " NULL " TO dstmt DISPLAY "PREPARE S11315 FROM :dstmt;" EXEC SQL PREPARE S11315 FROM :dstmt END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C11315 CURSOR FOR S11315;" EXEC SQL DECLARE C11315 CURSOR FOR S11315 END-EXEC DISPLAY " " DISPLAY "OPEN C11315;" EXEC SQL OPEN C11315 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 DISPLAY "FETCH C11315 INTO :int1;" EXEC SQL FETCH C11315 INTO :int1 END-EXEC 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 "CLOSE C11315;" EXEC SQL CLOSE C11315 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " EXEC SQL DELETE FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL INSERT INTO CONCATBUF VALUES ( 'SELECT COUNT(*) FROM TURNAROUND' || ' WHERE DWAIT IS NOT NULL' ) END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "PREPARE S11316 FROM :longst;" EXEC SQL PREPARE S11316 FROM :longst END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C11316 CURSOR FOR S11316;" EXEC SQL DECLARE C11316 CURSOR FOR S11316 END-EXEC DISPLAY " " DISPLAY "OPEN C11316;" EXEC SQL OPEN C11316 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 DISPLAY "FETCH C11316 INTO :int1;" EXEC SQL FETCH C11316 INTO :int1 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "int1 should be 1; its value is ", int1 if (int1 NOT = 1) then MOVE 0 TO flag END-IF DISPLAY "CLOSE C11316;" EXEC SQL CLOSE C11316 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " EXEC SQL DELETE FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL INSERT INTO CONCATBUF VALUES ( 'SELECT EXTRACT (DAY FROM RDATE)' || ' FROM MERCH, TURNAROUND WHERE MERCH.ITEMKEY =' || ' TURNAROUND.ITEMKEY' ) END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "PREPARE S11317 FROM :longst;" EXEC SQL PREPARE S11317 FROM :longst END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C11317 CURSOR FOR S11317;" EXEC SQL DECLARE C11317 CURSOR FOR S11317 END-EXEC DISPLAY " " DISPLAY "OPEN C11317;" EXEC SQL OPEN C11317 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 MOVE 2 TO ind1 DISPLAY "FETCH C11317 INTO :int1:ind1;" EXEC SQL FETCH C11317 INTO :int1:ind1 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "ind1 should be -1; its value is ", ind1 if (ind1 NOT = -1) then MOVE 0 TO flag END-IF DISPLAY "CLOSE C11317;" EXEC SQL CLOSE C11317 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "dstmt=""SELECT ITEMKEY FROM MERCH WHERE SOLD IS - " NOT NULL""" MOVE "SELECT ITEMKEY FROM MERCH WHERE SOLD IS NOT NULL " TO dstmt DISPLAY "PREPARE S11318 FROM :dstmt;" EXEC SQL PREPARE S11318 FROM :dstmt END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C11318 CURSOR FOR S11318;" EXEC SQL DECLARE C11318 CURSOR FOR S11318 END-EXEC DISPLAY " " DISPLAY "OPEN C11318;" EXEC SQL OPEN C11318 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 DISPLAY "FETCH C11318 INTO :int1;" EXEC SQL FETCH C11318 INTO :int1 END-EXEC 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 *Cursor left open. EXEC SQL DELETE FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL INSERT INTO CONCATBUF VALUES ( 'SELECT EXTRACT (HOUR FROM AVG (DWAIT))' || ' FROM MERCH, TURNAROUND WHERE' || ' MERCH.ITEMKEY = TURNAROUND.ITEMKEY OR' || ' TURNAROUND.ITEMKEY NOT IN' || ' (SELECT ITEMKEY FROM MERCH)' ) END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "PREPARE S11319 FROM :longst;" EXEC SQL PREPARE S11319 FROM :longst END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C11319 CURSOR FOR S11319;" EXEC SQL DECLARE C11319 CURSOR FOR S11319 END-EXEC DISPLAY " " DISPLAY "OPEN C11319;" EXEC SQL OPEN C11319 END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "SQLCODE should be >= 0; its value is ", SQL-COD if (SQLCODE < 0) then MOVE 0 TO flag END-IF DISPLAY "SQLSTATE can be 00000 or 01003; its value is ", SQLSTATE PERFORM NOSUBCLASS THRU EXIT-NOSUBCLASS if (NORMSQ NOT = "00000" AND SQLSTATE NOT = "01003") then MOVE 0 TO flag END-IF COMPUTE int1 = -1 DISPLAY "FETCH C11319 INTO :int1;" EXEC SQL FETCH C11319 INTO :int1 END-EXEC MOVE SQLCODE TO SQL-COD *DML083 set the precedent for requiring 01003 on the fetches. *One might argue that it ought to be returned just on the *open and never again. DML083 is under dispute, so status *codes are checked loosely here. DISPLAY "SQLCODE should be >= 0; its value is ", SQL-COD if (SQLCODE < 0) then MOVE 0 TO flag END-IF DISPLAY "SQLSTATE can be 00000 or 01003; its value is ", SQLSTATE PERFORM NOSUBCLASS THRU EXIT-NOSUBCLASS if (NORMSQ NOT = "00000" AND SQLSTATE NOT = "01003") then MOVE 0 TO flag END-IF DISPLAY "int1 should be 0; its value is ", int1 if (int1 NOT = 0) then MOVE 0 TO flag END-IF EXEC SQL DELETE FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL INSERT INTO CONCATBUF VALUES ( 'SELECT COUNT(*)' || ' FROM INVENTORY WHERE MWAIT IS NULL' || ' AND DWAIT IS NULL' ) END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "PREPARE S1131A FROM :longst;" EXEC SQL PREPARE S1131A FROM :longst END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C1131A CURSOR FOR S1131A;" EXEC SQL DECLARE C1131A CURSOR FOR S1131A END-EXEC DISPLAY " " DISPLAY "OPEN C1131A;" EXEC SQL OPEN C1131A END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 DISPLAY "FETCH C1131A INTO :int1;" EXEC SQL FETCH C1131A INTO :int1 END-EXEC 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 "COMMIT WORK;" EXEC SQL COMMIT WORK END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "dstmt=""DROP TABLE MERCH CASCADE""" MOVE "DROP TABLE MERCH CASCADE " TO dstmt DISPLAY "EXECUTE IMMEDIATE :dstmt;" EXEC SQL EXECUTE IMMEDIATE :dstmt 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 "dstmt=""DROP TABLE TURNAROUND CASCADE""" MOVE "DROP TABLE TURNAROUND CASCADE " TO dstmt DISPLAY "EXECUTE IMMEDIATE :dstmt;" EXEC SQL EXECUTE IMMEDIATE :dstmt 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('0622','pass','PCO') END-EXEC MOVE SQLCODE TO SQL-COD else DISPLAY " dml113.pco *** fail *** " EXEC SQL INSERT INTO HU.TESTREPORT VALUES('0622','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 TEST0622 ******************** ******************** BEGIN TEST0624 ******************* MOVE 1 TO flag DISPLAY " TEST0624 " DISPLAY "OUTER JOINs with NULLs and empty tables (dynamic)" DISPLAY "References:" DISPLAY " FIPS PUB 127-2 14.1 Transitional SQL features - " 1,4" DISPLAY " - - - - - - - - - - - - - - - - - - -" COMPUTE SQLCODE = -1 MOVE "xxxxx" TO SQLSTATE DISPLAY "dstmt=""CREATE TABLE JNULL1 (C1 INT, C2 INT)""" MOVE "CREATE TABLE JNULL1 (C1 INT, C2 INT) " TO dstmt DISPLAY "EXECUTE IMMEDIATE :dstmt;" EXEC SQL EXECUTE IMMEDIATE :dstmt 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 "dstmt=""CREATE TABLE JNULL2 (D1 INT, D2 INT)""" MOVE "CREATE TABLE JNULL2 (D1 INT, D2 INT) " TO dstmt DISPLAY "EXECUTE IMMEDIATE :dstmt;" EXEC SQL EXECUTE IMMEDIATE :dstmt 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 " " EXEC SQL DELETE FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL INSERT INTO CONCATBUF VALUES ( 'CREATE VIEW JNULL3 AS' || ' SELECT C1, D1, D2 FROM JNULL1 LEFT OUTER JOIN JNULL2' || ' ON C2 = D2' ) END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "COMMIT WORK;" EXEC SQL COMMIT WORK END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "EXECUTE IMMEDIATE :longst;" EXEC SQL EXECUTE IMMEDIATE :longst 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 " " EXEC SQL DELETE FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL INSERT INTO CONCATBUF VALUES ( 'CREATE VIEW JNULL4 AS' || ' SELECT D1, D2 AS C2 FROM JNULL2' ) END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "COMMIT WORK;" EXEC SQL COMMIT WORK END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "EXECUTE IMMEDIATE :longst;" EXEC SQL EXECUTE IMMEDIATE :longst 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 " " EXEC SQL DELETE FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL INSERT INTO CONCATBUF VALUES ( 'CREATE VIEW JNULL5 AS' || ' SELECT C1, D1, C2 FROM JNULL1 RIGHT OUTER JOIN JNULL4' || ' USING (C2)' ) END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "COMMIT WORK;" EXEC SQL COMMIT WORK END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "EXECUTE IMMEDIATE :longst;" EXEC SQL EXECUTE IMMEDIATE :longst 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 " " EXEC SQL DELETE FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL INSERT INTO CONCATBUF VALUES ( 'CREATE VIEW JNULL6 AS' || ' SELECT * FROM JNULL1 LEFT OUTER JOIN JNULL4' || ' USING (C2)' ) END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "COMMIT WORK;" EXEC SQL COMMIT WORK END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "EXECUTE IMMEDIATE :longst;" EXEC SQL EXECUTE IMMEDIATE :longst 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 "dstmt=""INSERT INTO JNULL1 VALUES (NULL, NULL)""" MOVE "INSERT INTO JNULL1 VALUES (NULL, NULL) " TO dstmt DISPLAY "EXECUTE IMMEDIATE :dstmt;" EXEC SQL EXECUTE IMMEDIATE :dstmt END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "dstmt=""INSERT INTO JNULL1 VALUES (1, NULL)""" MOVE "INSERT INTO JNULL1 VALUES (1, NULL) " TO dstmt DISPLAY "EXECUTE IMMEDIATE :dstmt;" EXEC SQL EXECUTE IMMEDIATE :dstmt END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "dstmt=""INSERT INTO JNULL1 VALUES (NULL, 1)""" MOVE "INSERT INTO JNULL1 VALUES (NULL, 1) " TO dstmt DISPLAY "EXECUTE IMMEDIATE :dstmt;" EXEC SQL EXECUTE IMMEDIATE :dstmt END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "dstmt=""INSERT INTO JNULL1 VALUES (1, 1)""" MOVE "INSERT INTO JNULL1 VALUES (1, 1) " TO dstmt DISPLAY "EXECUTE IMMEDIATE :dstmt;" EXEC SQL EXECUTE IMMEDIATE :dstmt END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "dstmt=""INSERT INTO JNULL1 VALUES (2, 2)""" MOVE "INSERT INTO JNULL1 VALUES (2, 2) " TO dstmt DISPLAY "EXECUTE IMMEDIATE :dstmt;" EXEC SQL EXECUTE IMMEDIATE :dstmt END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "dstmt=""SELECT COUNT(*) FROM JNULL3""" MOVE "SELECT COUNT(*) FROM JNULL3 - " " TO dstmt DISPLAY "PREPARE S11321 FROM :dstmt;" EXEC SQL PREPARE S11321 FROM :dstmt END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C11321 CURSOR FOR S11321;" EXEC SQL DECLARE C11321 CURSOR FOR S11321 END-EXEC DISPLAY " " DISPLAY "OPEN C11321;" EXEC SQL OPEN C11321 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 DISPLAY "FETCH C11321 INTO :int1;" EXEC SQL FETCH C11321 INTO :int1 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "int1 should be 5; its value is ", int1 if (int1 NOT = 5) then MOVE 0 TO flag END-IF DISPLAY "CLOSE C11321;" EXEC SQL CLOSE C11321 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " EXEC SQL DELETE FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL INSERT INTO CONCATBUF VALUES ( 'SELECT COUNT(*) FROM JNULL3' || ' WHERE D2 IS NOT NULL OR D1 IS NOT NULL' ) END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "PREPARE S11322 FROM :longst;" EXEC SQL PREPARE S11322 FROM :longst END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C11322 CURSOR FOR S11322;" EXEC SQL DECLARE C11322 CURSOR FOR S11322 END-EXEC DISPLAY " " DISPLAY "OPEN C11322;" EXEC SQL OPEN C11322 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 DISPLAY "FETCH C11322 INTO :int1;" EXEC SQL FETCH C11322 INTO :int1 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "int1 should be 0; its value is ", int1 if (int1 NOT = 0) then MOVE 0 TO flag END-IF DISPLAY "CLOSE C11322;" EXEC SQL CLOSE C11322 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " EXEC SQL DELETE FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL INSERT INTO CONCATBUF VALUES ( 'SELECT COUNT(*) FROM' || ' JNULL5' ) END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "PREPARE S11323 FROM :longst;" EXEC SQL PREPARE S11323 FROM :longst END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C11323 CURSOR FOR S11323;" EXEC SQL DECLARE C11323 CURSOR FOR S11323 END-EXEC DISPLAY " " DISPLAY "OPEN C11323;" EXEC SQL OPEN C11323 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 DISPLAY "FETCH C11323 INTO :int1;" EXEC SQL FETCH C11323 INTO :int1 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "int1 should be 0; its value is ", int1 if (int1 NOT = 0) then MOVE 0 TO flag END-IF DISPLAY "CLOSE C11323;" EXEC SQL CLOSE C11323 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " EXEC SQL DELETE FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL INSERT INTO CONCATBUF VALUES ( 'SELECT COUNT(*) FROM' || ' JNULL6' || ' WHERE C2 IS NOT NULL' ) END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "PREPARE S11324 FROM :longst;" EXEC SQL PREPARE S11324 FROM :longst END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C11324 CURSOR FOR S11324;" EXEC SQL DECLARE C11324 CURSOR FOR S11324 END-EXEC DISPLAY " " DISPLAY "OPEN C11324;" EXEC SQL OPEN C11324 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 DISPLAY "FETCH C11324 INTO :int1;" EXEC SQL FETCH C11324 INTO :int1 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "int1 should be 3; its value is ", int1 if (int1 NOT = 3) then MOVE 0 TO flag END-IF DISPLAY "CLOSE C11324;" EXEC SQL CLOSE C11324 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " *Nonempty table DISPLAY "dstmt=""INSERT INTO JNULL2 SELECT * FROM JNULL1""" MOVE "INSERT INTO JNULL2 SELECT * FROM JNULL1 - " " TO dstmt DISPLAY "EXECUTE IMMEDIATE :dstmt;" EXEC SQL EXECUTE IMMEDIATE :dstmt END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "dstmt=""UPDATE JNULL2 SET D2 = 1 WHERE D2 = 2""" MOVE "UPDATE JNULL2 SET D2 = 1 WHERE D2 = 2 " TO dstmt DISPLAY "EXECUTE IMMEDIATE :dstmt;" EXEC SQL EXECUTE IMMEDIATE :dstmt END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "OPEN C11321;" EXEC SQL OPEN C11321 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 DISPLAY "FETCH C11321 INTO :int1;" EXEC SQL FETCH C11321 INTO :int1 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "int1 should be 9; its value is ", int1 if (int1 NOT = 9) then MOVE 0 TO flag END-IF DISPLAY "CLOSE C11321;" EXEC SQL CLOSE C11321 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "dstmt=""SELECT COUNT(*) FROM JNULL3 WHERE C1 IS - " NULL""" MOVE "SELECT COUNT(*) FROM JNULL3 WHERE C1 IS - " NULL " TO dstmt DISPLAY "PREPARE S11325 FROM :dstmt;" EXEC SQL PREPARE S11325 FROM :dstmt END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C11325 CURSOR FOR S11325;" EXEC SQL DECLARE C11325 CURSOR FOR S11325 END-EXEC DISPLAY " " DISPLAY "OPEN C11325;" EXEC SQL OPEN C11325 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 DISPLAY "FETCH C11325 INTO :int1;" EXEC SQL FETCH C11325 INTO :int1 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "int1 should be 4; its value is ", int1 if (int1 NOT = 4) then MOVE 0 TO flag END-IF DISPLAY "CLOSE C11325;" EXEC SQL CLOSE C11325 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "dstmt=""SELECT COUNT(*) FROM JNULL3 WHERE D1 IS - " NULL""" MOVE "SELECT COUNT(*) FROM JNULL3 WHERE D1 IS - " NULL " TO dstmt DISPLAY "PREPARE S11326 FROM :dstmt;" EXEC SQL PREPARE S11326 FROM :dstmt END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C11326 CURSOR FOR S11326;" EXEC SQL DECLARE C11326 CURSOR FOR S11326 END-EXEC DISPLAY " " DISPLAY "OPEN C11326;" EXEC SQL OPEN C11326 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 DISPLAY "FETCH C11326 INTO :int1;" EXEC SQL FETCH C11326 INTO :int1 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "int1 should be 5; its value is ", int1 if (int1 NOT = 5) then MOVE 0 TO flag END-IF DISPLAY "CLOSE C11326;" EXEC SQL CLOSE C11326 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "dstmt=""SELECT COUNT(*) FROM JNULL3 WHERE D2 IS - " NULL""" MOVE "SELECT COUNT(*) FROM JNULL3 WHERE D2 IS - " NULL " TO dstmt DISPLAY "PREPARE S11327 FROM :dstmt;" EXEC SQL PREPARE S11327 FROM :dstmt END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C11327 CURSOR FOR S11327;" EXEC SQL DECLARE C11327 CURSOR FOR S11327 END-EXEC DISPLAY " " DISPLAY "OPEN C11327;" EXEC SQL OPEN C11327 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 DISPLAY "FETCH C11327 INTO :int1;" EXEC SQL FETCH C11327 INTO :int1 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "int1 should be 3; its value is ", int1 if (int1 NOT = 3) then MOVE 0 TO flag END-IF DISPLAY "CLOSE C11327;" EXEC SQL CLOSE C11327 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "dstmt=""SELECT AVG(D1) * 10 FROM JNULL3""" MOVE "SELECT AVG(D1) * 10 FROM JNULL3 - " " TO dstmt DISPLAY "PREPARE S11328 FROM :dstmt;" EXEC SQL PREPARE S11328 FROM :dstmt END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C11328 CURSOR FOR S11328;" EXEC SQL DECLARE C11328 CURSOR FOR S11328 END-EXEC DISPLAY " " DISPLAY "OPEN C11328;" EXEC SQL OPEN C11328 END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "SQLCODE should be >= 0; its value is ", SQL-COD if (SQLCODE < 0) then MOVE 0 TO flag END-IF DISPLAY "SQLSTATE can be 00000 or 01003; its value is ", SQLSTATE PERFORM NOSUBCLASS THRU EXIT-NOSUBCLASS if (NORMSQ NOT = "00000" AND SQLSTATE NOT = "01003") then MOVE 0 TO flag END-IF DISPLAY " " COMPUTE int1 = -1 DISPLAY "FETCH C11328 INTO :int1;" EXEC SQL FETCH C11328 INTO :int1 END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "SQLCODE should be >= 0; its value is ", SQL-COD if (SQLCODE < 0) then MOVE 0 TO flag END-IF DISPLAY "SQLSTATE can be 00000 or 01003; its value is ", SQLSTATE PERFORM NOSUBCLASS THRU EXIT-NOSUBCLASS if (NORMSQ NOT = "00000" AND SQLSTATE NOT = "01003") then MOVE 0 TO flag END-IF DISPLAY "int1 should be 15; its value is ", int1 if (int1 NOT = 15) then MOVE 0 TO flag END-IF DISPLAY "CLOSE C11328;" EXEC SQL CLOSE C11328 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " EXEC SQL DELETE FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL INSERT INTO CONCATBUF VALUES ( 'SELECT COUNT(*)' || ' FROM JNULL6' || ' WHERE C2 = 1' ) END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "PREPARE S11329 FROM :longst;" EXEC SQL PREPARE S11329 FROM :longst END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C11329 CURSOR FOR S11329;" EXEC SQL DECLARE C11329 CURSOR FOR S11329 END-EXEC DISPLAY " " DISPLAY "OPEN C11329;" EXEC SQL OPEN C11329 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 DISPLAY "FETCH C11329 INTO :int1;" EXEC SQL FETCH C11329 INTO :int1 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "int1 should be 6; its value is ", int1 if (int1 NOT = 6) then MOVE 0 TO flag END-IF DISPLAY "CLOSE C11329;" EXEC SQL CLOSE C11329 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " EXEC SQL DELETE FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL INSERT INTO CONCATBUF VALUES ( 'SELECT COUNT(*)' || ' FROM JNULL6' || ' WHERE C2 IS NULL' ) END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "PREPARE S1132A FROM :longst;" EXEC SQL PREPARE S1132A FROM :longst END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C1132A CURSOR FOR S1132A;" EXEC SQL DECLARE C1132A CURSOR FOR S1132A END-EXEC DISPLAY " " DISPLAY "OPEN C1132A;" EXEC SQL OPEN C1132A END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 DISPLAY "FETCH C1132A INTO :int1;" EXEC SQL FETCH C1132A INTO :int1 END-EXEC 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 "CLOSE C1132A;" EXEC SQL CLOSE C1132A END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " EXEC SQL DELETE FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL INSERT INTO CONCATBUF VALUES ( 'SELECT COUNT(*)' || ' FROM JNULL6' || ' WHERE C2 = C1 AND' || ' D1 IS NULL' ) END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "PREPARE S1132B FROM :longst;" EXEC SQL PREPARE S1132B FROM :longst END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C1132B CURSOR FOR S1132B;" EXEC SQL DECLARE C1132B CURSOR FOR S1132B END-EXEC DISPLAY " " DISPLAY "OPEN C1132B;" EXEC SQL OPEN C1132B END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 DISPLAY "FETCH C1132B INTO :int1;" EXEC SQL FETCH C1132B INTO :int1 END-EXEC 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 "CLOSE C1132B;" EXEC SQL CLOSE C1132B 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 "dstmt=""DROP TABLE JNULL1 CASCADE""" MOVE "DROP TABLE JNULL1 CASCADE " TO dstmt DISPLAY "EXECUTE IMMEDIATE :dstmt;" EXEC SQL EXECUTE IMMEDIATE :dstmt 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 "dstmt=""DROP TABLE JNULL2 CASCADE""" MOVE "DROP TABLE JNULL2 CASCADE " TO dstmt DISPLAY "EXECUTE IMMEDIATE :dstmt;" EXEC SQL EXECUTE IMMEDIATE :dstmt 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('0624','pass','PCO') END-EXEC MOVE SQLCODE TO SQL-COD else DISPLAY " dml113.pco *** fail *** " EXEC SQL INSERT INTO HU.TESTREPORT VALUES('0624','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 TEST0624 ******************** ******************** BEGIN TEST0626 ******************* MOVE 1 TO flag DISPLAY " TEST0626 " DISPLAY " ADD COLUMN and DROP COLUMN (dynamic)" DISPLAY "References:" DISPLAY " FIPS PUB 127-2 14.1 Transitional SQL feature - " 3" DISPLAY " 11.11 <add column definition>" DISPLAY " 11.15 <drop column definition>" DISPLAY " - - - - - - - - - - - - - - - - - - -" COMPUTE SQLCODE = -1 MOVE "xxxxx" TO SQLSTATE EXEC SQL DELETE FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL INSERT INTO CONCATBUF VALUES ( 'CREATE TABLE CHANGG' || ' (NAAM CHAR (14) NOT NULL PRIMARY KEY, AGE INT)' ) END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "COMMIT WORK;" EXEC SQL COMMIT WORK END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "EXECUTE IMMEDIATE :longst;" EXEC SQL EXECUTE IMMEDIATE :longst 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 "dstmt=""INSERT INTO CHANGG VALUES ('RALPH', 22)""" MOVE "INSERT INTO CHANGG VALUES ('RALPH', 22) " TO dstmt DISPLAY "EXECUTE IMMEDIATE :dstmt;" EXEC SQL EXECUTE IMMEDIATE :dstmt END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "dstmt=""INSERT INTO CHANGG VALUES ('RUDOLPH', - " 54)""" MOVE "INSERT INTO CHANGG VALUES ('RUDOLPH', 54) " TO dstmt DISPLAY "EXECUTE IMMEDIATE :dstmt;" EXEC SQL EXECUTE IMMEDIATE :dstmt END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "dstmt=""INSERT INTO CHANGG VALUES ('QUEEG', 33)""" MOVE "INSERT INTO CHANGG VALUES ('QUEEG', 33) " TO dstmt DISPLAY "EXECUTE IMMEDIATE :dstmt;" EXEC SQL EXECUTE IMMEDIATE :dstmt END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "dstmt=""INSERT INTO CHANGG VALUES ('BESSIE', - " 106)""" MOVE "INSERT INTO CHANGG VALUES ('BESSIE', 106) " TO dstmt DISPLAY "EXECUTE IMMEDIATE :dstmt;" EXEC SQL EXECUTE IMMEDIATE :dstmt END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " *This select should fail DISPLAY "dstmt=""SELECT COUNT(*) FROM CHANGG WHERE DIVORCES - " IS NULL""" MOVE "SELECT COUNT(*) FROM CHANGG WHERE DIVORCES - " IS NULL" TO dstmt DISPLAY "PREPARE S11331 FROM :dstmt;" EXEC SQL PREPARE S11331 FROM :dstmt END-EXEC MOVE SQLCODE TO SQL-COD if (SQLCODE = 0) then DISPLAY "SQLCODE is 0" GO TO P101 END-IF DISPLAY "SQLCODE is ", SQL-COD 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 GO TO P100 . P101. DISPLAY "DECLARE C11331 CURSOR FOR S11331;" EXEC SQL DECLARE C11331 CURSOR FOR S11331 END-EXEC DISPLAY " " DISPLAY "OPEN C11331;" EXEC SQL OPEN C11331 END-EXEC MOVE SQLCODE TO SQL-COD if (SQLCODE = 0) then DISPLAY "SQLCODE is 0" GO TO P102 END-IF DISPLAY "SQLCODE is ", SQL-COD 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 GO TO P100 . P102. DISPLAY "FETCH C11331 INTO :int1;" EXEC SQL FETCH C11331 INTO :int1 END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "SQLCODE should be < 0; its value is ", SQL-COD DISPLAY "SQLSTATE should be '42000'; its value is '", SQLSTATE "'" if (SQLCODE NOT < 0) then MOVE 0 TO flag END-IF 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 "CLOSE C11331;" EXEC SQL CLOSE C11331 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " . P100. DISPLAY "COMMIT WORK;" EXEC SQL COMMIT WORK END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "dstmt=""ALTER TABLE CHANGG ADD NUMBRR CHAR(11)""" MOVE "ALTER TABLE CHANGG ADD NUMBRR CHAR(11) " TO dstmt DISPLAY "EXECUTE IMMEDIATE :dstmt;" EXEC SQL EXECUTE IMMEDIATE :dstmt 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 " " *Optional keyword! EXEC SQL DELETE FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL INSERT INTO CONCATBUF VALUES ( 'ALTER TABLE CHANGG' || ' ADD COLUMN DIVORCES INT DEFAULT 0' ) END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "COMMIT WORK;" EXEC SQL COMMIT WORK END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "EXECUTE IMMEDIATE :longst;" EXEC SQL EXECUTE IMMEDIATE :longst 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 " " *Check that defaults are correct EXEC SQL DELETE FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL INSERT INTO CONCATBUF VALUES ( 'SELECT COUNT(*)' || ' FROM CHANGG WHERE NUMBRR IS NOT NULL' || ' OR DIVORCES <> 0' ) END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "PREPARE S11332 FROM :longst;" EXEC SQL PREPARE S11332 FROM :longst END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C11332 CURSOR FOR S11332;" EXEC SQL DECLARE C11332 CURSOR FOR S11332 END-EXEC DISPLAY " " DISPLAY "OPEN C11332;" EXEC SQL OPEN C11332 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 DISPLAY "FETCH C11332 INTO :int1;" EXEC SQL FETCH C11332 INTO :int1 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "int1 should be 0; its value is ", int1 if (int1 NOT = 0) then MOVE 0 TO flag END-IF DISPLAY "CLOSE C11332;" EXEC SQL CLOSE C11332 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " *Do some uncomplicated things EXEC SQL DELETE FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL INSERT INTO CONCATBUF VALUES ( 'UPDATE CHANGG' || ' SET NUMBRR = ''837-47-1847'', DIVORCES = 3' || ' WHERE NAAM = ''RUDOLPH''' ) END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "EXECUTE IMMEDIATE :longst;" EXEC SQL EXECUTE IMMEDIATE :longst END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " EXEC SQL DELETE FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL INSERT INTO CONCATBUF VALUES ( 'UPDATE CHANGG' || ' SET NUMBRR = ''738-47-1847'', DIVORCES = NULL' || ' WHERE NAAM = ''QUEEG''' ) END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "EXECUTE IMMEDIATE :longst;" EXEC SQL EXECUTE IMMEDIATE :longst END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "dstmt=""DELETE FROM CHANGG WHERE NUMBRR IS NULL""" MOVE "DELETE FROM CHANGG WHERE NUMBRR IS NULL " TO dstmt DISPLAY "EXECUTE IMMEDIATE :dstmt;" EXEC SQL EXECUTE IMMEDIATE :dstmt END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " EXEC SQL DELETE FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL INSERT INTO CONCATBUF VALUES ( 'INSERT INTO CHANGG (NAAM, AGE, NUMBRR)' || ' VALUES (''GOOBER'', 16, ''000-10-0001'')' ) END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "EXECUTE IMMEDIATE :longst;" EXEC SQL EXECUTE IMMEDIATE :longst END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " EXEC SQL DELETE FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL INSERT INTO CONCATBUF VALUES ( 'INSERT INTO CHANGG' || ' VALUES (''OLIVIA'', 20, ''111-11-1111'', 0)' ) END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "EXECUTE IMMEDIATE :longst;" EXEC SQL EXECUTE IMMEDIATE :longst END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " EXEC SQL DELETE FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL INSERT INTO CONCATBUF VALUES ( 'SELECT AGE, NUMBRR, DIVORCES FROM CHANGG' || ' WHERE NAAM = ?' ) END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "PREPARE S11333 FROM :longst;" EXEC SQL PREPARE S11333 FROM :longst END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C11333 CURSOR FOR S11333;" EXEC SQL DECLARE C11333 CURSOR FOR S11333 END-EXEC DISPLAY " " MOVE "RUDOLPH " TO ch3 DISPLAY "ch3 is '", CH3 "'" DISPLAY "OPEN C11333 USING :ch3;" EXEC SQL OPEN C11333 USING :ch3 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 COMPUTE int2 = -1 MOVE "xxxxxxxxxxx" TO ch1 DISPLAY "FETCH C11333 INTO :int1, :ch1, :int2;" EXEC SQL FETCH C11333 INTO :int1, :ch1, :int2 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "int1 should be 54; its value is ", int1 DISPLAY "int2 should be 3; its value is ", int2 DISPLAY "ch1 should be '837-47-1847'; its value is '", ch1 "'" if (int1 NOT = 54 OR int2 NOT = 3) then MOVE 0 TO flag END-IF if (ch1 NOT = "837-47-1847") then MOVE 0 TO flag END-IF DISPLAY "CLOSE C11333;" EXEC SQL CLOSE C11333 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " MOVE "QUEEG " TO ch3 DISPLAY "ch3 is '", CH3 "'" DISPLAY "OPEN C11333 USING :ch3;" EXEC SQL OPEN C11333 USING :ch3 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 COMPUTE int2 = -1 MOVE 10 TO ind1 MOVE "xxxxxxxxxxx" TO ch1 DISPLAY "FETCH C11333 INTO :int1, :ch1, :int2:ind1;" EXEC SQL FETCH C11333 INTO :int1, :ch1, :int2:ind1 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "int1 should be 33; its value is ", int1 DISPLAY "ind1 should be -1; its value is ", ind1 DISPLAY "ch1 should be '738-47-1847'; its value is '", ch1 "'" if (int1 NOT = 33 OR ind1 NOT = -1) then MOVE 0 TO flag END-IF if (ch1 NOT = "738-47-1847") then MOVE 0 TO flag END-IF DISPLAY "CLOSE C11333;" EXEC SQL CLOSE C11333 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " MOVE "GOOBER " TO ch3 DISPLAY "ch3 is '", CH3 "'" DISPLAY "OPEN C11333 USING :ch3;" EXEC SQL OPEN C11333 USING :ch3 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 COMPUTE int2 = -1 MOVE "xxxxxxxxxxx" TO ch1 DISPLAY "FETCH C11333 INTO :int1, :ch1, :int2;" EXEC SQL FETCH C11333 INTO :int1, :ch1, :int2 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "int1 should be 16; its value is ", int1 DISPLAY "int2 should be 0; its value is ", int2 DISPLAY "ch1 should be '000-10-0001'; its value is '", ch1 "'" if (int1 NOT = 16 OR int2 NOT = 0) then MOVE 0 TO flag END-IF if (ch1 NOT = "000-10-0001") then MOVE 0 TO flag END-IF DISPLAY "CLOSE C11333;" EXEC SQL CLOSE C11333 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " MOVE "OLIVIA " TO ch3 DISPLAY "ch3 is '", CH3 "'" DISPLAY "OPEN C11333 USING :ch3;" EXEC SQL OPEN C11333 USING :ch3 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 COMPUTE int2 = -1 MOVE "xxxxxxxxxxx" TO ch1 DISPLAY "FETCH C11333 INTO :int1, :ch1, :int2;" EXEC SQL FETCH C11333 INTO :int1, :ch1, :int2 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "int1 should be 20; its value is ", int1 DISPLAY "int2 should be 0; its value is ", int2 DISPLAY "ch1 should be '111-11-1111'; its value is '", ch1 "'" if (int1 NOT = 20 OR int2 NOT = 0) then MOVE 0 TO flag END-IF if (ch1 NOT = "111-11-1111") then MOVE 0 TO flag END-IF DISPLAY "CLOSE C11333;" EXEC SQL CLOSE C11333 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "dstmt=""SELECT COUNT(*) FROM CHANGG""" MOVE "SELECT COUNT(*) FROM CHANGG - " " TO dstmt DISPLAY "PREPARE S11334 FROM :dstmt;" EXEC SQL PREPARE S11334 FROM :dstmt END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C11334 CURSOR FOR S11334;" EXEC SQL DECLARE C11334 CURSOR FOR S11334 END-EXEC DISPLAY " " DISPLAY "OPEN C11334;" EXEC SQL OPEN C11334 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 DISPLAY "FETCH C11334 INTO :int1;" EXEC SQL FETCH C11334 INTO :int1 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "int1 should be 4; its value is ", int1 if (int1 NOT = 4) then MOVE 0 TO flag END-IF DISPLAY "CLOSE C11334;" EXEC SQL CLOSE C11334 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 " " *Age is nobody's business! DISPLAY "dstmt=""ALTER TABLE CHANGG DROP AGE CASCADE""" MOVE "ALTER TABLE CHANGG DROP AGE CASCADE " TO dstmt DISPLAY "EXECUTE IMMEDIATE :dstmt;" EXEC SQL EXECUTE IMMEDIATE :dstmt 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 " " *Optional keyword! DISPLAY "dstmt=""ALTER TABLE CHANGG DROP COLUMN DIVORCES - " RESTRICT""" MOVE "ALTER TABLE CHANGG DROP COLUMN DIVORCES RESTRICT " TO dstmt DISPLAY "EXECUTE IMMEDIATE :dstmt;" EXEC SQL EXECUTE IMMEDIATE :dstmt 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 " " *This select should fail DISPLAY "dstmt=""SELECT COUNT(*) FROM CHANGG WHERE AGE > - " 30""" MOVE "SELECT COUNT(*) FROM CHANGG WHERE AGE > 30 - " " TO dstmt DISPLAY "PREPARE S11335 FROM :dstmt;" EXEC SQL PREPARE S11335 FROM :dstmt END-EXEC MOVE SQLCODE TO SQL-COD if (SQLCODE = 0) then DISPLAY "SQLCODE is 0" GO TO P104 END-IF DISPLAY "SQLCODE is ", SQL-COD 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 GO TO P103 . P104. DISPLAY "DECLARE C11335 CURSOR FOR S11335;" EXEC SQL DECLARE C11335 CURSOR FOR S11335 END-EXEC DISPLAY " " DISPLAY "OPEN C11335;" EXEC SQL OPEN C11335 END-EXEC MOVE SQLCODE TO SQL-COD if (SQLCODE = 0) then DISPLAY "SQLCODE is 0" GO TO P105 END-IF DISPLAY "SQLCODE is ", SQL-COD 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 GO TO P103 . P105. DISPLAY "FETCH C11335 INTO :int1;" EXEC SQL FETCH C11335 INTO :int1 END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "SQLCODE should be < 0; its value is ", SQL-COD DISPLAY "SQLSTATE should be '42000'; its value is '", SQLSTATE "'" if (SQLCODE NOT < 0) then MOVE 0 TO flag END-IF 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 "CLOSE C11335;" EXEC SQL CLOSE C11335 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " . P103. MOVE "xxxxx" TO SQLSTATE *This select should fail too DISPLAY "dstmt=""SELECT COUNT(*) FROM CHANGG WHERE DIVORCES - " IS NULL""" MOVE "SELECT COUNT(*) FROM CHANGG WHERE DIVORCES - " IS NULL" TO dstmt DISPLAY "PREPARE S11336 FROM :dstmt;" EXEC SQL PREPARE S11336 FROM :dstmt END-EXEC MOVE SQLCODE TO SQL-COD if (SQLCODE = 0) then DISPLAY "SQLCODE is 0" GO TO P107 END-IF DISPLAY "SQLCODE is ", SQL-COD 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 GO TO P106 . P107. DISPLAY "DECLARE C11336 CURSOR FOR S11336;" EXEC SQL DECLARE C11336 CURSOR FOR S11336 END-EXEC DISPLAY " " DISPLAY "OPEN C11336;" EXEC SQL OPEN C11336 END-EXEC MOVE SQLCODE TO SQL-COD if (SQLCODE = 0) then DISPLAY "SQLCODE is 0" GO TO P108 END-IF DISPLAY "SQLCODE is ", SQL-COD 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 GO TO P106 . P108. DISPLAY "FETCH C11336 INTO :int1;" EXEC SQL FETCH C11336 INTO :int1 END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "SQLCODE should be < 0; its value is ", SQL-COD DISPLAY "SQLSTATE should be '42000'; its value is '", SQLSTATE "'" if (SQLCODE NOT < 0) then MOVE 0 TO flag END-IF 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 "CLOSE C11336;" EXEC SQL CLOSE C11336 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " . P106. MOVE "xxxxxxxxxxxxxx" TO ch2 DISPLAY "dstmt=""SELECT NAAM FROM CHANGG WHERE NUMBRR LIKE - " '%000%'""" MOVE "SELECT NAAM FROM CHANGG WHERE NUMBRR LIKE '%000%' " TO dstmt DISPLAY "PREPARE S11337 FROM :dstmt;" EXEC SQL PREPARE S11337 FROM :dstmt END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C11337 CURSOR FOR S11337;" EXEC SQL DECLARE C11337 CURSOR FOR S11337 END-EXEC DISPLAY " " DISPLAY "OPEN C11337;" EXEC SQL OPEN C11337 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 DISPLAY "FETCH C11337 INTO :ch2;" EXEC SQL FETCH C11337 INTO :ch2 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "ch2 should be 'GOOBER '; its value is '", ch2 "'" if (ch2 NOT = "GOOBER ") then MOVE 0 TO flag END-IF DISPLAY "CLOSE C11337;" EXEC SQL CLOSE C11337 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 "dstmt=""ALTER TABLE CHANGG DROP NAAM RESTRICT""" MOVE "ALTER TABLE CHANGG DROP NAAM RESTRICT " TO dstmt DISPLAY "EXECUTE IMMEDIATE :dstmt;" EXEC SQL EXECUTE IMMEDIATE :dstmt 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 " " *TEd Hook #1 Check 11.15 SR.3 (can't drop all the columns) DISPLAY "dstmt=""ALTER TABLE CHANGG DROP NUMBRR RESTRICT""" MOVE "ALTER TABLE CHANGG DROP NUMBRR RESTRICT " TO dstmt DISPLAY "EXECUTE IMMEDIATE :dstmt;" EXEC SQL EXECUTE IMMEDIATE :dstmt 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 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 "COMMIT WORK;" EXEC SQL COMMIT WORK END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY " " *TEd Hook #2 DISPLAY "dstmt=""DROP TABLE CHANGG CASCADE""" MOVE "DROP TABLE CHANGG CASCADE " TO dstmt DISPLAY "EXECUTE IMMEDIATE :dstmt;" EXEC SQL EXECUTE IMMEDIATE :dstmt 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('0626','pass','PCO') END-EXEC MOVE SQLCODE TO SQL-COD else DISPLAY " dml113.pco *** fail *** " EXEC SQL INSERT INTO HU.TESTREPORT VALUES('0626','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 TEST0626 ******************** ******************** BEGIN TEST0632 ******************* MOVE 1 TO flag DISPLAY " TEST0632 " DISPLAY " Datetimes in a <default clause> (dynamic)" DISPLAY "References:" DISPLAY " FIPS PUB 127-2 14.1 Transitional SQL features - " 1, 5" DISPLAY " 11.5 <default clause>" DISPLAY " 6.8 <datetime value function>" DISPLAY " 5.3 <literal>" DISPLAY " - - - - - - - - - - - - - - - - - - -" COMPUTE SQLCODE = -1 MOVE "xxxxx" TO SQLSTATE EXEC SQL DELETE FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL INSERT INTO CONCATBUF VALUES ( 'CREATE TABLE OBITUARIES (' || ' NAAM CHAR (14) NOT NULL PRIMARY KEY,' || ' BORN DATE DEFAULT DATE ''1880-01-01'',' || ' DIED DATE DEFAULT CURRENT_DATE,' || ' ENTERED TIMESTAMP DEFAULT CURRENT_TIMESTAMP,' || ' TESTING1 DATE, TESTING2 TIMESTAMP)' ) END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "COMMIT WORK;" EXEC SQL COMMIT WORK END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "EXECUTE IMMEDIATE :longst;" EXEC SQL EXECUTE IMMEDIATE :longst 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 " " EXEC SQL DELETE FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL INSERT INTO CONCATBUF VALUES ( 'CREATE TABLE BIRTHS (' || ' NAAM CHAR (14) NOT NULL PRIMARY KEY,' || ' CHECKIN TIME (0)' || ' DEFAULT TIME ''00:00:00'',' || ' LABOR INTERVAL HOUR' || ' DEFAULT INTERVAL ''4'' HOUR,' || ' CHECKOUT TIME' || ' DEFAULT CURRENT_TIME,' || ' TESTING TIME)' ) END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "COMMIT WORK;" EXEC SQL COMMIT WORK END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "EXECUTE IMMEDIATE :longst;" EXEC SQL EXECUTE IMMEDIATE :longst END-EXEC MOVE SQLCODE TO SQL-COD *The <default option> must be a <literal> or a <datetime value *function> so I can't use a CAST to change CURRENT_TIME to have *no fractional seconds. PERFORM CHCKOK DISPLAY " " DISPLAY "COMMIT WORK;" EXEC SQL COMMIT WORK END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " EXEC SQL DELETE FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL INSERT INTO CONCATBUF VALUES ( 'INSERT INTO OBITUARIES (NAAM, TESTING1, TESTING2)' || ' VALUES (?, CURRENT_DATE, CURRENT_TIMESTAMP)' ) END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "PREPARE S11341 FROM :longst;" EXEC SQL PREPARE S11341 FROM :longst END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " MOVE "KEITH " TO ch2 DISPLAY "ch2 is '", ch2 "'" DISPLAY "EXECUTE S11341 USING :ch2;" EXEC SQL EXECUTE S11341 USING :ch2 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " EXEC SQL DELETE FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL INSERT INTO CONCATBUF VALUES ( 'INSERT INTO BIRTHS (NAAM, TESTING)' || ' VALUES (''BJORN'', CURRENT_TIME)' ) END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "EXECUTE IMMEDIATE :longst;" EXEC SQL EXECUTE IMMEDIATE :longst END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " EXEC SQL DELETE FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL INSERT INTO CONCATBUF VALUES ( 'SELECT EXTRACT (HOUR FROM CHECKIN) +' || ' EXTRACT (MINUTE FROM CHECKIN) +' || ' EXTRACT (SECOND FROM CHECKIN)' || ' FROM BIRTHS' ) END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "PREPARE S11342 FROM :longst;" EXEC SQL PREPARE S11342 FROM :longst END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C11342 CURSOR FOR S11342;" EXEC SQL DECLARE C11342 CURSOR FOR S11342 END-EXEC DISPLAY " " DISPLAY "OPEN C11342;" EXEC SQL OPEN C11342 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 DISPLAY "FETCH C11342 INTO :int1;" EXEC SQL FETCH C11342 INTO :int1 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "int1 should be 0; its value is ", int1 if (int1 NOT = 0) then MOVE 0 TO flag END-IF DISPLAY "CLOSE C11342;" EXEC SQL CLOSE C11342 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "dstmt=""SELECT EXTRACT (HOUR FROM LABOR) FROM - " BIRTHS""" MOVE "SELECT EXTRACT (HOUR FROM LABOR) FROM BIRTHS " TO dstmt DISPLAY "PREPARE S11343 FROM :dstmt;" EXEC SQL PREPARE S11343 FROM :dstmt END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C11343 CURSOR FOR S11343;" EXEC SQL DECLARE C11343 CURSOR FOR S11343 END-EXEC DISPLAY " " DISPLAY "OPEN C11343;" EXEC SQL OPEN C11343 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 DISPLAY "FETCH C11343 INTO :int1;" EXEC SQL FETCH C11343 INTO :int1 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "int1 should be 4; its value is ", int1 if (int1 NOT = 4) then MOVE 0 TO flag END-IF DISPLAY "CLOSE C11343;" EXEC SQL CLOSE C11343 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " EXEC SQL DELETE FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL INSERT INTO CONCATBUF VALUES ( 'SELECT COUNT (*) FROM BIRTHS' || ' WHERE TESTING <> CHECKOUT OR CHECKOUT IS NULL' ) END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "PREPARE S11344 FROM :longst;" EXEC SQL PREPARE S11344 FROM :longst END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C11344 CURSOR FOR S11344;" EXEC SQL DECLARE C11344 CURSOR FOR S11344 END-EXEC DISPLAY " " DISPLAY "OPEN C11344;" EXEC SQL OPEN C11344 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 DISPLAY "FETCH C11344 INTO :int1;" EXEC SQL FETCH C11344 INTO :int1 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "int1 should be 0; its value is ", int1 if (int1 NOT = 0) then MOVE 0 TO flag END-IF DISPLAY "CLOSE C11344;" EXEC SQL CLOSE C11344 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " EXEC SQL DELETE FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL INSERT INTO CONCATBUF VALUES ( 'SELECT COUNT (*) FROM OBITUARIES' || ' WHERE BORN <> DATE ''1880-01-01''' || ' OR BORN IS NULL' || ' OR DIED <> TESTING1' || ' OR DIED IS NULL' || ' OR ENTERED <> TESTING2' || ' OR ENTERED IS NULL' ) END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "PREPARE S11345 FROM :longst;" EXEC SQL PREPARE S11345 FROM :longst END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C11345 CURSOR FOR S11345;" EXEC SQL DECLARE C11345 CURSOR FOR S11345 END-EXEC DISPLAY " " DISPLAY "OPEN C11345;" EXEC SQL OPEN C11345 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 DISPLAY "FETCH C11345 INTO :int1;" EXEC SQL FETCH C11345 INTO :int1 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "int1 should be 0; its value is ", int1 if (int1 NOT = 0) then MOVE 0 TO flag END-IF DISPLAY "CLOSE C11345;" EXEC SQL CLOSE C11345 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 "dstmt=""DROP TABLE BIRTHS CASCADE""" MOVE "DROP TABLE BIRTHS CASCADE " TO dstmt DISPLAY "EXECUTE IMMEDIATE :dstmt;" EXEC SQL EXECUTE IMMEDIATE :dstmt 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 "dstmt=""DROP TABLE OBITUARIES CASCADE""" MOVE "DROP TABLE OBITUARIES CASCADE " TO dstmt DISPLAY "EXECUTE IMMEDIATE :dstmt;" EXEC SQL EXECUTE IMMEDIATE :dstmt 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('0632','pass','PCO') END-EXEC MOVE SQLCODE TO SQL-COD else DISPLAY " dml113.pco *** fail *** " EXEC SQL INSERT INTO HU.TESTREPORT VALUES('0632','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 TEST0632 ******************** ******************** BEGIN TEST0634 ******************* MOVE 1 TO flag DISPLAY " TEST0634 " DISPLAY " TRIM function (dynamic)" DISPLAY "References:" DISPLAY " FIPS PUB 127-2 14.1 Transitional SQL features - " 1,7" DISPLAY " - - - - - - - - - - - - - - - - - - -" COMPUTE SQLCODE = -1 MOVE "xxxxx" TO SQLSTATE EXEC SQL DELETE FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL INSERT INTO CONCATBUF VALUES ( 'CREATE TABLE WEIRDPAD (' || ' NAAM CHAR (14),' || ' SPONSOR CHAR (14))' ) END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL COMMIT WORK END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "EXECUTE IMMEDIATE :longst;" EXEC SQL EXECUTE IMMEDIATE :longst 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 " " EXEC SQL DELETE FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL INSERT INTO CONCATBUF VALUES ( 'INSERT INTO WEIRDPAD VALUES' || ' (''KATEBBBBBBBBBB'', ''000000000KEITH'')' ) END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "EXECUTE IMMEDIATE :longst;" EXEC SQL EXECUTE IMMEDIATE :longst END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " EXEC SQL DELETE FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL INSERT INTO CONCATBUF VALUES ( 'INSERT INTO WEIRDPAD VALUES' || ' ('' KEITH '', ''XXXXKATEXXXXXX'')' ) END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "EXECUTE IMMEDIATE :longst;" EXEC SQL EXECUTE IMMEDIATE :longst END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " EXEC SQL DELETE FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL INSERT INTO CONCATBUF VALUES ( 'SELECT TRIM (''X'' FROM SPONSOR)' || ' FROM WEIRDPAD' || ' WHERE TRIM (NAAM) = ''KEITH''' ) END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "PREPARE S11351 FROM :longst;" EXEC SQL PREPARE S11351 FROM :longst END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C11351 CURSOR FOR S11351;" EXEC SQL DECLARE C11351 CURSOR FOR S11351 END-EXEC DISPLAY " " DISPLAY "OPEN C11351;" EXEC SQL OPEN C11351 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " MOVE "BLAHBLAHBLAHBL" TO ch2 DISPLAY "FETCH C11351 INTO :ch2;" EXEC SQL FETCH C11351 INTO :ch2 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "ch2 should be 'KATE'; its value is '", ch2 "'" *In languages with variable length strings, the result should *not have trailing blanks. * if (ch2 NOT = "KATE") if (ch2 NOT = "KATE") then MOVE 0 TO flag END-IF DISPLAY "CLOSE C11351;" EXEC SQL CLOSE C11351 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " EXEC SQL DELETE FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL INSERT INTO CONCATBUF VALUES ( 'SELECT TRIM (LEADING ''X'' FROM SPONSOR)' || ' FROM WEIRDPAD' || ' WHERE TRIM (TRAILING FROM NAAM) = '' KEITH''' ) END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "PREPARE S11352 FROM :longst;" EXEC SQL PREPARE S11352 FROM :longst END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C11352 CURSOR FOR S11352;" EXEC SQL DECLARE C11352 CURSOR FOR S11352 END-EXEC DISPLAY " " DISPLAY "OPEN C11352;" EXEC SQL OPEN C11352 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " MOVE "BLAHBLAHBLAHBL" TO ch2 DISPLAY "FETCH C11352 INTO :ch2;" EXEC SQL FETCH C11352 INTO :ch2 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "ch2 should be 'KATEXXXXXX'; its value is '", ch2 "'" *In languages with variable length strings, the result should *not have trailing blanks. * if (ch2 NOT = "KATEXXXXXX") if (ch2 NOT = "KATEXXXXXX") then MOVE 0 TO flag END-IF DISPLAY "CLOSE C11352;" EXEC SQL CLOSE C11352 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " EXEC SQL DELETE FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL INSERT INTO CONCATBUF VALUES ( 'SELECT COUNT(*) FROM WEIRDPAD A,' || ' WEIRDPAD B WHERE TRIM (BOTH ''B'' FROM A.NAAM)' || ' = TRIM (BOTH ''X'' FROM B.SPONSOR)' ) END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "PREPARE S11353 FROM :longst;" EXEC SQL PREPARE S11353 FROM :longst END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C11353 CURSOR FOR S11353;" EXEC SQL DECLARE C11353 CURSOR FOR S11353 END-EXEC DISPLAY " " DISPLAY "OPEN C11353;" EXEC SQL OPEN C11353 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 DISPLAY "FETCH C11353 INTO :int1;" EXEC SQL FETCH C11353 INTO :int1 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "int1 should be 1; its value is ", int1 if (int1 NOT = 1) then MOVE 0 TO flag END-IF DISPLAY "CLOSE C11353;" EXEC SQL CLOSE C11353 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " EXEC SQL DELETE FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL INSERT INTO CONCATBUF VALUES ( 'SELECT COUNT(*) FROM WEIRDPAD A,' || ' WEIRDPAD B WHERE TRIM (LEADING ''0'' FROM A.SPONSOR)' || ' = TRIM ('' '' FROM B.NAAM)' ) END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "PREPARE S11354 FROM :longst;" EXEC SQL PREPARE S11354 FROM :longst END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C11354 CURSOR FOR S11354;" EXEC SQL DECLARE C11354 CURSOR FOR S11354 END-EXEC DISPLAY " " DISPLAY "OPEN C11354;" EXEC SQL OPEN C11354 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 DISPLAY "FETCH C11354 INTO :int1;" EXEC SQL FETCH C11354 INTO :int1 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "int1 should be 1; its value is ", int1 if (int1 NOT = 1) then MOVE 0 TO flag END-IF DISPLAY "CLOSE C11354;" EXEC SQL CLOSE C11354 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " EXEC SQL DELETE FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL INSERT INTO CONCATBUF VALUES ( 'SELECT TRIM (''BB'' FROM NAAM)' || ' FROM WEIRDPAD WHERE NAAM LIKE' || ' ''KATE%''' ) END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "PREPARE S11355 FROM :longst;" EXEC SQL PREPARE S11355 FROM :longst END-EXEC MOVE SQLCODE TO SQL-COD if (SQLCODE = 0) then DISPLAY "SQLCODE is 0" GO TO P111 END-IF DISPLAY "SQLCODE should be < 0; its value is ", SQL-COD DISPLAY "SQLSTATE should be 22027; its value is ", SQLSTATE if (SQLCODE NOT < 0 OR SQLSTATE NOT = "22027") then MOVE 0 TO flag END-IF GO TO P110 . P111. DISPLAY "DECLARE C11355 CURSOR FOR S11355;" EXEC SQL DECLARE C11355 CURSOR FOR S11355 END-EXEC DISPLAY " " DISPLAY "OPEN C11355;" EXEC SQL OPEN C11355 END-EXEC MOVE SQLCODE TO SQL-COD if (SQLCODE = 0) then DISPLAY "SQLCODE is 0" GO TO P109 END-IF DISPLAY "SQLCODE should be < 0; its value is ", SQL-COD DISPLAY "SQLSTATE should be 22027; its value is ", SQLSTATE if (SQLCODE NOT < 0 OR SQLSTATE NOT = "22027") then MOVE 0 TO flag END-IF GO TO P110 . P109. DISPLAY "FETCH C11355 INTO :int1;" EXEC SQL FETCH C11355 INTO :int1 END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "SQLCODE should be < 0; its value is ", SQL-COD DISPLAY "SQLSTATE should be 22027; its value is ", SQLSTATE if (SQLCODE NOT < 0 OR SQLSTATE NOT = "22027") then MOVE 0 TO flag END-IF DISPLAY "CLOSE C11355;" EXEC SQL CLOSE C11355 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " . P110. DISPLAY "COMMIT WORK;" EXEC SQL COMMIT WORK END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "dstmt=""DROP TABLE WEIRDPAD CASCADE""" MOVE "DROP TABLE WEIRDPAD CASCADE " TO dstmt DISPLAY "EXECUTE IMMEDIATE :dstmt;" EXEC SQL EXECUTE IMMEDIATE :dstmt 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('0634','pass','PCO') END-EXEC MOVE SQLCODE TO SQL-COD else DISPLAY " dml113.pco *** fail *** " EXEC SQL INSERT INTO HU.TESTREPORT VALUES('0634','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 TEST0634 ******************** **** 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.119 Sekunden (vorverarbeitet am 2026-04-25) ¤*© Formatika GbR, Deutschland |
|
||||||||||||||
2026-05-26