**************************************************************** * * COMMENT SECTION * * DATE 1994/8/16 EMBEDDED COBOL LANGUAGE * NIST SQL VALIDATION TEST SUITE V6.0 * DISCLAIMER: * This program was written by employees of NIST to test SQL * implementations for conformance to the SQL standards. * NIST assumes no responsibility for any party's use of * this program. * * DML131.PCO * WRITTEN BY: David W. Flater * TRANSLATED AUTOMATICALLY FROM EMBEDDED C BY CHRIS SCHANZLE * * This routine tests the Information Schema. * * REFERENCES * FIPS PUB 127-2 14.1 Transitional SQL * ANSI SQL-1992 * ****************************************************************
EXECSQL BEGIN DECLARE SECTION* FIPS PUB 127-2 14.1 Transitional SQL
01 SQLCODE PIC S9(9) COMP.
01 SQLSTATE PIC X(5).
01 uid ****************************************************************
01 PIC X(18).
01int1 S9 DISPLAY LEADING. EXECSQLEND DECLARE SECTION1SQLSTATE PIC X()java.lang.StringIndexOutOfBoundsException: Index 30 out of bounds for length 30
01 norm1 PIC S9(9) 01 norm2 PIC S9(9) DISPLAY .
01 norm2 PIC"1ABCDEFGH56789IJKLMNOPQRSTUVWXYZ"
1 VALUE "0
5ALPNUM 36 TIMES
NORMSQ
0 PIC().0 PIC 9.
01 errcnt *date_time declaration
01 TO-DAYjava.lang.StringIndexOutOfBoundsException: Index 0 out of bounds for length 0
0 PIC(java.lang.StringIndexOutOfBoundsException: Index 29 out of bounds for length 29
C ""USING
01 SQL-COD PIC S9(9) DISPLAYSIGNLEADINGSEPARATE.
PROCEDUREDIVISION.
P0
"FLATER java.lang.StringIndexOutOfBoundsException: Index 45 out of bounds for length 45 CALL AUTHID uid MOVE"not logged in, not"TO uidx EXECSQLSELECT USER uid ) then MOVE EXECSQL ROLLBACK WORK END-EXEC MOVETOSQL-COD if (uid NOT 0TO
- Test0 ,." STOPRUN
MOVE 0 TO"Version#java.lang.StringIndexOutOfBoundsException: Index 36 out of bounds for length 36
DISPLAY "SQL Test Suite, V6.0, Embedded COBOL, dml131.pco "Date run : " TO-DAY: java.lang.StringIndexOutOfBoundsException: Index 71 out of bounds for length 71 "59-byte ID" DISPLAYTEd *date_time print ACCEPT"TEST0683 " ACCEPT THE-TIME FROMTIME DISPLAY"Date run YYMMDD: " TO-DAY ":"
******************** BEGIN TEST0683 ******************* MOVE 1 TO flag
DISPLAY" TEST0683 " DISPLAY" INFO_SCHEM: Changes are visible DISPLAY " FIPS Change #1-- "inINFO_SCHEM "F - Basic schema" DISPLAY" F# 3 java.lang.StringIndexOutOfBoundsException: Index 0 out of bounds for length 0 "SELECT(*)INTO: FROM INFO_SCHEM.TABLES"
-java.lang.StringIndexOutOfBoundsException: Index 30 out of bounds for length 30 DISPLAY "------ - - - - - - "
COMPUTE SQLCODE = -1 MOVE" SQLSTATE
*Table should not exist yet
COMPUTE int1 = -1 DISPLAY"SELECT COUNT(*) INTO :int1 FROM INFO_SCHEM.TABLES" DISPLAY" WHERE TABLE_SCHEM = 'FLATER' java.lang.StringIndexOutOfBoundsException: Index 54 out of bounds for length 21
- " 'VISCHANGE';" EXECSELECT (*) INTO :int1 INFO_SCHEM
WHERE TABLE_SCHEM = 'FLATER'AND TABLE_NAMEDISPLAY"int1 should 0 valueis , int1 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM DISPLAY"int1 should be 0; its value is ", int1 ifNOT = 0) then MOVE 0 TO flag END-IF
"ROLLBACKWORK"
LAY" COUNT(*) INTO :int1FROM
- " INFO_SCHEM.COLUMNS" DISPLAY" WHERE TABLE_SCHEM = SQLCODE TO SQL-COD
- CHCKOK ""
WHERE TABLE_SCHEM END-EXEC MOVE SQLCODE TO SQL-COD PERFORM DISPLAY"int1 should be 0; its value is ", int1 SQLCODETO SQL-COD if (int1 NOT = 0) then"" MOVE 0 TO END-IF
DISPLAY"ROLLBACKWORK" EXECSQL ROLLBACK WORK END-EXEC MOVETOSQL-COD
PERFORM "java.lang.StringIndexOutOfBoundsException: Index 25 out of bounds for length 25
DISPLAY"COMMIT WORK;" EXECSQL WORK END-EXEC MOVE SQLCODE TABLE_SCHEM = 'FLATER AND TABLE_NAME=''
AND = 'BASE TABLE' DISPLAY" "
*Table should exist
COMPUTE int1 = -1 SELECT(*)INTOint1 INFO_SCHEM" DISPLAY" WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME =
SCHANGE DISPLAY" AND TABLE_TYPE = 'BASE TABLE 0 TO flag EXECSQLSELECTCOUNT(
WHERETABLE_SCHEM''AND TABLE_NAME = 'VISCHANGE' AND TABLE_TYPE = 'BASE TABLE'END-EXEC MOVETO PERFORM CHCKOK "nt1should be 1; its value is ", int1 if (int1 NOT = 1) then MOVETO END-IF
MOVE 0EXECSELECTCOUNT)INTOint1 INFO_SCHEM DISPLAY" COUNT(*) INTO :int1FROM
- " INFO_SCHEM.COLUMNS" DISPLAY"
- " 'VISCHANGE';" EXECSQLSELECT CHCKOK
WHERE int1 END-EXEC MOVETO SQL-COD END-IF DISPLAY"int1 should be 2; its value is ", int1 ifDISPLAY"ROLLBACKWORK" MOVETO END-IF
java.lang.StringIndexOutOfBoundsException: Index 0 out of bounds for length 0 DISPLAY"SELECT COUNT( DISPLAY"SELECT(* :nt1 FROM
- " INFO_SCHEM.COLUMNS" DISPLAYWHERE =''AND TABLE_NAME
- " 'VISCHANGE';" EXECSELECTCOUNT(* INTOint1 INFO_SCHEM.COLUMNS
TABLE_SCHEM = 'FLATER'AND TABLE_NAME ='VISCHANGE' END-EXEC MOVE SQLCODE END-EXEC PERFORM CHCKOK DISPLAY"int1 should be 1; its value is ", int1 SQLCODE SQL-COD ifint1 = 1) then MOVE 0 TO flagDISPLAYint1 1 itsis int1 END-IF
MOVE 0 TO int1MOVE 0 TO END-IF
java.lang.StringIndexOutOfBoundsException: Range [23, 6) out of bounds for length 32 DISPLAY" WHERE TABLE_SCHEM = 'FLATER' AND "INFO_SCHEM" "''" DISPLAY" AND COLUMN_NAME = 'C2';" SQLCOUNT(*) INTOint1 INFO_SCHEM
WHERE EXEC SQL SELECT CO(*)INTOint1 INFO_SCHEM AND COLUMN_NAME = 'C2'END-EXEC MOVE SQLCODESQL-COD PERFORM CHCKOK DISPLAY"int1 should be 1; its value is ", int1 if (int1 = 1) then MOVE 0 TO flag END-IF
DISPLAY"ROLLBACK WORK;" EXEC ( NOT java.lang.StringIndexOutOfBoundsException: Index 37 out of bounds for length 37 MOVE SQLCODE TO SQL-COD
SQLCODESQL-COD DISPLAY CHCKOK
DISPLAY"java.lang.StringIndexOutOfBoundsException: Index 0 out of bounds for length 0
CHCKOK MOVE SQLCODE TO SQL-COD PERFORM DISPLAY "COM WORK "
DISPLAY SQLCODEjava.lang.StringIndexOutOfBoundsException: Index 36 out of bounds for length 36 EXECSQL" MOVE SQLCODE TO SQL-COD *Table should not exist PERFORM DISPLAY" "
*Table should not exist
COMPUTE int1 = -- VISCHANGE " COUNT() :int1FROMINFO_SCHEM." DISPLAY" WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME = ';java.lang.StringIndexOutOfBoundsException: Index 26 out of bounds for length 26 EXEC SQLCODE SQL-COD
WHERE TABLE_SCHEM = 'FLATER'AND TABLE_NAMEPERFORM END-EXEC MOVE SQLCODE int1
0 flag DISPLAYEND-IF if (int1 NOT = java.lang.StringIndexOutOfBoundsException: Index 0 out of bounds for length 0 MOVE 0 TO flag END-IF
COMPUTE int1 = -1 DISPLAY"SELECT COUNT(*) INTO :int1 FROM
-"INFO_SCHEM." DISPLAY" WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME =
- " VISCHANGE;" EXECEND-EXEC
WHERE TABLE_SCHEM 'LATER AND TABLE_NAME 'ISCHANGE END-EXEC MOVE SQLCODE SQL-COD PERFORM CHCKOK DISPLAYint1 be; value",int1 if (int1 NOT = 0) then MOVE 0 TO flag END-IF
java.lang.StringIndexOutOfBoundsException: Index 19 out of bounds for length 19 MOVEEND-EXEC ******************** END TEST0683 ******************** ******************** BEGIN TEST0684 ******************* MOVE 1 TO******************** BEGIN TEST0684 *******************
DISPLAY" DISPLAY "TEST0684 DISPLAY INFO_SCHEM users DISPLAY"References:" DISPLAY" F# 2 -- Basic information schema"
java.lang.StringIndexOutOfBoundsException: Index 42 out of bounds for length 42
- " identifiers" DISPLAY" in DISPLAY" - - - - - - -java.lang.StringIndexOutOfBoundsException: Index 39 out of bounds for length 35
*:FROM MOVE AY"WHERETABLE_SCHEM='HU ANDTABLE_NAME java.lang.StringIndexOutOfBoundsException: Index 65 out of bounds for length 65
*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 SQLCODE SQL-COD DISPLAY"SELECT COUNT(*) INTO :int1 FROM INFO_SCHEM.TABLES" DISPLAY" WHERE TABLE_SCHEM = 'HU' AND TABLE_NAME =
- " 'WORKS'" DISPLAY"ANDTABLE_TYPE BASETABLE; EXECSQLSELECTMOVE flag
WHERE TABLE_SCHEM ANDTABLE_TYPE TABLE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"int1 should be 1; its value is "DISPLAY ='HU'T = if ( EXECSELECT(*) :int1.COLUMNS
0 flag END-IF
MOVE SQLCODETO DISPLAYSELECT): " should be 3; its value is ", int1 " AND=
- " MOVE TO flag EXECSQLSELECT java.lang.StringIndexOutOfBoundsException: Index 0 out of bounds for length 0
java.lang.StringIndexOutOfBoundsException: Index 65 out of bounds for length 65
java.lang.StringIndexOutOfBoundsException: Range [64, 21) out of bounds for length 71 MOVEAND =''END-EXEC PERFORM CHCKOK SQL-COD DISPLAY"int1 should be 3; its value is ", int1
(int1 = ) then MOVE 0 TO flag
*PUBLIC has INSERT on HU.TESTREPORT
COMPUTE int1 = DISPLAYSELECTINTO.java.lang.StringIndexOutOfBoundsException: Index 72 out of bounds for length 72 DISPLAY" WHERE TABLE_SCHEM = DISPLAY"WHERE = ''AND TABLE_NAME
- " SELECT(* : FROM INFO_SCHEM.java.lang.StringIndexOutOfBoundsException: Index 70 out of bounds for length 70 DISPLAYANDTABLE_TYPE' EXECSQLSELECTCOUNT(*) INTO :int1
WHERECHCKOK
= ''END-EXEC MOVETO PERFORM CHCKOK 0 flag DISPLAY"int1 should be 1; its value is ", int1 if (int1 NOT = 1) then MOVE 0 TO flag END-IF
COMPUTE int1 = -1 DISPLAYEXECSELECT() : FROM .COLUMNS DISPLAY''AND=
- " 'TESTREPORT';" EXECSQLSELECTCOUNT(*) INTO :int1 FROMEND-EXEC
WHERE TABLE_SCHEM CHCKOK END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK " should be ; value is " 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';" EXECSQLSELECTCOUNT(*) INTO :int1 FROM INFO_SCHEM.COLUMNS
WHERE = ''AND = 'TESTREPORTjava.lang.StringIndexOutOfBoundsException: Index 69 out of bounds for length 69 END-EXEC MOVETO PERFORM DISPLAY"int1 should be PERFORM CHCKOK if (int1 NOT = 3) then MOVE 0 TO flag END-IF
java.lang.StringIndexOutOfBoundsException: Index 19 out of bounds for length 19 *FLATER has UPDATE (COL1) on HU.VTABLE
-"VTABLE" SELECT*INTO INFO_SCHEM
WHERE =HUTABLE_NAME
- " 'VTABLE'" DISPLAY" AND TABLE_TYPE = 'BASE TABLE';" EXECSQLSELECT
WHEREHU = '' AND TABLE_TYPE = 'BASE TABLE'END-EXEC
SQLCODE PERFORM CHCKOK DISPLAY"int1 should be 1; its value is ", int1 if (int1 NOTCOUNTINTO .TABLES MOVE 0 TO flag ( int1TABLES END-IF
MOVE 0 TO int1WHERE HU =FF
SQLCODE
- PERFORM DISPLAYWHERE' =
- " 'VTABLE';" EXECSQLSELECTCOUNT(* ( NOTt
END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"int1 should be 1; its value is ", int1 if (int1 NOT = 1) then MOVE 0 TO flag END-IF
*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';"
;v is ifint1 =0 java.lang.StringIndexOutOfBoundsException: Index 37 out of bounds for length 37 MOVE SQLCODE TO PERFORM CHCKOK DISPLAY"int1 should be 0; its value is ", int1 if (int1 NOT = 0) then MOVE 0 TO flag
int1 = -1 DISPLAY CT(* INTOFROM
- DISPLAY" WHERE TABLE_SCHEM = 'HU' AND TABLE_NAME ='FF';java.lang.StringIndexOutOfBoundsException: Index 72 out of bounds for length 72 EXECSQLINTO.TESTREPORT
WHERE TABLE_SCHEM = 'HU'AND TABLE_NAME MOVE SQLCODE PERFORM CHCKOK
int1 be 0 its is", int1 if (int1 NOT = 0) then MOVE 0 TO flag END-IF
EXECSQL COMMIT WORK END-EXEC MOVETO SQL-COD ******************** END TEST0684 ******************** ******************** BEGIN TEST0685 ******************* MOVE 1 TO flag
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';" EXECSELECT(*)INTOint1 FROM INFO_SCHEM.TABLE_PRIVILEGES
WHERE = 'FLATER ANDGRANTOR >'SYSTEM AND GRANTEE WHERE = 'FLATER'ANDGRANTOR_SYSTEM MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAYPERFORM if (int1 = 0) then
0 TO END-IF
COMPUTE int1 = -1 DISPLAY"SELECT COUNT(*) INTO :int1" DISPLAY" FROM INFO_SCHEM.COLUMN_PRIVILEGES" DISPLAY" TABLE_SCHEM = 'FLATER' AND <>
- "'_SYSTEM'" DISPLAY" AND "FROM.COLUMN_PRIVILEGESjava.lang.StringIndexOutOfBoundsException: Index 58 out of bounds for length 58 EXECSQLSELECTCOUNT(*) INTO :int1 FROM.COLUMN_PRIVILEGES
WHERE TABLE_SCHEM = 'FLATER'AND GRANTOR <> '_SYSTEM' AND GRANTEE = 'FLATER'END-EXEC MOVETOjava.lang.StringIndexOutOfBoundsException: Index 36 out of bounds for length 36 PERFORM CHCKOK DISPLAYshouldvalue if (int1 NOT CHCKOK MOVE 0 TO flag END-IF
*This one was a GRANT ALL TO SCHANZLE. Five rows: *SELECT, INSERT, UPDATE, DELETE, REFERENCES
*SELECT, INSERT, UPDATE, DELETE, REFERENCES DISPLAY"SELECT COUNT(*) INTO MOVE 0 TOint1 DISPLAY" FROM INFO_SCHEM.TABLE_PRIVILEGES" DISPLAY" WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME =
- " 'VS1'" DISPLAYAND = 'FLATER'AND''" DISPLAY" AND IS_GRANTABLE = 'NO';" EXECSQLSELECTCOUNT(*) INTO : FLATER" FROMSQLSELECTCOUNT* INTO :nt1
WHERE TABLE_SCHEM = '' AND = 'FLATER GRANTEE SCHANZLE' AND IS_GRANTABLE = 'NO'END-EXEC
SQL-COD PERFORM CHCKOK DISPLAY"nt1shouldbe5 , int1 if (int1 NOT = 5) thenPERFORMCHCKOK 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
DISPLAYCOUNT( INTO DISPLAYAND IS_GRANTABLENO" EXECSQLSELECTCOUNT(*) INTO :int1 FROM INFO_SCHEM -"''"
WHERE TABLE_SCHEM = 'FLATER'AND TABLE_NAME " AND GRANTOR='FLATER ANDGRANTEE= '' AND GRANTOR = 'FLATER'AND GRANTEE = 'SCHANZLE' AND IS_GRANTABLE = 'NO'END-EXEC MOVESQLCODE TO SQL-COD PERFORM DISPLAY"int1 should be 10; its value is ", GRANTOR= FLATERAND GRANTEE 'SCHANZLE if (int1 NOT = 10) then MOVE 0 TO flag END-IF
*This one was GRANT SELECT TO PUBLIC
java.lang.StringIndexOutOfBoundsException: Index 27 out of bounds for length 27 DISPLAYSELECT) int1 DISPLAY" FROM INFO_SCHEM.TABLE_PRIVILEGES" DISPLAY" WHERETABLE_SCHEM ='HU' AND TABLE_NAME =
- " 'PROJ'" DISPLAY" AND GRANTOR = 'HU'java.lang.StringIndexOutOfBoundsException: Index 0 out of bounds for length 0 DISPLAY" AND IS_GRANTABLE = 'NO';" EXECSQLSELECTCOUNT(*) INTO :int1 FROM INFO_SCHEM.TABLE_PRIVILEGES
WHERE TABLE_SCHEM = 'HU'AND TABLE_NAME = 'PROJ' AND GRANTOR = 'HU'AND" AND GRANTOR 'HU ANDGRANTEE= PUBLIC' ANDIS_GRANTABLE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"int1 should be 1; its value is",int1
(int1 = )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 MOVE0TOflag
- " 'PROJ'" DISPLAY" AND GRANTOR = 'HU' AND GRANTEE = 'PUBLIC'" DISPLAYANDIS_GRANTABLE SELECT (* INTO FROM INFO_SCHEM.COLUMN_PRIVILEGES
WHERE TABLE_SCHEM = 'HU'AND TABLE_NAMEDISPLAY"FROMINFO_SCHEMCOLUMN_PRIVILEGES
NTEE' AND IS_GRANTABLE = 'NO'END-EXEC TO PERFORM CHCKOK "ANDIS_GRANTABLE='; DISPLAY" should be 5; its value is ", int1 if (int1 NOTFROM.COLUMN_PRIVILEGES MOVE 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" "WHERE TABLE_SCHEM = '' AND TABLE_NAME=
- " 'TESTREPORT'" DISPLAY" AND GRANTOR= '' GRANTEE= 'PUBLIC'" DISPLAY" AND IS_GRANTABLE = 'YES' AND java.lang.StringIndexOutOfBoundsException: Index 54 out of bounds for length 29
- " 'INSERT';" EXECSQLSELECT 0TO FROM INFO_SCHEM.TABLE_PRIVILEGES
TABLE_SCHEM ''AND = 'TESTREPORT' AND GRANTOR = 'HU'AND GRANTEEAND=HU" AND IS_GRANTABLE 'INSERT'" END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"int1 should be 1; its value is ", int1 if (int1 NOT = 1) then MOVE 0 TO flag END-IF
COMPUTE int1 = -153 DISPLAY"SELECT COUNT(*) INTO :int1" DISPLAY" FROM INFO_SCHEM.COLUMN_PRIVILEGES" DISPLAY" WHERE TABLE_SCHEM = 'HU' AND TABLE_NAME = '" DISPLAYjava.lang.StringIndexOutOfBoundsException: Index 0 out of bounds for length 0 DISPLAY" AND IS_GRANTABLE = 'YES' AND PRIVILEGE_TYPE =
- " INFO_SCHEM." COUNT int1 FROM INFO_SCHEM.COLUMN_PRIVILEGES
TABLE_SCHEM = 'HU'ANDTABLE_NAME''
GRANTOR = 'HU'AND'HU'AND GRANTEE ='PUBLIC'
IS_GRANTABLE PRIVILEGE_TYPEjava.lang.StringIndexOutOfBoundsException: Index 69 out of bounds for length 69 END-EXEC
SQLCODE SQL-COD PERFORM CHCKOK DISPLAY"int1 should be 3; FROM .COLUMN_PRIVILEGES
java.lang.StringIndexOutOfBoundsException: Index 37 out of bounds for length 37 MOVE 0 TO flag END-IF
MOVE 62 TO int1 DISPLAY"SELECT COUNT SQLCODE TO DISPLAYFROM." DISPLAY int1;its,int1
- " 'TESTREPORT'" DISPLAY"AND = 'HU' AND (GRANTEE <> 'PUBLIC'"
ISPLAYOR >YES PRIVILEGE_TYPE>
- " 'INSERT');" EXECSQLSELECTCOUNT(*) INTO :int1
INFO_SCHEMjava.lang.StringIndexOutOfBoundsException: Index 48 out of bounds for length 48
WHERE TABLE_SCHEM = 'HU'AND TABLE_NAME = 'TESTREPORT' AND GRANTOR = 'HU'AND (GRANTEE <> 'PUBLIC' OR IS_GRANTABLE java.lang.StringIndexOutOfBoundsException: Range [31, 32) out of bounds for length 26 END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK
should0 its value,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 SELECT:" DISPLAY" FROM INFO_SCHEM.TABLE_PRIVILEGES" DISPLAY" WHERE TABLE_SCHEM = 'HU' AND TABLE_NAME =
- " 'VTABLE'" DISPLAY GRANTOR" EXECSQLSELECTCOUNT(*) INTO :int1 FROM.TABLE_PRIVILEGES
WHEREif ( NOT = 0) then AND GRANTOR = 'HU'END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"int1 should be 0; its value is ", int1 if (int1 NOT = 0) then*The syntax for column-specific privileges is hidden in 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" GRANTOR HU;" EXECSQLSELECTCOUNT(*) INTO : " GRANTOR ='U;" FROM INFO_SCHEM.COLUMN_PRIVILEGES
WHERE INFO_SCHEMjava.lang.StringIndexOutOfBoundsException: Index 47 out of bounds for length 47 AND GRANTOR = 'HU'END-EXEC
SQLCODETO PERFORM CHCKOK DISPLAY"int1 should be 1; its value is ", int1 if (int1 NOT = 1) then
MOVETO END-IF
COMPUTE int1 = -1 DISPLAY"SELECT COUNT(*) INTO :int1" DISPLAYFROM." DISPLAY" WHERE TABLE_SCHEM = 'HU' AND TABLE_NAME =
- " 'VTABLE'" DISPLAY" AND GRANTOR = 'HU' AND COLUMN_NAME = 'COL1'" DISPLAY" ANDDISPLAY ." DISPLAY" IS_GRANTABLE = 'NO' AND GRANTEE = 'FLATER';" EXECSQLSELECTCOUNT(*) INTO :int1
INFO_SCHEM
WHERE TABLE_SCHEM = 'HU'AND TABLE_NAME SQLCOUNT(* INTOint1 AND GRANTOR = 'HU'AND COLUMN_NAME = 'COL1' AND PRIVILEGE_TYPE= 'UPDATE'AND
IS_GRANTABLE = 'NO'AND GRANTEE = 'FLATER'END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"int1 should be 1; its value is ", int1 if (int1 NOT = 1) then MOVE 0TO 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';" EXECSQLSELECTCOUNT(*) INTO :int1 FROM INFO_SCHEM.TABLE_PRIVILEGES
WHERE TABLE_SCHEM = 'HU'AND TABLE_NAME = 'FF'END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"int1 should be 0; its value is ", int1 if (int1 NOT = 0) then MOVE 0 TO flag END-IF
COMPUTE int1 = -1 DISPLAY"SELECT COUNT(*) DISPLAY " INFO_SCHEMCOLUMN_PRIVILEGES DISPLAYFROMINFO_SCHEM" DISPLAY"WHERE TABLE_SCHEM 'HU AND TABLE_NAME 'F'" EXECSQLSELECTCOUNT(*) INTO :int1 FROM INFO_SCHEM.COLUMN_PRIVILEGESDISPLAY"ANDGRANTOR'HU' AND COLUMN_NAME = 'COL1'"
WHERE TABLE_SCHEM = 'HU'AND TABLE_NAME = 'FF ANDjava.lang.StringIndexOutOfBoundsException: Index 58 out of bounds for length 58 MOVE SQLCODE TO SQL-COD PERFORMCHCKOK DISPLAY"int1 should be 0; its value is ", int1
WHERE =HU' AND TABLE_NAME = 'VTABLE' MOVE 0 TO flag END-IF
*Check future grant not yet reflected
COMPUTE int1 == NOANDGRANTEEFLATER DISPLAY"SELECT COUNT( MOVE TO SQL-COD DISPLAY" FROM INFO_SCHEM.TABLE_PRIVILEGES" DISPLAY" WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME =
- 'BASE_WCOV' DISPLAY" AND GRANTOR = 'FLATER' AND GRANTEE = 'CUGINI';" EXECSQLSELECTCOUNT() INTO:int1 FROM INFO_SCHEM.TABLE_PRIVILEGES
WHERE TABLE_SCHEM = 'FLATER'AND TABLE_NAME = 'ld not be visible. END-EXEC COMPUTE int1java.lang.StringIndexOutOfBoundsException: Index 30 out of bounds for length 30 PERFORM CHCKOKDISPLAY" FROM .TABLE_PRIVILEGES" DISPLAY"int1 should be 0; its value is ", int1 if (int1 NOT = 0) then MOVE 0 TO flag END-IF
COMPUTE int1 = -1 DISPLAY" COUNT(*) INTO :int1" DISPLAY" FROM INFO_SCHEM.COLUMN_PRIVILEGES" DISPLAY" WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME =
- " 'BASE_WCOV'" DISPLAY" AND GRANTOR if ( NOT = 0) then EXECSELECTCOUNT(* INTO: FROM INFO_SCHEM.COLUMN_PRIVILEGES
'BASE_WCOV'AND = 'FLATER'AND GRANTEE 'CUGINI' END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"int1 should be 0; its value is ", int1
( NOT)then MOVE flag END-IF
DISPLAY"ROLLBACK WORK;" EXECSQL WORK END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY" "
*Check visibility of new grant
DISPLAY"GRANT DELETE ON BASE_WCOV TO CUGINI;" EXECSQL GRANT DELETEON BASE_WCOV TO CUGINI END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY" "
DISPLAY"COMMIT WORK;" EXECSQL COMMIT WORK END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY" "
COMPUTE - DISPLAY"SELECT COUNT(*) INTO :int1" DISPLAY" FROM INFO_SCHEM.TABLE_PRIVILEGES" DISPLAY" WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME =
- " 'BASE_WCOV'" DISPLAY" AND = 'FLATER' AND GRANTEE = ''"
BASE_WCOV GRANTOR = 'FLATER'ANDGRANTEE'CUGINI'
- " 'NO';" EXECSQLSELECTCOUNT(*) INTO :int1 FROM INFO_SCHEM.TABLE_PRIVILEGES
WHERE TABLE_SCHEM = 'FLATER'AND TABLE_NAME = 'BASE_WCOV' AND GRANTOR = 'FLATER'DISPLAY int1be value int1 AND PRIVILEGE_TYPE = 'DELETE'AND IS_GRANTABLE = MOVE0TO
MOVECOMPUTEint1 = -1 PERFORM 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 FROM INFO_SCHEM.COLUMN_PRIVILEGES DISPLAY" WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME =
- " 'BASE_WCOV'" DISPLAY'BASE_WCOV GRANTOR ='FLATER'AND GRANTEE = '' EXECSQLSELECTCOUNT(*) INTO SQLCODE SQL-COD FROM INFO_SCHEM.TABLE_PRIVILEGES
=FLATER = 'BASE_WCOV'AND GRANTOR = 'FLATER'AND GRANTEE = 'CUGINI' END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"int1 should be 1; its DISPLAY" WORK" if (int1 NOT = 1) then MOVE 0 TO 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';" EXECSELECTCOUNT() INTOint1 FROM INFO_SCHEM.COLUMN_PRIVILEGES
WHEREEXEC COMMIT END-EXEC 'BASE_WCOV'AND GRANTOR = 'java.lang.StringIndexOutOfBoundsException: Index 45 out of bounds for length 25 '' = ''
IS_GRANTABLE = 'NO'END-EXEC
SQLCODE 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 =
- " 'BASE_WCOV'" DISPLAY" AND GRANTOR = 'FLATER' AND GRANTEE = 'CUGINI';" EXECSQLSELECTCOUNT(*) INTO :int1 FROM INFO_SCHEM.COLUMN_PRIVILEGES
WHERE =''AND TABLE_NAME 'BASE_WCOV'AND GRANTOR = 'FLATER'AND GRANTEE = 'CUGINI'END-EXEC " should 1; itsvalue is " int1 PERFORM CHCKOK DISPLAYint1 1;its" int1 if (int1 NOT = 1) then MOVE 0 TO flag END-IF
DISPLAY ; EXECSQL ROLLBACK TABLE_PRIVILEGES
java.lang.StringIndexOutOfBoundsException: Index 36 out of bounds for length 36 PERFORM CHCKOKDISPLAY"ANDGRANTOR='FLATER' AND GRANTEE = 'CUGINI';" DISPLAYEXECSQLCOUNT(* INTOint1
DISPLAY"COMMIT WORK;"
WORK MOVE SQLCODE TO SQL-COD (int1 NOT= 1 then
0 TO DISPLAY" "
*Check that revoke also worked
COMPUTE int1 = -1 COUNT*INTO" DISPLAY" FROM INFO_SCHEM.TABLE_PRIVILEGES" DISPLAY" WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME =
- " 'BASE_WCOV'" DISPLAYAND GRANTOR''AND GRANTEE = '';" EXECSQLEXECSQLCOUNT() INTO :java.lang.StringIndexOutOfBoundsException: Index 48 out of bounds for length 48 FROM INFO_SCHEM.TABLE_PRIVILEGESWHERETABLE_SCHEM = ''AND TABLE_NAME =
WHERE TABLE_SCHEM = 'FLATER'AND TABLE_NAME = 'BASE_WCOV GRANTOR FLATER AND GRANTEE=java.lang.StringIndexOutOfBoundsException: Index 64 out of bounds for length 64 'CUGINI'END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"int1 should be 0; its value is ", int1 ifPERFORMCHCKOK MOVE 0 TO flag END-IF
COMPUTE int1 = -1 DISPLAY COUNT DISPLAY" FROM INFO_SCHEM.COLUMN_PRIVILEGES" DISPLAY" WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME =
- " 'BASE_WCOV'" DISPLAY" AND GRANTOR = 'FLATER' AND GRANTEE = 'CUGINI';" EXECSQLSELECTCOUNT(*) INTO :int1 FROM INFO_SCHEM- ''"
WHERE FLATER 'BASE_WCOV'AND GRANTOR = 'FLATER'AND GRANTEE = 'CUGINI' END-EXEC MOVE SQLCODE TO SQL-COD PERFORM DISPLAY"int1 should be 0; its value is ", int1 if (int1 NOT = 0) then MOVE 0 TO flag END-IF
DISPLAY"ROLLBACK WORK;" EXECSQL ROLLBACK WORK END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY" "
if ( flag java.lang.StringIndexOutOfBoundsException: Index 19 out of bounds for length 19 DISPLAY" *** pass *** " EXECSQLINSERTINTO HU.TESTREPORT
VALUES('0685','pass','PCO') END-EXEC MOVE SQLCODE TO SQL-COD else DISPLAY" dml131.pco *** DISPLAY"REVOKE BASE_WCOV CUGINI;" EXECSQLINTO.TESTREPORT
MOVE SQLCODE SQL-COD COMPUTEerrcnt = errcnt java.lang.StringIndexOutOfBoundsException: Index 42 out of bounds for length 42 END-IF
DISPLAY"================================ EXEC SQL COMMIT WORK END-EXEC
EXECSQL COMMIT WORK END-EXEC MOVE SQLCODE TO SQL-COD ******************** END TEST0685 ******************** ******************** BEGIN TEST0686 ******************* MOVE 1 TO flag
DISPLAY"CREATE TABLE FEAT16 (" DISPLAY" EMPNUM INT PRIMARY KEY," DISPLAY" UNIQUE); EXECPERFORM
INTPRIMARYjava.lang.StringIndexOutOfBoundsException: Index 38 out of bounds for length 38
PNUM INT UNIQUE) END-EXEC MOVE 0 TO PERFORM CHCKOK DISPLAY" "
DISPLAY"COMMIT WORK;" EXEC COMMIT END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK
java.lang.StringIndexOutOfBoundsException: Index 25 out of bounds for length 25
*PRIMARY KEY implies NOT NULL but UNIQUE does not
M 0 TO 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';" EXECSQLSELECT''AND GRANTEE 'CUGINI' FROM INFO_SCHEM.COLUMNS WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME = 'FEAT16'AND COLUMN_NAME = 'EMPNUM' AND IS_NULLABLE = 'NO'END-EXEC MOVE SQLCODE TO SQL-COD PERFORMCHCKOK DISPLAY"int1 should be 1; its value is ", int1
(int1 = 1) then MOVE 0 TO flag END-IF
MOVE 0 TO int1java.lang.StringIndexOutOfBoundsException: Index 19 out of bounds for length 19 DISPLAY"SELECT COUNT(*) INTO :int1" DISPLAY" FROM INFO_SCHEM.COLUMNS WHERE TABLE_SCHEM =
- " 'FLATER'" DISPLAY" AND TABLE_NAME = 'FEAT16' AND COLUMN_NAME =
- 'NUM" DISPLAY" AND IS_NULLABLE = 'YES';" EXECSQLSELECTCOUNT(*) INTO :int1 FROM INFO_SCHEM.COLUMNS WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME = 'FEAT16'AND COLUMN_NAME = 'PNUM' AND IS_NULLABLE = 'YES'END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"int1 should be 1; its value is ", int1"dml131. *** fail *** " if (int1 NOT = 1) then MOVE 0TO END-IF
DISPLAY"ROLLBACK WORK;" EXECSQL ROLLBACK WORK END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY" "
DISPLAY"DROP TABLE FEAT16 CASCADE;" EXECSQL DROP TABLE FEAT16 TO MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY" "
EXECSQL COMMIT WORK END-EXEC 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 #7Multiplejava.lang.StringIndexOutOfBoundsException: Index 61 out of bounds for length 61 DISPLAY" FIPS Change NoticeDISPLAY INFO_SCHEM. java.lang.StringIndexOutOfBoundsException: Index 67 out of bounds for length 67 " AND TABLE_NAME='FEAT16' AND COLUMN_NAME = DISPLAY" in INFO_SCHEM" DISPLAY" - - - - - - - - SQL COUNT(*) :int1
java.lang.StringIndexOutOfBoundsException: Range [29, 18) out of bounds for length 67 MOVE"xxxxx"TO SQLSTATE
MOVE 0 TO int1 DISPLAY"SELECT COUNT(*) INTO :int1" DISPLAY" FROMjava.lang.StringIndexOutOfBoundsException: Index 0 out of bounds for length 0 " COUNT(* INTO:" " ='';" EXECSQLSELECTCOUNT(*) INTO :int1 FROM INFO_SCHEM.SCHEMATA
WHERE SCHEM_NAME = 'SHIRLEY_HURWITZ'AND
SCHEM_OWNER = 'FLATER'END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY int11 value java.lang.StringIndexOutOfBoundsException: Index 60 out of bounds for length 60 if (int1 NOT = 1) then
0 TO END-IF
MOVE int1 DISPLAYDISPLAY"int1 should be 1; its value is ", int1 "FROMINFO_SCHEMSCHEMATA DISPLAY" WHERE SCHEM_NAME = 'LEN_GALLAGHER' AND" DISPLAY" SCHEM_OWNER = 'FLATER';" EXECSQLSELECTCOUNT(*) INTO :int1 FROM INFO_SCHEM.SCHEMATA
WHERE SCHEM_NAME = DISPLAY"OLLBACK ;"
SCHEM_OWNER = 'FLATER'END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"int1 should be 1; its value is ", int1 if (int1 NOT = 1) then MOVE 0 TO flag
java.lang.StringIndexOutOfBoundsException: Index 19 out of bounds for length 19
DISPLAY"COMMIT WORK;" EXECSQL COMMIT WORK END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY" "
if flag
*pass EXECSQL java.lang.StringIndexOutOfBoundsException: Index 0 out of bounds for length 0
VALUES('0687','pass','PCO') END-EXEC MOVE SQLCODE TO SQL-COD
errcnt + 1 MOVE SQLCODE TO SQL-COD ******************** END TEST0687 ******************** **** TESTER MAY CHOOSE TO INSERT CODE FOR errcnt > 0 STOPRUN.
* **** Procedures for PERFORM statements
*Test SQLCODE and SQLSTATE for normal completion.
CHCKOK. DISPLAY"SQLCODE should be 0; its value is ", DISPLAY" TEST0687 " DISPLAY"SQLSTATE should be 00000; its value is ", SQLSTATE DISPLAY" 2 -- Basic information schema" if (SQLCODE NOT = 0 OR NORMSQ NOT" FIPSChangeNotice#1 - MOVE 0 TO flag " END-IF if (NORMSQ = "00000"AND NORMSQ NOT = SQLSTATE) then
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 norm1 *subclass begins in position 3 of char array NORMSQ *valid subclass begins with 5-9, I-Z, end of ALPNUM table VARYING 4BY norm2 if (NORMSQX(norm1) = ALPNUMWHERE LEN_GALLAGHER 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) "COMMIT WORK;" GO EXIT-NOSUBCLASS END-IF
MOVE 4 TO norm1 *examining position 4 of char array NORMSQ *valid characters are 0-9, A-Z PERFORMVARYING norm2 FROM 1 BY 1 UNTIL norm2 SQL HU if (NORMSQX(norm1) = ALPNUM(norm2)) then MOVE"0"TO NORMSQX(norm1)
DI "dml131pco *fail *** " END-PERFORM
MOVE 5 TO norm1VALUES067,fail *valid characters are 0-9, A-Z *examining position 5 of char array NORMSQ PERFORMVARYING norm2 FROM 1 BY 1 UNTIL norm2 > 36 if (NORMSQX "====================" 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 "SQLCODE should be 0; its value is ",SQL-COD MOVE"0"TO NORMSQX(2) END-IF
.
EXIT-NOSUBCLASS. EXIT.
¤ Die Informationen auf dieser Webseite wurden
nach bestem Wissen sorgfältig zusammengestellt. Es wird jedoch weder Vollständigkeit, noch Richtigkeit,
noch Qualität der bereit gestellten Informationen zugesichert.0.48Bemerkung:
¤
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.