incompatible data type in conversion: from SQL type OTHER to java.lang.String

[welshsteve]

Hi. I'm trying to use the dynamic data type to grab data tables from an Access 2016 database into a MSSQL database, and getting the following error:

Starting job Access2016 at 11:03 27/04/2022.

[statistics] connecting to socket on port 3932

[statistics] connected

Exception in component tDBInput_5 (Access2016)

net.ucanaccess.jdbc.UcanaccessSQLException: incompatible data type in conversion: from SQL type OTHER to java.lang.String, value: instance of org.hsqldb.types.JavaObjectData

at net.ucanaccess.jdbc.UcanaccessResultSet.getString(UcanaccessResultSet.java:687)

at routines.system.DynamicUtils.readColumnsFromDatabase_Access(DynamicUtils.java:223)

at stevew_test.access2016_0_2.Access2016.tDBInput_5Process(Access2016.java:1069)

at stevew_test.access2016_0_2.Access2016.runJobInTOS(Access2016.java:2767)

at stevew_test.access2016_0_2.Access2016.main(Access2016.java:2362)

Caused by: java.sql.SQLSyntaxErrorException: incompatible data type in conversion: from SQL type OTHER to java.lang.String, value: instance of org.hsqldb.types.JavaObjectData

at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)

at org.hsqldb.jdbc.JDBCUtil.throwError(Unknown Source)

at org.hsqldb.jdbc.JDBCResultSet.getColumnInType(Unknown Source)

at org.hsqldb.jdbc.JDBCResultSet.getString(Unknown Source)

at net.ucanaccess.jdbc.UcanaccessResultSet.getString(UcanaccessResultSet.java:685)

... 4 more

Caused by: org.hsqldb.HsqlException: incompatible data type in conversion: from SQL type OTHER to java.lang.String, value: instance of org.hsqldb.types.JavaObjectData

at org.hsqldb.error.Error.error(Unknown Source)

at org.hsqldb.error.Error.error(Unknown Source)

... 7 more

[FATAL]: stevew_test.access2016_0_2.Access2016 - tDBInput_5 incompatible data type in conversion: from SQL type OTHER to java.lang.String, value: instance of org.hsqldb.types.JavaObjectData

net.ucanaccess.jdbc.UcanaccessSQLException: incompatible data type in conversion: from SQL type OTHER to java.lang.String, value: instance of org.hsqldb.types.JavaObjectData

at net.ucanaccess.jdbc.UcanaccessResultSet.getString(UcanaccessResultSet.java:687)

at routines.system.DynamicUtils.readColumnsFromDatabase_Access(DynamicUtils.java:223)

at stevew_test.access2016_0_2.Access2016.tDBInput_5Process(Access2016.java:1069)

at stevew_test.access2016_0_2.Access2016.runJobInTOS(Access2016.java:2767)

at stevew_test.access2016_0_2.Access2016.main(Access2016.java:2362)

Caused by: java.sql.SQLSyntaxErrorException: incompatible data type in conversion: from SQL type OTHER to java.lang.String, value: instance of org.hsqldb.types.JavaObjectData

at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)

at org.hsqldb.jdbc.JDBCUtil.throwError(Unknown Source)

at org.hsqldb.jdbc.JDBCResultSet.getColumnInType(Unknown Source)

at org.hsqldb.jdbc.JDBCResultSet.getString(Unknown Source)

at net.ucanaccess.jdbc.UcanaccessResultSet.getString(UcanaccessResultSet.java:685)

... 4 more

Caused by: org.hsqldb.HsqlException: incompatible data type in conversion: from SQL type OTHER to java.lang.String, value: instance of org.hsqldb.types.JavaObjectData

at org.hsqldb.error.Error.error(Unknown Source)

at org.hsqldb.error.Error.error(Unknown Source)

... 7 more

[statistics] disconnected

Job Access2016 ended at 11:04 27/04/2022. [Exit code = 1]

This is one of my real bug bears with Talend Studio. It doesn't tell you what column it's having an issue with, just throws an error that's really not very helpful at all. This table has dozens of columns, hence me using the dynamic data type so I don't have to spend ages building a manual schema for it.

I'm not quite sure why Talend is having an issue putting data into a string. The only source data types it needs to deal with are "Number", "Short Text", "Date/Time" and "Yes/No".

Anyone have any ideas how I can get around this?

[welshsteve]

Just to add to this. I created a brand new access database with one table, one row, and included all the data types in the original access database, and it has no issues, so something else in the data is causing an issue, it's just the error is not telling me hat

[Anonymous]

Hello,

We are suspecting that Schema defined in the component and in the destination table could be different.

You could use tMap to convert the data type here for the data coming from the source to match the destination DB and overcome this issue.

Best regards

Sabrina

[welshsteve]

Hi Sabrina

I have tried this. It just doesn't want to know and gives the same error.

The destination table doesn't exist as I'm telling the tDBOutput component to drop and recreate the table each time.

I have given up on this now and will just deal with this manually instead, as I just don't think the components in Studio are fully supporting Access 2016 data types.

[Anonymous]

Hello,

Could you please clarify in which Talend version/edition you are? I will make an investigation on your issue and then come back to you as soon as possible.

Best regards

Sabrina

[welshsteve]

Hi Sabrina. Yes I'm running Talend Studio R2022-04-7.3.1 (licensed version)