topic java sql exception - tMysqlinput to csv outfile in Talend Studio

java sql exception - tMysqlinput to csv outfile

Anonymous — Wed, 23 Apr 2014 10:14:10 GMT

Hi,
I am getting this error,
Exception in component tMysqlInput_1
java.sql.SQLException: Invalid value for getInt() - 'C1567F5325EE09C4B6A8750D1F985331'
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:988)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:974)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:919)
at com.mysql.jdbc.ResultSetImpl.getInt(ResultSetImpl.java:2825)
I am trying to take out file from mysql. When I try simply its working.
The scenario here is this particular value is stored on MySQL as BINARY datatype. When I try to use HEX() to convert its coming like this 'C1567F5325EE09C4B6A8750D1F985331'. I am not sure why talend is looking for a Integer value since the datatype on DB is BINARY and when I retrieve schema, its showing as boolean.
Please note that same like this few other columns are working. also when I try only with this column on SELECT query, its working.
Please suggest where I am wrong. PLease let me know if need any further details.

Re: java sql exception - tMysqlinput to csv outfile

willm1 — Wed, 23 Apr 2014 15:03:15 GMT

Hello - have you tried setting the datatype as Object when reading from MySQL?
Will you be processing/transforming this data with Talend?

Re: java sql exception - tMysqlinput to csv outfile

Anonymous — Thu, 24 Apr 2014 08:16:19 GMT

Hi Will, Thanks for the response.
I have tried by changing all the data types including Object and its not working.
The same column is working fine when I try with only that particular column. It is NOT working when I try with all the columns.
I am using Talend to get outfile from mysql.
Please let me know any particular thing to be checked.

Re: java sql exception - tMysqlinput to csv outfile

Anonymous — Thu, 24 Apr 2014 10:05:57 GMT

Hi,
Have you checked ?
mysql> SELECT HEX(CHAR(1,0)), HEX(CHAR(256));
+----------------+----------------+
| HEX(CHAR(1,0)) | HEX(CHAR(256)) |
+----------------+----------------+
| 0100 | 0100 |
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html
I think issue is in conversion and using Hex()
Vaibhav

Re: java sql exception - tMysqlinput to csv outfile

Anonymous — Fri, 25 Apr 2014 12:39:00 GMT

I dont see any issue from MySQL end.
I am getting this error, Invalid value for getLong() - 'DB001'.
This value 'DB001' is returning from a column and the data type of this is varchar(50) and I have defined on Talend as "Object"
Also I dont think Talend is throwing the error for that particular column. Because when I try to execute the same column with select query, its working fine.
Real error may be because of some other column, but Talend is not throwing the proper/exact column.
-Partha

Re: java sql exception - tMysqlinput to csv outfile

Anonymous — Fri, 25 Apr 2014 12:43:19 GMT

Can you pl put the screenshot of your job and important transformations if you are doing any?
Vaibhav