|
IDENTIFICATION DIVISION.
PROGRAM-ID. DML104.
ENVIRONMENT DIVISION.
CONFIGURATION SECTION.
SOURCE-COMPUTER. xyz.
OBJECT-COMPUTER. xyz.
DATA DIVISION.
WORKING-STORAGE SECTION.
* Standard COBOL (file "DML104.SCO") calling SQL
* procedures in file "DML104.MCO".
****************************************************************
*
* COMMENT SECTION
*
* DATE 1993/08/04 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.
*
* DML104.SCO
* WRITTEN BY: David W. Flater
* TRANSLATED AUTOMATICALLY FROM EMBEDDED COBOL BY CHRIS SCHANZLE
*
* This routine tests Fips Feature #4 for static TSQL. Fips
* Feature #4 is "joined table."
*
* REFERENCES
* FIPS PUB 127-2 14.1 Transitional SQL
* ANSI SQL-1992 7.5 <joined table>
*
****************************************************************
* 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, dml104.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 TEST0591 *******************
MOVE 1 TO flag
DISPLAY " TEST0591 "
DISPLAY " NATURAL JOIN (feature 4) (static)"
DISPLAY "References:"
DISPLAY " FIPS PUB 127-2 14.1 Transitional SQL feature
- " 4"
DISPLAY " ANSI SQL-1992 7.5 "
DISPLAY " - - - - - - - - - - - - - - - - - - -"
COMPUTE SQLCODE = -1
MOVE "xxxxx" TO SQLSTATE
DISPLAY "CREATE TABLE GROUPS1"
DISPLAY " (EMPNUM INT, GRP INT);"
* EXEC SQL CREATE TABLE GROUPS1
* (EMPNUM INT, GRP INT);
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 NAMES1"
DISPLAY " (EMPNUM INT, NAAM CHAR(5));"
* EXEC SQL CREATE TABLE NAMES1
* (EMPNUM INT, NAAM 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 " "
DISPLAY "CREATE VIEW NAMGRP1 AS"
DISPLAY " SELECT * FROM NAMES1 NATURAL JOIN GROUPS1;"
* EXEC SQL CREATE VIEW NAMGRP1 AS
* SELECT * FROM NAMES1 NATURAL JOIN GROUPS1;
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 GROUPS1 VALUES (0, 10);"
* EXEC SQL INSERT INTO GROUPS1 VALUES (0, 10);
CALL "SUB9" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO GROUPS1 VALUES (1, 20);"
* EXEC SQL INSERT INTO GROUPS1 VALUES (1, 20);
CALL "SUB10" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO GROUPS1 VALUES (2, 30);"
* EXEC SQL INSERT INTO GROUPS1 VALUES (2, 30);
CALL "SUB11" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO GROUPS1 VALUES (3, 40);"
* EXEC SQL INSERT INTO GROUPS1 VALUES (3, 40);
CALL "SUB12" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO NAMES1 VALUES (5, 'HARRY');"
* EXEC SQL INSERT INTO NAMES1 VALUES (5, 'HARRY');
CALL "SUB13" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO NAMES1 VALUES (1, 'MARY');"
* EXEC SQL INSERT INTO NAMES1 VALUES (1, 'MARY');
CALL "SUB14" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO NAMES1 VALUES (7, 'LARRY');"
* EXEC SQL INSERT INTO NAMES1 VALUES (7, 'LARRY');
CALL "SUB15" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO NAMES1 VALUES (0, 'KERI');"
* EXEC SQL INSERT INTO NAMES1 VALUES (0, 'KERI');
CALL "SUB16" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO NAMES1 VALUES (9, 'BARRY');"
* EXEC SQL INSERT INTO NAMES1 VALUES (9, 'BARRY');
CALL "SUB17" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
COMPUTE int1 = -1
DISPLAY "SELECT EMPNUM INTO :int1"
DISPLAY " FROM NAMGRP1"
DISPLAY " WHERE NAAM = 'KERI'"
DISPLAY " AND GRP = 10;"
* EXEC SQL SELECT EMPNUM INTO :int1
* FROM NAMGRP1
* WHERE NAAM = 'KERI'
* AND GRP = 10;
CALL "SUB18" 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 EMPNUM INTO :int1"
DISPLAY " FROM NAMGRP1"
DISPLAY " WHERE NAAM = 'MARY'"
DISPLAY " AND GRP = 20;"
* EXEC SQL SELECT EMPNUM INTO :int1
* FROM NAMGRP1
* WHERE NAAM = 'MARY'
* AND GRP = 20;
CALL "SUB19" 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
* EXEC SQL SELECT COUNT(*) INTO :int1
* FROM NAMGRP1
* WHERE (NAAM <> 'MARY'
* AND NAAM <> 'KERI')
* OR GRP <> 20
* AND GRP <> 10
* OR EMPNUM <> 0
* AND EMPNUM <> 1
* OR NAAM IS NULL
* OR GRP IS NULL
* OR EMPNUM IS NULL;
CALL "SUB20" USING SQLCODE SQLSTATE int1
MOVE SQLCODE TO SQL-COD
DISPLAY "SELECT COUNT(*) INTO :int1"
DISPLAY " FROM NAMGRP1"
DISPLAY " WHERE (NAAM <> 'MARY'"
DISPLAY " AND NAAM <> 'KERI')"
DISPLAY " OR GRP <> 20"
DISPLAY " AND GRP <> 10"
DISPLAY " OR EMPNUM <> 0"
DISPLAY " AND EMPNUM <> 1"
DISPLAY " OR NAAM IS NULL"
DISPLAY " OR GRP IS NULL"
DISPLAY " OR EMPNUM IS NULL;"
PERFORM CHCKOK
DISPLAY "int1 should be 0; its value is ", int1
if (int1 NOT = 0) then
MOVE 0 TO flag
END-IF
DISPLAY "ROLLBACK WORK;"
* EXEC SQL ROLLBACK WORK;
CALL "SUB21" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
DISPLAY "DROP TABLE NAMES1 CASCADE;"
* EXEC SQL DROP TABLE NAMES1 CASCADE;
CALL "SUB22" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
* EXEC SQL COMMIT WORK;
CALL "SUB23" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "DROP TABLE GROUPS1 CASCADE;"
* EXEC SQL DROP TABLE GROUPS1 CASCADE;
CALL "SUB24" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
* EXEC SQL COMMIT WORK;
CALL "SUB25" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
if ( flag = 1 ) then
DISPLAY " *** pass *** "
* EXEC SQL INSERT INTO HU.TESTREPORT
* VALUES('0591','pass','MCO');
CALL "SUB26" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
else
DISPLAY " dml104.sco *** fail *** "
* EXEC SQL INSERT INTO HU.TESTREPORT
* VALUES('0591','fail','MCO');
CALL "SUB27" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
COMPUTE errcnt = errcnt + 1
END-IF
DISPLAY "==============================================="
* EXEC SQL COMMIT WORK;
CALL "SUB28" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
******************** END TEST0591 ********************
******************** BEGIN TEST0592 *******************
MOVE 1 TO flag
DISPLAY " TEST0592 "
DISPLAY " INNER JOIN (feature 4) (static)"
DISPLAY "References:"
DISPLAY " FIPS PUB 127-2 14.1 Transitional SQL feature
- " 4"
DISPLAY " ANSI SQL-1992 7.5 "
DISPLAY " - - - - - - - - - - - - - - - - - - -"
COMPUTE SQLCODE = -1
MOVE "xxxxx" TO SQLSTATE
DISPLAY "CREATE TABLE GROUPS2"
DISPLAY " (EMPNUM SMALLINT, GRP INT);"
* EXEC SQL CREATE TABLE GROUPS2
* (EMPNUM SMALLINT, GRP INT);
CALL "SUB29" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
* EXEC SQL COMMIT WORK;
CALL "SUB30" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "CREATE TABLE NAMES2"
DISPLAY " (EMPNUM INT, NAAM CHAR(5));"
* EXEC SQL CREATE TABLE NAMES2
* (EMPNUM INT, NAAM CHAR(5));
CALL "SUB31" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
* EXEC SQL COMMIT WORK;
CALL "SUB32" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "CREATE VIEW NAMGRP2 AS"
DISPLAY " SELECT * FROM NAMES2 INNER JOIN GROUPS2 USING
- " (EMPNUM);"
* EXEC SQL CREATE VIEW NAMGRP2 AS
* SELECT * FROM NAMES2 INNER JOIN GROUPS2 USING (EMPNUM)
* ;
CALL "SUB33" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
* EXEC SQL COMMIT WORK;
CALL "SUB34" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
*Inner is implicit -- 7.5 SR.3
DISPLAY "CREATE VIEW NMGRP2 AS"
DISPLAY " SELECT * FROM NAMES2 JOIN GROUPS2"
DISPLAY " USING (EMPNUM) WHERE EMPNUM > 0;"
* EXEC SQL CREATE VIEW NMGRP2 AS
* SELECT * FROM NAMES2 JOIN GROUPS2
* USING (EMPNUM) WHERE EMPNUM > 0;
CALL "SUB35" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
* EXEC SQL COMMIT WORK;
CALL "SUB36" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO GROUPS2 VALUES (0, 10);"
* EXEC SQL INSERT INTO GROUPS2 VALUES (0, 10);
CALL "SUB37" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO GROUPS2 VALUES (1, 20);"
* EXEC SQL INSERT INTO GROUPS2 VALUES (1, 20);
CALL "SUB38" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO GROUPS2 VALUES (2, 30);"
* EXEC SQL INSERT INTO GROUPS2 VALUES (2, 30);
CALL "SUB39" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO GROUPS2 VALUES (3, 40);"
* EXEC SQL INSERT INTO GROUPS2 VALUES (3, 40);
CALL "SUB40" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO NAMES2 VALUES (5, 'HARRY');"
* EXEC SQL INSERT INTO NAMES2 VALUES (5, 'HARRY');
CALL "SUB41" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO NAMES2 VALUES (1, 'MARY');"
* EXEC SQL INSERT INTO NAMES2 VALUES (1, 'MARY');
CALL "SUB42" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO NAMES2 VALUES (7, 'LARRY');"
* EXEC SQL INSERT INTO NAMES2 VALUES (7, 'LARRY');
CALL "SUB43" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO NAMES2 VALUES (0, 'KERI');"
* EXEC SQL INSERT INTO NAMES2 VALUES (0, 'KERI');
CALL "SUB44" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO NAMES2 VALUES (9, 'BARRY');"
* EXEC SQL INSERT INTO NAMES2 VALUES (9, 'BARRY');
CALL "SUB45" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
COMPUTE int1 = -1
DISPLAY "SELECT EMPNUM INTO :int1"
DISPLAY " FROM NAMGRP2"
DISPLAY " WHERE NAAM = 'KERI'"
DISPLAY " AND GRP = 10;"
* EXEC SQL SELECT EMPNUM INTO :int1
* FROM NAMGRP2
* WHERE NAAM = 'KERI'
* AND GRP = 10;
CALL "SUB46" 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 EMPNUM INTO :int1"
DISPLAY " FROM NAMGRP2"
DISPLAY " WHERE NAAM = 'MARY'"
DISPLAY " AND GRP = 20;"
* EXEC SQL SELECT EMPNUM INTO :int1
* FROM NAMGRP2
* WHERE NAAM = 'MARY'
* AND GRP = 20;
CALL "SUB47" 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
* EXEC SQL SELECT COUNT(*) INTO :int1
* FROM NAMGRP2
* WHERE NAAM <> 'MARY'
* AND NAAM <> 'KERI'
* OR GRP <> 20
* AND GRP <> 10
* OR EMPNUM <> 0
* AND EMPNUM <> 1
* OR NAAM IS NULL
* OR GRP IS NULL
* OR EMPNUM IS NULL;
CALL "SUB48" USING SQLCODE SQLSTATE int1
MOVE SQLCODE TO SQL-COD
DISPLAY "SELECT COUNT(*) INTO :int1"
DISPLAY " FROM NAMGRP2"
DISPLAY " WHERE NAAM <> 'MARY'"
DISPLAY " AND NAAM <> 'KERI'"
DISPLAY " OR GRP <> 20"
DISPLAY " AND GRP <> 10"
DISPLAY " OR EMPNUM <> 0"
DISPLAY " AND EMPNUM <> 1"
DISPLAY " OR NAAM IS NULL"
DISPLAY " OR GRP IS NULL"
DISPLAY " OR EMPNUM IS NULL;"
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 EMPNUM INTO :int1"
DISPLAY " FROM NMGRP2"
DISPLAY " WHERE NAAM = 'MARY'"
DISPLAY " AND GRP = 20;"
* EXEC SQL SELECT EMPNUM INTO :int1
* FROM NMGRP2
* WHERE NAAM = 'MARY'
* AND GRP = 20;
CALL "SUB49" 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 NMGRP2"
DISPLAY " WHERE NAAM <> 'MARY'"
DISPLAY " OR GRP <> 20"
DISPLAY " OR EMPNUM <> 1"
DISPLAY " OR NAAM IS NULL"
DISPLAY " OR GRP IS NULL"
DISPLAY " OR EMPNUM IS NULL;"
* EXEC SQL SELECT COUNT(*) INTO :int1
* FROM NMGRP2
* WHERE NAAM <> 'MARY'
* OR GRP <> 20
* OR EMPNUM <> 1
* OR NAAM IS NULL
* OR GRP IS NULL
* OR EMPNUM IS NULL;
CALL "SUB50" 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
DISPLAY "ROLLBACK WORK;"
* EXEC SQL ROLLBACK WORK;
CALL "SUB51" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
DISPLAY "DROP TABLE NAMES2 CASCADE;"
* EXEC SQL DROP TABLE NAMES2 CASCADE;
CALL "SUB52" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
* EXEC SQL COMMIT WORK;
CALL "SUB53" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "DROP TABLE GROUPS2 CASCADE;"
* EXEC SQL DROP TABLE GROUPS2 CASCADE;
CALL "SUB54" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
* EXEC SQL COMMIT WORK;
CALL "SUB55" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
*NAMGRP and NMGRP2 should go away on their own due to implicit C
if ( flag = 1 ) then
DISPLAY " *** pass *** "
* EXEC SQL INSERT INTO HU.TESTREPORT
* VALUES('0592','pass','MCO');
CALL "SUB56" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
else
DISPLAY " dml104.sco *** fail *** "
* EXEC SQL INSERT INTO HU.TESTREPORT
* VALUES('0592','fail','MCO');
CALL "SUB57" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
COMPUTE errcnt = errcnt + 1
END-IF
DISPLAY "==============================================="
* EXEC SQL COMMIT WORK;
CALL "SUB58" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
******************** END TEST0592 ********************
******************** BEGIN TEST0593 *******************
MOVE 1 TO flag
DISPLAY " TEST0593 "
DISPLAY " LEFT OUTER JOIN (feature 4) (static)"
DISPLAY "References:"
DISPLAY " FIPS PUB 127-2 14.1 Transitional SQL feature
- " 4"
DISPLAY " ANSI SQL-1992 7.5 "
DISPLAY " - - - - - - - - - - - - - - - - - - -"
COMPUTE SQLCODE = -1
MOVE "xxxxx" TO SQLSTATE
DISPLAY "CREATE TABLE GROUPS3"
DISPLAY " (EMPNUM INT, GRP INT);"
* EXEC SQL CREATE TABLE GROUPS3
* (EMPNUM INT, GRP INT);
CALL "SUB59" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
* EXEC SQL COMMIT WORK;
CALL "SUB60" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "CREATE TABLE NAMES3"
DISPLAY " (EMPNUM INT, NAAM CHAR(5));"
* EXEC SQL CREATE TABLE NAMES3
* (EMPNUM INT, NAAM CHAR(5));
CALL "SUB61" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
* EXEC SQL COMMIT WORK;
CALL "SUB62" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "CREATE VIEW NAMGRP3 AS"
DISPLAY " SELECT * FROM NAMES3 LEFT OUTER JOIN GROUPS3
- " USING (EMPNUM);"
* EXEC SQL CREATE VIEW NAMGRP3 AS
* SELECT * FROM NAMES3 LEFT OUTER JOIN GROUPS3 USING
* (EMPNUM);
CALL "SUB63" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
* EXEC SQL COMMIT WORK;
CALL "SUB64" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
*This is just to see if ON is also supported
DISPLAY "CREATE VIEW NMGRP3 AS"
DISPLAY " SELECT NAAM, GRP FROM NAMES3 LEFT OUTER JOIN
- " GROUPS3"
DISPLAY " ON NAMES3.EMPNUM < GROUPS3.EMPNUM"
DISPLAY " WHERE NAAM <> 'KERI';"
* EXEC SQL CREATE VIEW NMGRP3 AS
* SELECT NAAM, GRP FROM NAMES3 LEFT OUTER JOIN GROUPS3
* ON NAMES3.EMPNUM < GROUPS3.EMPNUM
* WHERE NAAM <> 'KERI';
CALL "SUB65" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
* EXEC SQL COMMIT WORK;
CALL "SUB66" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO GROUPS3 VALUES (0, 10);"
* EXEC SQL INSERT INTO GROUPS3 VALUES (0, 10);
CALL "SUB67" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO GROUPS3 VALUES (1, 20);"
* EXEC SQL INSERT INTO GROUPS3 VALUES (1, 20);
CALL "SUB68" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO GROUPS3 VALUES (2, 30);"
* EXEC SQL INSERT INTO GROUPS3 VALUES (2, 30);
CALL "SUB69" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO GROUPS3 VALUES (3, 40);"
* EXEC SQL INSERT INTO GROUPS3 VALUES (3, 40);
CALL "SUB70" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO NAMES3 VALUES (5, 'HARRY');"
* EXEC SQL INSERT INTO NAMES3 VALUES (5, 'HARRY');
CALL "SUB71" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO NAMES3 VALUES (1, 'MARY');"
* EXEC SQL INSERT INTO NAMES3 VALUES (1, 'MARY');
CALL "SUB72" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO NAMES3 VALUES (7, 'LARRY');"
* EXEC SQL INSERT INTO NAMES3 VALUES (7, 'LARRY');
CALL "SUB73" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO NAMES3 VALUES (0, 'KERI');"
* EXEC SQL INSERT INTO NAMES3 VALUES (0, 'KERI');
CALL "SUB74" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO NAMES3 VALUES (9, 'BARRY');"
* EXEC SQL INSERT INTO NAMES3 VALUES (9, 'BARRY');
CALL "SUB75" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
COMPUTE int1 = -1
DISPLAY "SELECT COUNT(*) INTO :int1"
DISPLAY " FROM NAMGRP3"
DISPLAY " WHERE EMPNUM = 0"
DISPLAY " AND NAAM = 'KERI'"
DISPLAY " AND GRP = 10;"
* EXEC SQL SELECT COUNT(*) INTO :int1
* FROM NAMGRP3
* WHERE EMPNUM = 0
* AND NAAM = 'KERI'
* AND GRP = 10;
CALL "SUB76" 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 NAMGRP3"
DISPLAY " WHERE EMPNUM = 1"
DISPLAY " AND NAAM = 'MARY'"
DISPLAY " AND GRP = 20;"
* EXEC SQL SELECT COUNT(*) INTO :int1
* FROM NAMGRP3
* WHERE EMPNUM = 1
* AND NAAM = 'MARY'
* AND GRP = 20;
CALL "SUB77" 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 NAMGRP3"
DISPLAY " WHERE EMPNUM = 5"
DISPLAY " AND NAAM = 'HARRY'"
DISPLAY " AND GRP IS NULL;"
* EXEC SQL SELECT COUNT(*) INTO :int1
* FROM NAMGRP3
* WHERE EMPNUM = 5
* AND NAAM = 'HARRY'
* AND GRP IS NULL;
CALL "SUB78" 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 NAMGRP3"
DISPLAY " WHERE EMPNUM = 7"
DISPLAY " AND NAAM = 'LARRY'"
DISPLAY " AND GRP IS NULL;"
* EXEC SQL SELECT COUNT(*) INTO :int1
* FROM NAMGRP3
* WHERE EMPNUM = 7
* AND NAAM = 'LARRY'
* AND GRP IS NULL;
CALL "SUB79" 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 NAMGRP3"
DISPLAY " WHERE EMPNUM = 9"
DISPLAY " AND NAAM = 'BARRY'"
DISPLAY " AND GRP IS NULL;"
* EXEC SQL SELECT COUNT(*) INTO :int1
* FROM NAMGRP3
* WHERE EMPNUM = 9
* AND NAAM = 'BARRY'
* AND GRP IS NULL;
CALL "SUB80" 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 NAMGRP3;"
* EXEC SQL SELECT COUNT(*) INTO :int1
* FROM NAMGRP3;
CALL "SUB81" USING SQLCODE SQLSTATE int1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "int1 should be 5; its value is ", int1
if (int1 NOT = 5) then
MOVE 0 TO flag
END-IF
COMPUTE int1 = -1
DISPLAY "SELECT COUNT(*) INTO :int1"
DISPLAY " FROM NMGRP3"
DISPLAY " WHERE NAAM = 'HARRY'"
DISPLAY " AND GRP IS NULL;"
* EXEC SQL SELECT COUNT(*) INTO :int1
* FROM NMGRP3
* WHERE NAAM = 'HARRY'
* AND GRP IS NULL;
CALL "SUB82" 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 NMGRP3"
DISPLAY " WHERE NAAM = 'MARY'"
DISPLAY " AND GRP = 30;"
* EXEC SQL SELECT COUNT(*) INTO :int1
* FROM NMGRP3
* WHERE NAAM = 'MARY'
* AND GRP = 30;
CALL "SUB83" 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 NMGRP3"
DISPLAY " WHERE NAAM = 'MARY'"
DISPLAY " AND GRP = 40;"
* EXEC SQL SELECT COUNT(*) INTO :int1
* FROM NMGRP3
* WHERE NAAM = 'MARY'
* AND GRP = 40;
CALL "SUB84" 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 NMGRP3"
DISPLAY " WHERE NAAM = 'BARRY'"
DISPLAY " AND GRP IS NULL;"
* EXEC SQL SELECT COUNT(*) INTO :int1
* FROM NMGRP3
* WHERE NAAM = 'BARRY'
* AND GRP IS NULL;
CALL "SUB85" 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 NMGRP3"
DISPLAY " WHERE NAAM = 'LARRY'"
DISPLAY " AND GRP IS NULL;"
* EXEC SQL SELECT COUNT(*) INTO :int1
* FROM NMGRP3
* WHERE NAAM = 'LARRY'
* AND GRP IS NULL;
CALL "SUB86" 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 NMGRP3;"
* EXEC SQL SELECT COUNT(*) INTO :int1
* FROM NMGRP3;
CALL "SUB87" USING SQLCODE SQLSTATE int1
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "int1 should be 5; its value is ", int1
if (int1 NOT = 5) then
MOVE 0 TO flag
END-IF
DISPLAY "ROLLBACK WORK;"
* EXEC SQL ROLLBACK WORK;
CALL "SUB88" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
DISPLAY "DROP TABLE NAMES3 CASCADE;"
* EXEC SQL DROP TABLE NAMES3 CASCADE;
CALL "SUB89" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
* EXEC SQL COMMIT WORK;
CALL "SUB90" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "DROP TABLE GROUPS3 CASCADE;"
* EXEC SQL DROP TABLE GROUPS3 CASCADE;
CALL "SUB91" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
* EXEC SQL COMMIT WORK;
CALL "SUB92" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
*NAMGRP should go away on its own due to CASCADE.
if ( flag = 1 ) then
DISPLAY " *** pass *** "
* EXEC SQL INSERT INTO HU.TESTREPORT
* VALUES('0593','pass','MCO');
CALL "SUB93" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
else
DISPLAY " dml104.sco *** fail *** "
* EXEC SQL INSERT INTO HU.TESTREPORT
* VALUES('0593','fail','MCO');
CALL "SUB94" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
COMPUTE errcnt = errcnt + 1
END-IF
DISPLAY "==============================================="
* EXEC SQL COMMIT WORK;
CALL "SUB95" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
******************** END TEST0593 ********************
******************** BEGIN TEST0594 *******************
MOVE 1 TO flag
DISPLAY " TEST0594 "
DISPLAY " RIGHT OUTER JOIN (feature 4) (static)"
DISPLAY "References:"
DISPLAY " FIPS PUB 127-2 14.1 Transitional SQL feature
- " 4"
DISPLAY " ANSI SQL-1992 7.5 "
DISPLAY " - - - - - - - - - - - - - - - - - - -"
COMPUTE SQLCODE = -1
MOVE "xxxxx" TO SQLSTATE
DISPLAY "CREATE TABLE GROUPS4"
DISPLAY " (EMPNUM INT, GRP INT);"
* EXEC SQL CREATE TABLE GROUPS4
* (EMPNUM INT, GRP INT);
CALL "SUB96" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
* EXEC SQL COMMIT WORK;
CALL "SUB97" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "CREATE TABLE NAMES4"
DISPLAY " (EMPNUM DECIMAL (4, 2), NAAM CHAR(5));"
* EXEC SQL CREATE TABLE NAMES4
* (EMPNUM DECIMAL (4, 2), NAAM CHAR(5));
CALL "SUB98" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
* EXEC SQL COMMIT WORK;
CALL "SUB99" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "CREATE VIEW NAMGRP4 AS"
DISPLAY " SELECT * FROM NAMES4 RIGHT OUTER JOIN GROUPS4
- " USING (EMPNUM);"
* EXEC SQL CREATE VIEW NAMGRP4 AS
* SELECT * FROM NAMES4 RIGHT OUTER JOIN GROUPS4 USING
* (EMPNUM);
CALL "SUB100" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
* EXEC SQL COMMIT WORK;
CALL "SUB101" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO GROUPS4 VALUES (0, 10);"
* EXEC SQL INSERT INTO GROUPS4 VALUES (0, 10);
CALL "SUB102" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO GROUPS4 VALUES (1, 20);"
* EXEC SQL INSERT INTO GROUPS4 VALUES (1, 20);
CALL "SUB103" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO GROUPS4 VALUES (2, 30);"
* EXEC SQL INSERT INTO GROUPS4 VALUES (2, 30);
CALL "SUB104" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO GROUPS4 VALUES (3, 40);"
* EXEC SQL INSERT INTO GROUPS4 VALUES (3, 40);
CALL "SUB105" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO NAMES4 VALUES (5.0, 'HARRY');"
* EXEC SQL INSERT INTO NAMES4 VALUES (5.0, 'HARRY');
CALL "SUB106" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO NAMES4 VALUES (1.0, 'MARY');"
* EXEC SQL INSERT INTO NAMES4 VALUES (1.0, 'MARY');
CALL "SUB107" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO NAMES4 VALUES (7.0, 'LARRY');"
* EXEC SQL INSERT INTO NAMES4 VALUES (7.0, 'LARRY');
CALL "SUB108" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO NAMES4 VALUES (0.0, 'KERI');"
* EXEC SQL INSERT INTO NAMES4 VALUES (0.0, 'KERI');
CALL "SUB109" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO NAMES4 VALUES (9.0, 'BARRY');"
* EXEC SQL INSERT INTO NAMES4 VALUES (9.0, 'BARRY');
CALL "SUB110" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
COMPUTE int1 = -1
DISPLAY "SELECT COUNT(*) INTO :int1"
DISPLAY " FROM NAMGRP4"
DISPLAY " WHERE EMPNUM = 0"
DISPLAY " AND NAAM = 'KERI'"
DISPLAY " AND GRP = 10;"
* EXEC SQL SELECT COUNT(*) INTO :int1
* FROM NAMGRP4
* WHERE EMPNUM = 0
* AND NAAM = 'KERI'
* AND GRP = 10;
CALL "SUB111" 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 NAMGRP4"
DISPLAY " WHERE EMPNUM = 1"
DISPLAY " AND NAAM = 'MARY'"
DISPLAY " AND GRP = 20;"
* EXEC SQL SELECT COUNT(*) INTO :int1
* FROM NAMGRP4
* WHERE EMPNUM = 1
* AND NAAM = 'MARY'
* AND GRP = 20;
CALL "SUB112" 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 NAMGRP4"
DISPLAY " WHERE EMPNUM = 2"
DISPLAY " AND NAAM IS NULL"
DISPLAY " AND GRP = 30;"
* EXEC SQL SELECT COUNT(*) INTO :int1
* FROM NAMGRP4
* WHERE EMPNUM = 2
* AND NAAM IS NULL
* AND GRP = 30;
CALL "SUB113" 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 NAMGRP4"
DISPLAY " WHERE EMPNUM = 3"
DISPLAY " AND NAAM IS NULL"
DISPLAY " AND GRP = 40;"
* EXEC SQL SELECT COUNT(*) INTO :int1
* FROM NAMGRP4
* WHERE EMPNUM = 3
* AND NAAM IS NULL
* AND GRP = 40;
CALL "SUB114" 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 NAMGRP4;"
* EXEC SQL SELECT COUNT(*) INTO :int1
* FROM NAMGRP4;
CALL "SUB115" 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 "ROLLBACK WORK;"
* EXEC SQL ROLLBACK WORK;
CALL "SUB116" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
DISPLAY "DROP TABLE NAMES4 CASCADE;"
* EXEC SQL DROP TABLE NAMES4 CASCADE;
CALL "SUB117" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
* EXEC SQL COMMIT WORK;
CALL "SUB118" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "DROP TABLE GROUPS4 CASCADE;"
* EXEC SQL DROP TABLE GROUPS4 CASCADE;
CALL "SUB119" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
* EXEC SQL COMMIT WORK;
CALL "SUB120" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
*NAMGRP should go away on its own due to CASCADE.
if ( flag = 1 ) then
DISPLAY " *** pass *** "
* EXEC SQL INSERT INTO HU.TESTREPORT
* VALUES('0594','pass','MCO');
CALL "SUB121" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
else
DISPLAY " dml104.sco *** fail *** "
* EXEC SQL INSERT INTO HU.TESTREPORT
* VALUES('0594','fail','MCO');
CALL "SUB122" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
COMPUTE errcnt = errcnt + 1
END-IF
DISPLAY "==============================================="
* EXEC SQL COMMIT WORK;
CALL "SUB123" USING SQLCODE SQLSTATE
MOVE SQLCODE TO SQL-COD
******************** END TEST0594 ********************
**** 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.68 Sekunden
(vorverarbeitet)
¤
|
Haftungshinweis
Die Informationen auf dieser Webseite wurden
nach bestem Wissen sorgfältig zusammengestellt. Es wird jedoch weder Vollständigkeit, noch Richtigkeit,
noch Qualität der bereit gestellten Informationen zugesichert.
Bemerkung:
Die farbliche Syntaxdarstellung ist noch experimentell.
|
