|
IDENTIFICATION DIVISION.
PROGRAM-ID. DML154.
ENVIRONMENT DIVISION.
CONFIGURATION SECTION.
SOURCE-COMPUTER. xyz.
OBJECT-COMPUTER. xyz.
DATA DIVISION.
WORKING-STORAGE SECTION.
* Standard COBOL (file "DML154.SCO") calling SQL
* procedures in file "DML154.MCO".
****************************************************************
*
* COMMENT SECTION
*
* DATE 1995/09/08 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.
*
* DML154.SCO
* WRITTEN BY: David W. Flater
* TRANSLATED AUTOMATICALLY FROM EMBEDDED COBOL BY CHRIS SCHANZLE
*
* This routine tests Transitional SQL features.
*
* REFERENCES
* FIPS PUB 127-2 14.1 Transitional 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 dstmt PIC X(50).
* 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, dml154.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 TEST0849 *******************
MOVE 1 TO flag
DISPLAY " TEST0849 "
DISPLAY "Descriptors: datetime length in positions"
DISPLAY "References:"
DISPLAY " F# 1 -- Dynamic SQL"
DISPLAY " F# 5 -- DATETIME data types"
DISPLAY " 4.5 PP.4 -- Definition of length in positions"
DISPLAY " 6.1 SR.27 -- Clarification of length in
- " positions"
DISPLAY " X3H2-95-104 -- Erratum, length of interval
- " types"
DISPLAY " 6.1 -- esp. temporal types"
DISPLAY " 10.1 -- "
DISPLAY " 17.1 -- Description of SQL item descriptor
- " areas"
DISPLAY " 17.9 -- "
DISPLAY " 17.2 GR.3 -- limit on descriptor areas is"
DISPLAY " implementation-defined"
DISPLAY " - - - - - - - - - - - - - - - - - - -"
COMPUTE SQLCODE = -1
MOVE "xxxxx" TO SQLSTATE
DISPLAY "CREATE TABLE LOTSA_DATETIMES ("
DISPLAY " C1 DATE, C2 TIME, C3 TIMESTAMP,"
DISPLAY " C4 INTERVAL YEAR, C5 INTERVAL MONTH, C6 INTERVAL
- " DAY,"
DISPLAY " C7 INTERVAL HOUR, C8 INTERVAL MINUTE, C9
- " INTERVAL SECOND,"
DISPLAY " C10 INTERVAL YEAR TO MONTH,"
DISPLAY " C11 INTERVAL DAY TO HOUR,"
DISPLAY " C12 INTERVAL DAY TO MINUTE,"
DISPLAY " C13 INTERVAL DAY TO SECOND,"
DISPLAY " C14 INTERVAL HOUR TO MINUTE,"
DISPLAY " C15 INTERVAL HOUR TO SECOND,"
DISPLAY " C16 INTERVAL MINUTE TO SECOND);"
* EXEC SQL CREATE TABLE LOTSA_DATETIMES (
* C1 DATE, C2 TIME, C3 TIMESTAMP,
* C4 INTERVAL YEAR, C5 INTERVAL MONTH, C6 INTERVAL DAY,
* C7 INTERVAL HOUR, C8 INTERVAL MINUTE, C9 INTERVAL SECOND,
* C10 INTERVAL YEAR TO MONTH,
* C11 INTERVAL DAY TO HOUR,
* C12 INTERVAL DAY TO MINUTE,
* C13 INTERVAL DAY TO SECOND,
* C14 INTERVAL HOUR TO MINUTE,
* C15 INTERVAL HOUR TO SECOND,
* C16 INTERVAL MINUTE TO SECOND);
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 "ALLOCATE DESCRIPTOR 'D15411' WITH MAX 16;"
* EXEC SQL ALLOCATE DESCRIPTOR 'D15411' WITH MAX 16;
CALL "SUB5" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
if (SQLSTATE = "07009") then
DISPLAY "Received SQLSTATE 07009!"
DISPLAY "This test must be rewritten by NIST (in the
- " event of"
DISPLAY "a validation) to accomodate
- " implementation-defined"
DISPLAY "limit on ."
END-IF
DISPLAY " "
DISPLAY "dstmt=""SELECT * FROM LOTSA_DATETIMES"""
MOVE "SELECT * FROM LOTSA_DATETIMES "
TO dstmt
DISPLAY "PREPARE S15411 FROM :dstmt;"
* EXEC SQL PREPARE S15411 FROM :dstmt;
CALL "SUB6" USING SQLCODE SQLSTATE dstmt
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "DESCRIBE OUTPUT S15411 USING SQL DESCRIPTOR
- " 'D15411';"
* EXEC SQL DESCRIBE OUTPUT S15411 USING SQL DESCRIPTOR
* 'D15411';
CALL "SUB7" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
*Lengths of temporal data types
*as defined in 4.5 and clarified
*by 6.1 SR.27:
*DATE = 10
*TIME = 8
*TIMESTAMP = 26
*INTERVAL YEAR >= 2
*INTERVAL MONTH >= 2
*INTERVAL DAY >= 2
*INTERVAL HOUR >= 2
*INTERVAL MINUTE >= 2
*INTERVAL SECOND >= 9
*INTERVAL YEAR TO MONTH >= 5
*INTERVAL DAY TO HOUR >= 5
*INTERVAL DAY TO MINUTE >= 8
*INTERVAL DAY TO SECOND >= 18
*INTERVAL HOUR TO MINUTE >= 5
*INTERVAL HOUR TO SECOND >= 15
*INTERVAL MINUTE TO SECOND >= 12
COMPUTE int1 = -1
DISPLAY "GET DESCRIPTOR 'D15411' VALUE 1 :int1 = LENGTH;"
* EXEC SQL GET DESCRIPTOR 'D15411' VALUE 1 :int1 = LENGTH
* ;
CALL "SUB8" USING SQLCODE SQLSTATE int1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "int1 should be 10; its value is ", int1
if (int1 NOT = 10) then
MOVE 0 TO flag
END-IF
COMPUTE int1 = -1
DISPLAY "GET DESCRIPTOR 'D15411' VALUE 2 :int1 = LENGTH;"
* EXEC SQL GET DESCRIPTOR 'D15411' VALUE 2 :int1 = LENGTH
* ;
CALL "SUB9" USING SQLCODE SQLSTATE int1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "int1 should be 8; its value is ", int1
if (int1 NOT = 8) then
MOVE 0 TO flag
END-IF
COMPUTE int1 = -1
DISPLAY "GET DESCRIPTOR 'D15411' VALUE 3 :int1 = LENGTH;"
* EXEC SQL GET DESCRIPTOR 'D15411' VALUE 3 :int1 = LENGTH
* ;
CALL "SUB10" USING SQLCODE SQLSTATE int1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "int1 should be 26; its value is ", int1
if (int1 NOT = 26) then
MOVE 0 TO flag
END-IF
COMPUTE int1 = -1
DISPLAY "GET DESCRIPTOR 'D15411' VALUE 4 :int1 = LENGTH;"
* EXEC SQL GET DESCRIPTOR 'D15411' VALUE 4 :int1 = LENGTH
* ;
CALL "SUB11" USING SQLCODE SQLSTATE int1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "int1 should be >= 2; its value is ", int1
if (int1 < 2) then
MOVE 0 TO flag
END-IF
COMPUTE int1 = -1
DISPLAY "GET DESCRIPTOR 'D15411' VALUE 5 :int1 = LENGTH;"
* EXEC SQL GET DESCRIPTOR 'D15411' VALUE 5 :int1 = LENGTH
* ;
CALL "SUB12" USING SQLCODE SQLSTATE int1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "int1 should be >= 2; its value is ", int1
if (int1 < 2) then
MOVE 0 TO flag
END-IF
COMPUTE int1 = -1
DISPLAY "GET DESCRIPTOR 'D15411' VALUE 6 :int1 = LENGTH;"
* EXEC SQL GET DESCRIPTOR 'D15411' VALUE 6 :int1 = LENGTH
* ;
CALL "SUB13" USING SQLCODE SQLSTATE int1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "int1 should be >= 2; its value is ", int1
if (int1 < 2) then
MOVE 0 TO flag
END-IF
COMPUTE int1 = -1
DISPLAY "GET DESCRIPTOR 'D15411' VALUE 7 :int1 = LENGTH;"
* EXEC SQL GET DESCRIPTOR 'D15411' VALUE 7 :int1 = LENGTH
* ;
CALL "SUB14" USING SQLCODE SQLSTATE int1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "int1 should be >= 2; its value is ", int1
if (int1 < 2) then
MOVE 0 TO flag
END-IF
COMPUTE int1 = -1
DISPLAY "GET DESCRIPTOR 'D15411' VALUE 8 :int1 = LENGTH;"
* EXEC SQL GET DESCRIPTOR 'D15411' VALUE 8 :int1 = LENGTH
* ;
CALL "SUB15" USING SQLCODE SQLSTATE int1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "int1 should be >= 2; its value is ", int1
if (int1 < 2) then
MOVE 0 TO flag
END-IF
COMPUTE int1 = -1
DISPLAY "GET DESCRIPTOR 'D15411' VALUE 9 :int1 = LENGTH;"
* EXEC SQL GET DESCRIPTOR 'D15411' VALUE 9 :int1 = LENGTH
* ;
CALL "SUB16" USING SQLCODE SQLSTATE int1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "int1 should be >= 9; its value is ", int1
if (int1 < 9) then
MOVE 0 TO flag
END-IF
COMPUTE int1 = -1
DISPLAY "GET DESCRIPTOR 'D15411' VALUE 10 :int1 = LENGTH;"
* EXEC SQL GET DESCRIPTOR 'D15411' VALUE 10 :int1 = LENGTH
* ;
CALL "SUB17" USING SQLCODE SQLSTATE int1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "int1 should be >= 5; its value is ", int1
if (int1 < 5) then
MOVE 0 TO flag
END-IF
COMPUTE int1 = -1
DISPLAY "GET DESCRIPTOR 'D15411' VALUE 11 :int1 = LENGTH;"
* EXEC SQL GET DESCRIPTOR 'D15411' VALUE 11 :int1 = LENGTH
* ;
CALL "SUB18" USING SQLCODE SQLSTATE int1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "int1 should be >= 5; its value is ", int1
if (int1 < 5) then
MOVE 0 TO flag
END-IF
COMPUTE int1 = -1
DISPLAY "GET DESCRIPTOR 'D15411' VALUE 12 :int1 = LENGTH;"
* EXEC SQL GET DESCRIPTOR 'D15411' VALUE 12 :int1 = LENGTH
* ;
CALL "SUB19" USING SQLCODE SQLSTATE int1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "int1 should be >= 8; its value is ", int1
if (int1 < 8) then
MOVE 0 TO flag
END-IF
COMPUTE int1 = -1
DISPLAY "GET DESCRIPTOR 'D15411' VALUE 13 :int1 = LENGTH;"
* EXEC SQL GET DESCRIPTOR 'D15411' VALUE 13 :int1 = LENGTH
* ;
CALL "SUB20" USING SQLCODE SQLSTATE int1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "int1 should be >= 18; its value is ", int1
if (int1 < 18) then
MOVE 0 TO flag
END-IF
COMPUTE int1 = -1
DISPLAY "GET DESCRIPTOR 'D15411' VALUE 14 :int1 = LENGTH;"
* EXEC SQL GET DESCRIPTOR 'D15411' VALUE 14 :int1 = LENGTH
* ;
CALL "SUB21" USING SQLCODE SQLSTATE int1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "int1 should be >= 5; its value is ", int1
if (int1 < 5) then
MOVE 0 TO flag
END-IF
COMPUTE int1 = -1
DISPLAY "GET DESCRIPTOR 'D15411' VALUE 15 :int1 = LENGTH;"
* EXEC SQL GET DESCRIPTOR 'D15411' VALUE 15 :int1 = LENGTH
* ;
CALL "SUB22" USING SQLCODE SQLSTATE int1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "int1 should be >= 15; its value is ", int1
if (int1 < 15) then
MOVE 0 TO flag
END-IF
COMPUTE int1 = -1
DISPLAY "GET DESCRIPTOR 'D15411' VALUE 16 :int1 = LENGTH;"
* EXEC SQL GET DESCRIPTOR 'D15411' VALUE 16 :int1 = LENGTH
* ;
CALL "SUB23" USING SQLCODE SQLSTATE int1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "int1 should be >= 12; its value is ", int1
if (int1 < 12) then
MOVE 0 TO flag
END-IF
DISPLAY "ROLLBACK WORK;"
* EXEC SQL ROLLBACK WORK;
CALL "SUB24" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "DROP TABLE LOTSA_DATETIMES CASCADE;"
* EXEC SQL DROP TABLE LOTSA_DATETIMES 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 " "
if ( flag = 1 ) then
DISPLAY " *** pass *** "
* EXEC SQL INSERT INTO HU.TESTREPORT
* VALUES('0849','pass','MCO');
CALL "SUB27" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
else
DISPLAY " dml154.mco *** fail *** "
* EXEC SQL INSERT INTO HU.TESTREPORT
* VALUES('0849','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 TEST0849 ********************
******************** BEGIN TEST0852 *******************
MOVE 1 TO flag
DISPLAY " TEST0852 "
DISPLAY "Transitive grant in COLUMN_PRIV, TABLE_PRIV"
DISPLAY "References:"
DISPLAY " F# 2 -- Basic information schema"
DISPLAY " F# 23 -- Privilege tables"
DISPLAY " 21.2.10 -- TABLE_PRIVILEGES view"
DISPLAY " 21.2.11 -- COLUMN_PRIVILEGES view"
DISPLAY " - - - - - - - - - - - - - - - - - - -"
COMPUTE SQLCODE = -1
MOVE "xxxxx" TO SQLSTATE
*We were GRANT SELECT ON HU.STAFF TO FLATER by SULLIVAN1
*We also are GRANT ALL PRIVILEGES ON STAFF TO PUBLIC
MOVE 0 TO int1
DISPLAY "SELECT COUNT(*) INTO :int1"
DISPLAY " FROM INFO_SCHEM.TABLE_PRIVILEGES"
DISPLAY " WHERE TABLE_SCHEM = 'HU' AND TABLE_NAME =
- " 'STAFF'"
DISPLAY " AND GRANTOR = 'SULLIVAN1' AND GRANTEE =
- " 'FLATER'"
DISPLAY " AND IS_GRANTABLE = 'NO';"
* EXEC SQL SELECT COUNT(*) INTO :int1
* FROM INFO_SCHEM.TABLE_PRIVILEGES
* WHERE TABLE_SCHEM = 'HU' AND TABLE_NAME = 'STAFF'
* AND GRANTOR = 'SULLIVAN1' AND GRANTEE = 'FLATER'
* AND IS_GRANTABLE = 'NO';
CALL "SUB30" USING SQLCODE SQLSTATE int1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "int1 should be 1; its value is ", int1
if (int1 NOT = 1) then
MOVE 0 TO flag
END-IF
MOVE 0 TO int1
DISPLAY "SELECT COUNT(*) INTO :int1"
DISPLAY " FROM INFO_SCHEM.COLUMN_PRIVILEGES"
DISPLAY " WHERE TABLE_SCHEM = 'HU' AND TABLE_NAME =
- " 'STAFF'"
DISPLAY " AND GRANTOR = 'SULLIVAN1' AND GRANTEE =
- " 'FLATER'"
DISPLAY " AND IS_GRANTABLE = 'NO';"
* EXEC SQL SELECT COUNT(*) INTO :int1
* FROM INFO_SCHEM.COLUMN_PRIVILEGES
* WHERE TABLE_SCHEM = 'HU' AND TABLE_NAME = 'STAFF'
* AND GRANTOR = 'SULLIVAN1' AND GRANTEE = 'FLATER'
* AND IS_GRANTABLE = 'NO';
CALL "SUB31" USING SQLCODE SQLSTATE int1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "int1 should be 4; its value is ", int1
if (int1 NOT = 4) then
MOVE 0 TO flag
END-IF
DISPLAY "COMMIT WORK;"
* EXEC SQL COMMIT WORK;
CALL "SUB32" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
if ( flag = 1 ) then
DISPLAY " *** pass *** "
* EXEC SQL INSERT INTO HU.TESTREPORT
* VALUES('0852','pass','MCO');
CALL "SUB33" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
else
DISPLAY " dml154.mco *** fail *** "
* EXEC SQL INSERT INTO HU.TESTREPORT
* VALUES('0852','fail','MCO');
CALL "SUB34" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
COMPUTE errcnt = errcnt + 1
END-IF
DISPLAY "==============================================="
* EXEC SQL COMMIT WORK;
CALL "SUB35" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
******************** END TEST0852 ********************
******************** BEGIN TEST0854 *******************
MOVE 1 TO flag
DISPLAY " OPTIONAL TEST0854 "
DISPLAY " Informational: mixing SDL and DML"
DISPLAY "References:"
DISPLAY " 4.28 PP.2 -- Imp-defined whether can mix SDL
- " and DML"
DISPLAY " 12.3 GR.7 -- Invalid transaction state if not
- " supported"
DISPLAY " F# 3 -- Basic schema manipulation"
DISPLAY " - - - - - - - - - - - - - - - - - - -"
COMPUTE SQLCODE = -1
MOVE "xxxxx" TO SQLSTATE
DISPLAY "It is implementation-defined whether an SQL-data
- " statement may"
DISPLAY "occur within the same transaction as an SQL-schema
- " statement."
DISPLAY "If it is not supported, a 25000 SQLSTATE is
- " supposed to be"
DISPLAY "issued. However, even if it is supported, there
- " can be other"
DISPLAY "implementation-defined restrictions, requirements,
- " and"
DISPLAY "conditions. An example is only allowing one
- " SQL-schema statement"
DISPLAY "per transaction. If such a restriction is
- " violated, the returned"
DISPLAY "SQLSTATE is implementation-defined."
DISPLAY "CREATE TABLE TRANSIENT (WINDOW_ID INT);"
* EXEC SQL CREATE TABLE TRANSIENT (WINDOW_ID INT);
CALL "SUB36" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO TRANSIENT VALUES (1);"
* EXEC SQL INSERT INTO TRANSIENT VALUES (1);
CALL "SUB37" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "CREATE VIEW CTRANS (WIN_COUNT) AS"
DISPLAY " SELECT COUNT(*) FROM TRANSIENT;"
* EXEC SQL CREATE VIEW CTRANS (WIN_COUNT) AS
* SELECT COUNT(*) FROM TRANSIENT;
CALL "SUB38" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO TRANSIENT VALUES (2);"
* EXEC SQL INSERT INTO TRANSIENT VALUES (2);
CALL "SUB39" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
MOVE 0 TO int1
DISPLAY "SELECT * INTO :int1 FROM CTRANS;"
* EXEC SQL SELECT * INTO :int1 FROM CTRANS;
CALL "SUB40" USING SQLCODE SQLSTATE int1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "int1 should be 2; its value is ", int1
if (int1 NOT = 2) then
MOVE 0 TO flag
END-IF
DISPLAY " "
DISPLAY "DROP TABLE TRANSIENT CASCADE;"
* EXEC SQL DROP TABLE TRANSIENT CASCADE;
CALL "SUB41" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "ROLLBACK WORK;"
* EXEC SQL ROLLBACK WORK;
CALL "SUB42" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
DISPLAY " "
if ( flag = 1 ) then
DISPLAY " *** pass *** "
* EXEC SQL INSERT INTO HU.TESTREPORT
* VALUES('0854','pass','MCO');
CALL "SUB43" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
else
DISPLAY " dml154.mco *** fail *** "
* EXEC SQL INSERT INTO HU.TESTREPORT
* VALUES('0854','fail','MCO');
CALL "SUB44" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
COMPUTE errcnt = errcnt + 1
END-IF
DISPLAY "==============================================="
* EXEC SQL COMMIT WORK;
CALL "SUB45" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
******************** END TEST0854 ********************
**** 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.
[ zur Elbe Produktseite wechseln0.59Quellennavigators
Analyse erneut starten
]
|
