catch exception within a foreach loop and move to the next row
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 similiar 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 scenario
CREATE PROCEDURE X()
DEFINE v_cust_num CHAR(20);
BEGIN
ON EXCEPTION
END EXCEPTION WITH RESUME
FOREACH cs_insert FOR
SELECT cust_num INTO v_cust_num FROM A
INSERT INTO B(cust_num) VALUES(v_cust_num)
END FOR
END
END PROCEDURE
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?
Thank you,
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 similiar 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 scenario
> CREATE PROCEDURE X()>
> DEFINE v_cust_num CHAR(20);
>
> BEGIN
>
> ON EXCEPTION
> END EXCEPTION WITH RESUME
>
> FOREACH cs_insert FOR
> SELECT cust_num INTO v_cust_num FROM A
> INSERT INTO B(cust_num) VALUES(v_cust_num)>
> END FOR
>
> END
>
> END PROCEDURE
>
> 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?
>
> Thank you,
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
That compiles.
Yours,
Jonathan Leffler
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