| Quellcodebibliothek Statistik Leitseite products/Sources/formale Sprachen/Cobol/Test-Suite/SQL P/dml100-186/ Datei vom 4.1.2008 mit Größe 72 kB |
|
SSL dml105.cob
Interaktion und |
|||||||||
|
IDENTIFICATION DIVISION.
PROGRAM-ID. DML105. ENVIRONMENT DIVISION. CONFIGURATION SECTION. SOURCE-COMPUTER. xyz. OBJECT-COMPUTER. xyz. DATA DIVISION. WORKING-STORAGE SECTION. * EMBEDDED COBOL (file "DML105.PCO") **************************************************************** * * COMMENT SECTION * * DATE 1993/08/16 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. * * DML105.PCO * WRITTEN BY: David W. Flater * TRANSLATED AUTOMATICALLY FROM EMBEDDED C BY CHRIS SCHANZLE * * This routine tests Fips Feature #4 for dynamic TSQL. Fips * Feature #4 is "joined table." * * REFERENCES * FIPS PUB 127-2 14.1 Transitional SQL * ANSI SQL-1992 7.5 <joined table> * **************************************************************** 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 ivar1 PIC S9(9) DISPLAY SIGN LEADING SEPARATE. 01 ivar2 PIC S9(9) DISPLAY SIGN LEADING SEPARATE. 01 cvar1 PIC X(5). 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, dml105.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 TEST0595 ******************* MOVE 1 TO flag DISPLAY " TEST0595 " DISPLAY " NATURAL JOIN (feature 4) (dynamic)" DISPLAY "References:" DISPLAY " FIPS PUB 127-2 14.1 Transitional SQL feature - " 4" DISPLAY " ANSI SQL-1992 7.5 DISPLAY " - - - - - - - - - - - - - - - - - - -" COMPUTE SQLCODE = -1 MOVE "xxxxx" TO SQLSTATE DISPLAY "dstmt=""CREATE TABLE GROUPS1 (EMPNUM INT, GRP - " INT)""" MOVE "CREATE TABLE GROUPS1 (EMPNUM INT, GRP 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 NAMES1 (EMPNUM INT, NAME - " CHAR(5))""" MOVE "CREATE TABLE NAMES1 (EMPNUM INT, NAAM CHAR(5)) " 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 NAMGRP1 AS SELECT * ' || ' FROM NAMES1 NATURAL JOIN GROUPS1' ) 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 GROUPS1 VALUES (0, 10)""" MOVE "INSERT INTO GROUPS1 VALUES (0, 10) " 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 GROUPS1 VALUES (1, 20)""" MOVE "INSERT INTO GROUPS1 VALUES (1, 20) " 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 GROUPS1 VALUES (2, 30)""" MOVE "INSERT INTO GROUPS1 VALUES (2, 30) " 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 GROUPS1 VALUES (3, 40)""" MOVE "INSERT INTO GROUPS1 VALUES (3, 40) " 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 NAMES1 VALUES (5, 'HARRY')""" MOVE "INSERT INTO NAMES1 VALUES (5, 'HARRY') " 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 NAMES1 VALUES (1, 'MARY')""" MOVE "INSERT INTO NAMES1 VALUES (1, 'MARY') " 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 NAMES1 VALUES (7, 'LARRY')""" MOVE "INSERT INTO NAMES1 VALUES (7, 'LARRY') " 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 NAMES1 VALUES (0, 'KERI')""" MOVE "INSERT INTO NAMES1 VALUES (0, 'KERI') " 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 NAMES1 VALUES (9, 'BARRY')""" MOVE "INSERT INTO NAMES1 VALUES (9, 'BARRY') " 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 ( 'SELECT EMPNUM FROM NAMGRP1 WHERE NAAM = ? AND GRP = ?' ) 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 S10511 FROM :longst;" EXEC SQL PREPARE S10511 FROM :longst END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C10511 CURSOR FOR S10511;" EXEC SQL DECLARE C10511 CURSOR FOR S10511 END-EXEC DISPLAY " " DISPLAY "ivar1 = 10;" MOVE 10 TO ivar1 MOVE "KERI " TO cvar1 DISPLAY "OPEN C10511 USING :cvar1, :ivar1;" EXEC SQL OPEN C10511 USING :cvar1, :ivar1 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 DISPLAY "FETCH C10511 INTO :int1;" EXEC SQL FETCH C10511 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 C10511;" EXEC SQL CLOSE C10511 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "ivar1 = 20;" MOVE 20 TO ivar1 MOVE "MARY " TO cvar1 DISPLAY "OPEN C10511 USING :cvar1, :ivar1;" EXEC SQL OPEN C10511 USING :cvar1, :ivar1 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 DISPLAY "FETCH C10511 INTO :int1;" EXEC SQL FETCH C10511 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 C10511;" EXEC SQL CLOSE C10511 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 NAMGRP1 ' || ' WHERE (NAAM <> ''MARY''' || ' AND NAAM <> ''KERI'')' || ' OR (GRP <> 20 ' || ' AND GRP <> 10) ' || ' OR (EMPNUM <> 0 ' || ' AND EMPNUM <> 1) ' || ' OR NAAM IS NULL ' || ' OR GRP IS NULL ' || ' OR EMPNUM 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 S10512 FROM :longst;" EXEC SQL PREPARE S10512 FROM :longst END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C10512 CURSOR FOR S10512;" EXEC SQL DECLARE C10512 CURSOR FOR S10512 END-EXEC DISPLAY " " DISPLAY "OPEN C10512;" EXEC SQL OPEN C10512 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 DISPLAY "FETCH C10512 INTO :int1;" EXEC SQL FETCH C10512 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 C10512;" EXEC SQL CLOSE C10512 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "ROLLBACK WORK;" EXEC SQL ROLLBACK WORK END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "dstmt=""DROP TABLE NAMES1 CASCADE""" MOVE "DROP TABLE NAMES1 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 GROUPS1 CASCADE""" MOVE "DROP TABLE GROUPS1 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 " " *NAMGRP should go away on its own due to CASCADE. if ( flag = 1 ) then DISPLAY " *** pass *** " EXEC SQL INSERT INTO HU.TESTREPORT VALUES('0595','pass','PCO') END-EXEC MOVE SQLCODE TO SQL-COD else DISPLAY " dml105.pco *** fail *** " EXEC SQL INSERT INTO HU.TESTREPORT VALUES('0595','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 TEST0595 ******************** ******************** BEGIN TEST0596 ******************* MOVE 1 TO flag DISPLAY " TEST0596 " DISPLAY " INNER JOIN (feature 4) (dynamic)" DISPLAY "References:" DISPLAY " FIPS PUB 127-2 14.1 Transitional SQL feature - " 4" DISPLAY " ANSI SQL-1992 7.5 DISPLAY " - - - - - - - - - - - - - - - - - - -" COMPUTE SQLCODE = -1 MOVE "xxxxx" TO SQLSTATE DISPLAY "dstmt=""CREATE TABLE GROUPS2 (EMPNUM INT, GRP - " INT)""" MOVE "CREATE TABLE GROUPS2 (EMPNUM INT, GRP 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 NAMES2 (EMPNUM INT, NAME - " CHAR(5))""" MOVE "CREATE TABLE NAMES2 (EMPNUM INT, NAAM CHAR(5)) " 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 NAMGRP2 AS SELECT * ' || ' FROM NAMES2 INNER JOIN GROUPS2 USING (EMPNUM)' ) 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 " " *Inner is implicit -- 7.5 SR.3 EXEC SQL DELETE FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL INSERT INTO CONCATBUF VALUES ( 'CREATE VIEW NMGRP2 AS SELECT * ' || 'FROM NAMES2 JOIN GROUPS2 USING (EMPNUM) WHERE EMPNUM'|| '> 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 " " DISPLAY "dstmt=""INSERT INTO GROUPS2 VALUES (0, 10)""" MOVE "INSERT INTO GROUPS2 VALUES (0, 10) " 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 GROUPS2 VALUES (1, 20)""" MOVE "INSERT INTO GROUPS2 VALUES (1, 20) " 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 GROUPS2 VALUES (2, 30)""" MOVE "INSERT INTO GROUPS2 VALUES (2, 30) " 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 GROUPS2 VALUES (3, 40)""" MOVE "INSERT INTO GROUPS2 VALUES (3, 40) " 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 NAMES2 VALUES (5, 'HARRY')""" MOVE "INSERT INTO NAMES2 VALUES (5, 'HARRY') " 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 NAMES2 VALUES (1, 'MARY')""" MOVE "INSERT INTO NAMES2 VALUES (1, 'MARY') " 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 NAMES2 VALUES (7, 'LARRY')""" MOVE "INSERT INTO NAMES2 VALUES (7, 'LARRY') " 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 NAMES2 VALUES (0, 'KERI')""" MOVE "INSERT INTO NAMES2 VALUES (0, 'KERI') " 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 NAMES2 VALUES (9, 'BARRY')""" MOVE "INSERT INTO NAMES2 VALUES (9, 'BARRY') " 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 ( 'SELECT EMPNUM FROM NAMGRP2 WHERE NAAM = ? AND GRP = ?' ) 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 S10521 FROM :longst;" EXEC SQL PREPARE S10521 FROM :longst END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C10521 CURSOR FOR S10521;" EXEC SQL DECLARE C10521 CURSOR FOR S10521 END-EXEC DISPLAY " " DISPLAY "ivar1 = 10;" MOVE 10 TO ivar1 MOVE "KERI " TO cvar1 DISPLAY "OPEN C10521 USING :cvar1, :ivar1;" EXEC SQL OPEN C10521 USING :cvar1, :ivar1 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 DISPLAY "FETCH C10521 INTO :int1;" EXEC SQL FETCH C10521 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 C10521;" EXEC SQL CLOSE C10521 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "ivar1 = 20;" MOVE 20 TO ivar1 MOVE "MARY " TO cvar1 DISPLAY "OPEN C10521 USING :cvar1, :ivar1;" EXEC SQL OPEN C10521 USING :cvar1, :ivar1 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 DISPLAY "FETCH C10521 INTO :int1;" EXEC SQL FETCH C10521 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 C10521;" EXEC SQL CLOSE C10521 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 NAMGRP2 ' || ' WHERE NAAM <> ''MARY''' || ' AND NAAM <> ''KERI''' || ' OR GRP <> 20 ' || ' AND GRP <> 10 ' || ' OR EMPNUM <> 0 ' || ' AND EMPNUM <> 1 ' || ' OR NAAM IS NULL ' || ' OR GRP IS NULL ' || ' OR EMPNUM 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 S10522 FROM :longst;" EXEC SQL PREPARE S10522 FROM :longst END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C10522 CURSOR FOR S10522;" EXEC SQL DECLARE C10522 CURSOR FOR S10522 END-EXEC DISPLAY " " DISPLAY "OPEN C10522;" EXEC SQL OPEN C10522 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 DISPLAY "FETCH C10522 INTO :int1;" EXEC SQL FETCH C10522 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 C10522;" EXEC SQL CLOSE C10522 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 EMPNUM FROM NMGRP2 WHERE NAAM = ? AND GRP = ?' ) 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 S10524 FROM :longst;" EXEC SQL PREPARE S10524 FROM :longst END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C10524 CURSOR FOR S10524;" EXEC SQL DECLARE C10524 CURSOR FOR S10524 END-EXEC DISPLAY " " DISPLAY "ivar1 = 20;" MOVE 20 TO ivar1 MOVE "MARY " TO cvar1 DISPLAY "OPEN C10524 USING :cvar1, :ivar1;" EXEC SQL OPEN C10524 USING :cvar1, :ivar1 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 DISPLAY "FETCH C10524 INTO :int1;" EXEC SQL FETCH C10524 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 C10524;" EXEC SQL CLOSE C10524 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 NMGRP2 ' || ' WHERE NAAM <> ''MARY''' || ' OR GRP <> 20 ' || ' OR EMPNUM <> 1 ' || ' OR NAAM IS NULL ' || ' OR GRP IS NULL ' || ' OR EMPNUM 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 S10523 FROM :longst;" EXEC SQL PREPARE S10523 FROM :longst END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C10523 CURSOR FOR S10523;" EXEC SQL DECLARE C10523 CURSOR FOR S10523 END-EXEC DISPLAY " " DISPLAY "OPEN C10523;" EXEC SQL OPEN C10523 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 DISPLAY "FETCH C10523 INTO :int1;" EXEC SQL FETCH C10523 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 C10523;" EXEC SQL CLOSE C10523 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "ROLLBACK WORK;" EXEC SQL ROLLBACK WORK END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "dstmt=""DROP TABLE NAMES2 CASCADE""" MOVE "DROP TABLE NAMES2 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 GROUPS2 CASCADE""" MOVE "DROP TABLE GROUPS2 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 " " *NAMGRP and NMGRP2 should go away on their own due to CASCADE. if ( flag = 1 ) then DISPLAY " *** pass *** " EXEC SQL INSERT INTO HU.TESTREPORT VALUES('0596','pass','PCO') END-EXEC MOVE SQLCODE TO SQL-COD else DISPLAY " dml105.pco *** fail *** " EXEC SQL INSERT INTO HU.TESTREPORT VALUES('0596','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 TEST0596 ******************** ******************** BEGIN TEST0597 ******************* MOVE 1 TO flag DISPLAY " TEST0597 " DISPLAY " LEFT OUTER JOIN (feature 4) (dynamic)" DISPLAY "References:" DISPLAY " FIPS PUB 127-2 14.1 Transitional SQL feature - " 4" DISPLAY " ANSI SQL-1992 7.5 DISPLAY " - - - - - - - - - - - - - - - - - - -" COMPUTE SQLCODE = -1 MOVE "xxxxx" TO SQLSTATE DISPLAY "dstmt=""CREATE TABLE GROUPS3 (EMPNUM INT, GRP - " INT)""" MOVE "CREATE TABLE GROUPS3 (EMPNUM INT, GRP 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 NAMES3 (EMPNUM INT, NAME - " CHAR(5))""" MOVE "CREATE TABLE NAMES3 (EMPNUM INT, NAAM CHAR(5)) " 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 NAMGRP3 AS '|| 'SELECT * FROM NAMES3 LEFT OUTER JOIN GROUPS3 USING'|| '(EMPNUM)' ) 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 " " *This is just to see if ON is also supported EXEC SQL DELETE FROM CONCATBUF END-EXEC MOVE SQLCODE TO SQL-COD EXEC SQL INSERT INTO CONCATBUF VALUES ( 'CREATE VIEW NMGRP3 AS' || ' SELECT NAAM, GRP FROM NAMES3 LEFT OUTER JOIN GROUPS3' || ' ON NAMES3.EMPNUM < GROUPS3.EMPNUM' || ' WHERE NAAM <> ''KERI''' ) 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 GROUPS3 VALUES (0, 10)""" MOVE "INSERT INTO GROUPS3 VALUES (0, 10) " 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 GROUPS3 VALUES (1, 20)""" MOVE "INSERT INTO GROUPS3 VALUES (1, 20) " 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 GROUPS3 VALUES (2, 30)""" MOVE "INSERT INTO GROUPS3 VALUES (2, 30) " 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 GROUPS3 VALUES (3, 40)""" MOVE "INSERT INTO GROUPS3 VALUES (3, 40) " 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 NAMES3 VALUES (5, 'HARRY')""" MOVE "INSERT INTO NAMES3 VALUES (5, 'HARRY') " 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 NAMES3 VALUES (1, 'MARY')""" MOVE "INSERT INTO NAMES3 VALUES (1, 'MARY') " 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 NAMES3 VALUES (7, 'LARRY')""" MOVE "INSERT INTO NAMES3 VALUES (7, 'LARRY') " 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 NAMES3 VALUES (0, 'KERI')""" MOVE "INSERT INTO NAMES3 VALUES (0, 'KERI') " 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 NAMES3 VALUES (9, 'BARRY')""" MOVE "INSERT INTO NAMES3 VALUES (9, 'BARRY') " 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 ( 'SELECT COUNT(*) FROM NAMGRP3 WHERE EMPNUM = ? AND ' || ' NAAM = ? AND GRP = ?' ) 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 S10531 FROM :longst;" EXEC SQL PREPARE S10531 FROM :longst END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C10531 CURSOR FOR S10531;" EXEC SQL DECLARE C10531 CURSOR FOR S10531 END-EXEC DISPLAY " " DISPLAY "ivar1 = 10;" DISPLAY "ivar2 = 0;" MOVE 10 TO ivar1 MOVE 0 TO ivar2 MOVE "KERI " TO cvar1 DISPLAY "OPEN C10531 USING :ivar2, :cvar1, :ivar1;" EXEC SQL OPEN C10531 USING :ivar2, :cvar1, :ivar1 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 DISPLAY "FETCH C10531 INTO :int1;" EXEC SQL FETCH C10531 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 C10531;" EXEC SQL CLOSE C10531 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "ivar1 = 20;" DISPLAY "ivar2 = 1;" MOVE 20 TO ivar1 MOVE 1 TO ivar2 MOVE "MARY " TO cvar1 DISPLAY "OPEN C10531 USING :ivar2, :cvar1, :ivar1;" EXEC SQL OPEN C10531 USING :ivar2, :cvar1, :ivar1 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 DISPLAY "FETCH C10531 INTO :int1;" EXEC SQL FETCH C10531 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 C10531;" EXEC SQL CLOSE C10531 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 NAMGRP3 WHERE EMPNUM = ? AND ' || ' NAAM = ? AND GRP 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 S10533 FROM :longst;" EXEC SQL PREPARE S10533 FROM :longst END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C10533 CURSOR FOR S10533;" EXEC SQL DECLARE C10533 CURSOR FOR S10533 END-EXEC DISPLAY " " DISPLAY "ivar1 = 5;" MOVE 5 TO ivar1 MOVE "HARRY" TO cvar1 DISPLAY "OPEN C10533 USING :ivar1, :cvar1;" EXEC SQL OPEN C10533 USING :ivar1, :cvar1 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 DISPLAY "FETCH C10533 INTO :int1;" EXEC SQL FETCH C10533 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 C10533;" EXEC SQL CLOSE C10533 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "ivar1 = 7;" MOVE 7 TO ivar1 MOVE "LARRY" TO cvar1 DISPLAY "OPEN C10533 USING :ivar1, :cvar1;" EXEC SQL OPEN C10533 USING :ivar1, :cvar1 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 DISPLAY "FETCH C10533 INTO :int1;" EXEC SQL FETCH C10533 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 C10533;" EXEC SQL CLOSE C10533 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "ivar1 = 9;" MOVE 9 TO ivar1 MOVE "BARRY" TO cvar1 DISPLAY "OPEN C10533 USING :ivar1, :cvar1;" EXEC SQL OPEN C10533 USING :ivar1, :cvar1 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 DISPLAY "FETCH C10533 INTO :int1;" EXEC SQL FETCH C10533 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 C10533;" EXEC SQL CLOSE C10533 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "dstmt=""SELECT COUNT(*) FROM NAMGRP3""" MOVE "SELECT COUNT(*) FROM NAMGRP3 - " " TO dstmt DISPLAY "PREPARE S10532 FROM :dstmt;" EXEC SQL PREPARE S10532 FROM :dstmt END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C10532 CURSOR FOR S10532;" EXEC SQL DECLARE C10532 CURSOR FOR S10532 END-EXEC DISPLAY " " DISPLAY "OPEN C10532;" EXEC SQL OPEN C10532 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 DISPLAY "FETCH C10532 INTO :int1;" EXEC SQL FETCH C10532 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 C10532;" EXEC SQL CLOSE C10532 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 NMGRP3 WHERE' || ' NAAM = ''MARY'' AND GRP = ?' ) 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 S10534 FROM :longst;" EXEC SQL PREPARE S10534 FROM :longst END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C10534 CURSOR FOR S10534;" EXEC SQL DECLARE C10534 CURSOR FOR S10534 END-EXEC DISPLAY " " DISPLAY "ivar1 = 30;" MOVE 30 TO ivar1 DISPLAY "OPEN C10534 USING :ivar1;" EXEC SQL OPEN C10534 USING :ivar1 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 DISPLAY "FETCH C10534 INTO :int1;" EXEC SQL FETCH C10534 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 C10534;" EXEC SQL CLOSE C10534 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "ivar1 = 40;" MOVE 40 TO ivar1 DISPLAY "OPEN C10534 USING :ivar1;" EXEC SQL OPEN C10534 USING :ivar1 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 DISPLAY "FETCH C10534 INTO :int1;" EXEC SQL FETCH C10534 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 C10534;" EXEC SQL CLOSE C10534 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 NMGRP3 WHERE' || ' NAAM = ? AND GRP 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 S10535 FROM :longst;" EXEC SQL PREPARE S10535 FROM :longst END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C10535 CURSOR FOR S10535;" EXEC SQL DECLARE C10535 CURSOR FOR S10535 END-EXEC DISPLAY " " MOVE "BARRY" TO cvar1 DISPLAY "OPEN C10535 USING :cvar1;" EXEC SQL OPEN C10535 USING :cvar1 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 DISPLAY "FETCH C10535 INTO :int1;" EXEC SQL FETCH C10535 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 C10535;" EXEC SQL CLOSE C10535 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " MOVE "HARRY" TO cvar1 DISPLAY "OPEN C10535 USING :cvar1;" EXEC SQL OPEN C10535 USING :cvar1 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 DISPLAY "FETCH C10535 INTO :int1;" EXEC SQL FETCH C10535 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 C10535;" EXEC SQL CLOSE C10535 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " MOVE "LARRY" TO cvar1 DISPLAY "OPEN C10535 USING :cvar1;" EXEC SQL OPEN C10535 USING :cvar1 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 DISPLAY "FETCH C10535 INTO :int1;" EXEC SQL FETCH C10535 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 C10535;" EXEC SQL CLOSE C10535 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "dstmt=""SELECT COUNT(*) FROM NMGRP3""" MOVE "SELECT COUNT(*) FROM NMGRP3 - " " TO dstmt DISPLAY "PREPARE S10536 FROM :dstmt;" EXEC SQL PREPARE S10536 FROM :dstmt END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C10536 CURSOR FOR S10536;" EXEC SQL DECLARE C10536 CURSOR FOR S10536 END-EXEC DISPLAY " " DISPLAY "OPEN C10536;" EXEC SQL OPEN C10536 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 DISPLAY "FETCH C10536 INTO :int1;" EXEC SQL FETCH C10536 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 C10536;" EXEC SQL CLOSE C10536 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "ROLLBACK WORK;" EXEC SQL ROLLBACK WORK END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY "dstmt=""DROP TABLE NAMES3 CASCADE""" MOVE "DROP TABLE NAMES3 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 GROUPS3 CASCADE""" MOVE "DROP TABLE GROUPS3 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 " " *NAMGRP should go away on its own due to CASCADE. if ( flag = 1 ) then DISPLAY " *** pass *** " EXEC SQL INSERT INTO HU.TESTREPORT VALUES('0597','pass','PCO') END-EXEC MOVE SQLCODE TO SQL-COD else DISPLAY " dml105.pco *** fail *** " EXEC SQL INSERT INTO HU.TESTREPORT VALUES('0597','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 TEST0597 ******************** ******************** BEGIN TEST0598 ******************* MOVE 1 TO flag DISPLAY " TEST0598 " DISPLAY " RIGHT OUTER JOIN (feature 4) (dynamic)" DISPLAY "References:" DISPLAY " FIPS PUB 127-2 14.1 Transitional SQL feature - " 4" DISPLAY " ANSI SQL-1992 7.5 DISPLAY " - - - - - - - - - - - - - - - - - - -" COMPUTE SQLCODE = -1 MOVE "xxxxx" TO SQLSTATE DISPLAY "dstmt=""CREATE TABLE GROUPS4 (EMPNUM INT, GRP - " INT)""" MOVE "CREATE TABLE GROUPS4 (EMPNUM INT, GRP 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 NAMES4 (EMPNUM INT, NAME - " CHAR(5))""" MOVE "CREATE TABLE NAMES4 (EMPNUM INT, NAAM CHAR(5)) " 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 NAMGRP4 AS '|| 'SELECT * FROM NAMES4 RIGHT OUTER JOIN GROUPS4 USING '|| '(EMPNUM)' ) 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 GROUPS4 VALUES (0, 10)""" MOVE "INSERT INTO GROUPS4 VALUES (0, 10) " 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 GROUPS4 VALUES (1, 20)""" MOVE "INSERT INTO GROUPS4 VALUES (1, 20) " 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 GROUPS4 VALUES (2, 30)""" MOVE "INSERT INTO GROUPS4 VALUES (2, 30) " 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 GROUPS4 VALUES (3, 40)""" MOVE "INSERT INTO GROUPS4 VALUES (3, 40) " 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 NAMES4 VALUES (5, 'HARRY')""" MOVE "INSERT INTO NAMES4 VALUES (5, 'HARRY') " 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 NAMES4 VALUES (1, 'MARY')""" MOVE "INSERT INTO NAMES4 VALUES (1, 'MARY') " 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 NAMES4 VALUES (7, 'LARRY')""" MOVE "INSERT INTO NAMES4 VALUES (7, 'LARRY') " 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 NAMES4 VALUES (0, 'KERI')""" MOVE "INSERT INTO NAMES4 VALUES (0, 'KERI') " 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 NAMES4 VALUES (9, 'BARRY')""" MOVE "INSERT INTO NAMES4 VALUES (9, 'BARRY') " 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 ( 'SELECT COUNT(*) FROM NAMGRP4 WHERE EMPNUM = ? AND ' || ' NAAM = ? AND GRP = ?' ) 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 S10541 FROM :longst;" EXEC SQL PREPARE S10541 FROM :longst END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C10541 CURSOR FOR S10541;" EXEC SQL DECLARE C10541 CURSOR FOR S10541 END-EXEC DISPLAY " " DISPLAY "ivar1 = 10;" DISPLAY "ivar2 = 0;" MOVE 10 TO ivar1 MOVE 0 TO ivar2 MOVE "KERI" TO cvar1 DISPLAY "OPEN C10541 USING :ivar2, :cvar1, :ivar1;" EXEC SQL OPEN C10541 USING :ivar2, :cvar1, :ivar1 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 DISPLAY "FETCH C10541 INTO :int1;" EXEC SQL FETCH C10541 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 C10541;" EXEC SQL CLOSE C10541 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "ivar1 = 20;" DISPLAY "ivar2 = 1;" MOVE 20 TO ivar1 MOVE 1 TO ivar2 MOVE "MARY" TO cvar1 DISPLAY "OPEN C10541 USING :ivar2, :cvar1, :ivar1;" EXEC SQL OPEN C10541 USING :ivar2, :cvar1, :ivar1 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 DISPLAY "FETCH C10541 INTO :int1;" EXEC SQL FETCH C10541 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 C10541;" EXEC SQL CLOSE C10541 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 NAMGRP4 WHERE EMPNUM = ? AND ' || ' NAAM IS NULL AND GRP = ?' ) 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 S10542 FROM :longst;" EXEC SQL PREPARE S10542 FROM :longst END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C10542 CURSOR FOR S10542;" EXEC SQL DECLARE C10542 CURSOR FOR S10542 END-EXEC DISPLAY " " DISPLAY "ivar1 = 2;" DISPLAY "ivar2 = 30;" MOVE 2 TO ivar1 MOVE 30 TO ivar2 DISPLAY "OPEN C10542 USING :ivar1, :ivar2;" EXEC SQL OPEN C10542 USING :ivar1, :ivar2 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 DISPLAY "FETCH C10542 INTO :int1;" EXEC SQL FETCH C10542 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 --> -------------------- --> maximum size reached --> -------------------- ¤ 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.0.55Bemerkung: Wie Sie bei der Firma Beratungs- und Dienstleistungen beauftragen können ¤*Eine klare Vorstellung vom Zielzustand |
|
2026-03-28