IDENTIFICATION DIVISION .
PROGRAM-ID . DML182.
ENVIRONMENT DIVISION .
CONFIGURATION SECTION .
SOURCE-COMPUTER . xyz.
OBJECT-COMPUTER . xyz.
DATA DIVISION .
WORKING-STORAGE SECTION .
* Standard COBOL (file "DML182.SCO") calling SQL
* procedures in file "DML182.MCO".
****************************************************************
*
* COMMENT SECTION
*
* DATE 1996-05-20 Module COBOL LANGUAGE
* 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.
*
* DML182.SCO
* WRITTEN BY: David Flater
* TRANSLATED AUTOMATICALLY FROM EMBEDDED COBOL BY CHRIS SCHANZLE
*
* This routine tests Intermediate SQL.
*
* REFERENCES
* FIPS PUB 127-2 14.2 Intermediate SQL
* ANSI SQL-1992
*
****************************************************************
* 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 int1 PIC S9(9) DISPLAY SIGN LEADING SEPARATE .
01 int2 PIC S9(9) DISPLAY SIGN LEADING SEPARATE .
01 int3 PIC S9(9) DISPLAY SIGN LEADING SEPARATE .
01 int4 PIC S9(9) DISPLAY SIGN LEADING SEPARATE .
01 int5 PIC S9(9) DISPLAY SIGN LEADING SEPARATE .
01 int6 PIC S9(9) DISPLAY SIGN LEADING SEPARATE .
01 int7 PIC S9(9) DISPLAY SIGN LEADING SEPARATE .
01 int8 PIC S9(9) DISPLAY SIGN LEADING SEPARATE .
01 int9 PIC S9(9) DISPLAY SIGN LEADING SEPARATE .
01 int10 PIC S9(9) DISPLAY SIGN LEADING SEPARATE .
01 int11 PIC S9(9) DISPLAY SIGN LEADING SEPARATE .
01 int12 PIC S9(9) DISPLAY SIGN LEADING SEPARATE .
01 int13 PIC S9(9) DISPLAY SIGN LEADING SEPARATE .
01 int14 PIC S9(9) DISPLAY SIGN LEADING SEPARATE .
01 int15 PIC S9(9) DISPLAY SIGN LEADING SEPARATE .
01 flt1 PIC S9(4)V9(4) DISPLAY SIGN LEADING SEPARATE .
* 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 "FLATER " TO uid
CALL "AUTHID" USING uid
MOVE "not logged in, not" TO uidx
* EXEC SQL SELECT USER INTO :uidx FROM HU.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, dml182.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 TEST0895 *******************
MOVE 1 TO flag
DISPLAY " FIPS sizing TEST0895"
DISPLAY " FIPS sizing, columns in list >= 15"
DISPLAY "References:"
DISPLAY "FIPS 16.6 -- Sizing #28, Columns in a UNIQUE
- " constraint"
DISPLAY "FIPS 16.6 -- Sizing #30, Columns in GROUP BY
- " column list"
DISPLAY "FIPS 16.6 -- Sizing #32, Sort items in ORDER BY
- " clause"
DISPLAY "FIPS 16.6 -- Sizing #34, Referencing columns in
- " FOREIGN KEY"
DISPLAY "FIPS 16.6 -- Sizing #39, Columns in a named
- " columns JOIN"
DISPLAY " - - - - - - - - - - - - - - - - - - -"
COMPUTE SQLCODE = -1
MOVE "xxxxx" TO SQLSTATE
DISPLAY "CREATE TABLE ID_CODES ("
DISPLAY " CODE1 INT,"
DISPLAY " CODE2 INT,"
DISPLAY " CODE3 INT,"
DISPLAY " CODE4 INT,"
DISPLAY " CODE5 INT,"
DISPLAY " CODE6 INT,"
DISPLAY " CODE7 INT,"
DISPLAY " CODE8 INT,"
DISPLAY " CODE9 INT,"
DISPLAY " CODE10 INT,"
DISPLAY " CODE11 INT,"
DISPLAY " CODE12 INT,"
DISPLAY " CODE13 INT,"
DISPLAY " CODE14 INT,"
DISPLAY " CODE15 INT,"
DISPLAY " PRIMARY KEY (CODE1, CODE2, CODE3, CODE4, CODE5,"
DISPLAY " CODE6, CODE7, CODE8, CODE9, CODE10,"
DISPLAY " CODE11, CODE12, CODE13, CODE14, CODE15));"
* EXEC SQL CREATE TABLE ID_CODES (
* CODE1 INT,
* CODE2 INT,
* CODE3 INT,
* CODE4 INT,
* CODE5 INT,
* CODE6 INT,
* CODE7 INT,
* CODE8 INT,
* CODE9 INT,
* CODE10 INT,
* CODE11 INT,
* CODE12 INT,
* CODE13 INT,
* CODE14 INT,
* CODE15 INT,
* PRIMARY KEY (CODE1, CODE2, CODE3, CODE4, CODE5,
* CODE6, CODE7, CODE8, CODE9, CODE10,
* CODE11, CODE12, CODE13, CODE14, CODE15));
CALL "SUB3" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
* EXEC SQL COMMIT WORK;
CALL "SUB4" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "CREATE TABLE ORDERS ("
DISPLAY " CODE1 INT,"
DISPLAY " CODE2 INT,"
DISPLAY " CODE3 INT,"
DISPLAY " CODE4 INT,"
DISPLAY " CODE5 INT,"
DISPLAY " CODE6 INT,"
DISPLAY " CODE7 INT,"
DISPLAY " CODE8 INT,"
DISPLAY " CODE9 INT,"
DISPLAY " CODE10 INT,"
DISPLAY " CODE11 INT,"
DISPLAY " CODE12 INT,"
DISPLAY " CODE13 INT,"
DISPLAY " CODE14 INT,"
DISPLAY " CODE15 INT,"
DISPLAY " TITLE VARCHAR (80),"
DISPLAY " COST NUMERIC(5,2),"
DISPLAY " FOREIGN KEY (CODE1, CODE2, CODE3, CODE4, CODE5,"
DISPLAY " CODE6, CODE7, CODE8, CODE9, CODE10,"
DISPLAY " CODE11, CODE12, CODE13, CODE14, CODE15)"
DISPLAY " REFERENCES ID_CODES);"
* EXEC SQL CREATE TABLE ORDERS (
* CODE1 INT,
* CODE2 INT,
* CODE3 INT,
* CODE4 INT,
* CODE5 INT,
* CODE6 INT,
* CODE7 INT,
* CODE8 INT,
* CODE9 INT,
* CODE10 INT,
* CODE11 INT,
* CODE12 INT,
* CODE13 INT,
* CODE14 INT,
* CODE15 INT,
* TITLE VARCHAR (80),
* COST NUMERIC(5,2),
* FOREIGN KEY (CODE1, CODE2, CODE3, CODE4, CODE5,
* CODE6, CODE7, CODE8, CODE9, CODE10,
* CODE11, CODE12, CODE13, CODE14, CODE15)
* REFERENCES ID_CODES);
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 " "
DISPLAY "CREATE VIEW ID_ORDERS AS"
DISPLAY " SELECT * FROM ID_CODES JOIN ORDERS"
DISPLAY " USING (CODE1, CODE2, CODE3, CODE4, CODE5,"
DISPLAY " CODE6, CODE7, CODE8, CODE9, CODE10,"
DISPLAY " CODE11, CODE12, CODE13, CODE14, CODE15);"
* EXEC SQL CREATE VIEW ID_ORDERS AS
* SELECT * FROM ID_CODES JOIN ORDERS
* USING (CODE1, CODE2, CODE3, CODE4, CODE5,
* CODE6, CODE7, CODE8, CODE9, CODE10,
* CODE11, CODE12, CODE13, CODE14, CODE15);
CALL "SUB7" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
* EXEC SQL COMMIT WORK;
CALL "SUB8" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO ID_CODES VALUES ("
DISPLAY " 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14,
- " 15);"
* EXEC SQL INSERT INTO ID_CODES VALUES (
* 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15)
* ;
CALL "SUB9" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO ID_CODES VALUES ("
DISPLAY " 1, 2, 3, 4, 5, 6, 7, 9, 8, 10, 11, 12, 13, 14,
- " 15);"
* EXEC SQL INSERT INTO ID_CODES VALUES (
* 1, 2, 3, 4, 5, 6, 7, 9, 8, 10, 11, 12, 13, 14, 15)
* ;
CALL "SUB10" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO ORDERS VALUES ("
DISPLAY " 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14,
- " 15,"
DISPLAY "'Gidget Goes Skiing',"
DISPLAY "29.95);"
* EXEC SQL INSERT INTO ORDERS VALUES (
* 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15,
* 'Gidget Goes Skiing',
* 29.95);
CALL "SUB11" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO ORDERS VALUES ("
DISPLAY " 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14,
- " 15,"
DISPLAY "'Barney Goes Hawaiian',"
DISPLAY "19.95);"
* EXEC SQL INSERT INTO ORDERS VALUES (
* 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15,
* 'Barney Goes Hawaiian',
* 19.95);
CALL "SUB12" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO ORDERS VALUES ("
DISPLAY " 1, 2, 3, 4, 5, 6, 7, 9, 8, 10, 11, 12, 13, 14,
- " 15,"
DISPLAY "'Invasion of the Smurfs',"
DISPLAY "9.95);"
* EXEC SQL INSERT INTO ORDERS VALUES (
* 1, 2, 3, 4, 5, 6, 7, 9, 8, 10, 11, 12, 13, 14, 15,
* 'Invasion of the Smurfs',
* 9.95);
CALL "SUB13" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "DECLARE C0895 CURSOR FOR"
DISPLAY " SELECT CODE1, CODE2, CODE3, CODE4, CODE5,"
DISPLAY " CODE6, CODE7, CODE8, CODE9, CODE10,"
DISPLAY " CODE11, CODE12, CODE13, CODE14, CODE15,"
DISPLAY " AVG(COST)"
DISPLAY " FROM ID_ORDERS"
DISPLAY " GROUP BY CODE1, CODE2, CODE3, CODE4, CODE5,"
DISPLAY " CODE6, CODE7, CODE8, CODE9, CODE10,"
DISPLAY " CODE11, CODE12, CODE13, CODE14, CODE15"
DISPLAY " ORDER BY CODE1, CODE2, CODE3, CODE4, CODE5,"
DISPLAY " CODE6, CODE7, CODE8, CODE9, CODE10,"
DISPLAY " CODE11, CODE12, CODE13, CODE14, CODE15;"
* EXEC SQL DECLARE C0895 CURSOR FOR
* SELECT CODE1, CODE2, CODE3, CODE4, CODE5,
* CODE6, CODE7, CODE8, CODE9, CODE10,
* CODE11, CODE12, CODE13, CODE14, CODE15,
* AVG(COST)
* FROM ID_ORDERS
* GROUP BY CODE1, CODE2, CODE3, CODE4, CODE5,
* CODE6, CODE7, CODE8, CODE9, CODE10,
* CODE11, CODE12, CODE13, CODE14, CODE15
* ORDER BY CODE1, CODE2, CODE3, CODE4, CODE5,
* CODE6, CODE7, CODE8, CODE9, CODE10,
* CODE11, CODE12, CODE13, CODE14, CODE15 END-EXEC
DISPLAY "OPEN C0895;"
* EXEC SQL OPEN C0895;
CALL "SUB14" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
MOVE 0.0 TO flt1
DISPLAY "FETCH C0895 INTO :int1, :int2, :int3,"
DISPLAY ":int4, :int5, :int6, :int7, :int8, :int9, :int10,"
DISPLAY ":int11, :int12, :int13, :int14, :int15, :flt1;"
* EXEC SQL FETCH C0895 INTO :int1, :int2, :int3,
* :int4, :int5, :int6, :int7, :int8, :int9, :int10,
* :int11, :int12, :int13, :int14, :int15, :flt1;
CALL "SUB15" USING SQLCODE SQLSTATE int1 int2 int3 int4
int5 int6 int7 int8 int9 int10 int11 int12 int13 int14
int15 flt1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "flt1 should be 24.95 +-0.01; its value is " , flt1
if (flt1 < 24.94 OR flt1 > 24.96) then
MOVE 0 TO flag
END-IF
MOVE 0.0 TO flt1
DISPLAY "FETCH C0895 INTO :int1, :int2, :int3,"
DISPLAY ":int4, :int5, :int6, :int7, :int8, :int9, :int10,"
DISPLAY ":int11, :int12, :int13, :int14, :int15, :flt1;"
* EXEC SQL FETCH C0895 INTO :int1, :int2, :int3,
* :int4, :int5, :int6, :int7, :int8, :int9, :int10,
* :int11, :int12, :int13, :int14, :int15, :flt1;
CALL "SUB16" USING SQLCODE SQLSTATE int1 int2 int3 int4
int5 int6 int7 int8 int9 int10 int11 int12 int13 int14
int15 flt1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "flt1 should be 9.95 +-0.01; its value is " , flt1
if (flt1 < 9.94 OR flt1 > 9.96) then
MOVE 0 TO flag
END-IF
DISPLAY "COMMIT WORK;"
* EXEC SQL COMMIT WORK;
CALL "SUB17" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "DROP TABLE ORDERS CASCADE;"
* EXEC SQL DROP TABLE ORDERS 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 " "
DISPLAY "DROP TABLE ID_CODES CASCADE;"
* EXEC SQL DROP TABLE ID_CODES CASCADE;
CALL "SUB20" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
* EXEC SQL COMMIT WORK;
CALL "SUB21" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
if ( flag = 1 ) then
DISPLAY " *** pass *** "
* EXEC SQL INSERT INTO HU.TESTREPORT
* VALUES('0895','pass','MCO');
CALL "SUB22" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
else
DISPLAY " dml182.mco *** fail *** "
* EXEC SQL INSERT INTO HU.TESTREPORT
* VALUES('0895','fail','MCO');
CALL "SUB23" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
COMPUTE errcnt = errcnt + 1
END-IF
DISPLAY "==============================================="
* EXEC SQL COMMIT WORK;
CALL "SUB24" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
******************** END TEST0895 ********************
**** 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 .
quality 99%
¤ Dauer der Verarbeitung: 0.17 Sekunden
(vorverarbeitet)
¤
*© Formatika GbR, Deutschland