* Standard COBOL (file "XTS724.SCO") calling SQL * procedures in file "XTS724.MCO".
*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 * * XTS724.SCO * WRITTEN BY: Nickos Backalidis * TRANSLATED AUTOMATICALLY FROM EMBEDDED COBOL BY CHRIS SCHANZLE * * Access to COLUMN_DOMAIN_USAGE view * * REFERENCES * 21.2.25 -- COLUMN_DOMAIN_USAGE view * 11.21 -- <domain definition> * 11.27 -- <drop domain statement> * F#25 -- Domain definition * F#2 -- Basic information schema * F#3 -- Basic schema manipulation * F#34 -- Usage tables * * DATE LAST ALTERED 14/12/95 CTS5 Hand-over Test * * Cleanups and fixes by V. Kogakis 04/12/95: * Respect length of strings in variable initialization. * * QA Status: QA Check * * Revised by DWF 1/30/96 * Fixed SDL transactions * Fixed SQL syntax errors * Removed extraneous SQL syntax * Removed status checks after cursor definition * Upcased information schema identifiers ****************************************************************
*initialise host variable *we expect a zero so initialise it to a value <> 0 MOVE 9 TO counok
*Access the COLUMN_DOMAIN_USAGE view DISPLAY"SELECT COUNT(*) INTO :counok" DISPLAY"FROM INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGE" DISPLAY"WHERE DOMAIN_SCHEMA = 'CTS4';" * EXEC SQL SELECT COUNT(*) INTO :counok * FROM INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGE * WHERE DOMAIN_SCHEMA = 'CTS4'; CALL"SUB3"USING SQLCODE SQLSTATE counok MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"counok should be 0; its value is : ", counok if (counok NOT = 0) then MOVE 0 TO flag END-IF DISPLAY" "
*four rows should be returned by the cursor *fetch first row DISPLAY"FETCH ALPHA INTO :domsch,:colnam,:tabnam;" * EXEC SQL FETCH ALPHA INTO :domsch,:colnam,:tabnam; CALL"SUB10"USING SQLCODE SQLSTATE domsch colnam tabnam MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"DOMAIN_SCHEMA should be CTS4; it is ", domsch DISPLAY"COLUMN_NAME should be COLNUM1; it is ", colnam DISPLAY"TABLE_NAME should be TAB724A; it is ", tabnam if (domsch NOT = "CTS4"OR colnam NOT = "COLNUM1") then MOVE 0 TO flag END-IF if (tabnam NOT = "TAB724A") then MOVE 0 TO flag END-IF DISPLAY" "
*fetch the second row DISPLAY"FETCH ALPHA INTO :domsch,:colnam,:tabnam;" * EXEC SQL FETCH ALPHA INTO :domsch,:colnam,:tabnam; CALL"SUB11"USING SQLCODE SQLSTATE domsch colnam tabnam MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"DOMAIN_SCHEMA should be CTS4; it is ", domsch DISPLAY"COLUMN_NAME should be COLNUM2; it is ", colnam DISPLAY"TABLE_NAME should be TAB724A; it is ", tabnam if (domsch NOT = "CTS4"OR colnam NOT = "COLNUM2") then MOVE 0 TO flag END-IF if (tabnam NOT = "TAB724A") then MOVE 0 TO flag END-IF DISPLAY" "
*fetch third row DISPLAY"FETCH ALPHA INTO :domsch,:colnam,:tabnam;" * EXEC SQL FETCH ALPHA INTO :domsch,:colnam,:tabnam; CALL"SUB12"USING SQLCODE SQLSTATE domsch colnam tabnam MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"DOMAIN_SCHEMA should be CTS4; it is ", domsch DISPLAY"COLUMN_NAME should be COLNUM3; it is ", colnam DISPLAY"TABLE_NAME should be TAB724A; it is ", tabnam if (domsch NOT = "CTS4"OR colnam NOT = "COLNUM3") then MOVE 0 TO flag END-IF if (tabnam NOT = "TAB724A") then MOVE 0 TO flag END-IF DISPLAY" "
*fetch fourth and final row DISPLAY"FETCH ALPHA INTO :domsch,:colnam,:tabnam;" * EXEC SQL FETCH ALPHA INTO :domsch,:colnam,:tabnam; CALL"SUB13"USING SQLCODE SQLSTATE domsch colnam tabnam MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"DOMAIN_SCHEMA should be CTS4; it is ", domsch DISPLAY"COLUMN_NAME should be COLNUM4; it is ", colnam DISPLAY"TABLE_NAME should be TAB724A; it is ", tabnam if (domsch NOT = "CTS4"OR colnam NOT = "COLNUM4") then MOVE 0 TO flag END-IF if (tabnam NOT = "TAB724A") then MOVE 0 TO flag END-IF DISPLAY" "
*close the cursor DISPLAY"CLOSE ALPHA;" * EXEC SQL CLOSE ALPHA; CALL"SUB14"USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY" "
*Now access the COLUMN_DOMAIN_USAGE_VIEW *and check that there exist no references *to the above <schema elements> *First initialise host variable *we expect a zero so initialise it to a value <> 0 MOVE 9 TO counok DISPLAY"SELECT COUNT(*) INTO :counok" DISPLAY" FROM INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGE" DISPLAY" WHERE DOMAIN_SCHEMA = 'CTS4';" * EXEC SQL SELECT COUNT(*) INTO :counok * FROM INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGE * WHERE DOMAIN_SCHEMA = 'CTS4'; CALL"SUB20"USING SQLCODE SQLSTATE counok MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"counok should be 0; its value is : ", counok if (counok NOT = 0) then MOVE 0 TO flag END-IF DISPLAY" "
if ( flag = 1 ) then DISPLAY" xts724.mco *** pass *** " * EXEC SQL INSERT INTO CTS1.TESTREPORT * VALUES('7024','pass','MCO'); CALL"SUB22"USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD else DISPLAY" xts724.mco *** fail *** " * EXEC SQL INSERT INTO CTS1.TESTREPORT * VALUES('7024','fail','MCO'); CALL"SUB23"USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD COMPUTE errcnt = errcnt + 1 END-IF
DISPLAY"========================================"
* EXEC SQL COMMIT WORK; CALL"SUB24"USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD ******************** END TEST7024 ********************
**** 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.
¤ 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.0.19Bemerkung:
(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.