topic Re: The value supplied cannot be converted to INTEGER in Talend Studio

The value supplied cannot be converted to INTEGER

sivaa_m — Mon, 28 May 2012 08:37:47 GMT

Hi experts,
I m new to Talend. I m currently working on the DW project. Through online tutorial I have learnt how to do mapping using tmap. I need to insert data from the mssql table to another mssql table. From source only four columns need to be inserted into the target table. All are varchar columns. First tried to view the data of the source through tLogRow, while execution I m getting the below error
Exception in component tMSSqlInput_1
java.sql.SQLException: The value supplied cannot be converted to INTEGER.
at net.sourceforge.jtds.jdbc.Support.convert(Support.java:653)
at net.sourceforge.jtds.jdbc.JtdsResultSet.getInt(JtdsResultSet.java:641)
at edw.psp_dim_0_1.PSP_DIM.tMSSqlInput_1Process(PSP_DIM.java:778)
at edw.psp_dim_0_1.PSP_DIM.runJobInTOS(PSP_DIM.java:1268)
at edw.psp_dim_0_1.PSP_DIM.main(PSP_DIM.java:1136)
As there are no integer column, I could find why this error has occurred. Kindly do the needful. I have attached the screenshot for further use.
Thanks
Siva

Re: The value supplied cannot be converted to INTEGER

Anonymous — Mon, 28 May 2012 08:45:21 GMT

Hi Siva
Welcome to Talend Community!
I see a yellow warning at the right top corner of tMSSQLInput. Hover your mouse on this warning. Is there any hint?
Some of the tMSSQLInput settings must be wrong. You might retrieve schema from metadata->DB connections. It's a more easier way.
Regards,
Pedro

Re: The value supplied cannot be converted to INTEGER

sivaa_m — Mon, 28 May 2012 10:26:19 GMT

Hi,
Thanks for the reply.
I checked the warning, but no hint got displayed in the tooltip for the MSSqlInput. Please find the attached screenshot.
Thanks
Siva

Re: The value supplied cannot be converted to INTEGER

Anonymous — Mon, 28 May 2012 10:30:45 GMT

Hi Siva
Maybe the query of tMSSQLInput is different from the schema of this component.
You'd better check it. Or retrieve schema from metadata-> DB Connection.
Regards,
Pedro

Re: The value supplied cannot be converted to INTEGER

janhess — Mon, 28 May 2012 10:57:38 GMT

Are you trying to get string data into an integer column?

Re: The value supplied cannot be converted to INTEGER

sivaa_m — Mon, 28 May 2012 11:43:09 GMT

Hi Pedro,
I retrieved the schema from the metadata -> DB Connections only. Please find the attached select query used in the MSSqlInput.
SELECT PSP_STAGE.DB_LOOKUP_PAY_PROVIDER,
PSP_STAGE.TXN_TYPE,
PSP_STAGE.CREDIT_CARD,
PSP_STAGE.ANALYST_GROUPING
FROM PSP_STAGE
these are columns which i need to get inserted into the target, there are also other columns in the PSP_STAGE table, which are not used, whether bcoz of it, the error is occurring.
Kindly suggest.
Thanks
Siva

Re: The value supplied cannot be converted to INTEGER

janhess — Mon, 28 May 2012 11:44:19 GMT

But are you reading a string into an integer? What does the schema look like?
Does your schema match the query?

Re: The value supplied cannot be converted to INTEGER

Anonymous — Mon, 28 May 2012 11:48:59 GMT

Hey,
Could you please post the schema of your "PSP_STAGE" component in talend and the datatypes of the table PSP_STAGE?
Regards,
Madhao

Re: The value supplied cannot be converted to INTEGER

sivaa_m — Mon, 28 May 2012 11:58:34 GMT

Hi,
Please find the attached screenshot of the PSP_STAGE schema definition.
Thanks
Siva

Re: The value supplied cannot be converted to INTEGER

janhess — Mon, 28 May 2012 12:00:12 GMT

Well there's the answer. Your schema doesn't match the query. Click on the guess schema button.

Re: The value supplied cannot be converted to INTEGER

sivaa_m — Tue, 29 May 2012 10:04:00 GMT

hi,
Thanks for the reply. The error has been resolved after changing the select query, but while trying to insert into the target table. I need to apply substring on one of the column which I have mapped like
row1.DB_LOOKUP_PAY_PROVIDER.substring(0,24) in the tmap.
While execution I m getting the below error.
Exception in component tMap_1
java.lang.StringIndexOutOfBoundsException: String index out of range: 24
at java.lang.String.substring(Unknown Source)
at edw.psp_dim_0_1.PSP_DIM.tMSSqlInput_1Process(PSP_DIM.java:1015)
at edw.psp_dim_0_1.PSP_DIM.runJobInTOS(PSP_DIM.java:1460)
at edw.psp_dim_0_1.PSP_DIM.main(PSP_DIM.java:1328)
Kindly suggest.
Thanks
Siva

Re: The value supplied cannot be converted to INTEGER

Anonymous — Tue, 29 May 2012 17:33:20 GMT

try using the Long data type instead of the Integer. It can handle larger numbers.

Re: The value supplied cannot be converted to INTEGER

janhess — Wed, 30 May 2012 09:06:48 GMT

That's because your string is less than 24 characters.