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

37 views
Skip to first unread message

maher hattabi

unread,
Aug 20, 2017, 9:31:25 AM8/20/17
to Scriptella ETL
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

unread,
Aug 20, 2017, 7:09:37 PM8/20/17
to Scriptella ETL
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
Reply all
Reply to author
Forward
0 new messages