Is there any way to log failed insert row because of unique index violation - Informix

[cha...@gmail.com]

Hello,
When I am trying to insert a row into a table I am getting the
follwoing error. Is there any way to log this failed row ( row values)
because of unique index violation.

insert into t1
select * from t2 where col2 = 2 ;

239: Could not insert new row - duplicate value in a UNIQUE INDEX
column (Unique Index:idx1).
100: ISAM error: duplicate value for a record with unique key.

-- Chavan

[Everett Mills]

How about a trigger & stored procedure that checks if the record exists
and then writes it into an exception table if it's already there? You
could also record such things as the time and user ID, if you need it.

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

[Vagner]

You can enable violations tables, for examplo:

start violations table for t1;
set contraints for table t1 filtering without error;

You application doesnt raise error -239, and the row with duplicate
will be insert on violations table.

Vagner

[Jonathan Leffler]

On Fri, Feb 27, 2009 at 1:22 PM, <cha...@gmail.com> wrote:
>        When I am trying to insert a row into a table I am getting the

> following error. Is there any way to log this failed row (row values)

> because of unique index violation.
>
> insert into t1
> select * from t2 where col2 = 2 ;
>
>  239: Could not insert new row - duplicate value in a UNIQUE INDEX
> column (Unique Index:idx1).
>  100: ISAM error:  duplicate value for a record with unique key.

Consider whether START VIOLATIONS TABLE provides what you need (SQL
Syntax Manual).

If you are trying to debug an immediate problem, then this will
probably get you the answer.
If you are trying to do this long term, I'd probably think twice -- it
will still work, of course, but you really shouldn't need it except in
extraordinary situations.

--
Jonathan Leffler #include <disclaimer.h>
Email: jlef...@earthlink.net, jlef...@us.ibm.com
Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/
"Blessed are we who can laugh at ourselves, for we shall never cease
to be amused."
NB: Please do not use this email for correspondence.
I don't necessarily read it every week, even.

[cha...@gmail.com]

Jonathan, Everett, Vagner - Thank you very much for the responses. I
was able to log the row with START VIOLATIONS with the following
statements.

START VIOLATIONS TABLE FOR t1 max rows 1;
SET CONSTRAINTS FOR t1 FILTERING WITHOUT ERROR;
insert into t1
select * from t2 ;
STOP VIOLATIONS TABLE FOR t1;
select * from t1_vio ;
select * from t1_dia ;
drop table t1_vio ;
drop table t1_dia ;

Thanks & Regards,
-- Chavan

On Feb 28, 6:54 pm, Jonathan Leffler <jleffler.i...@gmail.com> wrote:

> On Fri, Feb 27, 2009 at 1:22 PM,  <chav...@gmail.com> wrote:
> >        When I am trying to insert a row into a table I am getting the
> > following error. Is there any way to log this failed row (row values)
> > because of unique index violation.
>
> > insert into t1
> > select * from t2 where col2 = 2 ;
>
> >  239: Could not insert new row - duplicate value in a UNIQUE INDEX
> > column (Unique Index:idx1).
> >  100: ISAM error:  duplicate value for a record with unique key.
>
> Consider whether START VIOLATIONS TABLE provides what you need (SQL
> Syntax Manual).
>
> If you are trying to debug an immediate problem, then this will
> probably get you the answer.
> If you are trying to do this long term, I'd probably think twice -- it
> will still work, of course, but you really shouldn't need it except in
> extraordinary situations.
>
> --
> Jonathan Leffler                   #include <disclaimer.h>

> Email: jleff...@earthlink.net, jleff...@us.ibm.com
> Guardian of DBD::Informix v2008.0513 --http://dbi.perl.org/