Cannot determine value type from string 'INR'

[shalinim1]

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

[vikramk]

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.

[shalinim1]

@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.

[vikramk]

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.

[shalinim1]

@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).

[vikramk]

Hi @Shalini M​ ,

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

[shalinim1]

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

[vikramk]

Hi @Shalini M​ ,

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

[shalinim1]

@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.