how to test on the column type

[maher hattabi]

Hello , guys i am trying to copy data from two database.i would like t copy a column from a table in the first  database .But to copied i need to test whether the column is numeric because i am copying a numerical code , here what i did any help plz thanks 

<?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="test" password="test" />

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

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

        password="maher" />

<query connection-id="in">

        SELECT CODE from test.TMP_FOURNISSEUR;

        <script connection-id="out" if =" code is numeric" >

            INSERT INTO public.suppliers

            (code) values

            (?CODE);

        </script>

    </query>

</etl>

Saisissez le code ici...

[Christian MICHON]

Hi,

you need to ensure the datatypes between source and destination are coherent, if possible exact same datatype. Not doing this would ensure data incoherence, or worse: no data copied at all.

Another alternative (since you're migrating data between 2 SQL engines) would be to use "cast(?code as integer)" (replace integer by the datatype you really need).

If cast is not possible (no need to have a jexl test if="", the SQL engine is checking if cast is possible or not), the transaction will fail and will be logged.

This is the rewritten code:

<query connection-id="in">

  SELECT CODE from test.TMP_FOURNISSEUR;

  <script connection-id="out">

    INSERT INTO public.suppliers (code) values (cast(?CODE as integer));

  </script>

</query>

Good luck.

Christian

[maher hattabi]

i have already tried this , but it did block , it say that i have a problem with special record and it won't copy the other correct record 

i got this issue  in the exception 

what should i do plz 

Location: /etl/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

integer),'TRUE',?,?,?,?,?,?,?,?,cast(? as bigint)). Parameters: [GENCFFRIST, 0, , xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx, , Local, , GENCFFRIST, 9999999999998, 99]

Error codes: [22P02, 0]

Driver exception: org.postgresql.util.PSQLException: ERREUR: syntaxe en entrée invalide pour l'entier : « GENCFFRIST »

[Christian MICHON]

Please share full query and script, it's hard to debug otherwise.

Obviously in your query id and reference have the same value GENCFFRIST on one of your records. If id is a new primary key to be generated (autoincrement), you could use null instead of the first cast.

But if you need to keep the id value from input database, you need to debug your query first.

[maher hattabi]

here is the full querry , the id need tb be numeric , but here the data i have the id is a string i would like to by pass it , not to insert it in the database , but logging it THe other correct record need to be copied it is not the case , because the bad record block the other insert i would like to copy the correct record and log the bad ones HEre what 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/testMonoprix2" user="postgres"

password="maher" />

<query connection-id="in">

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

FROM IPTECH.TMP_ARTICLE ;

<script connection-id="out">

INSERT INTO public.articles

(id,

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

values

(cast(?CODEARTICLE

as

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

2)} as integer));

</script>

</query>

</etl>

--
You received this message because you are subscribed to a topic in the Google Groups "Scriptella ETL" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/scriptella/ePqienIeCNQ/unsubscribe.
To unsubscribe from this group and all its topics, send an email to scriptella+unsubscribe@googlegroups.com.
To post to this group, send email to scrip...@googlegroups.com.
Visit this group at https://groups.google.com/group/scriptella.
For more options, visit https://groups.google.com/d/optout.

[Christian MICHON]

Should CODEARTICLE (from Oracle DB) be a id (likely integer primary key) in Postgres destination table?

If CODEARTICLE is not unique in source db, it will fail.

If the type is not numeric datatype but varchar, with sometimes numbers stored in it, it will give errors whenever it's a string.

If you wish to log when it's not a number and store when it's a number, then you need a test indeed, done with an extra attribute or an extra query.

I would go with the extra parameter:

    SELECT CODEARTICLE,STRUCTURE, DES,TYPEMARK,TYP,IMPLOC,MARQUE,GAMME,TAR, is_numeric(CODEARTICLE) as is_number

FROM IPTECH.TMP_ARTICLE ;

    

Then use is_number in the jexl test you did in 1st email:  if ="is_number==true"

Please note Oracle normally does not have a built-in function to detect if a string is numeric or not.

Please add function as described here: https://stackoverflow.com/questions/28819709/how-to-tell-if-a-value-is-not-numeric-in-oracle

If you cannot do it in Oracle, you need a janino block checking if the value from query is a number, and then set the result of the test as is_number.

    

Hope this clarifies. Sometime Scriptella is limited by the abilities of the SQL engines called in connections, but overall with jexl+janino it's a very good product.

Christian

To unsubscribe from this group and all its topics, send an email to scriptella+...@googlegroups.com.

[maher hattabi]

I tried someethin in janino like this , it didn't work .Any help to check whether the value is numeric or not , am ust new to this.really appreciate , thanks 

<?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/testMonoprix2" user="postgres"

password="maher" />

<connection id="log" driver="text" /> <!-- For printing debug information on the console -->

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

<query connection-id="in">

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

FROM IPTECH.TMP_ARTICLE ;

<script connection-id="java">

import java.io.*;

String ntteCode;

Boolean result;

Object c = get("CODEARTICLE");

test =Character.isDigit(c)

</script>

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

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>

</etl>

To unsubscribe from this group and all its topics, send an email to scriptella+unsubscribe@googlegroups.com.

[maher hattabi]

hello guys i figured out the solution here we go 

<?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>