* Standard COBOL (file "YTS798.SCO") calling SQL * procedures in file "YTS798.MCO".
*Copyright 1996 National Computing Centre Ltd, *and Computer Logic R&D S.A *on behalf of 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 * * YTS798.SCO * WRITTEN BY: Susan Watters * TRANSLATED AUTOMATICALLY FROM EMBEDDED COBOL BY CHRIS SCHANZLE * * <scalar subquery> in <select list> of single-row select * * * REFERENCES * 6.11 LR2e <value expression>, restriction lifted * 7.11 <scalar subquery>, <row subquery> and * <table subquery> * 13.5 F <select statement : single row> * F#47 Scalar subquery values * * DATE LAST ALTERED 02.01.96 CTS5 Hand-over Test * * QA Status: Full FC * * Revised by DWF 1996-03-25 * Added rollback after authid * Removed EXEC SQL from printf * Renamed variable * Fixed syntax errors * Fixed unintentional cardinality violations ****************************************************************
MOVE 0 TO a1 MOVE 0 TO a2 MOVE 0 TO a3 MOVE 99 TO indic1 MOVE"x"TO d1 MOVE"y"TO d2 MOVE"z"TO d3
*select row from TV where there are multiple corresponding rows *SELECT D ... is expected to be scalar but is not
DISPLAY"SELECT DISTINCT A," DISPLAY" (SELECT D FROM TW" DISPLAY" WHERE E = X.A)" DISPLAY" INTO :a1, :d1" DISPLAY" FROM TV AS X, TW AS Y" DISPLAY" WHERE 1 <" DISPLAY" (SELECT COUNT (*) FROM TV,
- " TW" DISPLAY" WHERE A = X.A" DISPLAY" AND A = E);" * EXEC SQL SELECT DISTINCT A, * (SELECT D FROM TW * WHERE E = X.A) * INTO :a1, :d1 * FROM TV AS X, TW AS Y * WHERE 1 < * (SELECT COUNT (*) FROM TV, TW * WHERE A = X.A * AND A = E); CALL"SUB3"USING SQLCODE SQLSTATE a1 d1 MOVE SQLCODE TO SQL-COD
*That should generate a cardinality violation *which is SQLSTATE 21000
DISPLAY"SQLCODE should be < 0; its value is ", SQL-COD if (SQLCODE NOT < 0) then MOVE 0 TO flag END-IF
DISPLAY"SQLSTATE should be 21000; its value is ", SQLSTATE PERFORM NOSUBCLASS THRU EXIT-NOSUBCLASS if (NORMSQ NOT = "21000") then MOVE 0 TO flag END-IF if (NORMSQ = "21000"AND NORMSQ NOT = SQLSTATE) then DISPLAY"Valid implementation-defined SQLSTATE accepted." END-IF DISPLAY" "
*select row from TV that has unique corresponding row in TW DISPLAY"SELECT DISTINCT A," DISPLAY" (SELECT D FROM TW" DISPLAY" WHERE E = X.A)" DISPLAY" INTO :a2, :d2" DISPLAY" FROM TV AS X, TW AS Y" DISPLAY" WHERE A = 1;"
* EXEC SQL SELECT DISTINCT A, * (SELECT D FROM TW * WHERE E = X.A) * INTO :a2, :d2 * FROM TV AS X, TW AS Y * WHERE A = 1; CALL"SUB4"USING SQLCODE SQLSTATE a2 d2 MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"a2 should be 1; its value is ", a2 DISPLAY"d2 should be g; its value is ", d2 if (a2 NOT = 1 OR d2 NOT = "g") then MOVE 0 TO flag END-IF
*select row in TV that has no corresponding row in TW
DISPLAY"SELECT DISTINCT A," DISPLAY" (SELECT D FROM TW" DISPLAY" WHERE E = X.A)" DISPLAY" INTO :a3, :d3:indic1" DISPLAY" FROM TV AS X, TW AS Y" DISPLAY" WHERE A = 3;" * EXEC SQL SELECT DISTINCT A, * (SELECT D FROM TW * WHERE E = X.A) * INTO :a3, :d3:indic1 * FROM TV AS X, TW AS Y * WHERE A = 3; CALL"SUB5"USING SQLCODE SQLSTATE a3 d3 indic1 MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY"a3 should be 3; its value is ", a3 DISPLAY"indic1 should be -1; its value is ", indic1 if (a3 NOT = 3 OR indic1 NOT = -1) then MOVE 0 TO flag END-IF
* EXEC SQL COMMIT WORK; CALL"SUB9"USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD ******************** END TEST7559 ********************
**** 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.20 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.
