| Quellcodebibliothek Statistik Leitseite products/Sources/formale Sprachen/Cobol/Test-Suite/SQL M/ Datei vom 4.1.2008 mit Größe 52 kB |
|
Quelle dml131.cob Sprache: Cobol |
|||||||||
|
IDENTIFICATION DIVISION.
PROGRAM-ID. DML131. ENVIRONMENT DIVISION. CONFIGURATION SECTION. SOURCE-COMPUTER. xyz. OBJECT-COMPUTER. xyz. DATA DIVISION. WORKING-STORAGE SECTION. * Standard COBOL (file "DML131.SCO") calling SQL * procedures in file "DML131.MCO". **************************************************************** * * COMMENT SECTION * * DATE 1994/8/16 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. * * DML131.SCO * WRITTEN BY: David W. Flater * TRANSLATED AUTOMATICALLY FROM EMBEDDED COBOL BY CHRIS SCHANZLE * * This routine tests the Information Schema. * * 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. * 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, dml131.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 TEST0683 ******************* MOVE 1 TO flag DISPLAY " TEST0683 " DISPLAY " INFO_SCHEM: Changes are visible" DISPLAY "References:" 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 COMPUTE int1 = -1 DISPLAY "SELECT COUNT(*) INTO :int1 FROM INFO_SCHEM.TABLES" DISPLAY " WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME = - " 'VISCHANGE';" * EXEC SQL SELECT COUNT(*) INTO :int1 FROM INFO_SCHEM.TABLES * WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME = 'VISCHANGE' * ; CALL "SUB3" 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 COMPUTE int1 = -1 DISPLAY "SELECT COUNT(*) INTO :int1 FROM - " INFO_SCHEM.COLUMNS" DISPLAY " WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME = - " 'VISCHANGE';" * EXEC SQL SELECT COUNT(*) INTO :int1 FROM INFO_SCHEM.COLUMNS * WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME = 'VISCHANGE' * ; CALL "SUB4" 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 "ROLLBACK WORK;" * EXEC SQL ROLLBACK WORK; CALL "SUB5" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "CREATE TABLE VISCHANGE (C1 INT, C2 FLOAT);" * EXEC SQL CREATE TABLE VISCHANGE (C1 INT, C2 FLOAT); CALL "SUB6" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "COMMIT WORK;" * EXEC SQL COMMIT WORK; CALL "SUB7" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " *Table should exist COMPUTE int1 = -1 DISPLAY "SELECT COUNT(*) INTO :int1 FROM INFO_SCHEM.TABLES" DISPLAY " WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME = - " 'VISCHANGE'" DISPLAY " AND TABLE_TYPE = 'BASE TABLE';" * EXEC SQL SELECT COUNT(*) INTO :int1 FROM INFO_SCHEM.TABLES * WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME = 'VISCHANGE' * AND TABLE_TYPE = 'BASE TABLE'; CALL "SUB8" 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 MOVE 0 TO int1 DISPLAY "SELECT COUNT(*) INTO :int1 FROM - " INFO_SCHEM.COLUMNS" DISPLAY " WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME = - " 'VISCHANGE';" * EXEC SQL SELECT COUNT(*) INTO :int1 FROM INFO_SCHEM.COLUMNS * WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME = 'VISCHANGE' * ; CALL "SUB9" 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 "ROLLBACK WORK;" * EXEC SQL ROLLBACK WORK; CALL "SUB10" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "ALTER TABLE VISCHANGE DROP C1 RESTRICT;" * EXEC SQL ALTER TABLE VISCHANGE DROP C1 RESTRICT; CALL "SUB11" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "COMMIT WORK;" * EXEC SQL COMMIT WORK; CALL "SUB12" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " *Deleted column should not exist MOVE 0 TO int1 DISPLAY "SELECT COUNT(*) INTO :int1 FROM - " INFO_SCHEM.COLUMNS" DISPLAY " WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME = - " 'VISCHANGE';" * EXEC SQL SELECT COUNT(*) INTO :int1 FROM INFO_SCHEM.COLUMNS * WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME = 'VISCHANGE' * ; CALL "SUB13" 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 MOVE 0 TO int1 DISPLAY "SELECT COUNT(*) INTO :int1 FROM - " INFO_SCHEM.COLUMNS" DISPLAY " WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME = - " 'VISCHANGE'" DISPLAY " AND COLUMN_NAME = 'C2';" * EXEC SQL SELECT COUNT(*) INTO :int1 FROM INFO_SCHEM.COLUMNS * WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME = 'VISCHANGE' * AND COLUMN_NAME = 'C2'; CALL "SUB14" 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 "ROLLBACK WORK;" * EXEC SQL ROLLBACK WORK; CALL "SUB15" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DROP TABLE VISCHANGE CASCADE;" * EXEC SQL DROP TABLE VISCHANGE CASCADE; CALL "SUB16" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "COMMIT WORK;" * EXEC SQL COMMIT WORK; CALL "SUB17" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " *Table should not exist COMPUTE int1 = -1 DISPLAY "SELECT COUNT(*) INTO :int1 FROM INFO_SCHEM.TABLES" DISPLAY " WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME = - " 'VISCHANGE';" * EXEC SQL SELECT COUNT(*) INTO :int1 FROM INFO_SCHEM.TABLES * WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME = 'VISCHANGE' * ; CALL "SUB18" 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 COMPUTE int1 = -1 DISPLAY "SELECT COUNT(*) INTO :int1 FROM - " INFO_SCHEM.COLUMNS" DISPLAY " WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME = - " 'VISCHANGE';" * EXEC SQL SELECT COUNT(*) INTO :int1 FROM INFO_SCHEM.COLUMNS * WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME = 'VISCHANGE' * ; CALL "SUB19" 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 "ROLLBACK WORK;" * EXEC SQL ROLLBACK WORK; CALL "SUB20" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " if ( flag = 1 ) then DISPLAY " *** pass *** " * EXEC SQL INSERT INTO HU.TESTREPORT * VALUES('0683','pass','MCO'); CALL "SUB21" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD else DISPLAY " dml131.sco *** fail *** " * EXEC SQL INSERT INTO HU.TESTREPORT * VALUES('0683','fail','MCO'); CALL "SUB22" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD COMPUTE errcnt = errcnt + 1 END-IF DISPLAY "===============================================" * EXEC SQL COMMIT WORK; CALL "SUB23" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD ******************** END TEST0683 ******************** ******************** BEGIN TEST0684 ******************* MOVE 1 TO flag DISPLAY " TEST0684 " DISPLAY " INFO_SCHEM: Visibility to other users" DISPLAY "References:" DISPLAY " F# 2 -- Basic information schema" DISPLAY " FIPS Change Notice #1 -- Shortened - " identifiers" DISPLAY " in INFO_SCHEM" DISPLAY " - - - - - - - - - - - - - - - - - - -" COMPUTE SQLCODE = -1 MOVE "xxxxx" TO SQLSTATE *This test checks for the correct levels of visibility in the *Information Schema when the user has different levels of *access to the target table. *FLATER has SELECT on HU.WORKS COMPUTE int1 = -1 DISPLAY "SELECT COUNT(*) INTO :int1 FROM INFO_SCHEM.TABLES" DISPLAY " WHERE TABLE_SCHEM = 'HU' AND TABLE_NAME = - " 'WORKS'" DISPLAY " AND TABLE_TYPE = 'BASE TABLE';" * EXEC SQL SELECT COUNT(*) INTO :int1 FROM INFO_SCHEM.TABLES * WHERE TABLE_SCHEM = 'HU' AND TABLE_NAME = 'WORKS' * AND TABLE_TYPE = 'BASE TABLE'; CALL "SUB24" 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 MOVE 0 TO int1 DISPLAY "SELECT COUNT(*) INTO :int1 FROM - " INFO_SCHEM.COLUMNS" DISPLAY " WHERE TABLE_SCHEM = 'HU' AND TABLE_NAME = - " 'WORKS';" * EXEC SQL SELECT COUNT(*) INTO :int1 FROM INFO_SCHEM.COLUMNS * WHERE TABLE_SCHEM = 'HU' AND TABLE_NAME = 'WORKS' * ; CALL "SUB25" 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 *PUBLIC has INSERT on HU.TESTREPORT COMPUTE int1 = -1 DISPLAY "SELECT COUNT(*) INTO :int1 FROM INFO_SCHEM.TABLES" DISPLAY " WHERE TABLE_SCHEM = 'HU' AND TABLE_NAME = - " 'TESTREPORT'" DISPLAY " AND TABLE_TYPE = 'VIEW';" * EXEC SQL SELECT COUNT(*) INTO :int1 FROM INFO_SCHEM.TABLES * WHERE TABLE_SCHEM = 'HU' AND TABLE_NAME = 'TESTREPORT' * AND TABLE_TYPE = 'VIEW'; CALL "SUB26" 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 INFO_SCHEM.VIEWS" DISPLAY " WHERE TABLE_SCHEM = 'HU' AND TABLE_NAME = - " 'TESTREPORT';" * EXEC SQL SELECT COUNT(*) INTO :int1 FROM INFO_SCHEM.VIEWS * WHERE TABLE_SCHEM = 'HU' AND TABLE_NAME = 'TESTREPORT' * ; CALL "SUB27" 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 MOVE 0 TO int1 DISPLAY "SELECT COUNT(*) INTO :int1 FROM - " INFO_SCHEM.COLUMNS" DISPLAY " WHERE TABLE_SCHEM = 'HU' AND TABLE_NAME = - " 'TESTREPORT';" * EXEC SQL SELECT COUNT(*) INTO :int1 FROM INFO_SCHEM.COLUMNS * WHERE TABLE_SCHEM = 'HU' AND TABLE_NAME = 'TESTREPORT' * ; CALL "SUB28" 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 *FLATER has UPDATE (COL1) on HU.VTABLE COMPUTE int1 = -1 DISPLAY "SELECT COUNT(*) INTO :int1 FROM INFO_SCHEM.TABLES" DISPLAY " WHERE TABLE_SCHEM = 'HU' AND TABLE_NAME = - " 'VTABLE'" DISPLAY " AND TABLE_TYPE = 'BASE TABLE';" * EXEC SQL SELECT COUNT(*) INTO :int1 FROM INFO_SCHEM.TABLES * WHERE TABLE_SCHEM = 'HU' AND TABLE_NAME = 'VTABLE' * AND TABLE_TYPE = 'BASE TABLE'; CALL "SUB29" 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 MOVE 0 TO int1 DISPLAY "SELECT COUNT(*) INTO :int1 FROM - " INFO_SCHEM.COLUMNS" DISPLAY " WHERE TABLE_SCHEM = 'HU' AND TABLE_NAME = - " 'VTABLE';" * EXEC SQL SELECT COUNT(*) INTO :int1 FROM INFO_SCHEM.COLUMNS * WHERE TABLE_SCHEM = 'HU' AND TABLE_NAME = 'VTABLE' * ; CALL "SUB30" 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 *FLATER has ZILCH on HU.FF COMPUTE int1 = -1 DISPLAY "SELECT COUNT(*) INTO :int1 FROM INFO_SCHEM.TABLES" DISPLAY " WHERE TABLE_SCHEM = 'HU' AND TABLE_NAME = 'FF';" * EXEC SQL SELECT COUNT(*) INTO :int1 FROM INFO_SCHEM.TABLES * WHERE TABLE_SCHEM = 'HU' AND TABLE_NAME = 'FF'; CALL "SUB31" 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 COMPUTE int1 = -1 DISPLAY "SELECT COUNT(*) INTO :int1 FROM - " INFO_SCHEM.COLUMNS" DISPLAY " WHERE TABLE_SCHEM = 'HU' AND TABLE_NAME = 'FF';" * EXEC SQL SELECT COUNT(*) INTO :int1 FROM INFO_SCHEM.COLUMNS * WHERE TABLE_SCHEM = 'HU' AND TABLE_NAME = 'FF'; CALL "SUB32" 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 "ROLLBACK WORK;" * EXEC SQL ROLLBACK WORK; CALL "SUB33" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " if ( flag = 1 ) then DISPLAY " *** pass *** " * EXEC SQL INSERT INTO HU.TESTREPORT * VALUES('0684','pass','MCO'); CALL "SUB34" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD else DISPLAY " dml131.sco *** fail *** " * EXEC SQL INSERT INTO HU.TESTREPORT * VALUES('0684','fail','MCO'); CALL "SUB35" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD COMPUTE errcnt = errcnt + 1 END-IF DISPLAY "===============================================" * EXEC SQL COMMIT WORK; CALL "SUB36" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD ******************** END TEST0684 ******************** ******************** BEGIN TEST0685 ******************* MOVE 1 TO flag DISPLAY " TEST0685 " DISPLAY " INFO_SCHEM: Privileges and privilege views" DISPLAY "References:" DISPLAY " F# 2 -- Basic information schema" DISPLAY " F# 3 -- Basic schema manipulation" DISPLAY " F# 23 -- Privilege tables" DISPLAY " FIPS Change Notice #1 -- Shortened - " identifiers" DISPLAY " in INFO_SCHEM" DISPLAY " - - - - - - - - - - - - - - - - - - -" COMPUTE SQLCODE = -1 MOVE "xxxxx" TO SQLSTATE *11.3 GR.4: owner privs are granted by _SYSTEM COMPUTE int1 = -1 DISPLAY "SELECT COUNT(*) INTO :int1" DISPLAY " FROM INFO_SCHEM.TABLE_PRIVILEGES" DISPLAY " WHERE TABLE_SCHEM = 'FLATER' AND GRANTOR <> - " '_SYSTEM'" DISPLAY " AND GRANTEE = 'FLATER';" * EXEC SQL SELECT COUNT(*) INTO :int1 * FROM INFO_SCHEM.TABLE_PRIVILEGES * WHERE TABLE_SCHEM = 'FLATER' AND GRANTOR <> '_SYSTEM' * AND GRANTEE = 'FLATER'; CALL "SUB37" 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 COMPUTE int1 = -1 DISPLAY "SELECT COUNT(*) INTO :int1" DISPLAY " FROM INFO_SCHEM.COLUMN_PRIVILEGES" DISPLAY " WHERE TABLE_SCHEM = 'FLATER' AND GRANTOR <> - " '_SYSTEM'" DISPLAY " AND GRANTEE = 'FLATER';" * EXEC SQL SELECT COUNT(*) INTO :int1 * FROM INFO_SCHEM.COLUMN_PRIVILEGES * WHERE TABLE_SCHEM = 'FLATER' AND GRANTOR <> '_SYSTEM' * AND GRANTEE = 'FLATER'; CALL "SUB38" 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 *This one was a GRANT ALL TO SCHANZLE. Five rows: *SELECT, INSERT, UPDATE, DELETE, REFERENCES MOVE 0 TO int1 DISPLAY "SELECT COUNT(*) INTO :int1" DISPLAY " FROM INFO_SCHEM.TABLE_PRIVILEGES" DISPLAY " WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME = - " 'VS1'" DISPLAY " AND GRANTOR = 'FLATER' AND GRANTEE = - "'SCHANZLE'" DISPLAY " AND IS_GRANTABLE = 'NO';" * EXEC SQL SELECT COUNT(*) INTO :int1 * FROM INFO_SCHEM.TABLE_PRIVILEGES * WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME = 'VS1' * AND GRANTOR = 'FLATER' AND GRANTEE = 'SCHANZLE' * AND IS_GRANTABLE = 'NO'; CALL "SUB39" 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 *5 times degree of view = 10 MOVE 0 TO int1 DISPLAY "SELECT COUNT(*) INTO :int1" DISPLAY " FROM INFO_SCHEM.COLUMN_PRIVILEGES" DISPLAY " WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME = - " 'VS1'" DISPLAY " AND GRANTOR = 'FLATER' AND GRANTEE = - " 'SCHANZLE'" DISPLAY " AND IS_GRANTABLE = 'NO';" * EXEC SQL SELECT COUNT(*) INTO :int1 * FROM INFO_SCHEM.COLUMN_PRIVILEGES * WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME = 'VS1' * AND GRANTOR = 'FLATER' AND GRANTEE = 'SCHANZLE' * AND IS_GRANTABLE = 'NO'; CALL "SUB40" 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 *This one was GRANT SELECT TO PUBLIC MOVE 0 TO int1 DISPLAY "SELECT COUNT(*) INTO :int1" DISPLAY " FROM INFO_SCHEM.TABLE_PRIVILEGES" DISPLAY " WHERE TABLE_SCHEM = 'HU' AND TABLE_NAME = - " 'PROJ'" DISPLAY " AND GRANTOR = 'HU' AND GRANTEE = 'PUBLIC'" DISPLAY " AND IS_GRANTABLE = 'NO';" * EXEC SQL SELECT COUNT(*) INTO :int1 * FROM INFO_SCHEM.TABLE_PRIVILEGES * WHERE TABLE_SCHEM = 'HU' AND TABLE_NAME = 'PROJ' * AND GRANTOR = 'HU' AND GRANTEE = 'PUBLIC' * AND IS_GRANTABLE = 'NO'; CALL "SUB41" 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 MOVE 0 TO int1 DISPLAY "SELECT COUNT(*) INTO :int1" DISPLAY " FROM INFO_SCHEM.COLUMN_PRIVILEGES" DISPLAY " WHERE TABLE_SCHEM = 'HU' AND TABLE_NAME = - " 'PROJ'" DISPLAY " AND GRANTOR = 'HU' AND GRANTEE = 'PUBLIC'" DISPLAY " AND IS_GRANTABLE = 'NO';" * EXEC SQL SELECT COUNT(*) INTO :int1 * FROM INFO_SCHEM.COLUMN_PRIVILEGES * WHERE TABLE_SCHEM = 'HU' AND TABLE_NAME = 'PROJ' * AND GRANTOR = 'HU' AND GRANTEE = 'PUBLIC' * AND IS_GRANTABLE = 'NO'; CALL "SUB42" 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 *GRANT INSERT TO PUBLIC WITH GRANT OPTION MOVE 0 TO int1 DISPLAY "SELECT COUNT(*) INTO :int1" DISPLAY " FROM INFO_SCHEM.TABLE_PRIVILEGES" DISPLAY " WHERE TABLE_SCHEM = 'HU' AND TABLE_NAME = - " 'TESTREPORT'" DISPLAY " AND GRANTOR = 'HU' AND GRANTEE = 'PUBLIC'" DISPLAY " AND IS_GRANTABLE = 'YES' AND PRIVILEGE_TYPE = - " 'INSERT';" * EXEC SQL SELECT COUNT(*) INTO :int1 * FROM INFO_SCHEM.TABLE_PRIVILEGES * WHERE TABLE_SCHEM = 'HU' AND TABLE_NAME = 'TESTREPORT' * AND GRANTOR = 'HU' AND GRANTEE = 'PUBLIC' * AND IS_GRANTABLE = 'YES' AND PRIVILEGE_TYPE = 'INSERT' * ; CALL "SUB43" 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 = -153 DISPLAY "SELECT COUNT(*) INTO :int1" DISPLAY " FROM INFO_SCHEM.COLUMN_PRIVILEGES" DISPLAY " WHERE TABLE_SCHEM = 'HU' AND TABLE_NAME = - " 'TESTREPORT'" DISPLAY " AND GRANTOR = 'HU' AND GRANTEE = 'PUBLIC'" DISPLAY " AND IS_GRANTABLE = 'YES' AND PRIVILEGE_TYPE = - " 'INSERT';" * EXEC SQL SELECT COUNT(*) INTO :int1 * FROM INFO_SCHEM.COLUMN_PRIVILEGES * WHERE TABLE_SCHEM = 'HU' AND TABLE_NAME = 'TESTREPORT' * AND GRANTOR = 'HU' AND GRANTEE = 'PUBLIC' * AND IS_GRANTABLE = 'YES' AND PRIVILEGE_TYPE = 'INSERT' * ; CALL "SUB44" 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 MOVE 62 TO int1 DISPLAY "SELECT COUNT(*) INTO :int1" DISPLAY " FROM INFO_SCHEM.COLUMN_PRIVILEGES" DISPLAY " WHERE TABLE_SCHEM = 'HU' AND TABLE_NAME = - " 'TESTREPORT'" DISPLAY " AND GRANTOR = 'HU' AND (GRANTEE <> 'PUBLIC'" DISPLAY " OR IS_GRANTABLE <> 'YES' OR PRIVILEGE_TYPE <> - " 'INSERT');" * EXEC SQL SELECT COUNT(*) INTO :int1 * FROM INFO_SCHEM.COLUMN_PRIVILEGES * WHERE TABLE_SCHEM = 'HU' AND TABLE_NAME = 'TESTREPORT' * AND GRANTOR = 'HU' AND (GRANTEE <> 'PUBLIC' * OR IS_GRANTABLE <> 'YES' OR PRIVILEGE_TYPE <> 'INSERT') * ; CALL "SUB45" 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 *GRANT UPDATE (COL1) ON VTABLE TO FLATER *The syntax for column-specific privileges is hidden in *10.3 <privileges> instead of 11.36 <grant statement> COMPUTE int1 = -1 DISPLAY "SELECT COUNT(*) INTO :int1" DISPLAY " FROM INFO_SCHEM.TABLE_PRIVILEGES" DISPLAY " WHERE TABLE_SCHEM = 'HU' AND TABLE_NAME = - " 'VTABLE'" DISPLAY " AND GRANTOR = 'HU';" * EXEC SQL SELECT COUNT(*) INTO :int1 * FROM INFO_SCHEM.TABLE_PRIVILEGES * WHERE TABLE_SCHEM = 'HU' AND TABLE_NAME = 'VTABLE' * AND GRANTOR = 'HU'; CALL "SUB46" 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 COMPUTE int1 = -1 DISPLAY "SELECT COUNT(*) INTO :int1" DISPLAY " FROM INFO_SCHEM.COLUMN_PRIVILEGES" DISPLAY " WHERE TABLE_SCHEM = 'HU' AND TABLE_NAME = - " 'VTABLE'" DISPLAY " AND GRANTOR = 'HU';" * EXEC SQL SELECT COUNT(*) INTO :int1 * FROM INFO_SCHEM.COLUMN_PRIVILEGES * WHERE TABLE_SCHEM = 'HU' AND TABLE_NAME = 'VTABLE' * AND GRANTOR = 'HU'; CALL "SUB47" 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" DISPLAY " FROM INFO_SCHEM.COLUMN_PRIVILEGES" DISPLAY " WHERE TABLE_SCHEM = 'HU' AND TABLE_NAME = - " 'VTABLE'" DISPLAY " AND GRANTOR = 'HU' AND COLUMN_NAME = 'COL1'" DISPLAY " AND PRIVILEGE_TYPE = 'UPDATE' AND" DISPLAY " IS_GRANTABLE = 'NO' AND GRANTEE = 'FLATER';" * EXEC SQL SELECT COUNT(*) INTO :int1 * FROM INFO_SCHEM.COLUMN_PRIVILEGES * WHERE TABLE_SCHEM = 'HU' AND TABLE_NAME = 'VTABLE' * AND GRANTOR = 'HU' AND COLUMN_NAME = 'COL1' * AND PRIVILEGE_TYPE = 'UPDATE' AND * IS_GRANTABLE = 'NO' AND GRANTEE = 'FLATER'; CALL "SUB48" 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 *This should not be visible. COMPUTE int1 = -1 DISPLAY "SELECT COUNT(*) INTO :int1" DISPLAY " FROM INFO_SCHEM.TABLE_PRIVILEGES" DISPLAY " WHERE TABLE_SCHEM = 'HU' AND TABLE_NAME = 'FF';" * EXEC SQL SELECT COUNT(*) INTO :int1 * FROM INFO_SCHEM.TABLE_PRIVILEGES * WHERE TABLE_SCHEM = 'HU' AND TABLE_NAME = 'FF'; CALL "SUB49" 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 COMPUTE int1 = -1 DISPLAY "SELECT COUNT(*) INTO :int1" DISPLAY " FROM INFO_SCHEM.COLUMN_PRIVILEGES" DISPLAY " WHERE TABLE_SCHEM = 'HU' AND TABLE_NAME = 'FF';" * EXEC SQL SELECT COUNT(*) INTO :int1 * FROM INFO_SCHEM.COLUMN_PRIVILEGES * WHERE TABLE_SCHEM = 'HU' AND TABLE_NAME = 'FF'; CALL "SUB50" 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 *Check future grant not yet reflected COMPUTE int1 = -1 DISPLAY "SELECT COUNT(*) INTO :int1" DISPLAY " FROM INFO_SCHEM.TABLE_PRIVILEGES" DISPLAY " WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME = - " 'BASE_WCOV'" DISPLAY " AND GRANTOR = 'FLATER' AND GRANTEE = 'CUGINI';" * EXEC SQL SELECT COUNT(*) INTO :int1 * FROM INFO_SCHEM.TABLE_PRIVILEGES * WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME = * 'BASE_WCOV' AND GRANTOR = 'FLATER' AND GRANTEE = 'CUGINI' * ; CALL "SUB51" 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 COMPUTE int1 = -1 DISPLAY "SELECT COUNT(*) INTO :int1" DISPLAY " FROM INFO_SCHEM.COLUMN_PRIVILEGES" DISPLAY " WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME = - " 'BASE_WCOV'" DISPLAY " AND GRANTOR = 'FLATER' AND GRANTEE = 'CUGINI';" * EXEC SQL SELECT COUNT(*) INTO :int1 * FROM INFO_SCHEM.COLUMN_PRIVILEGES * WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME = * 'BASE_WCOV' AND GRANTOR = 'FLATER' AND GRANTEE = 'CUGINI' * ; CALL "SUB52" 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 "ROLLBACK WORK;" * EXEC SQL ROLLBACK WORK; CALL "SUB53" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " *Check visibility of new grant DISPLAY "GRANT DELETE ON BASE_WCOV TO CUGINI;" * EXEC SQL GRANT DELETE ON BASE_WCOV TO CUGINI; CALL "SUB54" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "COMMIT WORK;" * EXEC SQL COMMIT WORK; CALL "SUB55" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " COMPUTE int1 = -1 DISPLAY "SELECT COUNT(*) INTO :int1" DISPLAY " FROM INFO_SCHEM.TABLE_PRIVILEGES" DISPLAY " WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME = - " 'BASE_WCOV'" DISPLAY " AND GRANTOR = 'FLATER' AND GRANTEE = 'CUGINI'" DISPLAY " AND PRIVILEGE_TYPE = 'DELETE' AND IS_GRANTABLE = - " 'NO';" * EXEC SQL SELECT COUNT(*) INTO :int1 * FROM INFO_SCHEM.TABLE_PRIVILEGES * WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME = 'BASE_WCOV' * AND GRANTOR = 'FLATER' AND GRANTEE = 'CUGINI' * AND PRIVILEGE_TYPE = 'DELETE' AND IS_GRANTABLE = 'NO' * ; CALL "SUB56" 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" DISPLAY " FROM INFO_SCHEM.TABLE_PRIVILEGES" DISPLAY " WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME = - " 'BASE_WCOV'" DISPLAY " AND GRANTOR = 'FLATER' AND GRANTEE = 'CUGINI';" * EXEC SQL SELECT COUNT(*) INTO :int1 * FROM INFO_SCHEM.TABLE_PRIVILEGES * WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME = * 'BASE_WCOV' AND GRANTOR = 'FLATER' AND GRANTEE = 'CUGINI' * ; CALL "SUB57" 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" DISPLAY " FROM INFO_SCHEM.COLUMN_PRIVILEGES" DISPLAY " WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME = - " 'BASE_WCOV'" DISPLAY " AND GRANTOR = 'FLATER' AND GRANTEE = 'CUGINI'" DISPLAY " AND PRIVILEGE_TYPE = 'DELETE' AND IS_GRANTABLE = - " 'NO';" * EXEC SQL SELECT COUNT(*) INTO :int1 * FROM INFO_SCHEM.COLUMN_PRIVILEGES * WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME = * 'BASE_WCOV' AND GRANTOR = 'FLATER' AND GRANTEE = * 'CUGINI' AND PRIVILEGE_TYPE = 'DELETE' AND * IS_GRANTABLE = 'NO'; CALL "SUB58" 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" DISPLAY " FROM INFO_SCHEM.COLUMN_PRIVILEGES" DISPLAY " WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME = - " 'BASE_WCOV'" DISPLAY " AND GRANTOR = 'FLATER' AND GRANTEE = 'CUGINI';" * EXEC SQL SELECT COUNT(*) INTO :int1 * FROM INFO_SCHEM.COLUMN_PRIVILEGES * WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME = * 'BASE_WCOV' AND GRANTOR = 'FLATER' AND GRANTEE = * 'CUGINI'; CALL "SUB59" 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 "ROLLBACK WORK;" * EXEC SQL ROLLBACK WORK; CALL "SUB60" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "REVOKE DELETE ON BASE_WCOV FROM CUGINI CASCADE;" * EXEC SQL REVOKE DELETE ON BASE_WCOV FROM CUGINI CASCADE * ; CALL "SUB61" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "COMMIT WORK;" * EXEC SQL COMMIT WORK; CALL "SUB62" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " *Check that revoke also worked COMPUTE int1 = -1 DISPLAY "SELECT COUNT(*) INTO :int1" DISPLAY " FROM INFO_SCHEM.TABLE_PRIVILEGES" DISPLAY " WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME = - " 'BASE_WCOV'" DISPLAY " AND GRANTOR = 'FLATER' AND GRANTEE = 'CUGINI';" * EXEC SQL SELECT COUNT(*) INTO :int1 * FROM INFO_SCHEM.TABLE_PRIVILEGES * WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME = * 'BASE_WCOV' AND GRANTOR = 'FLATER' AND GRANTEE = * 'CUGINI'; CALL "SUB63" 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 COMPUTE int1 = -1 DISPLAY "SELECT COUNT(*) INTO :int1" DISPLAY " FROM INFO_SCHEM.COLUMN_PRIVILEGES" DISPLAY " WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME = - " 'BASE_WCOV'" DISPLAY " AND GRANTOR = 'FLATER' AND GRANTEE = 'CUGINI';" * EXEC SQL SELECT COUNT(*) INTO :int1 * FROM INFO_SCHEM.COLUMN_PRIVILEGES * WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME = * 'BASE_WCOV' AND GRANTOR = 'FLATER' AND GRANTEE = 'CUGINI' * ; CALL "SUB64" 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 "ROLLBACK WORK;" * EXEC SQL ROLLBACK WORK; CALL "SUB65" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " if ( flag = 1 ) then DISPLAY " *** pass *** " * EXEC SQL INSERT INTO HU.TESTREPORT * VALUES('0685','pass','MCO'); CALL "SUB66" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD else DISPLAY " dml131.sco *** fail *** " * EXEC SQL INSERT INTO HU.TESTREPORT * VALUES('0685','fail','MCO'); CALL "SUB67" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD COMPUTE errcnt = errcnt + 1 END-IF DISPLAY "===============================================" * EXEC SQL COMMIT WORK; CALL "SUB68" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD ******************** END TEST0685 ******************** ******************** BEGIN TEST0686 ******************* MOVE 1 TO flag DISPLAY " TEST0686 " DISPLAY "INFO_SCHEM: Primary key enh. is not null" DISPLAY "References:" DISPLAY " F# 2 -- Basic information schema" DISPLAY " F# 3 -- Basic schema manipulation" DISPLAY " F# 16 -- PRIMARY KEY enhancement" DISPLAY " FIPS Change Notice #1 -- Shortened - " identifiers" DISPLAY " in INFO_SCHEM" DISPLAY " - - - - - - - - - - - - - - - - - - -" COMPUTE SQLCODE = -1 MOVE "xxxxx" TO SQLSTATE DISPLAY "CREATE TABLE FEAT16 (" DISPLAY " EMPNUM INT PRIMARY KEY," DISPLAY " PNUM INT UNIQUE);" * EXEC SQL CREATE TABLE FEAT16 ( * EMPNUM INT PRIMARY KEY, * PNUM INT UNIQUE); CALL "SUB69" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "COMMIT WORK;" * EXEC SQL COMMIT WORK; CALL "SUB70" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " *PRIMARY KEY implies NOT NULL but UNIQUE does not MOVE 0 TO int1 DISPLAY "SELECT COUNT(*) INTO :int1" DISPLAY " FROM INFO_SCHEM.COLUMNS WHERE TABLE_SCHEM = - " 'FLATER'" DISPLAY " AND TABLE_NAME = 'FEAT16' AND COLUMN_NAME = - " 'EMPNUM'" DISPLAY " AND IS_NULLABLE = 'NO';" * EXEC SQL SELECT COUNT(*) INTO :int1 * FROM INFO_SCHEM.COLUMNS WHERE TABLE_SCHEM = 'FLATER' * AND TABLE_NAME = 'FEAT16' AND COLUMN_NAME = 'EMPNUM' * AND IS_NULLABLE = 'NO'; CALL "SUB71" 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 MOVE 0 TO int1 DISPLAY "SELECT COUNT(*) INTO :int1" DISPLAY " FROM INFO_SCHEM.COLUMNS WHERE TABLE_SCHEM = - " 'FLATER'" DISPLAY " AND TABLE_NAME = 'FEAT16' AND COLUMN_NAME = - " 'PNUM'" DISPLAY " AND IS_NULLABLE = 'YES';" * EXEC SQL SELECT COUNT(*) INTO :int1 * FROM INFO_SCHEM.COLUMNS WHERE TABLE_SCHEM = 'FLATER' * AND TABLE_NAME = 'FEAT16' AND COLUMN_NAME = 'PNUM' * AND IS_NULLABLE = 'YES'; CALL "SUB72" 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 "ROLLBACK WORK;" * EXEC SQL ROLLBACK WORK; CALL "SUB73" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DROP TABLE FEAT16 CASCADE;" * EXEC SQL DROP TABLE FEAT16 CASCADE; CALL "SUB74" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "COMMIT WORK;" * EXEC SQL COMMIT WORK; CALL "SUB75" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " if ( flag = 1 ) then DISPLAY " *** pass *** " * EXEC SQL INSERT INTO HU.TESTREPORT * VALUES('0686','pass','MCO'); CALL "SUB76" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD else DISPLAY " dml131.sco *** fail *** " * EXEC SQL INSERT INTO HU.TESTREPORT * VALUES('0686','fail','MCO'); CALL "SUB77" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD COMPUTE errcnt = errcnt + 1 END-IF DISPLAY "===============================================" * EXEC SQL COMMIT WORK; CALL "SUB78" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD ******************** END TEST0686 ******************** ******************** BEGIN TEST0687 ******************* MOVE 1 TO flag DISPLAY " TEST0687 " DISPLAY " INFO_SCHEM: Multiple schemas per user" DISPLAY "References:" DISPLAY " F# 2 -- Basic information schema" DISPLAY " F# 17 -- Multiple schemas per user" DISPLAY " FIPS Change Notice #1 -- Shortened - " identifiers" DISPLAY " in INFO_SCHEM" DISPLAY " - - - - - - - - - - - - - - - - - - -" COMPUTE SQLCODE = -1 MOVE "xxxxx" TO SQLSTATE MOVE 0 TO int1 DISPLAY "SELECT COUNT(*) INTO :int1" DISPLAY " FROM INFO_SCHEM.SCHEMATA" DISPLAY " WHERE SCHEM_NAME = 'SHIRLEY_HURWITZ' AND" DISPLAY " SCHEM_OWNER = 'FLATER';" * EXEC SQL SELECT COUNT(*) INTO :int1 * FROM INFO_SCHEM.SCHEMATA * WHERE SCHEM_NAME = 'SHIRLEY_HURWITZ' AND * SCHEM_OWNER = 'FLATER'; CALL "SUB79" 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 MOVE 0 TO int1 DISPLAY "SELECT COUNT(*) INTO :int1" DISPLAY " FROM INFO_SCHEM.SCHEMATA" DISPLAY " WHERE SCHEM_NAME = 'LEN_GALLAGHER' AND" DISPLAY " SCHEM_OWNER = 'FLATER';" * EXEC SQL SELECT COUNT(*) INTO :int1 * FROM INFO_SCHEM.SCHEMATA * WHERE SCHEM_NAME = 'LEN_GALLAGHER' AND * SCHEM_OWNER = 'FLATER'; CALL "SUB80" 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 WORK;" * EXEC SQL COMMIT WORK; CALL "SUB81" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " if ( flag = 1 ) then DISPLAY " *** pass *** " * EXEC SQL INSERT INTO HU.TESTREPORT * VALUES('0687','pass','MCO'); CALL "SUB82" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD else DISPLAY " dml131.sco *** fail *** " * EXEC SQL INSERT INTO HU.TESTREPORT * VALUES('0687','fail','MCO'); CALL "SUB83" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD COMPUTE errcnt = errcnt + 1 END-IF DISPLAY "===============================================" * EXEC SQL COMMIT WORK; CALL "SUB84" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD ******************** END TEST0687 ******************** **** TESTER MAY CHOOSE TO INSERT CODE FOR errcnt > 0 STOP RUN. * **** Procedures for PERFORM statements *Test SQLCODE and SQLSTATE for normal completion. CHCKOK. DISPLAY "SQLCODE should be 0; its value is ", SQL-COD DISPLAY "SQLSTATE should be 00000; its value is ", SQLSTATE PERFORM NOSUBCLASS THRU EXIT-NOSUBCLASS if (SQLCODE NOT = 0 OR NORMSQ NOT = "00000") then MOVE 0 TO flag END-IF if (NORMSQ = "00000" AND NORMSQ NOT = SQLSTATE) then DISPLAY "Valid implementation-defined SQLSTATE accepted." END-IF . NOSUBCLASS. *This routine replaces valid implementation-defined *subclasses with 000. This replacement equates valid *implementation-defined subclasses with the 000 value *expected by the test case; otherwise the test will fail. *After calling NOSUBCLASS, NORMSQ will be tested * SQLSTATE will be printed. MOVE SQLSTATE TO NORMSQ MOVE 3 TO norm1 *subclass begins in position 3 of char array NORMSQ *valid subclass begins with 5-9, I-Z, end of ALPNUM table PERFORM VARYING norm2 FROM 14 BY 1 UNTIL norm2 > 36 if (NORMSQX(norm1) = ALPNUM(norm2)) then MOVE "0" TO NORMSQX(norm1) END-IF END-PERFORM *Quit if NORMSQ is unchanged. Subclass is not impl.-def. *Changed NORMSQ means implementation-defined subclass, *so proceed to zero it out, if valid (0-9,A-Z) if (NORMSQ = SQLSTATE) then GO TO EXIT-NOSUBCLASS END-IF MOVE 4 TO norm1 *examining position 4 of char array NORMSQ *valid characters are 0-9, A-Z PERFORM VARYING norm2 FROM 1 BY 1 UNTIL norm2 > 36 if (NORMSQX(norm1) = ALPNUM(norm2)) then MOVE "0" TO NORMSQX(norm1) END-IF END-PERFORM MOVE 5 TO norm1 *valid characters are 0-9, A-Z *examining position 5 of char array NORMSQ PERFORM VARYING norm2 FROM 1 BY 1 UNTIL norm2 > 36 if (NORMSQX(norm1) = ALPNUM(norm2)) then MOVE "0" TO NORMSQX(norm1) END-IF END-PERFORM *implementation-defined subclasses are allowed for warnings *(class = 01). These equate to successful completion *SQLSTATE values of 00000. *Reference SQL-92 4.28 SQL-transactions, paragraph 2 if (NORMSQX(1) = "0" AND NORMSQX(2) = "1") then MOVE "0" TO NORMSQX(2) END-IF . EXIT-NOSUBCLASS. EXIT. ¤ Dauer der Verarbeitung: 0.41 Sekunden (vorverarbeitet) ¤*© Formatika GbR, Deutschland |
|
2026-03-28