| products/sources/formale sprachen/Cobol/Test-Suite/SQL M/ |
|
Quellcode-Bibliothek
© Kompilation durch diese Firma
[Weder Korrektheit noch Funktionsfähigkeit der Software werden zugesichert.]
Datei: dml009.cob Sprache: Unknown
|
IDENTIFICATION DIVISION.
PROGRAM-ID. CDR008. ENVIRONMENT DIVISION. CONFIGURATION SECTION. SOURCE-COMPUTER. xyz. OBJECT-COMPUTER. xyz. DATA DIVISION. WORKING-STORAGE SECTION. * Standard COBOL (file "CDR008.SCO") calling SQL * procedures in file "CDR008.MCO" **************************************************************** * * COMMENT SECTION * * DATE 1989/04/07 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. * * CDR008.SCO * WRITTEN BY: SUN DAJUN * TRANSLATED AUTOMATICALLY FROM EMBEDDED COBOL BY CHRIS SCHANZLE * * THIS ROUTINE TESTS THE REFERENTIAL INTEGRITY CONSTRAINT FOR * SQL. * * REFERENCES * AMERICAN NATIONAL STANDARD database language - SQL * with Integrity Enhancement * * SECTION 6.7 <referential constraint definition> * <referential constraint definition>::= * FOREIGN KEY (<referencing columns>) * <references specification> * <references specification>::= * REFERENCES <referenced table and columns> * <referencing columns>::= * <reference column list> * <refereced table and columns>::= * <table name>((<reference column list> * <reference column list>::= * <column name>({,<column name>}...) * **************************************************************** * EXEC SQL BEGIN DECLARE SECTION END-EXEC 01 count1 PIC S9(9) DISPLAY SIGN LEADING SEPARATE. 01 EMPNO1 PIC X(3). 01 SQLCODE PIC S9(9) COMP. 01 errcnt PIC S9(4) DISPLAY SIGN LEADING SEPARATE. * EXEC SQL END DECLARE SECTION END-EXEC 01 code1 PIC S9(9) DISPLAY SIGN LEADING SEPARATE. 01 uid PIC X(18). 01 uidx PIC X(18). 01 SQL-COD PIC S9(9) DISPLAY SIGN LEADING SEPARATE. * date_time declaration * 01 TO-DAY PIC 9(6). 01 THE-TIME PIC 9(8). PROCEDURE DIVISION. P0. MOVE "SUN" TO uid CALL "AUTHID" USING uid MOVE "not logged in, not" TO uidx CALL "AUTHCK" USING SQLCODE uidx MOVE SQLCODE TO SQL-COD if (uid NOT = uidx) then DISPLAY "ERROR: User " uid " expected." DISPLAY "User " uidx " connected." DISPLAY " " STOP RUN END-IF MOVE 0 TO errcnt DISPLAY "SQL Test Suite, V6.0, Module COBOL, cdr008.sco" DISPLAY " " DISPLAY "59-byte ID" DISPLAY "TEd Version #" DISPLAY " " * date_time print * ACCEPT TO-DAY FROM DATE ACCEPT THE-TIME FROM TIME DISPLAY "Date run YYMMDD: " TO-DAY " at hhmmssff: " THE-TIME ******************** BEGIN TEST0323 ******************* *This test inserts a row (E1,P1,40) in FOREIGN KEY *TABLE -- WORKS3, there have been such value (*,P1,*) *in WORKS3 DISPLAY " TEST0323 " DISPLAY " PRIMARY KEY & FOREIGN KEY " DISPLAY " SQL with Integrity Enhancement section - " 6.5,6.6,6.7" DISPLAY " - - - - - - - - - - - - - - - - - - - - - - - - " DISPLAY " " DISPLAY " DELETE FROM WORKS3;" DISPLAY " " DISPLAY " DELETE FROM STAFF3;" DISPLAY " " DISPLAY " DELETE FROM PROJ3;" DISPLAY " " DISPLAY " INSERT INTO PROJ3" DISPLAY " VALUES - " ('P1','MASS','Design',10000,'Deale');" DISPLAY " " DISPLAY " INSERT INTO STAFF3" DISPLAY " SELECT * FROM STAFF;" DISPLAY " " DISPLAY " INSERT INTO WORKS3" DISPLAY " VALUES ('E1','P1',40);" DISPLAY " " DISPLAY " INSERT INTO WORKS3" DISPLAY " VALUES ('E2','P1',40);" DISPLAY " " DISPLAY " SELECT COUNT(*) INTO:count1" DISPLAY " FROM WORKS3;" * EXEC SQL DELETE FROM WORKS3; CALL "SUB1" USING SQLCODE MOVE SQLCODE TO SQL-COD * EXEC SQL DELETE FROM STAFF3; CALL "SUB2" USING SQLCODE MOVE SQLCODE TO SQL-COD * EXEC SQL DELETE FROM PROJ3; CALL "SUB3" USING SQLCODE MOVE SQLCODE TO SQL-COD * EXEC SQL INSERT INTO PROJ3 * VALUES ('P1','MASS','Design',10000,'Deale') END-EXEC CALL "SUB4" USING SQLCODE MOVE SQLCODE TO SQL-COD * EXEC SQL INSERT INTO STAFF3 * SELECT * FROM STAFF END-EXEC CALL "SUB5" USING SQLCODE MOVE SQLCODE TO SQL-COD * EXEC SQL INSERT INTO WORKS3 * VALUES ('E1','P1',40) END-EXEC CALL "SUB6" USING SQLCODE MOVE SQLCODE TO SQL-COD * EXEC SQL INSERT INTO WORKS3 * VALUES ('E2','P1',40) END-EXEC CALL "SUB7" USING SQLCODE MOVE SQLCODE TO SQL-COD MOVE SQLCODE TO code1 * EXEC SQL SELECT COUNT(*) INTO:count1 * FROM WORKS3 END-EXEC CALL "SUB8" USING SQLCODE count1 MOVE SQLCODE TO SQL-COD DISPLAY " The correct answer is :" DISPLAY " SQLCODE is equal to 0." DISPLAY " count1 is 2" DISPLAY " Your answer is :" DISPLAY " SQLCOD of second insert is ", code1 DISPLAY " count1 is ", count1 if (code1 = 0 AND count1 = 2) then * EXEC SQL INSERT INTO TESTREPORT * VALUES('0323','pass','MCO') END-EXEC CALL "SUB9" USING SQLCODE MOVE SQLCODE TO SQL-COD DISPLAY " *** pass *** " else * EXEC SQL INSERT INTO TESTREPORT * VALUES('0323','fail','MCO') END-EXEC ADD 1 TO errcnt CALL "SUB10" USING SQLCODE MOVE SQLCODE TO SQL-COD DISPLAY " cdr008.sco *** fail *** " END-IF DISPLAY "================================================" DISPLAY " " * EXEC SQL COMMIT WORK; CALL "SUB11" USING SQLCODE MOVE SQLCODE TO SQL-COD ************************ END TEST0323 ************************ ******************** BEGIN TEST0324 ******************* *This test inserts rows into FOREIGN KEY *TABLE -- WORKS3, there have been such a row *(P1,-,-,-,-) in PROJ,but no (P2,-,-,-,-) in PROJ DISPLAY " TEST0324 " DISPLAY " PRIMARY KEY & FOREIGN KEY " DISPLAY " SQL with Integrity Enhancement section - " 6.5,6.6,6.7" DISPLAY " - - - - - - - - - - - - - - - - - - - - - - - - " DISPLAY " " DISPLAY " DELETE FROM WORKS3;" DISPLAY " " DISPLAY " DELETE FROM STAFF3;" DISPLAY " " DISPLAY " DELETE FROM PROJ3;" DISPLAY " " DISPLAY " INSERT INTO PROJ3" DISPLAY " VALUES - " ('P1','MASS','Design',10000,'Deale');" DISPLAY " " DISPLAY " INSERT INTO STAFF3" DISPLAY " SELECT * FROM STAFF;" DISPLAY " " DISPLAY " INSERT INTO WORKS3" DISPLAY " VALUES ('E1','P1',40);" DISPLAY " " DISPLAY " INSERT INTO WORKS3" DISPLAY " VALUES ('E2','P2',40);" DISPLAY " " DISPLAY " SELECT COUNT(*) INTO:count1" DISPLAY " FROM WORKS3" DISPLAY " WHERE PNUM = 'P2';" COMPUTE count1 = -1 * EXEC SQL DELETE FROM WORKS3; CALL "SUB12" USING SQLCODE MOVE SQLCODE TO SQL-COD * EXEC SQL DELETE FROM STAFF3; CALL "SUB13" USING SQLCODE MOVE SQLCODE TO SQL-COD * EXEC SQL DELETE FROM PROJ3; CALL "SUB14" USING SQLCODE MOVE SQLCODE TO SQL-COD * EXEC SQL INSERT INTO PROJ3 * VALUES ('P1','MASS','Design',10000,'Deale') END-EXEC CALL "SUB15" USING SQLCODE MOVE SQLCODE TO SQL-COD * EXEC SQL INSERT INTO STAFF3 * SELECT * FROM STAFF END-EXEC CALL "SUB16" USING SQLCODE MOVE SQLCODE TO SQL-COD * EXEC SQL INSERT INTO WORKS3 * VALUES ('E1','P1',40) END-EXEC CALL "SUB17" USING SQLCODE MOVE SQLCODE TO SQL-COD * EXEC SQL INSERT INTO WORKS3 * VALUES ('E2','P2',40) END-EXEC CALL "SUB18" USING SQLCODE MOVE SQLCODE TO SQL-COD MOVE SQLCODE TO code1 * EXEC SQL SELECT COUNT(*) INTO:count1 * FROM WORKS3 * WHERE PNUM = 'P2' END-EXEC CALL "SUB19" USING SQLCODE count1 MOVE SQLCODE TO SQL-COD DISPLAY " The correct answer is :" DISPLAY " SQLCODE is less than 0." DISPLAY " count1 is 0" DISPLAY " Your answer is :" DISPLAY " SQLCOD of second insert is ", code1 DISPLAY " count1 is ", count1 if (code1 < 0 AND count1 = 0) then * EXEC SQL INSERT INTO TESTREPORT * VALUES('0324','pass','MCO') END-EXEC CALL "SUB20" USING SQLCODE MOVE SQLCODE TO SQL-COD DISPLAY " *** pass *** " else * EXEC SQL INSERT INTO TESTREPORT * VALUES('0324','fail','MCO') END-EXEC ADD 1 TO errcnt CALL "SUB21" USING SQLCODE MOVE SQLCODE TO SQL-COD DISPLAY " cdr008.sco *** fail *** " END-IF DISPLAY "================================================" DISPLAY " " * EXEC SQL COMMIT WORK; CALL "SUB22" USING SQLCODE MOVE SQLCODE TO SQL-COD ************************ END TEST0324 ************************ ******************** BEGIN TEST0325 ********************* *This test deletes a row (E1,-,-,-) from PRIMARY KEY *TABLE -- STAFF3, but there are such rows (E1,*,*) in *FOREIGN KEY TABLE -- WORK3, this should be an ERROR. DISPLAY " TEST0325 " DISPLAY " REFERENTIAL INTEGRITY " DISPLAY " SQL with Integrity Enhancement section - " 6.5,6.6,6.7" DISPLAY " - - - - - - - - - - - - - - - - - - - - - " DISPLAY " " DISPLAY " DELETE FROM WORKS3;" DISPLAY " " DISPLAY " DELETE FROM PROJ3;" DISPLAY " " DISPLAY " DELETE FROM STAFF3;" DISPLAY " " DISPLAY " INSERT INTO STAFF3" DISPLAY " SELECT * FROM STAFF;" DISPLAY " " DISPLAY " INSERT INTO PROJ3" DISPLAY " SELECT * FROM PROJ;" DISPLAY " " DISPLAY " INSERT INTO WORKS3" DISPLAY " SELECT * FROM WORKS;" DISPLAY " " DISPLAY " DELETE FROM STAFF3" DISPLAY " WHERE EMPNUM='E1';" DISPLAY " " DISPLAY "code1 = SQLCODE" DISPLAY " " DISPLAY " SELECT EMPNUM INTO :EMPNO1" DISPLAY " FROM STAFF3" DISPLAY " WHERE EMPNUM = 'E1';" * EXEC SQL DELETE FROM WORKS3; CALL "SUB23" USING SQLCODE MOVE SQLCODE TO SQL-COD * EXEC SQL DELETE FROM PROJ3; CALL "SUB24" USING SQLCODE MOVE SQLCODE TO SQL-COD * EXEC SQL DELETE FROM STAFF3; CALL "SUB25" USING SQLCODE MOVE SQLCODE TO SQL-COD * EXEC SQL INSERT INTO STAFF3 * SELECT * FROM STAFF END-EXEC CALL "SUB26" USING SQLCODE MOVE SQLCODE TO SQL-COD * EXEC SQL INSERT INTO PROJ3 * SELECT * FROM PROJ END-EXEC CALL "SUB27" USING SQLCODE MOVE SQLCODE TO SQL-COD * EXEC SQL INSERT INTO WORKS3 * SELECT * FROM WORKS END-EXEC CALL "SUB28" USING SQLCODE MOVE SQLCODE TO SQL-COD * EXEC SQL DELETE FROM STAFF3 * WHERE EMPNUM='E1' END-EXEC CALL "SUB29" USING SQLCODE MOVE SQLCODE TO SQL-COD MOVE SQLCODE TO code1 MOVE "NV" TO EMPNO1 * EXEC SQL SELECT EMPNUM INTO :EMPNO1 * FROM STAFF3 * WHERE EMPNUM = 'E1' END-EXEC CALL "SUB30" USING SQLCODE EMPNO1 MOVE SQLCODE TO SQL-COD DISPLAY " The correct result is code1 not equal to 0" DISPLAY " Your answer is code1=", code1 if (code1 NOT = 0 AND EMPNO1 = "E1") then * EXEC SQL INSERT INTO TESTREPORT * VALUES('0325','pass','MCO') END-EXEC CALL "SUB31" USING SQLCODE MOVE SQLCODE TO SQL-COD DISPLAY " *** pass *** " else * EXEC SQL INSERT INTO TESTREPORT * VALUES('0325','fail','MCO') END-EXEC ADD 1 TO errcnt CALL "SUB32" USING SQLCODE MOVE SQLCODE TO SQL-COD DISPLAY " cdr008.sco *** fail *** " END-IF DISPLAY "================================================" DISPLAY " " * EXEC SQL COMMIT WORK; CALL "SUB33" USING SQLCODE MOVE SQLCODE TO SQL-COD ****************** END TEST0325 ******************** ******************** BEGIN TEST0326 ********************* *This test set a row (E1,-,-,-) in PRIMARY KEY TABLE *-- STAFF3 to (E9,-,-,-), there is no such a row *(E1,*,*) in FOREIGN KEY TABLE -- WORK3. DISPLAY " TEST0326 " DISPLAY " REFERENTIAL INTEGRITY " DISPLAY " SQL with Integrity Enhancement section - " 6.5,6.6,6.7" DISPLAY " - - - - - - - - - - - - - - - - - - - - - " DISPLAY " " DISPLAY " DELETE FROM WORKS3;" DISPLAY " " DISPLAY " DELETE FROM STAFF3;" DISPLAY " " DISPLAY " INSERT INTO STAFF3" DISPLAY " SELECT * FROM STAFF;" DISPLAY " " DISPLAY " UPDATE STAFF3" DISPLAY " SET EMPNUM = 'E9'" DISPLAY " WHERE EMPNUM = 'E1';" DISPLAY " " DISPLAY "code1 = SQLCODE" DISPLAY " " DISPLAY " SELECT EMPNUM INTO :EMPNO1" DISPLAY " FROM STAFF3" DISPLAY " WHERE EMPNUM = 'E1';" * EXEC SQL DELETE FROM WORKS3; CALL "SUB34" USING SQLCODE MOVE SQLCODE TO SQL-COD * EXEC SQL DELETE FROM STAFF3; CALL "SUB35" USING SQLCODE MOVE SQLCODE TO SQL-COD * EXEC SQL INSERT INTO STAFF3 * SELECT * FROM STAFF END-EXEC CALL "SUB36" USING SQLCODE MOVE SQLCODE TO SQL-COD * EXEC SQL UPDATE STAFF3 * SET EMPNUM = 'E9' * WHERE EMPNUM = 'E1' END-EXEC CALL "SUB37" USING SQLCODE MOVE SQLCODE TO SQL-COD MOVE SQLCODE TO code1 MOVE "NV" TO EMPNO1 * EXEC SQL SELECT EMPNUM INTO :EMPNO1 * FROM STAFF3 * WHERE EMPNUM = 'E1' END-EXEC CALL "SUB38" USING SQLCODE EMPNO1 MOVE SQLCODE TO SQL-COD DISPLAY " The correct result is code1 equal to 0" DISPLAY " Your answer is code1=", code1 if (code1 = 0 AND (EMPNO1 = "NV")) then * EXEC SQL INSERT INTO TESTREPORT * VALUES('0326','pass','MCO') END-EXEC CALL "SUB39" USING SQLCODE MOVE SQLCODE TO SQL-COD DISPLAY " *** pass *** " else * EXEC SQL INSERT INTO TESTREPORT * VALUES('0326','fail','MCO') END-EXEC ADD 1 TO errcnt CALL "SUB40" USING SQLCODE MOVE SQLCODE TO SQL-COD DISPLAY " cdr008.sco *** fail *** " END-IF DISPLAY "================================================" DISPLAY " " * EXEC SQL COMMIT WORK; CALL "SUB41" USING SQLCODE MOVE SQLCODE TO SQL-COD ****************** END TEST0326 ******************** **** TESTER MAY CHOOSE TO INSERT CODE FOR errcnt > 0 STOP RUN. * **** Procedures for PERFORM statements [ zur Elbe Produktseite wechseln0.15Quellennavigators Analyse erneut starten ] |