|
IDENTIFICATION DIVISION.
PROGRAM-ID. DML117.
ENVIRONMENT DIVISION.
CONFIGURATION SECTION.
SOURCE-COMPUTER. xyz.
OBJECT-COMPUTER. xyz.
DATA DIVISION.
WORKING-STORAGE SECTION.
* Standard COBOL (file "DML117.SCO") calling SQL
* procedures in file "DML117.MCO".
****************************************************************
*
* COMMENT SECTION
*
* DATE 1994/5/17 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.
*
* DML117.SCO
* WRITTEN BY: David W. Flater
* TRANSLATED AUTOMATICALLY FROM EMBEDDED COBOL BY CHRIS SCHANZLE
*
* This routine tests FIPS feature 19.
* This is the non-dynamic version of DML118.PC.
*
* 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.
* 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, dml117.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 TEST0645 *******************
MOVE 1 TO flag
DISPLAY " TEST0645 "
DISPLAY " Feature 19, Referential delete actions (static)"
DISPLAY "References:"
DISPLAY " FIPS PUB 127-2 14.1 Transitional SQL features
- " 16, 19"
DISPLAY " 11.8 LR.2.a"
DISPLAY " 11.4 LR.2.b"
DISPLAY " - - - - - - - - - - - - - - - - - - -"
COMPUTE SQLCODE = -1
MOVE "xxxxx" TO SQLSTATE
*Simulate a tiny OS with hard quotas (ick!)
DISPLAY "CREATE TABLE LUSERS ("
DISPLAY " PRIMARY KEY (LUSER_ID),"
DISPLAY " NAAM CHAR (10),"
DISPLAY " LUSER_ID INT,"
DISPLAY " FILE_QUOTA INT,"
DISPLAY " FILE_USAGE INT NOT NULL,"
DISPLAY " CHECK (FILE_USAGE >= 0 AND"
DISPLAY " (FILE_QUOTA IS NULL OR FILE_QUOTA >=
- " FILE_USAGE)));"
* EXEC SQL CREATE TABLE LUSERS (
* PRIMARY KEY (LUSER_ID),
* NAAM CHAR (10),
* LUSER_ID INT,
* FILE_QUOTA INT,
* FILE_USAGE INT NOT NULL,
* CHECK (FILE_USAGE >= 0 AND
* (FILE_QUOTA IS NULL OR FILE_QUOTA >= FILE_USAGE)))
* ;
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 " "
*When you delete a user, all his/her files are also deleted
DISPLAY "CREATE TABLE LUSER_DATA ("
DISPLAY " FOREIGN KEY (LUSER_ID) REFERENCES LUSERS ON
- " DELETE CASCADE,"
DISPLAY " PRIMARY KEY (FILE_NAME, LUSER_ID),"
DISPLAY " FILE_NAME CHAR (8) NOT NULL,"
DISPLAY " LUSER_ID INT NOT NULL,"
DISPLAY " LUSER_DATA CHAR (30));"
* EXEC SQL CREATE TABLE LUSER_DATA (
* FOREIGN KEY (LUSER_ID) REFERENCES LUSERS ON DELETE
* CASCADE,
* PRIMARY KEY (FILE_NAME, LUSER_ID),
* FILE_NAME CHAR (8) NOT NULL,
* LUSER_ID INT NOT NULL,
* LUSER_DATA CHAR (30));
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 TABLE AUDIT_CODES ("
DISPLAY " ACTION_KEY INT PRIMARY KEY,"
DISPLAY " LUSER_ACTION CHAR (6) NOT NULL,"
DISPLAY " CHECK (LUSER_ACTION = 'INSERT' OR LUSER_ACTION =
- " 'ACCVIO'"
DISPLAY " OR LUSER_ACTION = 'DELETE'));"
* EXEC SQL CREATE TABLE AUDIT_CODES (
* ACTION_KEY INT PRIMARY KEY,
* LUSER_ACTION CHAR (6) NOT NULL,
* CHECK (LUSER_ACTION = 'INSERT' OR LUSER_ACTION = 'ACCVIO'
* OR LUSER_ACTION = 'DELETE'));
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 " "
*This table ostensibly separates UID's that have been valid at s
*time in history from those that have always been invalid.
DISPLAY "CREATE TABLE ALL_USER_IDS (LUSER_ID INT UNIQUE);"
* EXEC SQL CREATE TABLE ALL_USER_IDS (LUSER_ID INT UNIQUE)
* ;
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 " "
*The persistent audit records need to be consistent in the
*event that a LUSER_ID is recycled after someone is deleted.
*The original LUSER_ID is preserved in SAVED_LUSER_ID in case
*it is needed.
DISPLAY "CREATE TABLE AUDIT_RECORDS ("
DISPLAY " FOREIGN KEY (LUSER_ID) REFERENCES LUSERS ON
- " DELETE SET NULL,"
DISPLAY " LUSER_ID INT,"
DISPLAY " SAVED_LUSER_ID INT NOT NULL"
DISPLAY " REFERENCES ALL_USER_IDS (LUSER_ID) ON DELETE
- " NO ACTION,"
DISPLAY " ACTION_KEY INT DEFAULT 0 NOT NULL"
DISPLAY " REFERENCES AUDIT_CODES ON DELETE SET
- " DEFAULT);"
* EXEC SQL CREATE TABLE AUDIT_RECORDS (
* FOREIGN KEY (LUSER_ID) REFERENCES LUSERS ON DELETE SET
* NULL,
* LUSER_ID INT,
* SAVED_LUSER_ID INT NOT NULL
* REFERENCES ALL_USER_IDS (LUSER_ID) ON DELETE NO ACTION,
* ACTION_KEY INT DEFAULT 0 NOT NULL
* REFERENCES AUDIT_CODES ON DELETE SET DEFAULT);
CALL "SUB11" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
* EXEC SQL COMMIT WORK;
CALL "SUB12" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
*Initialize audit codes
DISPLAY "INSERT INTO AUDIT_CODES VALUES (0, 'ACCVIO');"
* EXEC SQL INSERT INTO AUDIT_CODES VALUES (0, 'ACCVIO')
* ;
CALL "SUB13" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO AUDIT_CODES VALUES (1, 'INSERT'); "
* EXEC SQL INSERT INTO AUDIT_CODES VALUES (1, 'INSERT')
* ;
CALL "SUB14" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO AUDIT_CODES VALUES (2, 'DELETE');"
* EXEC SQL INSERT INTO AUDIT_CODES VALUES (2, 'DELETE')
* ;
CALL "SUB15" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
*Once upon a time, there were three little users.
DISPLAY "INSERT INTO LUSERS VALUES ('root', 0, NULL, 2);"
* EXEC SQL INSERT INTO LUSERS VALUES ('root', 0, NULL, 2)
* ;
CALL "SUB16" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO LUSERS VALUES ('BIFF', 1, 0, 0);"
* EXEC SQL INSERT INTO LUSERS VALUES ('BIFF', 1, 0, 0)
* ;
CALL "SUB17" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO LUSERS VALUES ('Kibo', 2, 1, 1);"
* EXEC SQL INSERT INTO LUSERS VALUES ('Kibo', 2, 1, 1)
* ;
CALL "SUB18" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO ALL_USER_IDS VALUES (0);"
* EXEC SQL INSERT INTO ALL_USER_IDS VALUES (0);
CALL "SUB19" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO ALL_USER_IDS VALUES (1);"
* EXEC SQL INSERT INTO ALL_USER_IDS VALUES (1);
CALL "SUB20" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO ALL_USER_IDS VALUES (2);"
* EXEC SQL INSERT INTO ALL_USER_IDS VALUES (2);
CALL "SUB21" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
*Root hated Kibo only slightly less than BIFF
DISPLAY "INSERT INTO LUSER_DATA VALUES ('ROOT1',"
DISPLAY " 0, 'BIFF is a total loser');"
* EXEC SQL INSERT INTO LUSER_DATA VALUES ('ROOT1',
* 0, 'BIFF is a total loser');
CALL "SUB22" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO LUSER_DATA VALUES ('ROOT2',"
DISPLAY " 0, 'Kibo wastes disk space');"
* EXEC SQL INSERT INTO LUSER_DATA VALUES ('ROOT2',
* 0, 'Kibo wastes disk space');
CALL "SUB23" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO AUDIT_RECORDS VALUES (0, 0, 1);"
* EXEC SQL INSERT INTO AUDIT_RECORDS VALUES (0, 0, 1)
* ;
CALL "SUB24" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO AUDIT_RECORDS VALUES (0, 0, 1);"
* EXEC SQL INSERT INTO AUDIT_RECORDS VALUES (0, 0, 1)
* ;
CALL "SUB25" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
*One day, Kibo created a file.
DISPLAY "INSERT INTO AUDIT_RECORDS VALUES (2, 2, 1);"
* EXEC SQL INSERT INTO AUDIT_RECORDS VALUES (2, 2, 1)
* ;
CALL "SUB26" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
*Jealous BIFF (no quota) decided to hack Kibo's account
MOVE 0 TO int1
.
P199.
DISPLAY "INSERT INTO AUDIT_RECORDS VALUES (1, 1, 0);"
* EXEC SQL INSERT INTO AUDIT_RECORDS VALUES (1, 1, 0)
* ;
CALL "SUB27" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
COMPUTE int1 = int1 + 1
if (int1 NOT > 5) then
GO TO P199
END-IF
*Finally he succeeded and overwrote Kibo's file with garbage
DISPLAY "INSERT INTO AUDIT_RECORDS VALUES (2, 2, 0);"
* EXEC SQL INSERT INTO AUDIT_RECORDS VALUES (2, 2, 0)
* ;
CALL "SUB28" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO AUDIT_RECORDS VALUES (2, 2, 2);"
* EXEC SQL INSERT INTO AUDIT_RECORDS VALUES (2, 2, 2)
* ;
CALL "SUB29" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO AUDIT_RECORDS VALUES (2, 2, 1);"
* EXEC SQL INSERT INTO AUDIT_RECORDS VALUES (2, 2, 1)
* ;
CALL "SUB30" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO LUSER_DATA VALUES ('HAHA',"
DISPLAY " 2, 'I G0T KIB0Z PASSW0RD!!!');"
* EXEC SQL INSERT INTO LUSER_DATA VALUES ('HAHA',
* 2, 'I G0T KIB0Z PASSW0RD!!!');
CALL "SUB31" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
*This angered root, who instantly disbursed group punishment...
DISPLAY "DELETE FROM LUSERS"
DISPLAY " WHERE NAAM <> 'root';"
* EXEC SQL DELETE FROM LUSERS WHERE NAAM <> 'root';
CALL "SUB32" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
COMPUTE int1 = -1
DISPLAY "SELECT COUNT(*) INTO :int1"
DISPLAY " FROM LUSER_DATA;"
* EXEC SQL SELECT COUNT(*) INTO :int1 FROM LUSER_DATA
* ;
CALL "SUB33" 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
COMPUTE int1 = -1
DISPLAY "SELECT COUNT(*) INTO :int1"
DISPLAY " FROM LUSERS;"
* EXEC SQL SELECT COUNT(*) INTO :int1 FROM LUSERS;
CALL "SUB34" 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
COMPUTE int1 = -1
DISPLAY "SELECT COUNT(*) INTO :int1"
DISPLAY " FROM AUDIT_RECORDS;"
* EXEC SQL SELECT COUNT(*) INTO :int1 FROM AUDIT_RECORDS
* ;
CALL "SUB35" USING SQLCODE SQLSTATE int1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "int1 should be 12; its value is ", int1
if (int1 NOT = 12) then
MOVE 0 TO flag
END-IF
COMPUTE int1 = -1
DISPLAY "SELECT COUNT(*) INTO :int1"
DISPLAY " FROM AUDIT_RECORDS WHERE LUSER_ID IS NULL;"
* EXEC SQL SELECT COUNT(*) INTO :int1 FROM AUDIT_RECORDS
* WHERE LUSER_ID IS NULL;
CALL "SUB36" 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 "SELECT COUNT(*) INTO :int1"
DISPLAY " FROM AUDIT_RECORDS WHERE SAVED_LUSER_ID IS
- " NULL;"
* EXEC SQL SELECT COUNT(*) INTO :int1 FROM AUDIT_RECORDS
* WHERE SAVED_LUSER_ID IS NULL;
CALL "SUB37" USING SQLCODE SQLSTATE int1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "int1 should be 0; its value is ", int1
if (int1 NOT = 0) then
MOVE 0 TO flag
END-IF
*...and disabled deletes just for good measure.
DISPLAY "DELETE FROM AUDIT_CODES"
DISPLAY " WHERE LUSER_ACTION = 'DELETE';"
* EXEC SQL DELETE FROM AUDIT_CODES
* WHERE LUSER_ACTION = 'DELETE';
CALL "SUB38" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
COMPUTE int1 = -1
DISPLAY "SELECT COUNT(*) INTO :int1 FROM AUDIT_RECORDS"
DISPLAY " WHERE ACTION_KEY = 2;"
* EXEC SQL SELECT COUNT(*) INTO :int1 FROM AUDIT_RECORDS
* WHERE ACTION_KEY = 2;
CALL "SUB39" USING SQLCODE SQLSTATE int1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "int1 should be 0; its value is ", int1
if (int1 NOT = 0) then
MOVE 0 TO flag
END-IF
COMPUTE int1 = -1
DISPLAY "SELECT COUNT(*) INTO :int1 FROM AUDIT_RECORDS"
DISPLAY " WHERE ACTION_KEY = 0;"
* EXEC SQL SELECT COUNT(*) INTO :int1 FROM AUDIT_RECORDS
* WHERE ACTION_KEY = 0;
CALL "SUB40" 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
*See if that "ON DELETE NO ACTION" did what it was supposed to d
*It should _not_ allow us to create dangling references.
DISPLAY "DELETE FROM ALL_USER_IDS;"
* EXEC SQL DELETE FROM ALL_USER_IDS;
CALL "SUB41" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
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 "COMMIT WORK;"
* EXEC SQL COMMIT WORK;
CALL "SUB42" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "DROP TABLE AUDIT_RECORDS CASCADE;"
* EXEC SQL DROP TABLE AUDIT_RECORDS CASCADE;
CALL "SUB43" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
* EXEC SQL COMMIT WORK;
CALL "SUB44" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "DROP TABLE ALL_USER_IDS CASCADE;"
* EXEC SQL DROP TABLE ALL_USER_IDS CASCADE;
CALL "SUB45" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
* EXEC SQL COMMIT WORK;
CALL "SUB46" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "DROP TABLE AUDIT_CODES CASCADE;"
* EXEC SQL DROP TABLE AUDIT_CODES CASCADE;
CALL "SUB47" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
* EXEC SQL COMMIT WORK;
CALL "SUB48" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "DROP TABLE LUSER_DATA CASCADE;"
* EXEC SQL DROP TABLE LUSER_DATA CASCADE;
CALL "SUB49" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
* EXEC SQL COMMIT WORK;
CALL "SUB50" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "DROP TABLE LUSERS CASCADE;"
* EXEC SQL DROP TABLE LUSERS CASCADE;
CALL "SUB51" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
* EXEC SQL COMMIT WORK;
CALL "SUB52" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
if ( flag = 1 ) then
DISPLAY " *** pass *** "
* EXEC SQL INSERT INTO HU.TESTREPORT
* VALUES('0645','pass','MCO');
CALL "SUB53" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
else
DISPLAY " dml117.sco *** fail *** "
* EXEC SQL INSERT INTO HU.TESTREPORT
* VALUES('0645','fail','MCO');
CALL "SUB54" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
COMPUTE errcnt = errcnt + 1
END-IF
DISPLAY "==============================================="
* EXEC SQL COMMIT WORK;
CALL "SUB55" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
******************** END TEST0645 ********************
**** 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.32Quellennavigators
Analyse erneut starten
]
|
