Re: catch exception within a foreach loop and move to the next row
Posted in 2010
Topics: Stored Procedures & SPL, Java & JDBC Development, Jobs, Consulting & Announcements
Thank you,
This did it.
But also learned some other ways from the previous posts.
So thank you everyone for helping me out.
On Fri, Feb 5, 2010 at 8:46 AM, Jonathan Leffler
<jonathan.leffler@gmail.com> wrote:
> On Feb 5, 5:31 am, Jonathan Leffler <jonathan.leff...@gmail.com>
> wrote:
>> On Feb 4, 2:10 pm, Gentian Hila <genti.t...@gmail.com> wrote:
>> > I am trying to populate a table (B ) based with some data from another
>> > table (A) but at the same time should not override what it is in
>> > there.
>>
>> > So I created a stored procedure that has a foreach loop similar to this
>>
>> > FOREACH cs_insert FOR
>> > SELECT cust_num INTO v_cust_num FROM A
>> > INSERT INTO B(cust_num) VALUES(v_cust_num)>>
>> > END FOR
>>
>> > I was trying to use the ON EXCEPTION on cases when inserting
>> > encounters something in there to catch the exception and do nothing
>> > but continue inserting the next row, so the FOREACH loop does not
>> > fail.
>>
>> > I tried this [...]
>> > This works but the next instruction is not the next row within the
>> > FOREACH loop but the END PROCEDURE.
>>
>> > I also tried [...]
>>
>> > CREATE PROCEDURE X()>>
>> > DEFINE v_cust_num CHAR(20);
>>
>> > FOREACH cs_insert FOR
>> > BEGIN
>>
>> > ON EXCEPTION
>> > END EXCEPTION WITH RESUME
>> > SELECT cust_num INTO v_cust_num FROM A
>> > INSERT INTO B(cust_num) VALUES(v_cust_num)
>> > END
>> > END FOR>>
>> > END PROCEDURE
>>
>> > and this way I cannot even create the procedure at all.
>>
>> > I was thinking just like in java programming with TRY / CATCH clause.
>>
>> > How can I make the foreach loop move to the row n+1 when it encounters
>> > an error on row n?
>>
>> You are close with the second version - you just need to keep the
>> SELECT that drives the FOREACH with the FOREACH,
>> and remember to end a FOREACH loop with END FOREACH.
>>
>> Thus:
>>
>> CREATE PROCEDURE X()>>
>> DEFINE v_cust_num CHAR(20);
>>
>> FOREACH cs_insert FOR SELECT cust_num INTO v_cust_num FROM A
>> BEGIN
>> ON EXCEPTION
>> END EXCEPTION WITH RESUME;
>> INSERT INTO B(cust_num) VALUES(v_cust_num);>> END
>> END FOREACH
>>
>> END PROCEDURE
>
> Minutiae: the semi-colon after RESUME is optional - I don't think its
> presence affects anything, but I'm willing to be proved wrong.
> The semi-colon after the INSERT is required.
>
> Yours,
> Jonathan Leffler
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>