topic Re: Fail to insert long number into AS400 in Talend Studio

Fail to insert long number into AS400

Anonymous — Sat, 16 Nov 2024 14:07:11 GMT

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... :+)

Re: Fail to insert long number into AS400

Anonymous — Wed, 17 Dec 2008 16:27:48 GMT

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

Re: Fail to insert long number into AS400

youssef2 — Wed, 17 Dec 2008 16:33:29 GMT

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

Re: Fail to insert long number into AS400

Anonymous — Wed, 17 Dec 2008 16:36:12 GMT

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

Re: Fail to insert long number into AS400

Anonymous — Wed, 17 Dec 2008 16:45:16 GMT

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