| products/sources/formale Sprachen/Cobol/verschiedene-Autoren/Goedel/ |
|
Quellcode-Bibliothek© Kompilation durch diese Firma[Weder Korrektheit noch Funktionsfähigkeit der Software werden zugesichert.]Datei: dml181.cob Sprache: 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 DISPLAY " 11.15 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;" --> -------------------- --> maximum size reached --> -------------------- ¤ Dauer der Verarbeitung: 0.70 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.Bot Zugriff |
