*Copyright 1995 National Computing Centre Limited *and Computer Logic R&D S.A *on behalf of the CTS5 SQL2 Project. *All rights reserved. *The CTS5 SQL2 Project is sponsored by the European Community. * *The National Computing Centre Limited and Computer Logic R&D *have given permission to NIST to distribute this program *over the World Wide Web in order to promote SQL standards. *DISCLAIMER: *This program was reviewed by employees of NIST for *conformance to the SQL standards. *NIST assumes no responsibility for any party's use of *this program.
**************************************************************** * * COMMENT SECTION * * SQL VALIDATION TEST SUITE V6.0 * * XTS748.PCO * WRITTEN BY: Manolis Megaloikonomou * TRANSLATED AUTOMATICALLY FROM EMBEDDED C BY CHRIS SCHANZLE * * Named constraint in column definition in schema definition. * * REFERENCES * 11.1 -- <schema definition> * 11.3 -- <table definition> * 11.4 -- <column definition> * 11.4 SR.9 * 11.4 LR.2c -- Raised. Entry SQL restriction which * prohibited the specification of * <constraint name definition>. * 10.6 -- <constraint name definition> and * <constraint attributes> * 10.6 SR.1 * 10.6 SR.2 * 10.6 LR.2a -- Raised. Entry SQL restriction which * prohibited the specification of * <constraint name definition>. * 11.7 -- <unique constraint definition> * 11.8 -- <referential constraint definition> * 11.9 -- <check constraint definition> * F#2 -- Basic information schema. * F#1 -- Dynamic SQL. * F#3 -- Basic schema manipulation. * F#33 -- Constraint tables. * F#31 -- Schema definition. * F#49 -- Constraint management. * * DATE LAST ALTERED 18/12/95 CTS5 Hand-over Test * * Cleanups and fixes by V. Kogakis 08/12/95 * Print timestamp * Include Files * Define NOSUBCLASS/CHCKOK at test beginning * Include host variable initialisation * Clean-up database at the end of test * * QA STATUS : * * Revised by DWF 1996-02-15 * Cleanups * Removed status checks after cursor definitions * Fixed SDL transactions * Make variable names 6 chars * Removed field widths from printfs * Initialized vars ****************************************************************
MOVE"CTS1 "TO uid CALL"AUTHID"USING uid MOVE"not logged in, not"TO uidx EXECSQLSELECT USER INTO :uidx FROM CTS1.ECCO END-EXEC MOVE SQLCODE TO SQL-COD EXECSQL ROLLBACK WORK END-EXEC MOVE SQLCODE TO SQL-COD if (uid NOT = uidx) then DISPLAY"ERROR: User ", uid, " expected. User ", uidx, "
- " connected" STOPRUN END-IF MOVE 0 TO errcnt
DISPLAY"SQL Test Suite, V6.0, Embedded COBOL, xts748.pco" DISPLAY "59-byte ID" DISPLAY"TEd Version #" *date_time print ACCEPT TO-DAY FROMDATE ACCEPT THE-TIME FROMTIME DISPLAY"Date run YYMMDD: " TO-DAY " at hhmmssff: " THE-TIME
******************** BEGIN TEST7048 ******************* MOVE 1 TO flag DISPLAY" TEST7048 " DISPLAY" Named constraint in column definition in
- " schema definition." DISPLAY" References:" DISPLAY" 11.1 -- " DISPLAY" 11.3 --
DISPLAY"COMMIT WORK;" EXECSQL COMMIT WORK END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY" "
COMPUTE coun = -1 DISPLAY"SELECT COUNT(*) INTO :coun " DISPLAY"FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS" DISPLAY"WHERE CONSTRAINT_SCHEMA = 'T12549PC' AND
- " TABLE_SCHEMA = 'T12549PC';" EXECSQLSELECTCOUNT(*) INTO :coun FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = 'T12549PC'AND TABLE_SCHEMA = 'T12549PC'END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"Count should be 3; its value is ", coun if ( coun NOT = 3 ) then MOVE 0 TO flag END-IF DISPLAY" "
DISPLAY"DECLARE a CURSOR" DISPLAY"FOR SELECT CONSTRAINT_NAME, TABLE_NAME,
- " CONSTRAINT_TYPE" DISPLAY"FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS" DISPLAY"WHERE CONSTRAINT_SCHEMA = 'T12549PC' AND
- " TABLE_SCHEMA = 'T12549PC'" DISPLAY"ORDER BY CONSTRAINT_NAME;" EXECSQL DECLARE a CURSOR FORSELECT CONSTRAINT_NAME, TABLE_NAME, CONSTRAINT_TYPE FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = 'T12549PC'AND TABLE_SCHEMA = 'T12549PC' ORDERBY CONSTRAINT_NAME END-EXEC
DISPLAY"OPEN a;" EXECSQLOPEN a END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY" "
DISPLAY"FETCH a INTO :cname, :tname, :ctype;" EXECSQL FETCH a INTO :cname, :tname, :ctype END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"cname should be CND12549A; its value is ", cname DISPLAY"tname should be TEST12549; its value is ", tname DISPLAY"ctype should be CHECK; its value is ", ctype if (cname NOT = "CND12549A"OR tname NOT = "TEST12549" ) then MOVE 0 TO flag END-IF if (ctype NOT = "CHECK") then MOVE 0 TO flag END-IF DISPLAY" "
DISPLAY"FETCH a INTO :cname, :tname, :ctype;" EXECSQL FETCH a INTO :cname, :tname, :ctype END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"cname should be CND12549B; its value is ", cname DISPLAY"tname should be TEST12549; its value is ", tname DISPLAY"ctype should be UNIQUE; its value is ", ctype if (cname NOT = "CND12549B"OR tname NOT = "TEST12549") then MOVE 0 TO flag END-IF if (ctype NOT = "UNIQUE") then MOVE 0 TO flag END-IF DISPLAY" "
DISPLAY"FETCH a INTO :cname, :tname, :ctype;" EXECSQL FETCH a INTO :cname, :tname, :ctype END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"cname should be CND12549C; its value is ", cname DISPLAY"tname should be TEST12549; its value is ", tname DISPLAY"ctype should be CHECK; its value is ", ctype if (cname NOT = "CND12549C"OR tname NOT = "TEST12549") then MOVE 0 TO flag END-IF if (ctype NOT = "CHECK") then MOVE 0 TO flag END-IF DISPLAY" "
DISPLAY"CLOSE a;" EXECSQLCLOSE a END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY" "
DISPLAY"COMMIT WORK;" EXECSQL COMMIT WORK END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY" "
COMPUTE coun = -1 DISPLAY"SELECT COUNT(*) INTO :coun " DISPLAY"FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS" DISPLAY"WHERE CONSTRAINT_SCHEMA = 'DT12549PC' " DISPLAY"AND TABLE_SCHEMA = 'DT12549PC';" EXECSQLSELECTCOUNT(*) INTO :coun FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = 'DT12549PC'AND TABLE_SCHEMA = 'DT12549PC'END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"Count should be 3; its value is ", coun if ( coun NOT = 3 ) then MOVE 0 TO flag END-IF DISPLAY" "
DISPLAY"DECLARE b CURSOR" DISPLAY"FOR SELECT CONSTRAINT_NAME, TABLE_NAME,
- " CONSTRAINT_TYPE" DISPLAY"FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS" DISPLAY"WHERE CONSTRAINT_SCHEMA = 'DT12549PC'" DISPLAY"AND TABLE_SCHEMA = 'DT12549PC'" DISPLAY"ORDER BY CONSTRAINT_NAME;" EXECSQL DECLARE b CURSOR FORSELECT CONSTRAINT_NAME, TABLE_NAME, CONSTRAINT_TYPE FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = 'DT12549PC'AND TABLE_SCHEMA = 'DT12549PC' ORDERBY CONSTRAINT_NAME END-EXEC
DISPLAY"OPEN b;" EXECSQLOPEN b END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY" "
DISPLAY"FETCH b INTO :cname, :tname, :ctype;" EXECSQL FETCH b INTO :cname, :tname, :ctype END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"cname should be CND12549D; its value is ", cname DISPLAY"tname should be TEST12549; its value is ", tname DISPLAY"ctype should be UNIQUE; its value is ", ctype if ( cname NOT = "CND12549D"OR tname NOT = "TEST12549" ) then MOVE 0 TO flag END-IF if ( ctype NOT = "UNIQUE") then MOVE 0 TO flag END-IF DISPLAY" "
DISPLAY"FETCH b INTO :cname, :tname, :ctype;" EXECSQL FETCH b INTO :cname, :tname, :ctype END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"cname should be CND12549E; its value is ", cname DISPLAY"tname should be TEST12549; its value is ", tname DISPLAY"ctype should be CHECK; its value is ", ctype if (cname NOT = "CND12549E"OR tname NOT = "TEST12549") then MOVE 0 TO flag END-IF if (ctype NOT = "CHECK") then MOVE 0 TO flag END-IF DISPLAY" "
DISPLAY"FETCH b INTO :cname, :tname, :ctype;" EXECSQL FETCH b INTO :cname, :tname, :ctype END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"cname should be CND12549F; its value is ", cname DISPLAY"tname should be TEST12549; its value is ", tname DISPLAY"ctype should be CHECK; its value is ", ctype if (cname NOT = "CND12549F"OR tname NOT = "TEST12549") then MOVE 0 TO flag END-IF if (ctype NOT = "CHECK") then MOVE 0 TO flag END-IF DISPLAY" "
DISPLAY"CLOSE b;" EXECSQLCLOSE b END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY" "
DISPLAY"ROLLBACK WORK;" EXECSQL ROLLBACK WORK END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY" "
*Clean up database DISPLAY"DROP SCHEMA T12549PC CASCADE;" EXECSQL DROP SCHEMA T12549PC CASCADE END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY" "
DISPLAY"COMMIT WORK;" EXECSQL COMMIT WORK END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY" "
DISPLAY"DROP SCHEMA DT12549PC CASCADE;" EXECSQL DROP SCHEMA DT12549PC CASCADE END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY" "
DISPLAY"COMMIT WORK;" EXECSQL COMMIT WORK END-EXEC MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY" "
*Now record results
if ( flag = 1 ) then DISPLAY" xts748.pco *** pass *** " EXECSQLINSERTINTO CTS1.TESTREPORT
VALUES('7048','pass','PCO') END-EXEC MOVE SQLCODE TO SQL-COD else DISPLAY" xts748.pco *** fail *** " EXECSQLINSERTINTO CTS1.TESTREPORT
VALUES('7048','fail','PCO') END-EXEC MOVE SQLCODE TO SQL-COD COMPUTE errcnt = errcnt + 1 END-IF
DISPLAY"========================================"
EXECSQL COMMIT WORK END-EXEC MOVE SQLCODE TO SQL-COD
******************** END TEST7048 ********************
**** TESTER MAY CHOOSE TO INSERT CODE FOR errcnt > 0 STOPRUN.
* **** 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 PERFORMVARYING 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 GOTO EXIT-NOSUBCLASS END-IF
MOVE 4 TO norm1 *examining position 4 of char array NORMSQ *valid characters are 0-9, A-Z PERFORMVARYING 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 PERFORMVARYING 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.16 Sekunden
(vorverarbeitet)
¤
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.