|
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.85 Sekunden
(vorverarbeitet)
¤
|
Haftungshinweis
Die Informationen auf dieser Webseite wurden
nach bestem Wissen sorgfältig zusammengestellt. Es wird jedoch weder Vollständigkeit, noch Richtigkeit,
noch Qualität der bereit gestellten Informationen zugesichert.
Bemerkung:
Die farbliche Syntaxdarstellung ist noch experimentell.
|
