topic Re: Cannot determine value type from string 'INR' in Talend Studio

Cannot determine value type from string 'INR'

shalinim1 — Sat, 16 Nov 2024 01:06:38 GMT

Hi,

I have a tfileinputdelimited connected to db table with tmap, in input file there is a column called currency_type which has data like 'USD', 'INR', 'GBR'. The job ran successfully and the data was loaded to the db table.

When I tried to load from that db table to another table, it is throwing the following exception,

Exception in component tDBInput_1 (Partdimension)

java.sql.SQLDataException: Cannot determine value type from string 'INR'

at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:114)

at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)

at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)

at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)

at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:73)

at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:96)

at com.mysql.cj.jdbc.result.ResultSetImpl.getInt(ResultSetImpl.java:895)

at case_study_etl_process.partdimension_0_1.Partdimension.tDBInput_1Process(Partdimension.java:1718)

at case_study_etl_process.partdimension_0_1.Partdimension.runJobInTOS(Partdimension.java:3559)

at case_study_etl_process.partdimension_0_1.Partdimension.main(Partdimension.java:3409)

Caused by: com.mysql.cj.exceptions.DataConversionException: Cannot determine value type from string 'USD '

at com.mysql.cj.result.StringConverter.createFromBytes(StringConverter.java:131)

at com.mysql.cj.protocol.a.MysqlTextValueDecoder.decodeByteArray(MysqlTextValueDecoder.java:238)

at com.mysql.cj.protocol.result.AbstractResultsetRow.decodeAndCreateReturnValue(AbstractResultsetRow.java:143)

at com.mysql.cj.protocol.result.AbstractResultsetRow.getValueFromBytes(AbstractResultsetRow.java:250)

at com.mysql.cj.protocol.a.result.ByteArrayRow.getValue(ByteArrayRow.java:91)

at com.mysql.cj.jdbc.result.ResultSetImpl.getNonStringValueFromRow(ResultSetImpl.java:655)

... 4 more

Re: Cannot determine value type from string 'INR'

vikramk — Tue, 17 Nov 2020 04:13:06 GMT

Hi @Shalini M ,

Could you confirm the data type you have defined in the input and output schema for this column where you are getting 'INR'.

The error says Cannot determine value type from string. It means that you would have defined string type for the column and the value which is coming in the column is not recognized for its type.

You can cast the type to required type using tconvertype if the error is in the path of loading, or consider changing the type of data at source or target.

Re: Cannot determine value type from string 'INR'

shalinim1 — Tue, 17 Nov 2020 04:59:18 GMT

@Vikram Kumar, In every component the column is defined as datatype string. I tried to cast that particular column. But still getting the same exception.

Re: Cannot determine value type from string 'INR'

vikramk — Tue, 17 Nov 2020 05:22:22 GMT

Hi @Shalini M ,

You trying to read the value in string type but the values coming in the column is of int, would you mind checking your desired schema at the output table.

You should consider its type as int instead of string. Please have a check with your data model for its type.

Re: Cannot determine value type from string 'INR'

shalinim1 — Tue, 17 Nov 2020 05:39:30 GMT

@Vikram Kumar, The output schema has string datatype, when I changed it as int. It is throwing Detail Message: The method setInt(int, int) in the type PreparedStatement is not applicable for the arguments (int, String).

Re: Cannot determine value type from string 'INR'

vikramk — Tue, 17 Nov 2020 05:55:49 GMT

Hi @Shalini M ,

Have you implemented type casting to convert the fields to int type.

Re: Cannot determine value type from string 'INR'

shalinim1 — Tue, 17 Nov 2020 06:05:56 GMT

@Vikram Kumar, the data in that column is String like "INR", "USD".. How can we typecast it to int?

Re: Cannot determine value type from string 'INR'

vikramk — Tue, 17 Nov 2020 07:56:20 GMT

Hi @Shalini M ,

Sorry for correcting me, I thought your value is in int type.

Re: Cannot determine value type from string 'INR'

shalinim1 — Tue, 17 Nov 2020 08:04:39 GMT

@Vikram Kumar, That is why, I do not know how to solve this exception. My data is in string data type and schema also has string datatype. Still it is throwing this Exception.