how to test on the column type

32 views
Skip to first unread message

maher hattabi

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

unread,
Aug 19, 2017, 5:49:21 PM8/19/17
to Scriptella ETL
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

unread,
Aug 19, 2017, 6:13:13 PM8/19/17
to scrip...@googlegroups.com
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

unread,
Aug 19, 2017, 6:43:39 PM8/19/17
to Scriptella ETL
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

unread,
Aug 19, 2017, 6:50:12 PM8/19/17
to scrip...@googlegroups.com
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

unread,
Aug 19, 2017, 7:17:20 PM8/19/17
to Scriptella ETL
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.


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

unread,
Aug 19, 2017, 7:44:57 PM8/19/17
to scrip...@googlegroups.com
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

unread,
Aug 20, 2017, 9:24:57 AM8/20/17
to scrip...@googlegroups.com
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>
 
Reply all
Reply to author
Forward
0 new messages