catch exception within a foreach loop and move to the next row

[Gentian Hila]

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,

[Art Kagel]

VERSION INFORMATION PLEASE!!!!!!!!!!!!!!!!!!!!!!!!!!!!

IDS 11.50 has the new UPSERT statement that will update or insert.  If you have an earlier version of IDS you can use the sqlupsert utility in Jonathan Leffler's sqlcmd package.

If you mean to ignore rows that are already in the target you can use my dbcopy utility which has an options to ignore INSERT errors.  Dbcopy is in the utils2_ak package.

Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (a...@iiug.org)

See you at the 2010 IIUG Informix Conference
April 25-28, 2010
Overland Park (Kansas City), KS
www.iiug.org/conf

Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference.  Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.

_______________________________________________
Informix-list mailing list
Inform...@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list

[Gentian Hila]

Thanks,

Right now I use 9.40. We are upgrading in 11.5 in about 2 months.

Yes, I want to ignore rows that are already in the target.

But this is will be a temporary table that will be created once a
month for a report. Not a database copy though.

[Superboer]

i would use violations for this eq

start violation table for b;
create unique index ixie on b(cust_num) filtering without error;

Then do the foreach;
the duplicates will be stored in the violations table... so no need
for exceptions..

when done set the index enabled and stop violations.
the diag and vio tables can be dropped later if not needed..

please check the manual i may have a typo in the syntax ... am getting
older...

Superboer

On 5 feb, 04:45, Gentian Hila <genti.t...@gmail.com> wrote:
> Thanks,
>
> Right now I use 9.40. We are upgrading in 11.5 in about 2 months.
>
> Yes, I want to ignore rows that are already in the target.
>
> But this is will be a temporary table that will be created once a
> month for a report. Not a database copy though.
>

> On Thu, Feb 4, 2010 at 8:13 PM, Art Kagel <art.ka...@gmail.com> wrote:
> > VERSION INFORMATION PLEASE!!!!!!!!!!!!!!!!!!!!!!!!!!!!
>
> > IDS 11.50 has the new UPSERT statement that will update or insert.  If you
> > have an earlier version of IDS you can use the sqlupsert utility in Jonathan
> > Leffler's sqlcmd package.
>
> > If you mean to ignore rows that are already in the target you can use my
> > dbcopy utility which has an options to ignore INSERT errors.  Dbcopy is in
> > the utils2_ak package.
>
> > Art S. Kagel
> > Advanced DataTools (www.advancedatatools.com)
> > IIUG Board of Directors (a...@iiug.org)
>
> > See you at the 2010 IIUG Informix Conference
> > April 25-28, 2010
> > Overland Park (Kansas City), KS
> >www.iiug.org/conf
>
> > Disclaimer: Please keep in mind that my own opinions are my own opinions and
> > do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
> > organization with which I am associated either explicitly, implicitly, or by
> > inference.  Neither do those opinions reflect those of other individuals
> > affiliated with any entity with which I am affiliated nor those of the
> > entities themselves.
>

> >> Informix-l...@iiug.org
> >>http://www.iiug.org/mailman/listinfo/informix-list

[Jonathan Leffler]

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

[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

[Gentian Hila]

Thank you,

This did it.

But also learned some other ways from the previous posts.

So thank you everyone for helping me out.