|
IDENTIFICATION DIVISION.
PROGRAM-ID. YTS805.
ENVIRONMENT DIVISION.
CONFIGURATION SECTION.
SOURCE-COMPUTER. xyz.
OBJECT-COMPUTER. xyz.
DATA DIVISION.
WORKING-STORAGE SECTION.
* Standard COBOL (file "YTS805.SCO") calling SQL
* procedures in file "YTS805.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
*
* YTS805.SCO
* WRITTEN BY: Susan Watters
* TRANSLATED AUTOMATICALLY FROM EMBEDDED COBOL BY CHRIS SCHANZLE
*
* This routine tests Schema with crossed referential
* constraints between tables
*
*
* REFERENCES
* 11.1 -- <schema definition>
* 11.8 F -- <referential constraint definition>
* 11.8 SR2
* 11.7 SR2b -- <unique constraint definition>
* F#17 -- Multiple schemas per user
* F#31 -- Schema definition statement
* F#33 -- Constraint tables
*
* DATE LAST ALTERED 02.01.96 CTS5 Hand-over Test
*
* QA Status: Full FC
*
* Revised by DWF 1996-03-27
* Added rollback after authid
* Removed EXEC SQL from printf
* Fixed wrong printf formats
* Fixed information schema usage
****************************************************************
* EXEC SQL BEGIN DECLARE SECTION END-EXEC
01 SQLCODE PIC S9(9) COMP.
01 SQLSTATE PIC X(5).
01 co PIC S9(4) DISPLAY SIGN LEADING SEPARATE.
01 uid PIC X(18).
01 uidx PIC X(18).
* 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 "CTS1 " TO uid
CALL "AUTHID" USING uid
MOVE "not logged in, not" TO uidx
* EXEC SQL SELECT USER INTO :uidx FROM CTS1.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, yts805.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 TEST7562 *******************
MOVE 1 TO flag
DISPLAY " TEST7562 "
DISPLAY "Schema with crossed referential const. bet.
- " tables"
DISPLAY "References:"
DISPLAY " 11.1 -- "
DISPLAY " 11.8F -- "
DISPLAY " 11.8 SR2"
DISPLAY " 11.7 SR2b -- "
DISPLAY " F#17 -- Multiple schemas per user"
DISPLAY " F#31 -- Schema definition statement"
DISPLAY " F#33 -- Constraint tables"
DISPLAY " - - - - - - - - - - - - - - - - - - -"
COMPUTE SQLCODE = -1
MOVE "xxxxx" TO SQLSTATE
*create schema ZZZ
DISPLAY "CREATE SCHEMA ZZZ AUTHORIZATION CTS1"
DISPLAY " CREATE TABLE AAA"
DISPLAY " (A1 CHAR(3),"
DISPLAY " A2 INTEGER,"
DISPLAY " A3 DECIMAL(4),"
DISPLAY " A4 NUMERIC(5),"
DISPLAY " A5 SMALLINT,"
DISPLAY " PRIMARY KEY (A1, A2),"
DISPLAY " FOREIGN KEY (A3, A4) REFERENCES BBB(B1, B2),"
DISPLAY " FOREIGN KEY (A5) REFERENCES CCC(C3))"
DISPLAY " CREATE TABLE BBB"
DISPLAY " (B1 DECIMAL(4),"
DISPLAY " B2 NUMERIC(5),"
DISPLAY " A1 CHAR(3),"
DISPLAY " A2 INTEGER,"
DISPLAY " PRIMARY KEY (B1, B2),"
DISPLAY " FOREIGN KEY (A1, A2) REFERENCES AAA(A1, A2),"
DISPLAY " UNIQUE (A1, A2))"
DISPLAY " CREATE TABLE CCC"
DISPLAY " (B1 DECIMAL(4),"
DISPLAY " B2 NUMERIC(5),"
DISPLAY " C3 SMALLINT,"
DISPLAY " PRIMARY KEY (C3),"
DISPLAY " FOREIGN KEY (B1,B2) REFERENCES BBB(B1, B2));"
* EXEC SQL CREATE SCHEMA ZZZ AUTHORIZATION CTS1
* CREATE TABLE AAA
* (A1 CHAR(3),
* A2 INTEGER,
* A3 DECIMAL(4),
* A4 NUMERIC(5),
* A5 SMALLINT,
* PRIMARY KEY (A1, A2),
* FOREIGN KEY (A3, A4) REFERENCES BBB(B1, B2),
* FOREIGN KEY (A5) REFERENCES CCC(C3))
* CREATE TABLE BBB
* (B1 DECIMAL(4),
* B2 NUMERIC(5),
* A1 CHAR(3),
* A2 INTEGER,
* PRIMARY KEY (B1, B2),
* FOREIGN KEY (A1, A2) REFERENCES AAA(A1, A2),
* UNIQUE (A1, A2))
* CREATE TABLE CCC
* (B1 DECIMAL(4),
* B2 NUMERIC(5),
* C3 SMALLINT,
* PRIMARY KEY (C3),
* FOREIGN KEY (B1,B2) REFERENCES BBB(B1, B2));
CALL "SUB3" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
*COMMIT WORK
DISPLAY "COMMIT WORK;"
* EXEC SQL COMMIT WORK;
CALL "SUB4" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
*check a row referencing ZZZ appears in the Schemata view
*expect co to be 0 so initialise to some other value
MOVE 99 TO co
DISPLAY "SELECT COUNT (*) INTO :co"
DISPLAY "FROM INFORMATION_SCHEMA.SCHEMATA"
DISPLAY "WHERE SCHEMA_NAME = 'ZZZ';"
* EXEC SQL SELECT COUNT (*) INTO :co
* FROM INFORMATION_SCHEMA.SCHEMATA
* WHERE SCHEMA_NAME = 'ZZZ';
CALL "SUB5" USING SQLCODE SQLSTATE co
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "co should be 1; its value is ", co
if (co NOT = 1) then
MOVE 0 TO flag
END-IF
*check TABLE_CONSTRAINTS view references constraints in ZZZ
MOVE 99 TO co
DISPLAY "SELECT COUNT (*) INTO :co"
DISPLAY "FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS"
DISPLAY "WHERE TABLE_SCHEMA = 'ZZZ' AND"
DISPLAY "TABLE_NAME = 'AAA' AND"
DISPLAY "CONSTRAINT_TYPE = 'FOREIGN KEY';"
* EXEC SQL SELECT COUNT (*) INTO :co
* FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
* WHERE TABLE_SCHEMA = 'ZZZ' AND
* TABLE_NAME = 'AAA' AND
* CONSTRAINT_TYPE = 'FOREIGN KEY';
CALL "SUB6" USING SQLCODE SQLSTATE co
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "co should be 2; its value is ", co
if (co NOT = 2) then
MOVE 0 TO flag
END-IF
MOVE 99 TO co
DISPLAY "SELECT COUNT (*) INTO :co"
DISPLAY "FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS"
DISPLAY "WHERE TABLE_SCHEMA = 'ZZZ' AND"
DISPLAY "TABLE_NAME = 'BBB' AND"
DISPLAY "CONSTRAINT_TYPE = 'FOREIGN KEY';"
* EXEC SQL SELECT COUNT (*) INTO :co
* FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
* WHERE TABLE_SCHEMA = 'ZZZ' AND
* TABLE_NAME = 'BBB' AND
* CONSTRAINT_TYPE = 'FOREIGN KEY';
CALL "SUB7" USING SQLCODE SQLSTATE co
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "co should be 1; its value is ", co
if (co NOT = 1) then
MOVE 0 TO flag
END-IF
MOVE 99 TO co
DISPLAY "SELECT COUNT (*) INTO :co"
DISPLAY "FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS"
DISPLAY "WHERE TABLE_SCHEMA = 'ZZZ' AND"
DISPLAY "TABLE_NAME = 'BBB' AND"
DISPLAY "CONSTRAINT_TYPE = 'UNIQUE';"
* EXEC SQL SELECT COUNT (*) INTO :co
* FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
* WHERE TABLE_SCHEMA = 'ZZZ' AND
* TABLE_NAME = 'BBB' AND
* CONSTRAINT_TYPE = 'UNIQUE';
CALL "SUB8" USING SQLCODE SQLSTATE co
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "co should be 1; its value is ", co
if (co NOT = 1) then
MOVE 0 TO flag
END-IF
MOVE 99 TO co
DISPLAY "SELECT COUNT (*) INTO :co"
DISPLAY "FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS"
DISPLAY "WHERE TABLE_SCHEMA = 'ZZZ' AND"
DISPLAY "TABLE_NAME = 'CCC' AND"
DISPLAY "CONSTRAINT_TYPE = 'FOREIGN KEY';"
* EXEC SQL SELECT COUNT (*) INTO :co
* FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
* WHERE TABLE_SCHEMA = 'ZZZ' AND
* TABLE_NAME = 'CCC' AND
* CONSTRAINT_TYPE = 'FOREIGN KEY';
CALL "SUB9" USING SQLCODE SQLSTATE co
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "co should be 1; its value is ", co
if (co NOT = 1) then
MOVE 0 TO flag
END-IF
DISPLAY "INSERT INTO ZZZ.AAA VALUES ('sue',23,45,67,89);"
* EXEC SQL INSERT INTO ZZZ.AAA values ('sue',23,45,67,89)
* ;
CALL "SUB10" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
*That should generate an integrity constraint violation
*which is SQLSTATE 23000
DISPLAY "SQLCODE should be < 0; its value is ", SQL-COD
if (SQLCODE NOT < 0) then
MOVE 0 TO flag
END-IF
DISPLAY "SQLSTATE should be 23000; its value is ", SQLSTATE
PERFORM NOSUBCLASS THRU EXIT-NOSUBCLASS
if (NORMSQ NOT = "23000") then
MOVE 0 TO flag
END-IF
if (NORMSQ = "23000" AND NORMSQ NOT = SQLSTATE)
then
DISPLAY "Valid implementation-defined SQLSTATE accepted."
END-IF
DISPLAY " "
DISPLAY "INSERT INTO ZZZ.BBB VALUES (53,778,'mat',1);"
* EXEC SQL INSERT INTO ZZZ.BBB VALUES (53,778,'mat',1)
* ;
CALL "SUB11" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
*That should generate an integrity constraint violation
*which is SQLSTATE 23000
DISPLAY "SQLCODE should be < 0; its value is ", SQL-COD
if (SQLCODE NOT < 0) then
MOVE 0 TO flag
END-IF
DISPLAY "SQLSTATE should be 23000; its value is ", SQLSTATE
PERFORM NOSUBCLASS THRU EXIT-NOSUBCLASS
if (NORMSQ NOT = "23000") then
MOVE 0 TO flag
END-IF
if (NORMSQ = "23000" AND NORMSQ NOT = SQLSTATE)
then
DISPLAY "Valid implementation-defined SQLSTATE accepted."
END-IF
DISPLAY " "
DISPLAY "INSERT INTO ZZZ.CCC VALUES (456,231,5);"
* EXEC SQL INSERT INTO ZZZ.CCC VALUES (456,231,5);
CALL "SUB12" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
*That should generate an integrity constraint violation
*which is SQLSTATE 23000
DISPLAY "SQLCODE should be < 0; its value is ", SQL-COD
if (SQLCODE NOT < 0) then
MOVE 0 TO flag
END-IF
DISPLAY "SQLSTATE should be 23000; its value is ", SQLSTATE
PERFORM NOSUBCLASS THRU EXIT-NOSUBCLASS
if (NORMSQ NOT = "23000") then
MOVE 0 TO flag
END-IF
if (NORMSQ = "23000" AND NORMSQ NOT = SQLSTATE)
then
DISPLAY "Valid implementation-defined SQLSTATE accepted."
END-IF
DISPLAY " "
*insert a row into AAA with non-null values in A1 & A2
DISPLAY "INSERT INTO ZZZ.AAA VALUES ('sue',25,NULL, NULL,
- " NULL);"
* EXEC SQL INSERT INTO ZZZ.AAA VALUES ('sue',25, NULL, NULL,
* NULL);
CALL "SUB13" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
*insert a row into BBB with non-null vals in all cols
*and vals in A1 and A2 = to AAA.A1, AAA.A2
DISPLAY "INSERT INTO ZZZ.BBB VALUES (345,8441,'sue',25);"
* EXEC SQL INSERT INTO ZZZ.BBB VALUES (345,8441,'sue',25)
* ;
CALL "SUB14" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
*Insert into CCC wih non-null vals in all cols
*B1 = BBB.B1 B2 = BBB.B2
DISPLAY "INSERT INTO ZZZ.CCC VALUES (345, 8441, 1);"
* EXEC SQL INSERT INTO ZZZ.CCC VALUES (345, 8441, 1);
CALL "SUB15" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
*insert a row into AAA with non-null vals in all cols
*A3 = BBB.B1, A4 = BBB.B2, a5 = CCC.c3
DISPLAY "INSERT INTO ZZZ.AAA VALUES ('ben',22,345,8441,1);"
* EXEC SQL INSERT INTO ZZZ.AAA VALUES ('ben',22,345,8441,1)
* ;
CALL "SUB16" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "ROLLBACK WORK;"
* EXEC SQL ROLLBACK WORK;
CALL "SUB17" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "DROP SCHEMA ZZZ CASCADE;"
* EXEC SQL DROP SCHEMA ZZZ CASCADE;
CALL "SUB18" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
* EXEC SQL COMMIT WORK;
CALL "SUB19" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
if ( flag = 1 ) then
DISPLAY " yts805.mco *** pass *** "
* EXEC SQL INSERT INTO CTS1.TESTREPORT
* VALUES('7562','pass','MCO');
CALL "SUB20" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
else
DISPLAY " yts805.mco *** fail *** "
* EXEC SQL INSERT INTO CTS1.TESTREPORT
* VALUES('7562','fail','MCO');
CALL "SUB21" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
COMPUTE errcnt = errcnt + 1
END-IF
DISPLAY "==============================================="
* EXEC SQL COMMIT WORK;
CALL "SUB22" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
******************** END TEST7562 ********************
**** 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.22 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.
|
