|
IDENTIFICATION DIVISION.
PROGRAM-ID. DML185.
ENVIRONMENT DIVISION.
CONFIGURATION SECTION.
SOURCE-COMPUTER. xyz.
OBJECT-COMPUTER. xyz.
DATA DIVISION.
WORKING-STORAGE SECTION.
* Standard COBOL (file "DML185.SCO") calling SQL
* procedures in file "DML185.MCO".
****************************************************************
*
* COMMENT SECTION
*
* DATE 1996-05-24 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.
*
* DML185.SCO
* WRITTEN BY: Joan Sullivan and David Flater
* TRANSLATED AUTOMATICALLY FROM EMBEDDED COBOL BY CHRIS SCHANZLE
*
* This routine tests Transitional 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 H PIC X(32).
01 indic1 PIC S9(4) DISPLAY SIGN LEADING SEPARATE.
01 intval PIC S9(9) DISPLAY SIGN LEADING SEPARATE.
01 fltval 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, dml185.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 TEST0898 *******************
MOVE 1 TO flag
DISPLAY " TEST0898"
DISPLAY " COLUMN_DEFAULT interpretation"
DISPLAY "References:"
DISPLAY " F#2 -- Basic information schema"
DISPLAY " 21.2.9 -- COLUMNS view"
DISPLAY " - - - - - - - - - - - - - - - - - - -"
COMPUTE SQLCODE = -1
MOVE "xxxxx" TO SQLSTATE
DISPLAY "CREATE TABLE XX"
DISPLAY " (C1 CHAR(18) DEFAULT USER,"
DISPLAY " C2 CHAR(18) DEFAULT 'USER',"
DISPLAY " C3 CHAR(18) DEFAULT 'Hello World',"
DISPLAY " C4 CHAR(18) DEFAULT NULL,"
DISPLAY " C5 TIMESTAMP DEFAULT TIMESTAMP '1993-11-10
- " 00:02:00',"
DISPLAY " C6 REAL DEFAULT 3.3E1,"
DISPLAY " C7 REAL);"
* EXEC SQL CREATE TABLE XX
* (C1 CHAR(18) DEFAULT USER,
* C2 CHAR(18) DEFAULT 'USER',
* C3 CHAR(18) DEFAULT 'Hello World',
* C4 CHAR(18) DEFAULT NULL,
* C5 TIMESTAMP DEFAULT
* TIMESTAMP '1993-11-10 00:02:00',
* C6 REAL DEFAULT 3.3E1,
* C7 REAL);
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 " "
MOVE "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" TO H
MOVE 2 TO indic1
DISPLAY "SELECT COLUMN_DEF"
DISPLAY " INTO :H :indic1"
DISPLAY " FROM INFO_SCHEM.COLUMNS"
DISPLAY " WHERE TABLE_SCHEM = 'FLATER'"
DISPLAY " AND TABLE_NAME = 'XX'"
DISPLAY " AND COLUMN_DEF = 'USER';"
* EXEC SQL SELECT COLUMN_DEF
* INTO :H :indic1
* FROM INFO_SCHEM.COLUMNS
* WHERE TABLE_SCHEM = 'FLATER'
* AND TABLE_NAME = 'XX'
* AND COLUMN_DEF = 'USER';
CALL "SUB5" USING SQLCODE SQLSTATE H indic1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "indic1 should be 0; its value is ", indic1
DISPLAY "H should be USER (no quotes); its value is ", H
if (indic1 NOT = 0 OR H NOT = "USER") then
MOVE 0 TO flag
END-IF
MOVE "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" TO H
MOVE 2 TO indic1
DISPLAY "SELECT COLUMN_DEF INTO :H :indic1"
DISPLAY " FROM INFO_SCHEM.COLUMNS WHERE TABLE_SCHEM =
- " 'FLATER'"
DISPLAY " AND TABLE_NAME = 'XX' AND COLUMN_DEF =
- " '''USER''';"
* EXEC SQL SELECT COLUMN_DEF INTO :H :indic1
* FROM INFO_SCHEM.COLUMNS WHERE TABLE_SCHEM = 'FLATER'
* AND TABLE_NAME = 'XX' AND COLUMN_DEF = '''USER'''
* ;
CALL "SUB6" USING SQLCODE SQLSTATE H indic1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "indic1 should be 0; its value is ", indic1
DISPLAY "H should be 'USER' (with quotes); its value is ",
H
if (indic1 NOT = 0 OR H NOT = "'USER'") then
MOVE 0 TO flag
END-IF
MOVE "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" TO H
MOVE 2 TO indic1
DISPLAY "SELECT COLUMN_DEF INTO :H :indic1"
DISPLAY " FROM INFO_SCHEM.COLUMNS WHERE TABLE_SCHEM =
- " 'FLATER'"
DISPLAY " AND TABLE_NAME = 'XX' AND COLUMN_DEF = '''Hello
- " World''';"
* EXEC SQL SELECT COLUMN_DEF INTO :H :indic1
* FROM INFO_SCHEM.COLUMNS WHERE TABLE_SCHEM = 'FLATER'
* AND TABLE_NAME = 'XX' AND COLUMN_DEF = '''Hello World'''
* ;
CALL "SUB7" USING SQLCODE SQLSTATE H indic1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "indic1 should be 0; its value is ", indic1
DISPLAY "H should be 'Hello World'; its value is ", H
if (indic1 NOT = 0 OR H NOT = "'Hello World'") then
MOVE 0 TO flag
END-IF
MOVE "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" TO H
MOVE 2 TO indic1
DISPLAY "SELECT COLUMN_DEF INTO :H :indic1"
DISPLAY " FROM INFO_SCHEM.COLUMNS WHERE TABLE_SCHEM =
- " 'FLATER'"
DISPLAY " AND TABLE_NAME = 'XX' AND COLUMN_DEF = 'NULL';"
* EXEC SQL SELECT COLUMN_DEF INTO :H :indic1
* FROM INFO_SCHEM.COLUMNS WHERE TABLE_SCHEM = 'FLATER'
* AND TABLE_NAME = 'XX' AND COLUMN_DEF = 'NULL';
CALL "SUB8" USING SQLCODE SQLSTATE H indic1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "indic1 should be 0; its value is ", indic1
DISPLAY "H should be NULL (the char string); its value is
- " ", H
if (indic1 NOT = 0 OR H NOT = "NULL") then
MOVE 0 TO flag
END-IF
MOVE "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" TO H
MOVE 2 TO indic1
DISPLAY "SELECT COLUMN_DEF INTO :H :indic1"
DISPLAY " FROM INFO_SCHEM.COLUMNS WHERE TABLE_SCHEM =
- " 'FLATER'"
DISPLAY " AND TABLE_NAME = 'XX' AND ORDINAL_POSITION = 5;"
* EXEC SQL SELECT COLUMN_DEF INTO :H :indic1
* FROM INFO_SCHEM.COLUMNS WHERE TABLE_SCHEM = 'FLATER'
* AND TABLE_NAME = 'XX' AND ORDINAL_POSITION = 5;
CALL "SUB9" USING SQLCODE SQLSTATE H indic1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO XX (C4, C5)"
DISPLAY " VALUES ('Timestamp', CAST (:H:indic1 AS
- " TIMESTAMP));"
* EXEC SQL INSERT INTO XX (C4, C5)
* VALUES ('Timestamp', CAST (:H:indic1 AS TIMESTAMP))
* ;
CALL "SUB10" USING SQLCODE SQLSTATE H indic1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
MOVE 0 TO intval
DISPLAY "SELECT COUNT(*) INTO :intval"
DISPLAY " FROM XX WHERE C4 = 'Timestamp' "
DISPLAY " AND C5 BETWEEN TIMESTAMP '1993-11-10 00:01:00'
- " AND"
DISPLAY " TIMESTAMP '1993-11-10 00:03:00';"
* EXEC SQL SELECT COUNT(*) INTO :intval
* FROM XX WHERE C4 = 'Timestamp'
* AND C5 BETWEEN TIMESTAMP '1993-11-10 00:01:00' AND
* TIMESTAMP '1993-11-10 00:03:00';
CALL "SUB11" USING SQLCODE SQLSTATE intval
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "intval should be 1; its value is ", intval
if (intval NOT = 1) then
MOVE 0 TO flag
END-IF
DISPLAY "ROLLBACK WORK;"
* EXEC SQL ROLLBACK WORK;
CALL "SUB12" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
MOVE 0 TO indic1
MOVE 0.0 TO fltval
DISPLAY "SELECT CAST (COLUMN_DEF AS REAL)"
DISPLAY " INTO :fltval :indic1"
DISPLAY " FROM INFO_SCHEM.COLUMNS"
DISPLAY " WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME =
- " 'XX'"
DISPLAY " AND ORDINAL_POSITION = 6;"
* EXEC SQL SELECT CAST (COLUMN_DEF AS REAL)
* INTO :fltval :indic1
* FROM INFO_SCHEM.COLUMNS
* WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME = 'XX'
* AND ORDINAL_POSITION = 6;
CALL "SUB13" USING SQLCODE SQLSTATE fltval indic1
MOVE SQLCODE TO SQL-COD
*COLUMN_DEF character string value may be 33, 3.3E1, +33, etc.
PERFORM CHCKOK
DISPLAY "indic1 should be 0; its value is ", indic1
DISPLAY "fltval should be 33 +- 0.1; its value is ", fltval
if (indic1 NOT = 0 OR fltval < 32.9 OR fltval >
33.1) then
MOVE 0 TO flag
END-IF
MOVE 0 TO indic1
DISPLAY "SELECT COLUMN_DEF INTO :H :indic1"
DISPLAY " FROM INFO_SCHEM.COLUMNS WHERE TABLE_SCHEM =
- " 'FLATER'"
DISPLAY " AND TABLE_NAME = 'XX' AND COLUMN_DEF IS NULL;"
* EXEC SQL SELECT COLUMN_DEF INTO :H :indic1
* FROM INFO_SCHEM.COLUMNS WHERE TABLE_SCHEM = 'FLATER'
* AND TABLE_NAME = 'XX' AND COLUMN_DEF IS NULL;
CALL "SUB14" USING SQLCODE SQLSTATE H indic1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "indic1 should be -1; its value is ", indic1
if (indic1 NOT = -1) then
MOVE 0 TO flag
END-IF
DISPLAY "COMMIT WORK;"
* EXEC SQL COMMIT WORK;
CALL "SUB15" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "DROP TABLE XX CASCADE;"
* EXEC SQL DROP TABLE XX CASCADE;
CALL "SUB16" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
* EXEC SQL COMMIT WORK;
CALL "SUB17" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
if ( flag = 1 ) then
DISPLAY " *** pass *** "
* EXEC SQL INSERT INTO HU.TESTREPORT
* VALUES('0898','pass','MCO');
CALL "SUB18" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
else
DISPLAY " dml185.mco *** fail *** "
* EXEC SQL INSERT INTO HU.TESTREPORT
* VALUES('0898','fail','MCO');
CALL "SUB19" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
COMPUTE errcnt = errcnt + 1
END-IF
DISPLAY "==============================================="
* EXEC SQL COMMIT WORK;
CALL "SUB20" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
******************** END TEST0898 ********************
**** 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.46Quellennavigators
Analyse erneut starten
]
|
