| products/sources/formale sprachen/Cobol/Test-Suite/SQL M/ |
|
Quellcode-Bibliothek
© Kompilation durch diese Firma
[Weder Korrektheit noch Funktionsfähigkeit der Software werden zugesichert.]
Datei: mpb004.cob Sprache: Unknown
|
IDENTIFICATION DIVISION.
PROGRAM-ID. DML134. ENVIRONMENT DIVISION. CONFIGURATION SECTION. SOURCE-COMPUTER. xyz. OBJECT-COMPUTER. xyz. DATA DIVISION. WORKING-STORAGE SECTION. * Standard COBOL (file "DML134.SCO") calling SQL * procedures in file "DML134.MCO". **************************************************************** * * COMMENT SECTION * * DATE 1994/8/19 STANDARD 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. * * DML134.SCO * WRITTEN BY: David W. Flater * TRANSLATED AUTOMATICALLY FROM EMBEDDED COBOL BY CHRIS SCHANZLE * * This program tests the Information Schema and tests multiple * Trans SQL features together. * * 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 int3 PIC S9(9) DISPLAY SIGN LEADING SEPARATE. 01 dstmt PIC X(50). 01 longst PIC X(240). 01 codev PIC S9(9) DISPLAY SIGN LEADING SEPARATE. 01 indic1 PIC S9(4) DISPLAY SIGN LEADING SEPARATE. * EXEC SQL END DECLARE SECTION END-EXEC 01 norm1 PIC S9(9) DISPLAY SIGN LEADING SEPARATE. 01 norm2 PIC S9(9) DISPLAY SIGN LEADING SEPARATE. 01 ALPNUM-TABLE VALUE IS "01234ABCDEFGH56789IJKLMNOPQRSTUVWXYZ". 05 ALPNUM PIC X OCCURS 36 TIMES. 01 NORMSQ. 05 NORMSQX PIC X OCCURS 5 TIMES. 01 errcnt PIC S9(9) DISPLAY SIGN LEADING SEPARATE. *date_time declaration 01 TO-DAY PIC 9(6). 01 THE-TIME PIC 9(8). 01 flag PIC S9(9) DISPLAY SIGN LEADING SEPARATE. 01 SQL-COD PIC S9(9) DISPLAY SIGN LEADING SEPARATE. PROCEDURE DIVISION. P0. MOVE "FLATER " TO uid CALL "AUTHID" USING uid MOVE "not logged in, not" TO uidx * EXEC SQL SELECT USER INTO :uidx FROM HU.ECCO; CALL "SUB1" USING SQLCODE SQLSTATE uidx MOVE SQLCODE TO SQL-COD * EXEC SQL ROLLBACK WORK; CALL "SUB2" USING SQLCODE SQLSTATE 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, Module COBOL, dml134.sco" 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 TEST0688 ******************* MOVE 1 TO flag DISPLAY " TEST0688 " DISPLAY " INFO_SCHEM: Dynamic changes are visible" DISPLAY "References:" DISPLAY " F# 1 -- Dynamic SQL" DISPLAY " F# 2 -- Basic information schema" DISPLAY " F# 3 -- Basic schema manipulation" DISPLAY " FIPS Change Notice #1 -- Shortened - " identifiers" DISPLAY " in INFO_SCHEM" DISPLAY " - - - - - - - - - - - - - - - - - - -" COMPUTE SQLCODE = -1 MOVE "xxxxx" TO SQLSTATE *Table should not exist yet * EXEC SQL DELETE FROM CONCATBUF; CALL "SUB3" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD * EXEC SQL INSERT INTO CONCATBUF VALUES ( * 'SELECT COUNT(*) FROM INFO_SCHEM.TABLES' || * ' WHERE TABLE_SCHEM = ''FLATER'' AND' || * ' TABLE_NAME = ''VISCHANGE''' * ); CALL "SUB4" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD * EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF; CALL "SUB5" USING SQLCODE SQLSTATE longst MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "PREPARE S13411 FROM :longst;" * EXEC SQL PREPARE S13411 FROM :longst; CALL "SUB6" USING SQLCODE SQLSTATE longst MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C13411 CURSOR FOR S13411;" * EXEC SQL DECLARE C13411 CURSOR FOR S13411 END-EXEC DISPLAY "OPEN C13411;" * EXEC SQL OPEN C13411; CALL "SUB7" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " MOVE 50 TO int1 DISPLAY "FETCH C13411 INTO :int1;" * EXEC SQL FETCH C13411 INTO :int1; CALL "SUB8" USING SQLCODE SQLSTATE int1 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 C13411;" * EXEC SQL CLOSE C13411; CALL "SUB9" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " * EXEC SQL DELETE FROM CONCATBUF; CALL "SUB10" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD * EXEC SQL INSERT INTO CONCATBUF VALUES ( * 'SELECT COUNT(*) FROM INFO_SCHEM.COLUMNS' || * ' WHERE TABLE_SCHEM = ''FLATER'' AND' || * ' TABLE_NAME = ''VISCHANGE''' * ); CALL "SUB11" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD * EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF; CALL "SUB12" USING SQLCODE SQLSTATE longst MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "PREPARE S13412 FROM :longst;" * EXEC SQL PREPARE S13412 FROM :longst; CALL "SUB13" USING SQLCODE SQLSTATE longst MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C13412 CURSOR FOR S13412;" * EXEC SQL DECLARE C13412 CURSOR FOR S13412 END-EXEC DISPLAY "OPEN C13412;" * EXEC SQL OPEN C13412; CALL "SUB14" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " MOVE 50 TO int1 DISPLAY "FETCH C13412 INTO :int1;" * EXEC SQL FETCH C13412 INTO :int1; CALL "SUB15" USING SQLCODE SQLSTATE int1 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 C13412;" * EXEC SQL CLOSE C13412; CALL "SUB16" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "ROLLBACK WORK;" * EXEC SQL ROLLBACK WORK; CALL "SUB17" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "dstmt=""CREATE TABLE VISCHANGE (C1 INT, C2 - " FLOAT)""" MOVE "CREATE TABLE VISCHANGE (C1 INT, C2 FLOAT) " TO dstmt DISPLAY "EXECUTE IMMEDIATE :dstmt;" * EXEC SQL EXECUTE IMMEDIATE :dstmt; CALL "SUB18" USING SQLCODE SQLSTATE dstmt MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "COMMIT WORK;" * EXEC SQL COMMIT WORK; CALL "SUB19" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " *Table should exist * EXEC SQL DELETE FROM CONCATBUF; CALL "SUB20" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD * EXEC SQL INSERT INTO CONCATBUF VALUES ( * 'SELECT COUNT(*) FROM INFO_SCHEM.TABLES' || * ' WHERE TABLE_SCHEM = ''FLATER'' AND' || * ' TABLE_NAME = ''VISCHANGE''' * ); CALL "SUB21" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD * EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF; CALL "SUB22" USING SQLCODE SQLSTATE longst MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "PREPARE S13413 FROM :longst;" * EXEC SQL PREPARE S13413 FROM :longst; CALL "SUB23" USING SQLCODE SQLSTATE longst MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C13413 CURSOR FOR S13413;" * EXEC SQL DECLARE C13413 CURSOR FOR S13413 END-EXEC DISPLAY "OPEN C13413;" * EXEC SQL OPEN C13413; CALL "SUB24" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " MOVE 50 TO int1 DISPLAY "FETCH C13413 INTO :int1;" * EXEC SQL FETCH C13413 INTO :int1; CALL "SUB25" USING SQLCODE SQLSTATE int1 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 C13413;" * EXEC SQL CLOSE C13413; CALL "SUB26" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " * EXEC SQL DELETE FROM CONCATBUF; CALL "SUB27" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD * EXEC SQL INSERT INTO CONCATBUF VALUES ( * 'SELECT COUNT(*) FROM INFO_SCHEM.COLUMNS' || * ' WHERE TABLE_SCHEM = ''FLATER'' AND' || * ' TABLE_NAME = ''VISCHANGE''' * ); CALL "SUB28" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD * EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF; CALL "SUB29" USING SQLCODE SQLSTATE longst MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "PREPARE S13414 FROM :longst;" * EXEC SQL PREPARE S13414 FROM :longst; CALL "SUB30" USING SQLCODE SQLSTATE longst MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C13414 CURSOR FOR S13414;" * EXEC SQL DECLARE C13414 CURSOR FOR S13414 END-EXEC DISPLAY "OPEN C13414;" * EXEC SQL OPEN C13414; CALL "SUB31" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " MOVE 50 TO int1 DISPLAY "FETCH C13414 INTO :int1;" * EXEC SQL FETCH C13414 INTO :int1; CALL "SUB32" USING SQLCODE SQLSTATE int1 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 C13414;" * EXEC SQL CLOSE C13414; CALL "SUB33" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "ROLLBACK WORK;" * EXEC SQL ROLLBACK WORK; CALL "SUB34" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "dstmt=""ALTER TABLE VISCHANGE DROP C1 RESTRICT""" MOVE "ALTER TABLE VISCHANGE DROP C1 RESTRICT " TO dstmt DISPLAY "EXECUTE IMMEDIATE :dstmt;" * EXEC SQL EXECUTE IMMEDIATE :dstmt; CALL "SUB35" USING SQLCODE SQLSTATE dstmt MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "COMMIT WORK;" * EXEC SQL COMMIT WORK; CALL "SUB36" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " *Deleted column should not exist * EXEC SQL DELETE FROM CONCATBUF; CALL "SUB37" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD * EXEC SQL INSERT INTO CONCATBUF VALUES ( * 'SELECT COUNT(*) FROM INFO_SCHEM.TABLES' || * ' WHERE TABLE_SCHEM = ''FLATER'' AND' || * ' TABLE_NAME = ''VISCHANGE''' * ); CALL "SUB38" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD * EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF; CALL "SUB39" USING SQLCODE SQLSTATE longst MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "PREPARE S13415 FROM :longst;" * EXEC SQL PREPARE S13415 FROM :longst; CALL "SUB40" USING SQLCODE SQLSTATE longst MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C13415 CURSOR FOR S13415;" * EXEC SQL DECLARE C13415 CURSOR FOR S13415 END-EXEC DISPLAY "OPEN C13415;" * EXEC SQL OPEN C13415; CALL "SUB41" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " MOVE 50 TO int1 DISPLAY "FETCH C13415 INTO :int1;" * EXEC SQL FETCH C13415 INTO :int1; CALL "SUB42" USING SQLCODE SQLSTATE int1 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 C13415;" * EXEC SQL CLOSE C13415; CALL "SUB43" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " * EXEC SQL DELETE FROM CONCATBUF; CALL "SUB44" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD * EXEC SQL INSERT INTO CONCATBUF VALUES ( * 'SELECT COUNT(*) FROM INFO_SCHEM.COLUMNS' || * ' WHERE TABLE_SCHEM = ''FLATER'' AND' || * ' TABLE_NAME = ''VISCHANGE''' * ); CALL "SUB45" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD * EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF; CALL "SUB46" USING SQLCODE SQLSTATE longst MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "PREPARE S13416 FROM :longst;" * EXEC SQL PREPARE S13416 FROM :longst; CALL "SUB47" USING SQLCODE SQLSTATE longst MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C13416 CURSOR FOR S13416;" * EXEC SQL DECLARE C13416 CURSOR FOR S13416 END-EXEC DISPLAY "OPEN C13416;" * EXEC SQL OPEN C13416; CALL "SUB48" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " MOVE 50 TO int1 DISPLAY "FETCH C13416 INTO :int1;" * EXEC SQL FETCH C13416 INTO :int1; CALL "SUB49" USING SQLCODE SQLSTATE int1 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 C13416;" * EXEC SQL CLOSE C13416; CALL "SUB50" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "ROLLBACK WORK;" * EXEC SQL ROLLBACK WORK; CALL "SUB51" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "dstmt=""DROP TABLE VISCHANGE CASCADE""" MOVE "DROP TABLE VISCHANGE CASCADE " TO dstmt DISPLAY "EXECUTE IMMEDIATE :dstmt;" * EXEC SQL EXECUTE IMMEDIATE :dstmt; CALL "SUB52" USING SQLCODE SQLSTATE dstmt MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "COMMIT WORK;" * EXEC SQL COMMIT WORK; CALL "SUB53" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " *Table should not exist * EXEC SQL DELETE FROM CONCATBUF; CALL "SUB54" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD * EXEC SQL INSERT INTO CONCATBUF VALUES ( * 'SELECT COUNT(*) FROM INFO_SCHEM.TABLES' || * ' WHERE TABLE_SCHEM = ''FLATER'' AND' || * ' TABLE_NAME = ''VISCHANGE''' * ); CALL "SUB55" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD * EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF; CALL "SUB56" USING SQLCODE SQLSTATE longst MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "PREPARE S13417 FROM :longst;" * EXEC SQL PREPARE S13417 FROM :longst; CALL "SUB57" USING SQLCODE SQLSTATE longst MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C13417 CURSOR FOR S13417;" * EXEC SQL DECLARE C13417 CURSOR FOR S13417 END-EXEC DISPLAY "OPEN C13417;" * EXEC SQL OPEN C13417; CALL "SUB58" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " MOVE 50 TO int1 DISPLAY "FETCH C13417 INTO :int1;" * EXEC SQL FETCH C13417 INTO :int1; CALL "SUB59" USING SQLCODE SQLSTATE int1 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 C13417;" * EXEC SQL CLOSE C13417; CALL "SUB60" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " * EXEC SQL DELETE FROM CONCATBUF; CALL "SUB61" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD * EXEC SQL INSERT INTO CONCATBUF VALUES ( * 'SELECT COUNT(*) FROM INFO_SCHEM.COLUMNS' || * ' WHERE TABLE_SCHEM = ''FLATER'' AND' || * ' TABLE_NAME = ''VISCHANGE''' * ); CALL "SUB62" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD * EXEC SQL SELECT ZZ INTO :longst FROM CONCATBUF; CALL "SUB63" USING SQLCODE SQLSTATE longst MOVE SQLCODE TO SQL-COD DISPLAY "longst=""", longst """" DISPLAY "PREPARE S13418 FROM :longst;" * EXEC SQL PREPARE S13418 FROM :longst; CALL "SUB64" USING SQLCODE SQLSTATE longst MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE C13418 CURSOR FOR S13418;" * EXEC SQL DECLARE C13418 CURSOR FOR S13418 END-EXEC DISPLAY "OPEN C13418;" * EXEC SQL OPEN C13418; CALL "SUB65" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " MOVE 50 TO int1 DISPLAY "FETCH C13418 INTO :int1;" * EXEC SQL FETCH C13418 INTO :int1; CALL "SUB66" USING SQLCODE SQLSTATE int1 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 C13418;" * EXEC SQL CLOSE C13418; CALL "SUB67" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "ROLLBACK WORK;" * EXEC SQL ROLLBACK WORK; CALL "SUB68" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " if ( flag = 1 ) then DISPLAY " *** pass *** " * EXEC SQL INSERT INTO HU.TESTREPORT * VALUES('0688','pass','MCO'); CALL "SUB69" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD else DISPLAY " dml134.sco *** fail *** " * EXEC SQL INSERT INTO HU.TESTREPORT * VALUES('0688','fail','MCO'); CALL "SUB70" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD COMPUTE errcnt = errcnt + 1 END-IF DISPLAY "===============================================" * EXEC SQL COMMIT WORK; CALL "SUB71" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD ******************** END TEST0688 ******************** ******************** BEGIN TEST0689 ******************* MOVE 1 TO flag DISPLAY " TEST0689 " DISPLAY " Many Trans SQL features #1: inventory system" DISPLAY "References:" DISPLAY " F# 1 -- Dynamic SQL" DISPLAY " F# 3 -- Basic schema manipulation" DISPLAY " F# 4 -- Joined table" DISPLAY " F# 6 -- VARCHAR data type" DISPLAY " F# 7 -- TRIM function" DISPLAY " F# 8 -- UNION in views" DISPLAY " F# 16 -- PRIMARY KEY enhancement" DISPLAY " F# 21 -- INSERT expressions" DISPLAY " F# 24 -- Keyword relaxations" DISPLAY " - - - - - - - - - - - - - - - - - - -" COMPUTE SQLCODE = -1 MOVE "xxxxx" TO SQLSTATE DISPLAY "CREATE TABLE COST_CODES (" DISPLAY " COSTCODE INT UNIQUE," DISPLAY " COSTTEXT VARCHAR (50) NOT NULL);" * EXEC SQL CREATE TABLE COST_CODES ( * COSTCODE INT UNIQUE, * COSTTEXT VARCHAR (50) NOT NULL); CALL "SUB72" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "COMMIT;" * EXEC SQL COMMIT; CALL "SUB73" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "CREATE TABLE CONDITION_CODES (" DISPLAY " CONDCODE INT UNIQUE," DISPLAY " CONDTEXT VARCHAR (50) NOT NULL);" * EXEC SQL CREATE TABLE CONDITION_CODES ( * CONDCODE INT UNIQUE, * CONDTEXT VARCHAR (50) NOT NULL); CALL "SUB74" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "COMMIT;" * EXEC SQL COMMIT; CALL "SUB75" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "CREATE TABLE ITEM_CODES (" DISPLAY " ITEMCODE INT PRIMARY KEY," DISPLAY " ITEMTEXT VARCHAR (50) NOT NULL);" * EXEC SQL CREATE TABLE ITEM_CODES ( * ITEMCODE INT PRIMARY KEY, * ITEMTEXT VARCHAR (50) NOT NULL); CALL "SUB76" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "COMMIT;" * EXEC SQL COMMIT; CALL "SUB77" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "CREATE TABLE INVENTORY (" DISPLAY " COSTCODE INT REFERENCES COST_CODES (COSTCODE)," DISPLAY " CONDCODE INT REFERENCES CONDITION_CODES - " (CONDCODE)," DISPLAY " ITEMCODE INT REFERENCES ITEM_CODES);" * EXEC SQL CREATE TABLE INVENTORY ( * COSTCODE INT REFERENCES COST_CODES (COSTCODE), * CONDCODE INT REFERENCES CONDITION_CODES (CONDCODE), * ITEMCODE INT REFERENCES ITEM_CODES); CALL "SUB78" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "COMMIT;" * EXEC SQL COMMIT; CALL "SUB79" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "CREATE VIEW COMPLETES AS" DISPLAY " SELECT ITEMTEXT, CONDTEXT, COSTTEXT" DISPLAY " FROM INVENTORY NATURAL JOIN COST_CODES" DISPLAY " NATURAL JOIN CONDITION_CODES" DISPLAY " NATURAL JOIN ITEM_CODES;" * EXEC SQL CREATE VIEW COMPLETES AS * SELECT ITEMTEXT, CONDTEXT, COSTTEXT * FROM INVENTORY NATURAL JOIN COST_CODES * NATURAL JOIN CONDITION_CODES * NATURAL JOIN ITEM_CODES; CALL "SUB80" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "COMMIT;" * EXEC SQL COMMIT; CALL "SUB81" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "CREATE VIEW INCOMPLETES AS" DISPLAY " SELECT ITEMTEXT, CONDTEXT, COSTTEXT" DISPLAY " FROM INVENTORY, COST_CODES, CONDITION_CODES, - " ITEM_CODES" DISPLAY " WHERE INVENTORY.ITEMCODE = - " ITEM_CODES.ITEMCODE" DISPLAY " AND ((INVENTORY.CONDCODE = - " CONDITION_CODES.CONDCODE" DISPLAY " AND INVENTORY.COSTCODE IS NULL" DISPLAY " AND COST_CODES.COSTCODE IS NULL)" DISPLAY " OR (INVENTORY.COSTCODE = - " COST_CODES.COSTCODE" DISPLAY " AND INVENTORY.CONDCODE IS NULL" DISPLAY " AND CONDITION_CODES.CONDCODE IS - " NULL));" * EXEC SQL CREATE VIEW INCOMPLETES AS * SELECT ITEMTEXT, CONDTEXT, COSTTEXT * FROM INVENTORY, COST_CODES, CONDITION_CODES, ITEM_CODES * WHERE INVENTORY.ITEMCODE = ITEM_CODES.ITEMCODE * AND ((INVENTORY.CONDCODE = CONDITION_CODES.CONDCODE * AND INVENTORY.COSTCODE IS NULL * AND COST_CODES.COSTCODE IS NULL) * OR (INVENTORY.COSTCODE = COST_CODES.COSTCODE * AND INVENTORY.CONDCODE IS NULL * AND CONDITION_CODES.CONDCODE IS NULL)); CALL "SUB82" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "COMMIT;" * EXEC SQL COMMIT; CALL "SUB83" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "CREATE VIEW VERBOSE_INV AS" DISPLAY " SELECT * FROM COMPLETES UNION SELECT * FROM - " INCOMPLETES;" * EXEC SQL CREATE VIEW VERBOSE_INV AS * SELECT * FROM COMPLETES UNION SELECT * FROM INCOMPLETES * ; CALL "SUB84" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "COMMIT;" * EXEC SQL COMMIT; CALL "SUB85" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " *In a "real" program, all this would be read from files. DISPLAY "dstmt=""INSERT INTO COST_CODES VALUES (?, TRIM - " (?))""" MOVE "INSERT INTO COST_CODES VALUES (?, TRIM (?)) " TO dstmt DISPLAY "PREPARE S13421 FROM :dstmt;" * EXEC SQL PREPARE S13421 FROM :dstmt; CALL "SUB86" USING SQLCODE SQLSTATE dstmt MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE indic1 = -1 MOVE "No cost code assigned " TO dstmt DISPLAY "indic1 = -1" DISPLAY "dstmt=""No cost code assigned""" DISPLAY "EXECUTE S13421 USING :codev:indic1, :dstmt;" * EXEC SQL EXECUTE S13421 USING :codev:indic1, :dstmt * ; CALL "SUB87" USING SQLCODE SQLSTATE codev indic1 dstmt MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " MOVE 0 TO codev MOVE "Expensive " TO dstmt DISPLAY "codev = 0" DISPLAY "dstmt=""Expensive""" DISPLAY "EXECUTE S13421 USING :codev, :dstmt;" * EXEC SQL EXECUTE S13421 USING :codev, :dstmt; CALL "SUB88" USING SQLCODE SQLSTATE codev dstmt MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " MOVE 1 TO codev MOVE "Absurdly expensive " TO dstmt DISPLAY "codev = 1" DISPLAY "dstmt=""Absurdly expensive""" DISPLAY "EXECUTE S13421 USING :codev, :dstmt;" * EXEC SQL EXECUTE S13421 USING :codev, :dstmt; CALL "SUB89" USING SQLCODE SQLSTATE codev dstmt MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " MOVE 2 TO codev MOVE "Outrageously expensive " TO dstmt DISPLAY "codev = 2" DISPLAY "dstmt=""Outrageously expensive""" DISPLAY "EXECUTE S13421 USING :codev, :dstmt;" * EXEC SQL EXECUTE S13421 USING :codev, :dstmt; CALL "SUB90" USING SQLCODE SQLSTATE codev dstmt MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " MOVE 3 TO codev MOVE "Robbery; a complete and total rip-off - " " TO dstmt DISPLAY "codev = 3" DISPLAY "dstmt=""Robbery; a complete and total rip-off""" DISPLAY "EXECUTE S13421 USING :codev, :dstmt;" * EXEC SQL EXECUTE S13421 USING :codev, :dstmt; CALL "SUB91" USING SQLCODE SQLSTATE codev dstmt MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "dstmt=""INSERT INTO CONDITION_CODES VALUES (?, - " TRIM (?))""" MOVE "INSERT INTO CONDITION_CODES VALUES (?, TRIM (?)) " TO dstmt DISPLAY "PREPARE S13422 FROM :dstmt;" * EXEC SQL PREPARE S13422 FROM :dstmt; CALL "SUB92" USING SQLCODE SQLSTATE dstmt MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE indic1 = -1 MOVE "Unknown " TO dstmt DISPLAY "indic1 = -1" DISPLAY "dstmt=""Unknown""" DISPLAY "EXECUTE S13422 USING :codev:indic1, :dstmt;" * EXEC SQL EXECUTE S13422 USING :codev:indic1, :dstmt * ; CALL "SUB93" USING SQLCODE SQLSTATE codev indic1 dstmt MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " MOVE 1 TO codev MOVE "Slightly used " TO dstmt DISPLAY "codev = 1" DISPLAY "dstmt=""Slightly used""" DISPLAY "EXECUTE S13422 USING :codev, :dstmt;" * EXEC SQL EXECUTE S13422 USING :codev, :dstmt; CALL "SUB94" USING SQLCODE SQLSTATE codev dstmt MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " MOVE 2 TO codev MOVE "Returned as defective " TO dstmt DISPLAY "codev = 2" DISPLAY "dstmt=""Returned as defective""" DISPLAY "EXECUTE S13422 USING :codev, :dstmt;" * EXEC SQL EXECUTE S13422 USING :codev, :dstmt; CALL "SUB95" USING SQLCODE SQLSTATE codev dstmt MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " MOVE 3 TO codev MOVE "Visibly damaged (no returns) " TO dstmt DISPLAY "codev = 3" DISPLAY "dstmt=""Visibly damaged (no returns)""" DISPLAY "EXECUTE S13422 USING :codev, :dstmt;" * EXEC SQL EXECUTE S13422 USING :codev, :dstmt; CALL "SUB96" USING SQLCODE SQLSTATE codev dstmt MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "dstmt=""INSERT INTO ITEM_CODES VALUES (?, TRIM - " (?))""" MOVE "INSERT INTO ITEM_CODES VALUES (?, TRIM (?)) " TO dstmt DISPLAY "PREPARE S13423 FROM :dstmt;" * EXEC SQL PREPARE S13423 FROM :dstmt; CALL "SUB97" USING SQLCODE SQLSTATE dstmt MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " MOVE 1 TO codev MOVE "Lousy excuse for a tape deck " TO dstmt DISPLAY "codev = 1" DISPLAY "dstmt=""Lousy excuse for a tape deck""" DISPLAY "EXECUTE S13423 USING :codev, :dstmt;" * EXEC SQL EXECUTE S13423 USING :codev, :dstmt; CALL "SUB98" USING SQLCODE SQLSTATE codev dstmt MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " MOVE 3 TO codev MOVE "World's worst VCR " TO dstmt DISPLAY "codev = 3" DISPLAY "dstmt=""World's worst VCR""" DISPLAY "EXECUTE S13423 USING :codev, :dstmt;" * EXEC SQL EXECUTE S13423 USING :codev, :dstmt; CALL "SUB99" USING SQLCODE SQLSTATE codev dstmt MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " MOVE 4 TO codev MOVE "Irreparable intermittent CD player " TO dstmt DISPLAY "codev = 4" DISPLAY "dstmt=""Irreparable intermittent CD player""" DISPLAY "EXECUTE S13423 USING :codev, :dstmt;" * EXEC SQL EXECUTE S13423 USING :codev, :dstmt; CALL "SUB100" USING SQLCODE SQLSTATE codev dstmt MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " MOVE 7 TO codev MOVE "Self-destruct VGA monitor w/ critical need - " detect " TO dstmt DISPLAY "codev = 7" DISPLAY "dstmt=""Self-destruct VGA monitor w/ critical need - " detect""" DISPLAY "EXECUTE S13423 USING :codev, :dstmt;" * EXEC SQL EXECUTE S13423 USING :codev, :dstmt; CALL "SUB101" USING SQLCODE SQLSTATE codev dstmt MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "dstmt=""INSERT INTO INVENTORY VALUES (?, ?, ?)""" MOVE "INSERT INTO INVENTORY VALUES (?, ?, ?) " TO dstmt DISPLAY "PREPARE S13424 FROM :dstmt;" * EXEC SQL PREPARE S13424 FROM :dstmt; CALL "SUB102" USING SQLCODE SQLSTATE dstmt MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " MOVE 3 TO int1 COMPUTE indic1 = -1 MOVE 4 TO int3 DISPLAY "int1 = 3" DISPLAY "indic1 = -1" DISPLAY "int3 = 4" DISPLAY "EXECUTE S13424 USING :int1, :int2:indic1, :int3;" * EXEC SQL EXECUTE S13424 USING :int1, :int2:indic1, :int3 * ; CALL "SUB103" USING SQLCODE SQLSTATE int1 int2 indic1 int3 MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " MOVE 1 TO int1 MOVE 2 TO int2 MOVE 3 TO int3 DISPLAY "int1 = 1" DISPLAY "int2 = 2" DISPLAY "int3 = 3" DISPLAY "EXECUTE S13424 USING :int1, :int2, :int3;" * EXEC SQL EXECUTE S13424 USING :int1, :int2, :int3; CALL "SUB104" USING SQLCODE SQLSTATE int1 int2 int3 MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " MOVE 2 TO int1 MOVE 3 TO int2 MOVE 7 TO int3 DISPLAY "int1 = 2" DISPLAY "int2 = 3" DISPLAY "int3 = 7" DISPLAY "EXECUTE S13424 USING :int1, :int2, :int3;" * EXEC SQL EXECUTE S13424 USING :int1, :int2, :int3; CALL "SUB105" USING SQLCODE SQLSTATE int1 int2 int3 MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " MOVE 0 TO int1 MOVE 3 TO int2 MOVE 1 TO int3 DISPLAY "int1 = 0" DISPLAY "int2 = 3" DISPLAY "int3 = 1" DISPLAY "EXECUTE S13424 USING :int1, :int2, :int3;" * EXEC SQL EXECUTE S13424 USING :int1, :int2, :int3; CALL "SUB106" USING SQLCODE SQLSTATE int1 int2 int3 MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " MOVE 3 TO int1 MOVE 1 TO int2 MOVE 7 TO int3 DISPLAY "int1 = 3" DISPLAY "int2 = 1" DISPLAY "int3 = 7" DISPLAY "EXECUTE S13424 USING :int1, :int2, :int3;" * EXEC SQL EXECUTE S13424 USING :int1, :int2, :int3; CALL "SUB107" USING SQLCODE SQLSTATE int1 int2 int3 MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " *End of data loading COMPUTE int1 = -1 DISPLAY "SELECT COUNT(*) INTO :int1 FROM VERBOSE_INV;" * EXEC SQL SELECT COUNT(*) INTO :int1 FROM VERBOSE_INV * ; CALL "SUB108" USING SQLCODE SQLSTATE int1 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 COMPUTE int1 = -1 DISPLAY "SELECT COUNT(*) INTO :int1 FROM INCOMPLETES;" * EXEC SQL SELECT COUNT(*) INTO :int1 FROM INCOMPLETES * ; CALL "SUB109" USING SQLCODE SQLSTATE int1 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 COMPUTE int1 = -1 DISPLAY "SELECT COUNT(*) INTO :int1 FROM COMPLETES;" * EXEC SQL SELECT COUNT(*) INTO :int1 FROM COMPLETES; CALL "SUB110" USING SQLCODE SQLSTATE int1 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 COMPUTE int1 = -1 DISPLAY "SELECT COUNT(*) INTO :int1 FROM VERBOSE_INV" DISPLAY " WHERE ITEMTEXT = 'Irreparable intermittent CD - " player'" DISPLAY " AND CONDTEXT = 'Unknown'" DISPLAY " AND COSTTEXT = 'Robbery; a complete and total - " rip-off';" * EXEC SQL SELECT COUNT(*) INTO :int1 FROM VERBOSE_INV * WHERE ITEMTEXT = 'Irreparable intermittent CD player' * AND CONDTEXT = 'Unknown' * AND COSTTEXT = 'Robbery; a complete and total rip-off' * ; CALL "SUB111" USING SQLCODE SQLSTATE int1 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 COMPUTE int1 = -1 DISPLAY "SELECT COUNT(*) INTO :int1 FROM VERBOSE_INV" DISPLAY " WHERE ITEMTEXT = 'Lousy excuse for a tape deck'" DISPLAY " AND CONDTEXT = 'Visibly damaged (no returns)'" DISPLAY " AND COSTTEXT = 'Expensive';" * EXEC SQL SELECT COUNT(*) INTO :int1 FROM VERBOSE_INV * WHERE ITEMTEXT = 'Lousy excuse for a tape deck' * AND CONDTEXT = 'Visibly damaged (no returns)' * AND COSTTEXT = 'Expensive'; CALL "SUB112" USING SQLCODE SQLSTATE int1 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 COMPUTE int1 = -1 DISPLAY "SELECT COUNT(*) INTO :int1 FROM VERBOSE_INV" DISPLAY " WHERE ITEMTEXT =" DISPLAY " 'Self-destruct VGA monitor w/ critical need - " detect'" DISPLAY " AND CONDTEXT = 'Slightly used'" DISPLAY " AND COSTTEXT = 'Robbery; a complete and total - " rip-off';" * EXEC SQL SELECT COUNT(*) INTO :int1 FROM VERBOSE_INV * WHERE ITEMTEXT = * 'Self-destruct VGA monitor w/ critical need detect' * AND CONDTEXT = 'Slightly used' * AND COSTTEXT = 'Robbery; a complete and total rip-off' * ; CALL "SUB113" USING SQLCODE SQLSTATE int1 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 COMPUTE int1 = -1 DISPLAY "SELECT COUNT(*) INTO :int1 FROM VERBOSE_INV" DISPLAY " WHERE ITEMTEXT =" DISPLAY " 'Self-destruct VGA monitor w/ critical need - " detect'" DISPLAY " AND CONDTEXT = 'Visibly damaged (no returns)'" DISPLAY " AND COSTTEXT = 'Outrageously expensive';" * EXEC SQL SELECT COUNT(*) INTO :int1 FROM VERBOSE_INV * WHERE ITEMTEXT = * 'Self-destruct VGA monitor w/ critical need detect' * AND CONDTEXT = 'Visibly damaged (no returns)' * AND COSTTEXT = 'Outrageously expensive'; CALL "SUB114" USING SQLCODE SQLSTATE int1 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 COMPUTE int1 = -1 DISPLAY "SELECT COUNT(*) INTO :int1 FROM VERBOSE_INV" DISPLAY " WHERE ITEMTEXT = 'World''s worst VCR'" DISPLAY " AND CONDTEXT = 'Returned as defective'" DISPLAY " AND COSTTEXT = 'Absurdly expensive';" * EXEC SQL SELECT COUNT(*) INTO :int1 FROM VERBOSE_INV * WHERE ITEMTEXT = 'World''s worst VCR' * AND CONDTEXT = 'Returned as defective' * AND COSTTEXT = 'Absurdly expensive'; CALL "SUB115" USING SQLCODE SQLSTATE int1 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 "COMMIT;" * EXEC SQL COMMIT; CALL "SUB116" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DROP TABLE INVENTORY CASCADE;" * EXEC SQL DROP TABLE INVENTORY CASCADE; CALL "SUB117" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "COMMIT;" * EXEC SQL COMMIT; CALL "SUB118" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DROP TABLE COST_CODES CASCADE;" * EXEC SQL DROP TABLE COST_CODES CASCADE; CALL "SUB119" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "COMMIT;" * EXEC SQL COMMIT; CALL "SUB120" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DROP TABLE CONDITION_CODES CASCADE;" * EXEC SQL DROP TABLE CONDITION_CODES CASCADE; CALL "SUB121" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "COMMIT;" * EXEC SQL COMMIT; CALL "SUB122" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DROP TABLE ITEM_CODES CASCADE;" * EXEC SQL DROP TABLE ITEM_CODES CASCADE; CALL "SUB123" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "COMMIT;" * EXEC SQL COMMIT; CALL "SUB124" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " if ( flag = 1 ) then DISPLAY " *** pass *** " * EXEC SQL INSERT INTO HU.TESTREPORT * VALUES('0689','pass','MCO'); CALL "SUB125" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD else DISPLAY " dml134.sco *** fail *** " * EXEC SQL INSERT INTO HU.TESTREPORT * VALUES('0689','fail','MCO'); CALL "SUB126" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD COMPUTE errcnt = errcnt + 1 END-IF DISPLAY "===============================================" * EXEC SQL COMMIT WORK; CALL "SUB127" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD ******************** END TEST0689 ******************** ******************** BEGIN TEST0690 ******************* MOVE 1 TO flag DISPLAY " TEST0690 " DISPLAY " Many Trans SQL features #2: talk show schedule" DISPLAY "References:" DISPLAY " F# 3 -- Basic schema manipulation" DISPLAY " F# 5 -- DATETIME data types" DISPLAY " F# 6 -- VARCHAR data type" DISPLAY " F# 16 -- PRIMARY KEY enhancement" DISPLAY " F# 20 -- CAST functions" DISPLAY " F# 21 -- INSERT expressions" DISPLAY " F# 24 -- Keyword relaxations" DISPLAY " TC #2 5.3 6.10" DISPLAY " X3H2-94-015/DBL SOU-031, SQL-92 Errata:" DISPLAY " Casting Between Datetimes and Character - " Strings" DISPLAY " - - - - - - - - - - - - - - - - - - -" COMPUTE SQLCODE = -1 MOVE "xxxxx" TO SQLSTATE DISPLAY "CREATE TABLE PORGRAM (" DISPLAY " SEGNO INT PRIMARY KEY," DISPLAY " STARTS TIME NOT NULL," DISPLAY " LASTS INTERVAL MINUTE TO SECOND NOT NULL," DISPLAY " SEGMENT VARCHAR (50));" * EXEC SQL CREATE TABLE PORGRAM ( * SEGNO INT PRIMARY KEY, * STARTS TIME NOT NULL, * LASTS INTERVAL MINUTE TO SECOND NOT NULL, * SEGMENT VARCHAR (50)); CALL "SUB128" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "COMMIT;" * EXEC SQL COMMIT; CALL "SUB129" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "CREATE VIEW GAPS AS" DISPLAY " SELECT * FROM PORGRAM AS OUTERR WHERE NOT - " EXISTS" DISPLAY " (SELECT * FROM PORGRAM AS INNERR WHERE - " OUTERR.STARTS" DISPLAY " + OUTERR.LASTS = INNERR.STARTS);" * EXEC SQL CREATE VIEW GAPS AS * SELECT * FROM PORGRAM AS OUTERR WHERE NOT EXISTS * (SELECT * FROM PORGRAM AS INNERR WHERE OUTERR.STARTS * + OUTERR.LASTS = INNERR.STARTS); CALL "SUB130" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "COMMIT;" * EXEC SQL COMMIT; CALL "SUB131" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "INSERT INTO PORGRAM VALUES (" DISPLAY " 1, TIME '12:00:00'," DISPLAY " CAST ('10:00' AS INTERVAL MINUTE TO SECOND)," DISPLAY " 'Monologue');" * EXEC SQL INSERT INTO PORGRAM VALUES ( * 1, TIME '12:00:00', * CAST ('10:00' AS INTERVAL MINUTE TO SECOND), * 'Monologue'); CALL "SUB132" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "INSERT INTO PORGRAM VALUES (" DISPLAY " 2, TIME '12:10:00'," DISPLAY " CAST ('04:30' AS INTERVAL MINUTE TO SECOND)," DISPLAY " 'Stupid commercials');" * EXEC SQL INSERT INTO PORGRAM VALUES ( * 2, TIME '12:10:00', * CAST ('04:30' AS INTERVAL MINUTE TO SECOND), * 'Stupid commercials'); CALL "SUB133" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "INSERT INTO PORGRAM VALUES (" DISPLAY " 3, TIME '12:14:30'," DISPLAY " CAST ('12:30' AS INTERVAL MINUTE TO SECOND)," DISPLAY " 'Braunschweiger, plug Explosion Man II');" * EXEC SQL INSERT INTO PORGRAM VALUES ( * 3, TIME '12:14:30', * CAST ('12:30' AS INTERVAL MINUTE TO SECOND), * 'Braunschweiger, plug Explosion Man II'); CALL "SUB134" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "INSERT INTO PORGRAM VALUES (" DISPLAY " 4, TIME '12:27:00'," DISPLAY " CAST ('03:00' AS INTERVAL MINUTE TO SECOND)," DISPLAY " 'Stupid commercials');" * EXEC SQL INSERT INTO PORGRAM VALUES ( * 4, TIME '12:27:00', * CAST ('03:00' AS INTERVAL MINUTE TO SECOND), * 'Stupid commercials'); CALL "SUB135" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "INSERT INTO PORGRAM VALUES (" DISPLAY " 5, TIME '12:30:00'," DISPLAY " CAST ('00:10' AS INTERVAL MINUTE TO SECOND)," DISPLAY " 'Tease');" * EXEC SQL INSERT INTO PORGRAM VALUES ( * 5, TIME '12:30:00', * CAST ('00:10' AS INTERVAL MINUTE TO SECOND), * 'Tease'); CALL "SUB136" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "INSERT INTO PORGRAM VALUES (" DISPLAY " 6, TIME '12:30:10'," DISPLAY " CAST ('03:50' AS INTERVAL MINUTE TO SECOND)," DISPLAY " 'Stupid commercials, local news');" * EXEC SQL INSERT INTO PORGRAM VALUES ( * 6, TIME '12:30:10', * CAST ('03:50' AS INTERVAL MINUTE TO SECOND), * 'Stupid commercials, local news'); CALL "SUB137" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "INSERT INTO PORGRAM VALUES (" DISPLAY " 7, TIME '12:34:00'," DISPLAY " CAST ('11:00' AS INTERVAL MINUTE TO SECOND)," DISPLAY " 'Spinal Tap, plug Asexual Harassment');" * EXEC SQL INSERT INTO PORGRAM VALUES ( * 7, TIME '12:34:00', * CAST ('11:00' AS INTERVAL MINUTE TO SECOND), * 'Spinal Tap, plug Asexual Harassment'); CALL "SUB138" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "INSERT INTO PORGRAM VALUES (" DISPLAY " 8, TIME '12:45:00'," DISPLAY " CAST ('05:00' AS INTERVAL MINUTE TO SECOND)," DISPLAY " 'Stupid commercials');" * EXEC SQL INSERT INTO PORGRAM VALUES ( * 8, TIME '12:45:00', * CAST ('05:00' AS INTERVAL MINUTE TO SECOND), * 'Stupid commercials'); CALL "SUB139" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "INSERT INTO PORGRAM VALUES (" DISPLAY " 9, TIME '12:50:00'," DISPLAY " CAST ('05:00' AS INTERVAL MINUTE TO SECOND)," DISPLAY " 'Spinal Tap, play Ode du Toilette');" * EXEC SQL INSERT INTO PORGRAM VALUES ( * 9, TIME '12:50:00', * CAST ('05:00' AS INTERVAL MINUTE TO SECOND), * 'Spinal Tap, play Ode du Toilette'); CALL "SUB140" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "INSERT INTO PORGRAM VALUES (" DISPLAY " 10, TIME '12:55:00'," DISPLAY " CAST ('03:00' AS INTERVAL MINUTE TO SECOND)," DISPLAY " 'Stupid commercials');" * EXEC SQL INSERT INTO PORGRAM VALUES ( * 10, TIME '12:55:00', * CAST ('03:00' AS INTERVAL MINUTE TO SECOND), * 'Stupid commercials'); CALL "SUB141" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "INSERT INTO PORGRAM VALUES (" DISPLAY " 11, TIME '12:58:00'," DISPLAY " CAST ('00:10' AS INTERVAL MINUTE TO SECOND)," DISPLAY " 'Credits');" * EXEC SQL INSERT INTO PORGRAM VALUES ( * 11, TIME '12:58:00', * CAST ('00:10' AS INTERVAL MINUTE TO SECOND), * 'Credits'); CALL "SUB142" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "INSERT INTO PORGRAM VALUES (" DISPLAY " 12, TIME '12:58:10'," DISPLAY " CAST ('01:50' AS INTERVAL MINUTE TO SECOND)," DISPLAY " 'Stupid commercials');" * EXEC SQL INSERT INTO PORGRAM VALUES ( * 12, TIME '12:58:10', * CAST ('01:50' AS INTERVAL MINUTE TO SECOND), * 'Stupid commercials'); CALL "SUB143" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "INSERT INTO PORGRAM VALUES (" DISPLAY " 13, TIME '13:00:00'," DISPLAY " CAST ('00:00' AS INTERVAL MINUTE TO SECOND)," DISPLAY " 'END');" * EXEC SQL INSERT INTO PORGRAM VALUES ( * 13, TIME '13:00:00', * CAST ('00:00' AS INTERVAL MINUTE TO SECOND), * 'END'); CALL "SUB144" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " *In the beginning, all was good. COMPUTE int1 = -1 DISPLAY "SELECT COUNT(*) INTO :int1 FROM GAPS;" * EXEC SQL SELECT COUNT(*) INTO :int1 FROM GAPS; CALL "SUB145" USING SQLCODE SQLSTATE int1 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 *Braunschweiger was late. Move up Spinal Tap. DISPLAY "UPDATE PORGRAM" DISPLAY " SET STARTS = TIME '12:14:30'" DISPLAY " WHERE SEGNO = 7;" * EXEC SQL UPDATE PORGRAM * SET STARTS = TIME '12:14:30' * WHERE SEGNO = 7; CALL "SUB146" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "UPDATE PORGRAM SET STARTS = STARTS -" DISPLAY " CAST ('01:30' AS INTERVAL MINUTE TO SECOND)" DISPLAY " WHERE SEGNO >= 4 AND SEGNO <= 6;" * EXEC SQL UPDATE PORGRAM SET STARTS = STARTS - * CAST ('01:30' AS INTERVAL MINUTE TO SECOND) * WHERE SEGNO >= 4 AND SEGNO <= 6; CALL "SUB147" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "UPDATE PORGRAM SET STARTS = TIME '12:28:40' +" DISPLAY " CAST ('03:50' AS INTERVAL MINUTE TO SECOND)" DISPLAY " WHERE SEGNO = 3;" * EXEC SQL UPDATE PORGRAM SET STARTS = TIME '12:28:40' + * CAST ('03:50' AS INTERVAL MINUTE TO SECOND) * WHERE SEGNO = 3; CALL "SUB148" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 DISPLAY "SELECT COUNT(*) INTO :int1 FROM GAPS;" * EXEC SQL SELECT COUNT(*) INTO :int1 FROM GAPS; CALL "SUB149" USING SQLCODE SQLSTATE int1 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 "DECLARE ARNOLD CURSOR FOR" DISPLAY " SELECT SEGNO FROM PORGRAM ORDER BY STARTS;" * EXEC SQL DECLARE ARNOLD CURSOR FOR * SELECT SEGNO FROM PORGRAM ORDER BY STARTS END-EXEC DISPLAY "OPEN ARNOLD;" * EXEC SQL OPEN ARNOLD; CALL "SUB150" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 DISPLAY "FETCH ARNOLD INTO :int1;" * EXEC SQL FETCH ARNOLD INTO :int1; CALL "SUB151" USING SQLCODE SQLSTATE int1 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 COMPUTE int1 = -1 DISPLAY "FETCH ARNOLD INTO :int1;" * EXEC SQL FETCH ARNOLD INTO :int1; CALL "SUB152" USING SQLCODE SQLSTATE int1 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 COMPUTE int1 = -1 DISPLAY "FETCH ARNOLD INTO :int1;" * EXEC SQL FETCH ARNOLD INTO :int1; CALL "SUB153" USING SQLCODE SQLSTATE int1 MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "int1 should be 7; its value is ", int1 if (int1 NOT = 7) then MOVE 0 TO flag END-IF COMPUTE int1 = -1 DISPLAY "FETCH ARNOLD INTO :int1;" * EXEC SQL FETCH ARNOLD INTO :int1; CALL "SUB154" USING SQLCODE SQLSTATE int1 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 COMPUTE int1 = -1 DISPLAY "FETCH ARNOLD INTO :int1;" * EXEC SQL FETCH ARNOLD INTO :int1; CALL "SUB155" USING SQLCODE SQLSTATE int1 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 COMPUTE int1 = -1 DISPLAY "FETCH ARNOLD INTO :int1;" * EXEC SQL FETCH ARNOLD INTO :int1; CALL "SUB156" USING SQLCODE SQLSTATE int1 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 COMPUTE int1 = -1 DISPLAY "FETCH ARNOLD INTO :int1;" * EXEC SQL FETCH ARNOLD INTO :int1; CALL "SUB157" USING SQLCODE SQLSTATE int1 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 COMPUTE int1 = -1 DISPLAY "FETCH ARNOLD INTO :int1;" * EXEC SQL FETCH ARNOLD INTO :int1; CALL "SUB158" USING SQLCODE SQLSTATE int1 MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "int1 should be 8; its value is ", int1 if (int1 NOT = 8) then MOVE 0 TO flag END-IF COMPUTE int1 = -1 DISPLAY "FETCH ARNOLD INTO :int1;" * EXEC SQL FETCH ARNOLD INTO :int1; CALL "SUB159" USING SQLCODE SQLSTATE int1 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 COMPUTE int1 = -1 DISPLAY "FETCH ARNOLD INTO :int1;" * EXEC SQL FETCH ARNOLD INTO :int1; CALL "SUB160" USING SQLCODE SQLSTATE int1 MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "int1 should be 10; its value is ", int1 if (int1 NOT = 10) then MOVE 0 TO flag END-IF COMPUTE int1 = -1 DISPLAY "FETCH ARNOLD INTO :int1;" * EXEC SQL FETCH ARNOLD INTO :int1; CALL "SUB161" USING SQLCODE SQLSTATE int1 MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "int1 should be 11; its value is ", int1 if (int1 NOT = 11) then MOVE 0 TO flag END-IF COMPUTE int1 = -1 DISPLAY "FETCH ARNOLD INTO :int1;" * EXEC SQL FETCH ARNOLD INTO :int1; CALL "SUB162" USING SQLCODE SQLSTATE int1 MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "int1 should be 12; its value is ", int1 if (int1 NOT = 12) then MOVE 0 TO flag END-IF COMPUTE int1 = -1 DISPLAY "FETCH ARNOLD INTO :int1;" * EXEC SQL FETCH ARNOLD INTO :int1; CALL "SUB163" USING SQLCODE SQLSTATE int1 MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "int1 should be 13; its value is ", int1 if (int1 NOT = 13) then MOVE 0 TO flag END-IF DISPLAY "CLOSE ARNOLD;" * EXEC SQL CLOSE ARNOLD; CALL "SUB164" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " --> -------------------- --> maximum size reached --> -------------------- [ zur Elbe Produktseite wechseln0.82Quellennavigators Analyse erneut starten ] |