* Standard COBOL (file "YTS765.SCO") calling SQL * procedures in file "YTS765.MCO".
*Copyright 1996 National Computing Centre Ltd, *and Computer Logic R&D S.A *on behalf of CTS5 SQL2 Project. *All rights reserved. *The CTS5 SQL2 Project is sponsored by the European Community. * *The National Computing Centre Limited and Computer Logic R&D *have given permission to NIST to distribute this program *over the World Wide Web in order to promote SQL standards. *DISCLAIMER: *This program was reviewed by employees of NIST for *conformance to the SQL standards. *NIST assumes no responsibility for any party's use of *this program.
**************************************************************** * * COMMENT SECTION * * SQL VALIDATION TEST SUITE V6.0 * * YTS765.SCO * WRITTEN BY: Susan Watters * TRANSLATED AUTOMATICALLY FROM EMBEDDED COBOL BY CHRIS SCHANZLE * * Access to TABLE_CONSTRAINTS view * a) Explicit table constraints in TABLE_CONSTRAINTS view * b) Column constraints in TABLE_CONSTRAINTS view * c) Unique identification in TABLE_CONSTRAINTS view * * * REFERENCES * 21.2.13 TABLE_CONSTRAINTS view * 21.3.13 TABLE_CONSTRAINTS base table * F#33 Constraint tables * F#2 Basic information schema * * a) 11.6 SR.1 <table constraint definition> * 11.6 GR.2 * 11.7 <unique constraint definition> * 11.8 <referential constraint definition> * 11.9 <check constraint definition> * * b) 11.4 <column definition> * 11.4 SR.9 * 11.7 <unique constraint definition> * 11.8 <referential constraint definition> * 11.9 <check constraint definition> * *c) 10.6 <constraint name definition> and * <constraint attributes> * 10.6 SR.2 * 11.6 SR.2 * * DATE LAST ALTERED 02.01.96 CTS5 Hand-over Test * 6th December 1995. Derived table replaced as requires FULL * SQL. * * QA Status: Full FC * * Revised by DWF 1996-03-08 * Added rollback after authid * Fixed identifiers in info schem * More output * Removed EXEC SQL from inside printf * Fixed syntax error ****************************************************************
DISPLAY"SELECT CONSTRAINT_SCHEMA, TABLE_SCHEMA,
- " IS_DEFERRABLE," DISPLAY"INITIALLY_DEFERRED INTO :consch, :tabsch,
- " :isdef,:init" DISPLAY"FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS" DISPLAY"WHERE TABLE_NAME = 'WORKS3A' AND" DISPLAY"CONSTRAINT_TYPE = 'FOREIGN KEY';" * EXEC SQL SELECT CONSTRAINT_SCHEMA, TABLE_SCHEMA, * IS_DEFERRABLE, INITIALLY_DEFERRED * INTO :consch, :tabsch, :isdef, :init * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS * WHERE TABLE_NAME = 'WORKS3A' AND * CONSTRAINT_TYPE = 'FOREIGN KEY'; CALL"SUB3"USING SQLCODE SQLSTATE consch tabsch isdef init MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"consch is ", consch DISPLAY"tabsch should be CTS1; its value is ", tabsch if (tabsch NOT = "CTS1") then MOVE 0 TO flag END-IF DISPLAY"init should be NO; its value is ", init if (init NOT = "NO") then MOVE 0 TO flag END-IF DISPLAY"isdef should be NO; its value is ", isdef if (isdef NOT = "NO") then MOVE 0 TO flag END-IF
DISPLAY"SELECT CONSTRAINT_SCHEMA, TABLE_SCHEMA,
- " IS_DEFERRABLE," DISPLAY"INITIALLY_DEFERRED INTO :consch, :tabsch,
- " :isdef,:init" DISPLAY"FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS" DISPLAY"WHERE TABLE_NAME = 'WORKS' AND CONSTRAINT_TYPE =
- " 'UNIQUE';" * EXEC SQL SELECT CONSTRAINT_SCHEMA, TABLE_SCHEMA, * IS_DEFERRABLE, INITIALLY_DEFERRED * INTO :consch, :tabsch, :isdef, :init * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS * WHERE TABLE_NAME = 'WORKS' AND CONSTRAINT_TYPE = 'UNIQUE' * ; CALL"SUB4"USING SQLCODE SQLSTATE consch tabsch isdef init MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"consch is ", consch DISPLAY"tabsch should be CTS1; its value is ", tabsch if (tabsch NOT = "CTS1") then MOVE 0 TO flag END-IF DISPLAY"init should be NO; its value is ", init if (init NOT = "NO") then MOVE 0 TO flag END-IF DISPLAY"isdef should be NO; its value is ", isdef if (isdef NOT = "NO") then MOVE 0 TO flag END-IF
DISPLAY"SELECT CONSTRAINT_SCHEMA, TABLE_SCHEMA,
- " IS_DEFERRABLE," DISPLAY"INITIALLY_DEFERRED INTO :consch, :tabsch,
- " :isdef,:init" DISPLAY"FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS" DISPLAY"WHERE TABLE_NAME = 'STAFF7' AND" DISPLAY"CONSTRAINT_TYPE = 'PRIMARY KEY';" * EXEC SQL SELECT CONSTRAINT_SCHEMA, TABLE_SCHEMA, * IS_DEFERRABLE, INITIALLY_DEFERRED * INTO :consch, :tabsch, :isdef, :init * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS * WHERE TABLE_NAME = 'STAFF7' AND CONSTRAINT_TYPE = * 'PRIMARY KEY'; CALL"SUB5"USING SQLCODE SQLSTATE consch tabsch isdef init MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"consch is ", consch DISPLAY"tabsch should be CTS1; its value is ", tabsch if (tabsch NOT = "CTS1") then MOVE 0 TO flag END-IF DISPLAY"init should be NO; its value is ", init if (init NOT = "NO") then MOVE 0 TO flag END-IF DISPLAY"isdef should be NO; its value is ", isdef if (isdef NOT = "NO") then MOVE 0 TO flag END-IF
DISPLAY"SELECT CONSTRAINT_SCHEMA, TABLE_SCHEMA,
- " IS_DEFERRABLE," DISPLAY"INITIALLY_DEFERRED INTO :consch, :tabsch,
- " :isdef,:init" DISPLAY"FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS" DISPLAY"WHERE TABLE_NAME = 'STAFF7' AND CONSTRAINT_TYPE =
- " 'CHECK';" * EXEC SQL SELECT CONSTRAINT_SCHEMA, TABLE_SCHEMA, * IS_DEFERRABLE, INITIALLY_DEFERRED * INTO :consch, :tabsch, :isdef, :init * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS * WHERE TABLE_NAME = 'STAFF7' AND CONSTRAINT_TYPE = 'CHECK' * ; CALL"SUB6"USING SQLCODE SQLSTATE consch tabsch isdef init MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"consch is ", consch DISPLAY"tabsch should be CTS1; its value is ", tabsch if (tabsch NOT = "CTS1") then MOVE 0 TO flag END-IF DISPLAY"init should be NO; its value is ", init if (init NOT = "NO") then MOVE 0 TO flag END-IF DISPLAY"isdef should be NO; its value is ", isdef if (isdef NOT = "NO") then MOVE 0 TO flag END-IF
DISPLAY"SELECT CONSTRAINT_SCHEMA, TABLE_SCHEMA,
- " IS_DEFERRABLE," DISPLAY"INITIALLY_DEFERRED INTO :consch, :tabsch,
- " :isdef,:init" DISPLAY"FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS" DISPLAY"WHERE TABLE_NAME = 'STAFF1' AND" DISPLAY"CONSTRAINT_TYPE = 'CHECK';" * EXEC SQL SELECT CONSTRAINT_SCHEMA, TABLE_SCHEMA, * IS_DEFERRABLE, INITIALLY_DEFERRED * INTO :consch, :tabsch, :isdef, :init * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS * WHERE TABLE_NAME = 'STAFF1' AND CONSTRAINT_TYPE = 'CHECK' * ; CALL"SUB11"USING SQLCODE SQLSTATE consch tabsch isdef
init MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"consch is ", consch DISPLAY"tabsch should be CTS1; its value is ", tabsch if (tabsch NOT = "CTS1") then MOVE 0 TO flag END-IF DISPLAY"init should be NO; its value is ", init if (init NOT = "NO") then MOVE 0 TO flag END-IF DISPLAY"isdef should be NO; its value is ", isdef if (isdef NOT = "NO") then MOVE 0 TO flag END-IF
DISPLAY"SELECT CONSTRAINT_SCHEMA, TABLE_SCHEMA,
- " IS_DEFERRABLE," DISPLAY"INITIALLY_DEFERRED INTO :consch, :tabsch,
- " :isdef,:init" DISPLAY"FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS" DISPLAY"WHERE TABLE_NAME = 'STAFF' AND" DISPLAY"CONSTRAINT_TYPE = 'UNIQUE';" * EXEC SQL SELECT CONSTRAINT_SCHEMA, TABLE_SCHEMA, * IS_DEFERRABLE, INITIALLY_DEFERRED * INTO :consch, :tabsch, :isdef, :init * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS * WHERE TABLE_NAME = 'STAFF' AND CONSTRAINT_TYPE = 'UNIQUE' * ; CALL"SUB12"USING SQLCODE SQLSTATE consch tabsch isdef
init MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"consch is ", consch DISPLAY"tabsch should be CTS1; its value is ", tabsch if (tabsch NOT = "CTS1") then MOVE 0 TO flag END-IF DISPLAY"init should be NO; its value is ", init if (init NOT = "NO") then MOVE 0 TO flag END-IF DISPLAY"isdef should be NO; its value is ", isdef if (isdef NOT = "NO") then MOVE 0 TO flag END-IF
*check references to FOREIGN KEY column constraint
DISPLAY"SELECT CONSTRAINT_SCHEMA, TABLE_SCHEMA,
- " IS_DEFERRABLE," DISPLAY"INITIALLY_DEFERRED INTO :consch, :tabsch,
- " :isdef,:init" DISPLAY"FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS" DISPLAY"WHERE TABLE_NAME = 'STAFFZ' AND" DISPLAY"CONSTRAINT_TYPE = 'FOREIGN KEY';" * EXEC SQL SELECT CONSTRAINT_SCHEMA, TABLE_SCHEMA, * IS_DEFERRABLE, INITIALLY_DEFERRED * INTO :consch, :tabsch, :isdef, :init * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS * WHERE TABLE_NAME = 'STAFFZ' AND CONSTRAINT_TYPE = * 'FOREIGN KEY'; CALL"SUB13"USING SQLCODE SQLSTATE consch tabsch isdef
init MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"consch is ", consch DISPLAY"tabsch should be CTS1; its value is ", tabsch if (tabsch NOT = "CTS1") then MOVE 0 TO flag END-IF DISPLAY"init should be NO; its value is ", init if (init NOT = "NO") then MOVE 0 TO flag END-IF DISPLAY"isdef should be NO; its value is ", isdef if (isdef NOT = "NO") then MOVE 0 TO flag END-IF
DISPLAY"SELECT CONSTRAINT_SCHEMA, TABLE_SCHEMA,
- " IS_DEFERRABLE," DISPLAY"INITIALLY_DEFERRED INTO :consch, :tabsch,
- " :isdef,:init" DISPLAY"FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS" DISPLAY"WHERE TABLE_NAME = 'STAFFZ' AND" DISPLAY"CONSTRAINT_TYPE = 'CHECK';" * EXEC SQL SELECT CONSTRAINT_SCHEMA, TABLE_SCHEMA, * IS_DEFERRABLE, INITIALLY_DEFERRED * INTO :consch, :tabsch, :isdef, :init * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS * WHERE TABLE_NAME = 'STAFFZ' AND CONSTRAINT_TYPE = 'CHECK' * ; CALL"SUB14"USING SQLCODE SQLSTATE consch tabsch isdef
init MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"consch is ", consch DISPLAY"tabsch should be CTS1; its value is ", tabsch if (tabsch NOT = "CTS1") then MOVE 0 TO flag END-IF DISPLAY"init should be NO; its value is ", init if (init NOT = "NO") then MOVE 0 TO flag END-IF DISPLAY"isdef should be NO; its value is ", isdef if (isdef NOT = "NO") then MOVE 0 TO flag END-IF
*determine that values in CONSTRAINT_CATALOG are not NULL
MOVE 99 TO co DISPLAY"SELECT COUNT (*) INTO :co " DISPLAY"FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS" DISPLAY"WHERE CONSTRAINT_CATALOG IS NULL;" * EXEC SQL SELECT COUNT (*) INTO :co * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS * WHERE CONSTRAINT_CATALOG IS NULL; CALL"SUB19"USING SQLCODE SQLSTATE co MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"co should be 0; its value is ", co if (co NOT = 0) then MOVE 0 TO flag END-IF
*determine that values in CONSTRAINT_SCHEMA are not NULL
MOVE 99 TO co DISPLAY"SELECT COUNT (*) INTO :co" DISPLAY"FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS" DISPLAY"WHERE CONSTRAINT_SCHEMA IS NULL;" * EXEC SQL SELECT COUNT (*) INTO :co * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS * WHERE CONSTRAINT_SCHEMA IS NULL; CALL"SUB20"USING SQLCODE SQLSTATE co MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"co should be 0; its value is ", co if (co NOT = 0) then MOVE 0 TO flag END-IF
*determine that values in CONSTRAINT_NAME are not NULL
MOVE 99 TO co DISPLAY"SELECT COUNT (*) INTO :co" DISPLAY"FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS" DISPLAY"WHERE CONSTRAINT_NAME IS NULL;" * EXEC SQL SELECT COUNT (*) INTO :co * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS * WHERE CONSTRAINT_NAME IS NULL; CALL"SUB21"USING SQLCODE SQLSTATE co MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"co should be 0; its value is ", co if (co NOT = 0) then MOVE 0 TO flag END-IF
*determine that CONSTRAINT_CATALOG,CONSTRAINT_NAME,CONSTRAINT_SC *contain a different set of values in each row
*initialise host variables
COMPUTE co = -1 COMPUTE co2 = -1
COMPUTE SQLCODE = -1 MOVE"xxxxx"TO SQLSTATE
DISPLAY"SELECT COUNT (*) INTO :co" DISPLAY"FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS;" * EXEC SQL SELECT COUNT (*) INTO :co * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS; CALL"SUB22"USING SQLCODE SQLSTATE co MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK DISPLAY" "
COMPUTE SQLCODE = -1 MOVE"xxxxx"TO SQLSTATE
DISPLAY"SELECT COUNT (*) INTO :co2" DISPLAY"FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS T" DISPLAY"WHERE" DISPLAY"UNIQUE (" DISPLAY" SELECT * FROM
- " INFORMATION_SCHEMA.TABLE_CONSTRAINTS" DISPLAY" WHERE CONSTRAINT_CATALOG =
- " T.CONSTRAINT_CATALOG AND" DISPLAY" CONSTRAINT_NAME = T.CONSTRAINT_NAME AND" DISPLAY" CONSTRAINT_SCHEMA = T.CONSTRAINT_SCHEMA" DISPLAY" );"
* EXEC SQL SELECT COUNT (*) INTO :co2 * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS T * WHERE * UNIQUE ( * SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS * WHERE CONSTRAINT_CATALOG = T.CONSTRAINT_CATALOG AND * CONSTRAINT_NAME = T.CONSTRAINT_NAME AND * CONSTRAINT_SCHEMA = T.CONSTRAINT_SCHEMA * ); CALL"SUB23"USING SQLCODE SQLSTATE co2 MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK DISPLAY"co should be equal to co2; co is ", co, ", co2 is
- " ", co2 if (co NOT = co2) then MOVE 0 TO flag END-IF
* EXEC SQL COMMIT WORK; CALL"SUB27"USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD ******************** END TEST7539 ********************
**** 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 ", 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 PERFORMVARYING 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 GOTO 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 > 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 PERFORMVARYING 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.24 Sekunden
(vorverarbeitet)
¤
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.
