Ziele Untersuchung
mit Columbo Integrität von
Datenbanken Interaktion und
Portierbarkeit Ergonomie der
Schnittstellen

Angebot Produkte Projekt Beratung

Mittel Analytik Modellierung Sprachen Algebra Logik Hardware Denken Kreativität

Zusammenhänge Gesellschaft Wirtschaft Branche Firma


products/sources/formale Sprachen/Cobol/Test-Suite/SQL M/ Datei vom 4.1.2008 mit Größe 5 kB

Quelle xts720.mco Sprache: unbekannt

-- SQL Test Suite, V6.0, SQL Module xts720.mco
-- 59-byte ID
--
MODULE XTS720
LANGUAGE COBOL
AUTHORIZATION CTS4

DECLARE NICK1  CURSOR FOR S
DECLARE NICK2  CURSOR FOR T
DECLARE ALPHA  CURSOR FOR SELECT VIEW_SCHEMA,
               VIEW_NAME,TABLE_NAME
               FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
               WHERE VIEW_SCHEMA = 'CTS4' AND TABLE_SCHEMA = 'CTS4'
               ORDER BY TABLE_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,
    :COUNOK NUMERIC (9));
      SELECT COUNT(*) INTO :COUNOK
                    FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
                    WHERE VIEW_SCHEMA = 'CTS4';

PROCEDURE SUB4 (SQLCODE, SQLSTATE);
      COMMIT WORK;

PROCEDURE SUB5 (SQLCODE, SQLSTATE);
      CREATE TABLE TAB720a
                    (COLNUM1 NUMERIC(5),
                    COLNUM2 NUMERIC(7),
                    COLSTR1 CHAR(10),
                    COLSTR2 CHAR(5));

PROCEDURE SUB6 (SQLCODE, SQLSTATE);
      COMMIT WORK;

PROCEDURE SUB7 (SQLCODE, SQLSTATE);
      CREATE TABLE TAB720b
                    (CNUM1 NUMERIC(5),
                    CNUM2 NUMERIC(7),
                    CSTR1 CHAR(10),
                    CSTR2 CHAR(5));

PROCEDURE SUB8 (SQLCODE, SQLSTATE);
      COMMIT WORK;

PROCEDURE SUB9 (SQLCODE, SQLSTATE);
      CREATE VIEW VA_720a AS SELECT
                  COLNUM1,COLNUM2,CNUM1,NUM
                    FROM TAB720a,TAB720b,CTS1.DATA_TYPE;

PROCEDURE SUB10 (SQLCODE, SQLSTATE);
      COMMIT WORK;

PROCEDURE SUB11 (SQLCODE, SQLSTATE);
      CREATE VIEW VA_720b AS SELECT
                  COLSTR1,COLSTR2,CSTR1,CSTR2
                    FROM TAB720a,TAB720b;

PROCEDURE SUB12 (SQLCODE, SQLSTATE);
      COMMIT WORK;

PROCEDURE SUB13 (SQLCODE, SQLSTATE);
      DELETE FROM CTS1.CONCATBUF;

PROCEDURE SUB14 (SQLCODE, SQLSTATE);
      INSERT INTO CTS1.CONCATBUF VALUES(
                    'SELECT COUNT(*)' ||
                    ' FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE' ||
                    ' WHERE VIEW_SCHEMA = ''CTS4''' ||
                    ' AND VIEW_NAME = ''VA_720A''');

PROCEDURE SUB15 (SQLCODE, SQLSTATE,
    :SQLCMD CHAR(240));
      SELECT ZZ INTO :SQLCMD FROM CTS1.CONCATBUF
                 ;

PROCEDURE SUB16 (SQLCODE, SQLSTATE,
    :SQLCMD CHAR(240));
      PREPARE S FROM :SQLCMD;

PROCEDURE SUB17 (SQLCODE, SQLSTATE);
    OPEN NICK1;

PROCEDURE SUB18 (SQLCODE, SQLSTATE,
    :COUNOK NUMERIC (9));
      FETCH NICK1 INTO :COUNOK;

PROCEDURE SUB19 (SQLCODE, SQLSTATE);
      CLOSE NICK1;

PROCEDURE SUB20 (SQLCODE, SQLSTATE);
      COMMIT WORK;

PROCEDURE SUB21 (SQLCODE, SQLSTATE);
      DELETE FROM CTS1.CONCATBUF;

PROCEDURE SUB22 (SQLCODE, SQLSTATE);
      INSERT INTO CTS1.CONCATBUF VALUES(
                    'SELECT COUNT(*)' ||
                    ' FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE' ||
                    ' WHERE VIEW_SCHEMA = ''CTS4'''||
                    ' AND VIEW_NAME = ''VA_720B''');

PROCEDURE SUB23 (SQLCODE, SQLSTATE,
    :SQLCMD CHAR(240));
      SELECT ZZ INTO :SQLCMD FROM CTS1.CONCATBUF
                 ;

PROCEDURE SUB24 (SQLCODE, SQLSTATE,
    :SQLCMD CHAR(240));
      PREPARE T FROM :SQLCMD;

PROCEDURE SUB25 (SQLCODE, SQLSTATE);
    OPEN NICK2;

PROCEDURE SUB26 (SQLCODE, SQLSTATE,
    :COUNOK NUMERIC (9));
      FETCH NICK2 INTO :COUNOK;

PROCEDURE SUB27 (SQLCODE, SQLSTATE);
      CLOSE NICK2;

PROCEDURE SUB28 (SQLCODE, SQLSTATE);
      COMMIT WORK;

PROCEDURE SUB29 (SQLCODE, SQLSTATE);
      DROP VIEW VA_720a CASCADE;

PROCEDURE SUB30 (SQLCODE, SQLSTATE);
      COMMIT WORK;

PROCEDURE SUB31 (SQLCODE, SQLSTATE);
    OPEN ALPHA;

PROCEDURE SUB32 (SQLCODE, SQLSTATE,
    :VSCHMA CHAR(128),
    :V_NAME CHAR(128),
    :TABNAM CHAR(128));
      FETCH ALPHA INTO :VSCHMA,:V_NAME,:TABNAM;

PROCEDURE SUB33 (SQLCODE, SQLSTATE,
    :VSCHMA CHAR(128),
    :V_NAME CHAR(128),
    :TABNAM CHAR(128));
      FETCH ALPHA INTO :VSCHMA,:V_NAME,:TABNAM;

PROCEDURE SUB34 (SQLCODE, SQLSTATE);
      CLOSE ALPHA;

PROCEDURE SUB35 (SQLCODE, SQLSTATE);
      COMMIT WORK;

PROCEDURE SUB36 (SQLCODE, SQLSTATE);
      DROP VIEW VA_720b CASCADE;

PROCEDURE SUB37 (SQLCODE, SQLSTATE);
      COMMIT WORK;

PROCEDURE SUB38 (SQLCODE, SQLSTATE,
    :VSCHMA CHAR(128),
    :V_NAME CHAR(128),
    :TABNAM CHAR(128));
      SELECT VIEW_SCHEMA,VIEW_NAME,TABLE_NAME
                    INTO :VSCHMA,:V_NAME,:TABNAM
                    FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
                    WHERE VIEW_SCHEMA = 'CTS4' AND TABLE_SCHEMA = 'CTS4'
                 ;

PROCEDURE SUB39 (SQLCODE, SQLSTATE);
      ROLLBACK WORK;

PROCEDURE SUB40 (SQLCODE, SQLSTATE);
      DROP TABLE TAB720a CASCADE;

PROCEDURE SUB41 (SQLCODE, SQLSTATE);
      COMMIT WORK;

PROCEDURE SUB42 (SQLCODE, SQLSTATE);
      DROP TABLE TAB720b CASCADE;

PROCEDURE SUB43 (SQLCODE, SQLSTATE);
      COMMIT WORK;

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

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

PROCEDURE SUB46 (SQLCODE, SQLSTATE);
      COMMIT WORK;