Sql scripting (an example)

Hi Robert,

    I notice you've had many suggestions already about how to handle this.
    They are all correct. But I think I should add a warning about autocommit
    and rollback. Be warned that your transactions will commit on exiting sql
    even if you dont specifically say so! Be further warned that unless you
    take specific steps to avoid it an error in a multi-statement transaction
    will not cause the rest to be rolled back. We have an infamous example 
    of the latter case, a programmer wrote a piece of code like:

        update subject set subject_name='FE'+... ;
        where subject_code in ...

    Spot the error? That blasted ';' really ruined her and the DBA's team day.
    After all, when somone goes and puts the letters 'FE' in front of all
    subjects offered by the Uni , trust me on this, people notice.

    Consequently I suggest all your batch sql code have the following
    structure:
    \r
    \nocontinue (*Indicates that if any statement in a block fails then skip
                  the rest of the block, delineated by the \g and recommence 
                  execution at the first statement of the next block
                 *)
    \p\g
    ... (*Your multistatement transaction*)
    commit (*Because of the \nocontinue the commit will only execute if ALL
             statements have executed without error
             *)
    \p\g
    rollback (*This rollback will be executed if:
                1. Any statement in the prior block had an error,
                2. All statements in the prior block succeeded and were
                   committed. In which case the rollback rolls nothing back.
    \p\g
    ... (*repeat the above structure as often as you like*)
    rollback (*Making the last command a rollback can also be very useful*)
    \p\g
    \q

    How would this have helped our hapless programmer?
    If her code had been:
    \r
    \nocontinue
    update subject set subject_name='FE'+... ;
            where subject_code in ...
    ;commit
    \p\g
    rollback
    \p\g
    \q

    Then:
    1. The update would still succeed, but
    2. The subsequent statement 'where subject_code in ...' will fail! The
       \nocontinue will now jump the execution to the first statement of the
       next block ie the rollback.

    If only she had done this the three hours down time while we recovered the
    tables from the journals would not have been required.

    Martin Bowes.




> Sorry -- INGRES AIX Version 6.4/05 
> 
> On Tue, 18 May 1999 18:40:14 GMT, tony.slade@dal.ca (tony) wrote:
> 
> >I want to rollback the entire transaction below if there are any
> >errors.  The Ingres SQL Reference Manual says I can do something like:
> >
> >
> >if error  
> >   rollback 
> >...
> >
> >but I can't figure out the syntax.  I'd love to be able to use 
> >"set session with on_error  = rollback transaction  "  but can't
> >within a MST.
> >
> >Any suggestions are welcome.
> >
> >begin transaction
> >\p\g
> >select dbmsinfo('transaction_state')
> >\p\g
> >create table tmp1 (col1 integer)
> >\p\g
> >drop table tmp1
> >\p\g
> >end transaction
> >\p\g

Hi Tony,

    There is no 'if' statement in SQL.

    However, you can get around this to some extent using go-block processing.

    Under 6.4/05 this requires use of the \nocontinue option. The switch
    indicates that on error detection that the rest of the current block
    should be skipped and execution commenced at the next block.

    ie:
    \nocontinue
    query1;
    query2;
    query3;
    commit;
    \p\g /*Marks the end of the first block*/
    rollback
    \p\g /*Marks the end of the second block*/
    query4
    ...

    In this case if say query2 produces an error then query3 and the commit
    (ie the rest of the MS trans) are never executed. Instead the program will
    commence at the first statement of the next block. In this case a
    rollback.

    Note that if query1, 2 and 3 are all okay then the commit is executed.
    Then the program will execute a rollback before proceeding to query4. This
    is okay as the rollback has nothing to do in this case.

    One word of warning. All errors will trigger the execution skip. Even if
    some of them are really warnings or informational messages. Unfortunatly,
    there is no way of altering that. I think the point to remember here is
    that this is just a simple SQL script. If you want more complicated
    behaviour then you'll have to go to ESQL or ABF.

    Note that under OI1.2 the behaviour of the \nocontinue was changed! To get
    the above to work under OI1.2 you must set environment variables:
        II_TM_ON_ERROR=nocontinue
        II_TM_SWITCH=true

    Have fun,

    Martin Bowes
Ingres Q & A
Back to William's Home Page
Email William