topic Re: [resolved] Bad format for number tmysqlInput. in Talend Studio

[resolved] Bad format for number tmysqlInput.

Anonymous — Thu, 24 May 2012 10:53:09 GMT

Hi folks,
I am trying to run a query in tmysqlInput which pulls a field OracleID from DB .
Sample value for OracleID- 250000736H.00.09
It throws an error - "Bad format for number '250000736H.00.09' in column 45" when I run the job.
Field type in DB- varchar
Field type in talend schema- String.
P.S the query runs fine in the MySQL client but throws the error when run in talend.
Any suggestions??
Thanks in adv,
Baddy

Re: [resolved] Bad format for number tmysqlInput.

Anonymous — Thu, 24 May 2012 10:59:16 GMT

Hi Baddy
Are you sure the data type of this column is String(varchar)?
Please paste the entire error log.
Regards,
Pedro

Re: [resolved] Bad format for number tmysqlInput.

Anonymous — Thu, 24 May 2012 11:01:39 GMT

Hey Pedro ,
here you go.
Exception in component tMysqlInput_5
java.sql.SQLException: Bad format for number '250000736H.00.09' in column 45.
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1075)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:929)
at com.mysql.jdbc.ResultSetImpl.getDoubleInternal(ResultSetImpl.java:2522)
at com.mysql.jdbc.ResultSetImpl.getDoubleInternal(ResultSetImpl.java:2461)
at com.mysql.jdbc.ResultSetImpl.getDouble(ResultSetImpl.java:2422)
at entf.webdb_bhe_orders_feed_0_1.WebDB_BHE_Orders_Feed.tMysqlInput_2Process(WebDB_BHE_Orders_Feed.java:6682)
at entf.webdb_bhe_orders_feed_0_1.WebDB_BHE_Orders_Feed.runJobInTOS(WebDB_BHE_Orders_Feed.java:12360)
at entf.webdb_bhe_orders_feed_0_1.WebDB_BHE_Orders_Feed.main(WebDB_BHE_Orders_Feed.java:12164)
2012-05-24 15:19:03|VIUYlA|VIUYlA|VIUYlA|ENTF|WebDB_BHE_Orders_Feed|DEV|6|Java Exception|tMysqlInput_5|java.sql.SQLException:Bad format for number '250000736H.00.09' in column 45.|1
2012-05-24 15:19:10|VIUYlA|VIUYlA|VIUYlA|3972|ENTF|WebDB_BHE_Orders_Feed|_YnQssJofEeGSCfB9ZSjSYg|0.1|DEV||end|success|18438
disconnected

Re: [resolved] Bad format for number tmysqlInput.

Anonymous — Thu, 24 May 2012 11:17:37 GMT

Hi Pedro,
Waiting for your suggestion! Kinda urgent!
thanks,
Baddy

Re: [resolved] Bad format for number tmysqlInput.

janhess — Thu, 24 May 2012 11:28:16 GMT

Can you post an image of your schema.

Re: [resolved] Bad format for number tmysqlInput.

Anonymous — Thu, 24 May 2012 11:33:03 GMT

Hi Janhess,
Here is the tmysqlinput schema.
The last row is the OracleID field which is errorneous!

Re: [resolved] Bad format for number tmysqlInput.

janhess — Thu, 24 May 2012 11:37:13 GMT

Are you sure your schema matches your query? Did you create it using guess schema?

Re: [resolved] Bad format for number tmysqlInput.

Anonymous — Thu, 24 May 2012 11:38:59 GMT

No I wrote the query manually and tested on the MySQL client.
Also, if I give '000000000000000' as the default value in query to OracleID field , it runs fine.

Re: [resolved] Bad format for number tmysqlInput.

janhess — Thu, 24 May 2012 11:41:58 GMT

I don't think your schema matches the query. I think the data is mapping to one of the doubles. Thr selecting guess query. It should work then.

Re: [resolved] Bad format for number tmysqlInput.

Anonymous — Thu, 24 May 2012 11:46:38 GMT

Thanks Janhess,
I rechecked the query . It missed one one column and hence was not matching the query,
Thanks for your help. You guys rock!