Quelle  xts748.mco

-- SQL Test Suite, V6.0, SQL Module xts748.mco
-- 59-byte ID
-- 
MODULE XTS748
LANGUAGE COBOL
AUTHORIZATION CTS1

DECLARE A  CURSOR
               FOR SELECT CONSTRAINT_NAME, TABLE_NAME, CONSTRAINT_TYPE
               FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
               WHERE CONSTRAINT_SCHEMA = 'T12549PC' AND TABLE_SCHEMA =
             'T12549PC'
               ORDER BY CONSTRAINT_NAME
DECLARE B  CURSOR
               FOR SELECT CONSTRAINT_NAME, TABLE_NAME, CONSTRAINT_TYPE
               FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
               WHERE CONSTRAINT_SCHEMA = 'DT12549PC' AND TABLE_SCHEMA =
             'DT12549PC'
               ORDER BY CONSTRAINT_NAME


PROCEDURE SUB1 (SQLCODE, SQLSTATE,
    :UIDX CHAR(18));
      SELECT USER INTO :UIDX FROM CTS1.ECCO;

PROCEDURE SUB2 (SQLCODE, SQLSTATE);
      ROLLBACK WORK;

PROCEDURE SUB3 (SQLCODE, SQLSTATE);
      CREATE SCHEMA T12549PC
                    CREATE TABLE TEST12549
                    (TNUM1 NUMERIC(5)
                    CONSTRAINT CND12549A NOT NULL,
                    TNUM2 NUMERIC(5)
                    CONSTRAINT CND12549B UNIQUE,
                    TNUM3 NUMERIC(5)
                    CONSTRAINT CND12549C CHECK(TNUM3 > 0));

PROCEDURE SUB4 (SQLCODE, SQLSTATE);
      COMMIT WORK;

PROCEDURE SUB5 (SQLCODE, SQLSTATE,
    :COUN NUMERIC (9));
      SELECT COUNT(*) INTO :COUN FROM
                  INFORMATION_SCHEMA.TABLE_CONSTRAINTS
                    WHERE CONSTRAINT_SCHEMA = 'T12549PC' AND TABLE_SCHEMA =
                  'T12549PC';

PROCEDURE SUB6 (SQLCODE, SQLSTATE);
    OPEN a;

PROCEDURE SUB7 (SQLCODE, SQLSTATE,
    :CNAME CHAR(29),
    :TNAME CHAR(29),
    :CTYPE CHAR(29));
      FETCH a INTO :CNAME, :TNAME, :CTYPE;

PROCEDURE SUB8 (SQLCODE, SQLSTATE,
    :CNAME CHAR(29),
    :TNAME CHAR(29),
    :CTYPE CHAR(29));
      FETCH a INTO :CNAME, :TNAME, :CTYPE;

PROCEDURE SUB9 (SQLCODE, SQLSTATE,
    :CNAME CHAR(29),
    :TNAME CHAR(29),
    :CTYPE CHAR(29));
      FETCH a INTO :CNAME, :TNAME, :CTYPE;

PROCEDURE SUB10 (SQLCODE, SQLSTATE);
      CLOSE a;

PROCEDURE SUB11 (SQLCODE, SQLSTATE);
      DELETE FROM CONCATBUF;

PROCEDURE SUB12 (SQLCODE, SQLSTATE);
      INSERT INTO CONCATBUF VALUES (
                    'CREATE SCHEMA DT12549PC CREATE TABLE TEST12549'         
                      ||
                    ' (TNUM1 NUMERIC(5) CONSTRAINT CND12549D UNIQUE,'        
                      ||
                    ' TNUM2 NUMERIC(5) CONSTRAINT CND12549E CHECK(TNUM2 >'
                      ||
                    ' 1000),'
                      ||
                    ' TNUM3 NUMERIC(5) CONSTRAINT CND12549F NOT NULL)'
                    );

PROCEDURE SUB13 (SQLCODE, SQLSTATE,
    :LONGST CHAR(240));
      SELECT ZZ INTO :LONGST FROM CONCATBUF;

PROCEDURE SUB14 (SQLCODE, SQLSTATE);
      COMMIT WORK;

PROCEDURE SUB15 (SQLCODE, SQLSTATE,
    :LONGST CHAR(240));
      EXECUTE IMMEDIATE :LONGST;

PROCEDURE SUB16 (SQLCODE, SQLSTATE);
      COMMIT WORK;

PROCEDURE SUB17 (SQLCODE, SQLSTATE,
    :COUN NUMERIC (9));
      SELECT COUNT(*) INTO :COUN FROM
                  INFORMATION_SCHEMA.TABLE_CONSTRAINTS
                    WHERE CONSTRAINT_SCHEMA = 'DT12549PC' AND TABLE_SCHEMA =
                  'DT12549PC';

PROCEDURE SUB18 (SQLCODE, SQLSTATE);
    OPEN b;

PROCEDURE SUB19 (SQLCODE, SQLSTATE,
    :CNAME CHAR(29),
    :TNAME CHAR(29),
    :CTYPE CHAR(29));
      FETCH b INTO :CNAME, :TNAME, :CTYPE;

PROCEDURE SUB20 (SQLCODE, SQLSTATE,
    :CNAME CHAR(29),
    :TNAME CHAR(29),
    :CTYPE CHAR(29));
      FETCH b INTO :CNAME, :TNAME, :CTYPE;

PROCEDURE SUB21 (SQLCODE, SQLSTATE,
    :CNAME CHAR(29),
    :TNAME CHAR(29),
    :CTYPE CHAR(29));
      FETCH b INTO :CNAME, :TNAME, :CTYPE;

PROCEDURE SUB22 (SQLCODE, SQLSTATE);
      CLOSE b;

PROCEDURE SUB23 (SQLCODE, SQLSTATE);
      ROLLBACK WORK;

PROCEDURE SUB24 (SQLCODE, SQLSTATE);
      DROP SCHEMA T12549PC CASCADE;

PROCEDURE SUB25 (SQLCODE, SQLSTATE);
      COMMIT WORK;

PROCEDURE SUB26 (SQLCODE, SQLSTATE);
      DROP SCHEMA DT12549PC CASCADE;

PROCEDURE SUB27 (SQLCODE, SQLSTATE);
      COMMIT WORK;

PROCEDURE SUB28 (SQLCODE, SQLSTATE);
      INSERT INTO CTS1.TESTREPORT
                      VALUES('7048','pass','MCO');

PROCEDURE SUB29 (SQLCODE, SQLSTATE);
      INSERT INTO CTS1.TESTREPORT
                      VALUES('7048','fail','MCO');

PROCEDURE SUB30 (SQLCODE, SQLSTATE);
      COMMIT WORK;