| 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: xts719.cob Sprache: Unknown
|
IDENTIFICATION DIVISION.
PROGRAM-ID. XTS719. ENVIRONMENT DIVISION. CONFIGURATION SECTION. SOURCE-COMPUTER. xyz. OBJECT-COMPUTER. xyz. DATA DIVISION. WORKING-STORAGE SECTION. * Standard COBOL (file "XTS719.SCO") calling SQL * procedures in file "XTS719.MCO". *Copyright 1995 National Computing Centre Limited *and Computer Logic R&D S.A *on behalf of the 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 * * XTS719.SCO * WRITTEN BY: Nickos Backalidis * TRANSLATED AUTOMATICALLY FROM EMBEDDED COBOL BY CHRIS SCHANZLE * * Access to KEY_COLUMN_USAGE view * * REFERENCES * 21.2.16 -- KEY_COLUMN_USAGE view * 21.3.14 -- KEY_COLUMN_USAGE base table. * F#1 -- Dynamic SQL * F#2 -- Basic information schema * F#3 -- Basic schema manipulation * F#34 -- Usage tables * * DATE LAST ALTERED 14/12/95 CTS5 Hand-over Test * * Cleanups and fixes by V. Kogakis 01/12/95: * Change flag from int to long * DROP TABLE TAB719a first instead of TAB719b * * QA Status: QA check * *Revised by DWF 1/24/96 * Fixed bogus dynamic SQL * Fixed SDL transactions * Fixed checks for wrong identifiers in information schema * Fixed string length problems * Fixed bad schema references * Removed status checks after cursor definition * Fixed expected results, tightened pass criteria slightly **************************************************************** * 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 counok PIC S9(9) DISPLAY SIGN LEADING SEPARATE. 01 sqlcmd PIC X(240). * 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 "CTS4 " 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, xts719.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 TEST7019 ******************* MOVE 1 TO flag DISPLAY " TEST7019" DISPLAY " Access to KEY_COLUMN_USAGE view " DISPLAY " References " DISPLAY " 21.2.16 -- KEY_COLUMN_USAGE view " DISPLAY " 21.3.14 -- KEY_COLUMN_USAGE base table" DISPLAY " F#2 -- Basic information schema " DISPLAY " F#3 -- Basic schema manipulation" DISPLAY " F#34 -- Usage tables " DISPLAY " - - - - - - - - - - - - - - - - - - -" *initialise error reporting variables COMPUTE SQLCODE = -1 MOVE "xxxxx" TO SQLSTATE *initialise host variable *expected value zero - initialise to a value <> 0 MOVE 9 TO counok *Access the KEY_COLUMN_USAGE_VIEW DISPLAY "SELECT COUNT(*) INTO :counok" DISPLAY "FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE" DISPLAY "WHERE CONSTRAINT_SCHEMA = 'CTS4'; " * EXEC SQL SELECT COUNT(*) INTO :counok * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE * WHERE CONSTRAINT_SCHEMA = 'CTS4'; CALL "SUB3" USING SQLCODE SQLSTATE counok MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "counok should be 0; its value is : ", counok if (counok NOT = 0) then MOVE 0 TO flag END-IF DISPLAY " " DISPLAY "COMMIT WORK;" * EXEC SQL COMMIT WORK; CALL "SUB4" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " *Create table with keys in two columns DISPLAY "CREATE TABLE TAB719a " DISPLAY " (COLNUM1 NUMERIC(5) PRIMARY KEY," DISPLAY " COLNUM2 NUMERIC(7) UNIQUE," DISPLAY " COLSTR1 CHAR(10)," DISPLAY " COLSTR2 CHAR(5));" * EXEC SQL CREATE TABLE TAB719a * (COLNUM1 NUMERIC(5) PRIMARY KEY, * COLNUM2 NUMERIC(7) UNIQUE, * COLSTR1 CHAR(10), * COLSTR2 CHAR(5)); CALL "SUB5" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "COMMIT WORK;" * EXEC SQL COMMIT WORK; CALL "SUB6" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " *Check for correct creation of table and Access to KEY_COLUMN_US MOVE 0 TO counok DISPLAY "SELECT COUNT(COLUMN_NAME) INTO :counok" DISPLAY "FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE" DISPLAY "WHERE TABLE_SCHEMA = 'CTS4' AND TABLE_NAME = - " 'TAB719A';" * EXEC SQL SELECT COUNT(COLUMN_NAME) INTO :counok * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE * WHERE TABLE_SCHEMA = 'CTS4' AND TABLE_NAME = 'TAB719A' * ; CALL "SUB7" USING SQLCODE SQLSTATE counok MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "Counok should be 2; it is ", counok if (counok NOT = 2) then MOVE 0 TO flag END-IF DISPLAY " " DISPLAY "COMMIT WORK;" * EXEC SQL COMMIT WORK; CALL "SUB8" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " *Create second table DISPLAY "CREATE TABLE TAB719b" DISPLAY "( CNUM1 NUMERIC(5)," DISPLAY " CNUM2 NUMERIC(7)," DISPLAY " CSTR1 CHAR(10) UNIQUE NOT NULL," DISPLAY " CSTR2 CHAR(5)," DISPLAY " FOREIGN KEY (CNUM1) REFERENCES - " TAB719a(COLNUM1));" * EXEC SQL CREATE TABLE TAB719b * (CNUM1 NUMERIC(5), * CNUM2 NUMERIC(7), * CSTR1 CHAR(10) UNIQUE NOT NULL, * CSTR2 CHAR(5), * FOREIGN KEY (CNUM1) REFERENCES TAB719a(COLNUM1)); CALL "SUB9" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "COMMIT WORK;" * EXEC SQL COMMIT WORK; CALL "SUB10" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " *Returned columns: TAB719A.COLNUM1, TAB719A.COLNUM2, *TAB719B.CSTR1, TAB719B.CNUM1 *(KEY_COLUMN_USAGE contains only the referencing columns in a *foreign key constraint) *Check for creation of 2nd table and access to KEY_COLUMN_USAGE DISPLAY "SELECT COUNT(*) INTO :counok" DISPLAY "FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE" DISPLAY "WHERE TABLE_SCHEMA = 'CTS4';" MOVE 0 TO counok * EXEC SQL SELECT COUNT(*) INTO :counok * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE * WHERE TABLE_SCHEMA = 'CTS4'; CALL "SUB11" USING SQLCODE SQLSTATE counok MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "Counok should be 4; it is ", counok if (counok NOT = 4) then MOVE 0 TO flag END-IF DISPLAY " " MOVE 0 TO counok DISPLAY "SELECT COUNT(*) INTO :counok" DISPLAY " FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE" DISPLAY " WHERE TABLE_SCHEMA = 'CTS4'" DISPLAY " AND TABLE_NAME = 'TAB719A'" DISPLAY " AND COLUMN_NAME = 'COLNUM1';" * EXEC SQL SELECT COUNT(*) INTO :counok * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE * WHERE TABLE_SCHEMA = 'CTS4' * AND TABLE_NAME = 'TAB719A' * AND COLUMN_NAME = 'COLNUM1'; CALL "SUB12" USING SQLCODE SQLSTATE counok MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "Counok should be 1; it is ", counok if (counok NOT = 1) then MOVE 0 TO flag END-IF DISPLAY " " MOVE 0 TO counok DISPLAY "SELECT COUNT(*) INTO :counok" DISPLAY " FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE" DISPLAY " WHERE TABLE_SCHEMA = 'CTS4'" DISPLAY " AND TABLE_NAME = 'TAB719B'" DISPLAY " AND COLUMN_NAME = 'CNUM1';" * EXEC SQL SELECT COUNT(*) INTO :counok * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE * WHERE TABLE_SCHEMA = 'CTS4' * AND TABLE_NAME = 'TAB719B' * AND COLUMN_NAME = 'CNUM1'; CALL "SUB13" USING SQLCODE SQLSTATE counok MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "Counok should be 1; it is ", counok if (counok NOT = 1) then MOVE 0 TO flag END-IF DISPLAY " " DISPLAY "COMMIT WORK;" * EXEC SQL COMMIT WORK; CALL "SUB14" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " *DROP TABLE TAB719a DISPLAY "DROP TABLE TAB719a CASCADE;" * EXEC SQL DROP TABLE TAB719a CASCADE; CALL "SUB15" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "COMMIT WORK;" * EXEC SQL COMMIT WORK; CALL "SUB16" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " *Use Dynamic SQL with a preparable statement *and access the key column usage view * EXEC SQL DELETE FROM CTS1.CONCATBUF; CALL "SUB17" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD * EXEC SQL INSERT INTO CTS1.CONCATBUF VALUES( * 'SELECT COUNT(*)' || * ' FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE' || * ' WHERE TABLE_SCHEMA = ''CTS4'''); CALL "SUB18" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD * EXEC SQL SELECT ZZ INTO :sqlcmd FROM CTS1.CONCATBUF * ; CALL "SUB19" USING SQLCODE SQLSTATE sqlcmd MOVE SQLCODE TO SQL-COD DISPLAY "sqlcmd = """, sqlcmd, """" DISPLAY "PREPARE S FROM :sqlcmd;" * EXEC SQL PREPARE S FROM :sqlcmd; CALL "SUB20" USING SQLCODE SQLSTATE sqlcmd MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DECLARE NICK1 CURSOR FOR S;" * EXEC SQL DECLARE NICK1 CURSOR FOR S END-EXEC DISPLAY "OPEN NICK1;" * EXEC SQL OPEN NICK1; CALL "SUB21" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " MOVE 0 TO counok DISPLAY "FETCH NICK1 INTO :counok;" * EXEC SQL FETCH NICK1 INTO :counok; CALL "SUB22" USING SQLCODE SQLSTATE counok MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY "counok should be 1; its value is ", counok if (counok NOT = 1) then MOVE 0 TO flag END-IF DISPLAY " " DISPLAY "CLOSE NICK1;" * EXEC SQL CLOSE NICK1; CALL "SUB23" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "ROLLBACK WORK;" * EXEC SQL ROLLBACK WORK; CALL "SUB24" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DROP TABLE TAB719b CASCADE;" * EXEC SQL DROP TABLE TAB719b CASCADE; CALL "SUB25" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "COMMIT WORK;" * EXEC SQL COMMIT WORK; CALL "SUB26" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " *record results if ( flag = 1 ) then DISPLAY " xts719.mco *** pass *** " * EXEC SQL INSERT INTO CTS1.TESTREPORT * VALUES('7019','pass','MCO'); CALL "SUB27" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD else DISPLAY " xts719.mco *** fail *** " * EXEC SQL INSERT INTO CTS1.TESTREPORT * VALUES('7019','fail','MCO'); CALL "SUB28" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD COMPUTE errcnt = errcnt + 1 END-IF DISPLAY "========================================" * EXEC SQL COMMIT WORK; CALL "SUB29" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD ******************** END TEST7019 ******************** **** 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.5 Sekunden (vorverarbeitet) ] |