Re: catch exception within a foreach loop and move to the next row
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.kagel@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 (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
>
>
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.
>
> > On Thu, Feb 4, 2010 at 5: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,
> >> _______________________________________________
> >> Informix-list mailing list
> >> Informix-l...@iiug.org
> >>http://www.iiug.org/mailman/listinfo/informix-list