The value supplied cannot be converted to INTEGER

Four Stars

The value supplied cannot be converted to INTEGER

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
One Star

Re: The value supplied cannot be converted to INTEGER

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
Four Stars

Re: The value supplied cannot be converted to INTEGER

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
One Star

Re: The value supplied cannot be converted to INTEGER

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
One Star

Re: The value supplied cannot be converted to INTEGER

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

Re: The value supplied cannot be converted to INTEGER

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
One Star

Re: The value supplied cannot be converted to INTEGER

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

Re: The value supplied cannot be converted to INTEGER

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

Re: The value supplied cannot be converted to INTEGER

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

Re: The value supplied cannot be converted to INTEGER

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

Re: The value supplied cannot be converted to INTEGER

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
One Star

Re: The value supplied cannot be converted to INTEGER

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

Re: The value supplied cannot be converted to INTEGER

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