insert into database and log duplicated rows and continue inserting the other record

[maher hattabi]

hi guys , i would like to copy data from oracle into PostreSQL, but there are duplicated rows with keys , i would like to log duplicated ones and continue copying he other rows , here what i did .Here the issue i got 

Location: /etl/query[1]/query[1]/script[1]

JDBC provider exception: Unable to execute statement

Error statement: 

INSERT INTO public.articles

(id,

is_enabled,type_marketing,type_tarif,description,gamme,import_local,marque,reference,struct,family_id)

values

(cast(? as bigint)

,'TRUE',?,?,?,?,?,?,?,?,cast(? as bigint)). Parameters: [662180137, 2, , xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx, , Local, , 662180137, 1173201099902, 11]

Error codes: [23505, 0]

Driver exception: org.postgresql.util.PSQLException: ERREUR: la valeur d'une clé dupliquée rompt la contrainte unique « articles_pkey »

 Detail: La clé « (id)=(662180137) » existe déjà.

and here the code i did 

<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE etl SYSTEM "http://scriptella.javaforge.com/dtd/etl.dtd">

<etl>

<description>

test script Pour table article

</description>

<connection id="in" driver="oracle"

url="jdbc:oracle:thin:@localhost:1521:XE" user="IPTECH" password="IPTECH" />

<connection id="out" driver="postgresql"

url="jdbc:postgresql://localhost:5432/gemodb" user="postgres"

password="maher" />

<connection id="janino" driver="janino" />

<connection id="log" driver="text" />

<query connection-id="in">

SELECT CODEARTICLE,STRUCTURE, DES,TYPEMARK,TYP,IMPLOC,MARQUE,GAMME,TAR

FROM IPTECH.TMP_ARTICLE ;

<query connection-id="janino">

import java.io.*;

import java.lang.*;

import org.apache.commons.lang3.StringUtils;

import org.apache.commons.lang3.*;

Boolean result= false ;

Object obj =get("CODEARTICLE");

if (StringUtils.isNumeric(obj.toString())) {

result=true ;}

else{

result=false ;}

set("result", result);

next();

<script connection-id="out" if="result">

INSERT INTO public.articles

(id,

is_enabled,type_marketing,type_tarif,description,gamme,import_local,marque,reference,struct,family_id)

values

(cast(?CODEARTICLE as bigint)

,'TRUE',?TYPEMARK,?TAR,?DES,?GAMME,?IMPLOC,?MARQUE,?CODEARTICLE,?STRUCTURE,cast(?{STRUCTURE.substring(0,

2)} as bigint));

</script>

</query>

</query>

</etl>

Any help would be appreciated 

[Christian MICHON]

Hi,

CODEARTICLE cast as bigint is not unique does not imply the rest of the attributes to be identical. So using CODEARTICLE from previous db as a primary key (or unique key) for destination table is not wise.

Maybe you need to just insert a null value as id: replace "cast(?CODEARTICLE as bigint)" by "null" if id is a primary key. You can remove the test on "result" and the janino block.

Please try out and let the mailing list know...

/Christian