Re: Ignoring error messages in SQL scripts

[Luis Rodriguez <luisro58@xxxxxxxxx>]

Rob,

(From memory, which can and does fail frequently :-))

You can DECLARE conditions and a HANDLER for those conditions. For example:

DECLARE no_table CONDITION FOR 'SQERR';

Where SQERRR is the code for the SQL exception. Later on in your declare
section, you code:
DECLARE CONTINUE HANDLER FOR no_table SET flag = 1;

Or, if you prefer, DECLARE EXIT HANDLER;

Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries
--



On Fri, Oct 21, 2011 at 11:31 AM, <rob@xxxxxxxxx> wrote:

> Scripts and stored procedures are two different animals.  I do not believe
> there is such a beast as MONMSG in sql script.  There is, however, the
> WHENEVER clause in stored procedures.  Or you could do some testing
> instead.  Search for DRPCODE in these archives.  Modify it to search for
> the view from SYSVIEWS.
>
>
> Rob Berendt
> --
> Group Dekko
> Dept 1600
> Mail to:  2505 Dekko Drive
>          Garrett, IN 46738
> Ship to:  Dock 108
>          6928N 400E
>          Kendallville, IN 46755
> http://www.dekko.com
>
>
>
>
>
> From:   Alan Shore <ashore@xxxxxxxx>
> To:     "midrange-l@xxxxxxxxxxxx" <midrange-l@xxxxxxxxxxxx>,
> Date:   10/21/2011 11:25 AM
> Subject:        Ignoring error messages in SQL scripts
> Sent by:        midrange-l-bounces@xxxxxxxxxxxx
>
>
>
> Good morning all
> I have an SQL script run by the command
> RUNSQLSTM SRCFILE(libname/QSQLSRC) SRCMBR(MBRNAME)
> Within the script MBRNAME I have
>
> DROP VIEW library/viewname
>
> Followed by
>
> Create view library/viewname
> Etc.
>
> In this particular instance the view library/viewname had already been
> dropped, so my RUNSQLSTM failed on the drop
> My question is
> I knew why the view wasn't there, so I wasn't bothered about the drop in
> the SQL script, so how would I ignore such an error?
> Is there the equivalent to a MONMSG  within SQL scripts?
> Is there a reference manual that I could read regarding SQL scripts
>
> As always, any and all responses MUCH appreciated
>
> Alan Shore
> Programmer/Analyst, Direct Response
> E:AShore@xxxxxxxx
> P:(631) 200-5019
> C:(631) 880-8640
> "If you're going through Hell, keep going" - Winston Churchill
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.