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

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 (art@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. On Thu, Feb 4, 2010 at 5:10 PM, Gentian Hila <genti.tech@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, > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >