Fail to insert long number into AS400

One Star

Fail to insert long number into AS400

Hi,

Very easy to reproduce, I simply have a tFileInputPositional linked to an AS400Output with same schema (use the sync button in order to be sure, and I've done a double-check !)
In the tFileInputPositional, I've got a big number field.

*If i try to set it as Integer :
For input string: "000002500000000"
For input string: "000002500000000"
For input string: "000003125000000"
For input string: "000007562500000"
For input string: "000002500000000"
For input string: "000002500000000"
For input string: "000003125000000"
For input string: "000007562500000"
As expected, because my number is too large for an integer... (by the way, is there a way to have the full stack trace instead of this very short message ? The full message should be "NumberFormatException For input string blah blah")
*But if I set it as Long :
Exception in component tAS400Output_1
java.sql.DataTruncation: Data truncation
at com.ibm.as400.access.AS400JDBCPreparedStatement.testDataTruncation(AS400JDBCPreparedStatement.java:3040)
at com.ibm.as400.access.AS400JDBCPreparedStatement.setValue(AS400JDBCPreparedStatement.java:2951)
at com.ibm.as400.access.AS400JDBCPreparedStatement.setLong(AS400JDBCPreparedStatement.java:2323)
at test.testnombre2_0_1.TestNombre2.tFileInputPositional_1Process(TestNombre2.java:2580)
at test.testnombre2_0_1.TestNombre2.runJobInTOS(TestNombre2.java:2969)
at test.testnombre2_0_1.TestNombre2.main(TestNombre2.java:2882)
Yes, this time it's the AS400Output component which is complaining...

*And eventually if I set my field as String, it works fine (but I want a Long column, not varchar !)
How could you explain that ?
Thank you

edit : maybe there's a link between this strange behaviour and my previous post about strange character at the begining of my inserted fields.
Maybe the AS400 jdbc driver try to add the -now well known- extra character in front of my number ? A lot of maybe... :+)
One Star

Re: Fail to insert long number into AS400

I've just tried with a tFixedFlowInput with only one column (which contain my "2500000000" long number) connected to the AS400Output : same error !
I think it's a jdbc driver issue
Five Stars

Re: Fail to insert long number into AS400

Hi,
Did you verify your column length in your DB schema? It should be greater than 10 to hold your number "2500000000".
Regards,
Youssef
One Star

Re: Fail to insert long number into AS400

Hi,
Did you verify your column length in your DB schema? It should be greater than 10 to hold your number "2500000000".
Regards,
Youssef

Yes, my column length is 15 characters on both side. Moreover, the AS400Output is set with "Drop table if exist then create" option
One Star

Re: Fail to insert long number into AS400

Shame on me, it seems that long type in db is not the same that long type in java/c/c++, etc
I set my field as "BigDecimal" on both side and it seems to work.
Anyone to confirm that "long" in db world is not "long" in java world ?
thanks !
edit : according to this website ( http://www.ss64.com/orasyntax/datatypes.html ) a LONG is "Character data of variable length (A bigger version the VARCHAR2 datatype)"
so, sorry for these messages Smiley Happy

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog