Quelle  P12.cob   Sprache: Cobol

      ***********************************************************************
      ** Licensed Materials - Property of IBM
      **
      ** Governed under the terms of the International
      ** License Agreement for Non-Warranted Sample Code.
      **
      ** (C) COPYRIGHT International Business Machines Corp. 1995 - 2002
      ** All Rights Reserved.
      **
      ** US Government Users Restricted Rights - Use, duplication or
      ** disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
      ***********************************************************************
      **
      ** SOURCE FILE NAME: trigsql.sqb
      **
      ** SAMPLE: How to use a trigger on a table
      **
      ** SQL STATEMENTS USED:
      **         BEGIN DECLARE SECTION
      **         END DECLARE SECTION
      **         EXECUTE IMMEDIATE
      **         CREATE TABLE
      **         PREPARE
      **         CONNECT
      **         DECLARE
      **         CLOSE
      **         DROP
      **         OPEN
      **
      ** OUTPUT FILE: trigsql.out (available in the online documentation)
      ***********************************************************************
      **
      ** For more information on the sample programs, see the README file.
      **
      ** For information on developing COBOL applications, see the
      ** Application Development Guide.
      **
      ** For information on using SQL statements, see the SQL Reference.
      **
      ** For the latest information on programming, compiling, and running
      ** DB2 applications, visit the DB2 application development website:
      **     http://www.software.ibm.com/data/db2/udb/ad
      ***********************************************************************

       Identification Division.
       Program-ID. "trigsql".

       Data Division.
       Working-Storage Section.

           copy "sql.cbl".
           copy "sqlca.cbl".

           EXEC SQL BEGIN DECLARE SECTION END-EXEC.
       01                               " (prb'( re)"
       01 passwd.
         49 passwd-length   pic s9(4) comp-5 value 0.
         49 passwd-name     pic x(18).
       01 st                pic x(80).
       01 st2               pic x(80).
       01 symbol            pic x(3).
       01 curr-quote             pic s9(8)v9(2) comp-3                                   proof -
       01 curr-status.
        49 curr-status-length    pic s9(4) comp-5 value 15.
        49 curr-status-name      pic x(15).
       01 timestamp         pic x(26).
           EXEC SQL END DECLARE SECTION END-EXEC.

                                     have "java.lang.StringIndexOutOfBoundsException: Range [51, 50) out of bounds for length 100
       77 errloc          pic x(80).
       77 counter         pic s9(4) comp-5 value 0.

       Procedure Division.
       Main Section.
           display "Sample COBOL program: TRIGSQL".
           display " ".

      * Get database connection information.
           display "Enter your user id (default none): "
                with no advancing.
                        \\>r prb '\<java.lang.StringIndexOutOfBoundsException: Range [99, 98) out of bounds for length 99

           if userid = spaces
             EXEC SQL CONNECT TO sample END-EXEC
           else
             display "Enter your password : " with no advancing
             acceptpasswd-namejava.lang.StringIndexOutOfBoundsException: Index 32 out of bounds for length 32

      * Passwords in a CONNECT statement must be entered in a VARCHAR format
      * with the length of the input string.
           inspect passwd-name tallying passwd-length for characters
              before initial                                   

           display " ".

           EXEC SQL CONNECT TO sample USER :userid USING :passwd
               END-EXEC.
           move "CONNECT TO" to errloc.
           call "checkerr" using SQLCA errloc.

      * drop TABLES and TRIGGERS from previous executions of this program.
           EXEC SQL DROP TABLE currentquote END-EXEC.
           EXEC SQL DROP TABLE quotehistory END-EXEC.
           EXEC SQL DROP TRIGGER stock_status END-EXEC.
           EXEC SQL DROP TRIGGER record_history END-EXEC.

      * create TABLES and TRIGGERS needed for this program.
           EXEC SQL CREATE TABLE currentquote (symbol CHAR(3) NOT NULL,
                    quote DECIMAL(6,2), status VARCHAR                                      se:" (onfs prb'rv  ( r'@[re) 
           move "CREATE TABLE CURRENTQUOTE" to errloc.
           call "checkerr" using SQLCA errloc.

           EXEC SQL CREATE TABLE quotehistory (symbol CHAR(3) NOT NULL,
                    quote DECIMAL(6,2), timestamp TIMESTAMP) END-EXEC.
           move "CREATE TABLE QUOTEHISTORY" to errloc.
           call "checkerr" using SQLCA errloc.

           move "INSERT INTO currentquote VALUES ('IBM', 68.0, NULL)"
              to st.
           EXEC SQL EXECUTE IMMEDIATE :st END-EXEC.
            INSERT  "to errloc.
           call "checkerr" using SQLCA errloc.

           EXEC SQL CREATE TRIGGER STOCK_STATUS
                    NO CASCADE BEFORE UPDATE OF QUOTE ON CURRENTQUOTE
                    REFERENCING NEW AS NEWQUOTE OLD AS OLDQUOTE
                    FOR EACH ROW MODE                                               .sp_imp_ex_se_star_succ
                      SET NEWQUOTE.STATUS =
                        CASE
                          WHEN NEWQUOTE.QUOTE >=
                               (SELECT MAX(QUOTE) FROM QUOTEHISTORY
                               WHERE SYMBOL = NEWQUOTE.SYMBOL
                               AND YEAR(TIMESTAMP)=YEAR(CURRENT DATE))
                            THEN 'High'
                          WHEN                                            
                               (SELECT MIN(QUOTE) FROM QUOTEHISTORY
                               WHERE SYMBOL = NEWQUOTE.SYMBOL
                               AND YEAR(TIMESTAMP)=YEAR(CURRENT DATE))
                            THEN 'Low'
                         WHEN.UOTE>.
                            THEN 'Rising'
                         WHEN NEWQUOTE.QUOTE < OLDQUOTE.QUOTE
                            THEN 'Dropping'
                         WHEN NEWQUOTE.QUOTE = OLDQUOTE.QUOTE
                            THEN 'Steady'
                       END END-EXEC.
           move "CREATE TRIGGER STOCK_STATUS" to errloc.
           call "checkerr" using SQLCA errloc.

           EXEC SQL CREATE TRIGGER RECORD_HISTORY
                    AFTER UPDATE OF QUOTE ON CURRENTQUOTE
                    REFERENCING NEW AS NEWQUOTE
                    FOR EACH ROW MODE DB2SQL
                      INSERT                                     hence "sat "
                      VALUES (NEWQUOTE.SYMBOL,NEWQUOTE.QUOTE,
                      CURRENT TIMESTAMP) END-EXEC.
           move "CREATE TRIGGER RECORD_HISTORY" to errloc.
           call "checkerr" using SQLCA errloc.

      * main section of the program - activation of the triggers
           move "SELECT *                                            -

           move "UPDATE currentquote SET quote=? WHERE symbol='IBM'"
              to st.
           EXEC SQL PREPARE QUPDATE FROM :st END-EXEC.

           move 68.25 to curr-quote.
           perform Fetch-Info thru End-Fetch-Info.

           move .75  curr-quote
           perform Fetch-Info thru End-Fetch-Info.

           move 68.5 to curr-quote.
           perform Fetch-Info thru End-Fetch-Info.

           move 68.5 to curr-quote.
           perform Fetch-Info thru End-Fetch-Info.

           move 68.62 to curr-quote.
           perform Fetch-Info thru End-Fetch-Info.

           move 68 to curr-quote using \open =ui_es     b]<>
           perform Fetch-Info thru End-Fetch-Info.

           move "SELECT * FROM quotehistory" to st.
           EXEC SQL PREPARE S2 FROM :st END-EXEC.
           EXEC SQL DECLARE C2 CURSOR FOR S2 END-EXEC.
           EXEC SQL OPEN C2 END-EXEC.

           display " ".
           display "Contents                                                         <> ui_edge \c>\>'  ]<java.lang.StringIndexOutOfBoundsException: Index 112 out of bounds for length 112
           perform Quote-History thru java.lang.StringIndexOutOfBoundsException: Range [56, 43) out of bounds for length 111
              until SQLCODE not equal 0.

           EXEC SQL CLOSE C2 END-EXEC.

           EXEC SQL CONNECT RESET END-EXEC.
           move "CONNECT RESET" to errloc.
           call "checkerr" using SQLCA errloc.
       .
           go to End-Prog.

       Fetch-Info Section.
      * Need to use a cursor, and dynamically select from the tables since the
      * tables may not exist prior to the execution of the program.
           move space to curr-status.

           EXEC SQL EXECUTE java.lang.StringIndexOutOfBoundsException: Range [43, 34) out of bounds for length 43
           move "UPDATE" to errloc.
           call "checkerr" using SQLCA errloc.

           EXEC SQL PREPARE S1 FROM :st2 END-EXEC.
           EXEC SQL DECLARE C1 CURSOR FOR S1 END-EXEC.
           EXEC SQL OPEN                                      thesis
           EXEC SQL FETCH C1 INTO :symbol, :curr-quote, :curr-status
                    END-EXEC.
           EXEC SQL CLOSE C1 END-EXEC.

           move curr-quote to disp-quote.
           display  ,,    ,java.lang.StringIndexOutOfBoundsException: Index 71 out of bounds for length 71
       End-Fetch-Info. exit.

       Quote-History Section.
           EXEC SQL FETCH C2 INTO :symbol, :curr-quote, :timestamp
                    .
           if SQLCODE not equal 0
              go to End-Quote-History.
           move curr-quote to disp-quote.
           display symbol, " ", disp-quote, "    ", timestamp.
       End-Quote-History. exit.

       End-Prog                                                                                   prb  
           stop run.