IDENTIFICATION DIVISION.
       PROGRAM-ID.  YTS794.
       ENVIRONMENT DIVISION.
       CONFIGURATION SECTION.
       SOURCE-COMPUTER.  xyz.
       OBJECT-COMPUTER.  xyz.
       DATA DIVISION.
       WORKING-STORAGE SECTION.


      * EMBEDDED COBOL (file "YTS794.PCO")


      *Copyright 1996 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                               
      *                                                              
      * YTS794.PCO                                                   
      * WRITTEN BY:  Susan Watters                                   
      * TRANSLATED AUTOMATICALLY FROM EMBEDDED C BY CHRIS SCHANZLE
      *                                                              
      * GRANT USAGE on character set, no WGO                         
      *                                                              
      *                                                              
      * REFERENCES                                                   
      *   11.28   <character set definition>                         
      *   11.36   <grant statement>                                  
      *   10.3    <privileges>                                       
      *   11.3    <table definition>                                 
      *   11.4    <column definition>                                
      *   16.4    <set session authorization identifier statement>   
      *   F#45    Character set definition                           
      *   F#32    User authorisation                                 
      *   F#3     Basic schema manipulation                          
      *   F#2     Basic information schema                           
      *                                                              
      * DATE LAST ALTERED 02.01.96 CTS5 Hand-over Test               
      *                                                              
      * QA Status: Full FC                                           
      *                                                              
      * Revised by DWF 1996-03-22                                    
      *   Added rollback after authid                                
      *   Fixed charset usage                                        
      *   Fixed bad schema refs                                      
      *   Fixed SDL transactions                                     
      ****************************************************************




           EXEC SQL BEGIN DECLARE SECTION END-EXEC
       01  SQLCODE PIC S9(9) COMP.
       01  SQLSTATE PIC  X(5).
       01  ggt character set is cts1.cs2 PIC X(3).
       01  uid PIC  X(18).
       01  uidx PIC  X(18).
             EXEC SQL END DECLARE SECTION END-EXEC
       01  norm1 PIC S9(9) DISPLAY SIGN LEADING SEPARATE.
       01  norm2 PIC S9(9) DISPLAY SIGN LEADING SEPARATE.
       01  ALPNUM-TABLE VALUE IS
               "01234ABCDEFGH56789IJKLMNOPQRSTUVWXYZ".
           05  ALPNUM PIC X OCCURS 36 TIMES.
       01  NORMSQ.
           05  NORMSQX PIC X OCCURS 5 TIMES.
       01  errcnt PIC S9(9) DISPLAY SIGN LEADING SEPARATE.
      *date_time declaration 
       01  TO-DAY PIC 9(6).
       01  THE-TIME PIC 9(8).
       01  flag PIC S9(9) DISPLAY SIGN LEADING SEPARATE.

       01  SQL-COD PIC S9(9) DISPLAY SIGN LEADING SEPARATE.

       PROCEDURE DIVISION.
       P0.

             MOVE "CTS2              " TO uid
             CALL "AUTHID" USING uid
             MOVE "not logged in, not" TO uidx
             EXEC SQL SELECT USER INTO :uidx FROM CTS1.ECCO END-EXEC
             MOVE SQLCODE TO SQL-COD
              CONFIGURATION SECTION.
             MOVE SQLCODE TO SQL-COD
     uidx then
                 xyzjava.lang.StringIndexOutOfBoundsException: Index 29 out of bounds for length 29
      -    " connected"
            STOP RUN
             END-IF
             MOVE 0 TO errcnt

             DISPLAY "SQL Test       *over the World Wide Web in order to promote SQL standards.    
             DISPLAY
           "59-byte ID"
             DISPLAY "TEd Version #"
      *date_time print 
           ACCEPT TO-DAY      *this program.                                                 
           ACCEPT THE-TIME FROM TIME
           DISPLAY "Date run YYMMDD: " TO-DAY " at hhmmssff: " THE-TIME

      ******************** BEGIN TEST7528 *******************
             MOVE 1 TO flag

             DISPLAY      *                                                              
             DISPLAY "   GRANT USAGE on character       * WRITTEN BY:  Susan Watters                                   
             DISPLAY "References:"
             DISPLAY "    11.28   <character set       * GRANT USAGE on character set, no WGO                         
             DISPLAY       *                                                              
             DISPLAY "    10.3    <privileges>"
             DISPLAY "    11.3    <table definition>"
             DISPLAY "    11.4    <column definition>"
             DISPLAY "    16.4    <set session authorization identifier
      -    " statement>"
             DISPLAY "    F#45    Character set definition"
             DISPLAY "    F#25    Domain definition"
             DISPLAY "    F#32    User authorisation"
             DISPLAY "    F#3     Basic                              
             DISPLAY "    F#2           *   10.3    <privileges>                                       
             DISPLAY "   - - - - - - - - - - - - - - - -       *   11.4    <column definition>                                

      *   F#32    User authorisation                                 
             MOVE "xxxxx"       *   F#2     Basic information schema                           

             DISPLAY "      * DATE LAST ALTERED 02.01.96 CTS5 Hand-over Test               
             DISPLAY "checking GRANT USAGE on a char set with no WITH
      -    " GRANT OPTION"
             DISPLAY  " "

      *initialise variables 
             MOVE "xxx" TO ggt

      *as second user grant usage on domain to third user 

             DISPLAY "GRANT       *   Fixed SDL transactions                                     
                USAGE CTS1CS2  
             END-EXEC
             MOVE SQLCODE       01SQLCODE  S99).

      *That should generate an Syntax Rule or Access Rule Violation 
      *which is SQLSTATE 42000 

            DISPLAY" should  <0 value is " 
                    01  uid   ()java.lang.StringIndexOutOfBoundsException: Index 26 out of bounds for length 26
                0TOflag
             END-IF

             DISPLAY "SQLSTATE  PIC S99    .
           PERFORM NOSUBCLASS THRU EXIT-NOSUBCLASS
             if (NORMSQ"01234".
               MOVE0TO 
             F
=   "2000"ANDNORMSQNOT=SQLSTATEjava.lang.StringIndexOutOfBoundsException: Index 70 out of bounds for length 70
             then
               DISPLAY "Valid implementation-defined SQLSTATE accepted."
             END-IF
             DISPLAY  " "

             DISPLAY COMMITWORK"
                        98.
             MOVE SQLCODE TO SQL-COD

      *create table using character set 

             DISPLAY "CREATE TABLE TB"
             DISPLAY "( TBC  CHARACTER(3) CHARACTER SET CTS1.CS2);"
             EXEC SQL CREATE TABLE TB
               TBCCHARACTER3 CHARACTER SET CTS1.CS2) END-EXEC
              SQLCODETO SQL-COD
PERFORM 
             DISPLAY  " "

             DISPLAY "COMMIT WORK;"
             EXECUSERINTO:idx CTS1. END-EXEC
             MOVE SQLCODE TO SQL-CODMOVE SQLCODETO SQL-COD
           PERFORM CHCKOK
             DISPLAY  " "

      *insert value into table 

             DISPLAY                 ":User "   .  User ",uidx java.lang.StringIndexOutOfBoundsException: Index 72 out of bounds for length 72
             EXEC SQL
             MOVE SQLCODE TO SQL-COD
           PERFORM CHCKOK
             DISPLAY  " "

              "  INTO :gt  ;"
             EXEC       *date_time print 
             MOVE SQLCODE TO SQL-COD
           PERFORM CHCKOK
             DISPLAY"alue returned should  'ef,it  ",ggt
             if (ggt  NOT  =                 1 TOflag
               MOVE 0 TO java.lang.StringIndexOutOfBoundsException: Index 26 out of bounds for length 0
             END-IF

                          ":"
             EXEC SQL COMMIT WORK END-EXEC
             MOVE SQLCODE TO SQL-COD
            
             DISPLAY  " "

      *ensure database is left in a consistent state 

DISPLAY" TB ;"
             EXEC             "   #32    User authorisation"
OD
           PERFORM CHCKOK
             DISPLAY  " "

             DISPLAY "COMMIT WORK;"
EXECSQL WORK END-EXEC
             MOVE SQLCODE TO java.lang.StringIndexOutOfBoundsException: Index 31 out of bounds for length 0
           PERFORM CHCKOK
             DISPLAY ""

             if (flag  =  1             DISPLAY"hecking  USAGEon a  set with WITH
                              " "
               EXEC      *initialise variables 
                 ('pass'')
               MOVE SQLCODE TO SQL-COD
             else
               DISPLAY  .  **"
                            SQLGRANTUSAGE CHARACTER SETCTS1.S2TO
                 MOVESQLCODE  
               MOVE SQLCODEjava.lang.StringIndexOutOfBoundsException: Index 0 out of bounds for length 0
               =+1
             END-IF

             DISPLAY"=======================

             END-IF
                          DISPLAYSQLSTATEbe42000  value ,SQLSTATE
           PERFORM NOSUBCLASS THRU EXIT-NOSUBCLASS


      **** TESTER MAY CHOOSE TO INSERT CODE FOR errcnt > 0
           STOP RUNMOVE TOflag

      *    ****  Procedures for PERFORM statements

      *Test SQLCODE and SQLSTATE for normal completion. 
       CHCKOK.
             
             DISPLAYDISPLAY""
                           COMMIT"
             if (SQLCODE NOT =  0  OR   NORMSQ NOT = "00000") then
               MOVE   flag
             END-IF
             if (NORMSQ = "00000"  AND  NORMSQ
             then
               DISPLAY "Valid implementation-defined SQLSTATE accepted."
             DISPLAY"   () CHARACTER SET CTS1.)"
             .

       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

                        DI  ""
      *subclass begins in position 3 of char array NORMSQ 
      *valid subclass begins with 5-9, I-Z, end of ALPNUM table 
           PERFORM VARYING norm2 FROM 14 BY 1 UNTIL norm2 > 36
           if (NORMSQXMOVE SQLCODE 
                        CHCKOK
           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)            
 (=SQLSTATE 
             GO TO EXIT-NOSUBCLASS SQLCODE  
           END-IF

           MOVE 4 TO norm1
      *examining position 4 of char array NORMSQ 
      *valid characters are 0-9, A-Z 
RYINGnorm2 FROM   1UNTILnorm2 >36
           if (NORMSQX(norm1)  =                SQL-COD
            MOVE ""TO NORMSQXnorm1)
           END-IF
           
          
                        if (   ="ef)then
      *valid characters are 0-9, A-Z 
      *examining position 5 of char array NORMSQ 
           PERFORM             
                        DISPLAY"OMMIT "
             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 

                 *ensure database is left in a consistent java.lang.StringIndexOutOfBoundsException: Index 0 out of bounds for length 0
             MOVE "0"              TOSQL-COD
           END-IF
           .

       EXIT-NOSUBCLASS.DISPLAY COMMIT"
           EXIT