java.sql.SQLException: Unable to convert between [B and TIMESTAMP.

Highlighted
One Star

java.sql.SQLException: Unable to convert between [B and TIMESTAMP.

Talend throws the following error in a 2 step job.
java.sql.SQLException: Unable to convert between [B and TIMESTAMP.

The job is:
MSSqlInput --> tLogRow

The source is MS SQL Server 2005, and several columns in the table are defined as DATETIME -- to which Talend tries to assign a Date type.
What am I doing wrong?
Highlighted
Five Stars

Re: java.sql.SQLException: Unable to convert between [B and TIMESTAMP.

Hi Eric,
You're not doing anything wrong, rather Talend is incorrectly tries to map a MS SQL timestamp field to a datetime, which it shouldn't. You need to change the mapping_MSSQL.xml file (Preferences > Talend > Metadata of TalendType), under the <TalendToDbTypes> and <dbToTalendTypes> nodes.
Change the <TalendToDbTypes> node for the id_String mapping to add dbType of TIMESTAMP
			<talendType type="id_String">
<dbType type="VARCHAR" default="true" />
<dbType type="NVARCHAR" />
<dbType type="NCHAR" />
<dbType type="NTEXT" />
<dbType type="TEXT" />
<dbType type="TIMESTAMP" />
</talendType>

The change the <dbToTalendTypes> for TIMESTAMP:
				<dbType type="TIMESTAMP">
<talendType type="id_String" default="true" />
</dbType>

- Brian
Highlighted
One Star

Re: java.sql.SQLException: Unable to convert between [B and TIMESTAMP.

Change the <TalendToDbTypes> node for the id_String mapping to add dbType of TIMESTAMP
			<talendType type="id_String">
<dbType type="VARCHAR" default="true" />
<dbType type="NVARCHAR" />
<dbType type="NCHAR" />
<dbType type="NTEXT" />
<dbType type="TEXT" />
<dbType type="TIMESTAMP" />
</talendType>

The change the <dbToTalendTypes> for TIMESTAMP:
				<dbType type="TIMESTAMP">
<talendType type="id_String" default="true" />
</dbType>


I have added the TIMESTAMP dbType and modified the dbType entries as illustrated above, but Talend is not updating the schema. After changing the xml file I have clicked the "Reset DB Types" button in the Schema, I have removed the instance of the MSSQL Input object from the job and replaced it, and I have restarted Talend -- and the DATE datatypes have still not updated to String.
The two entries in mapping_MSSQL.xml now look like this:
				<talendType type="id_String">
<dbType type="VARCHAR" default="true" />
<dbType type="NVARCHAR" />
<dbType type="NCHAR" />
<dbType type="NTEXT" />
<dbType type="TEXT" />
<dbType type="TIMESTAMP" />
</talendType>
<dbType type="TIMESTAMP">
<talendType type="id_String" default="true" />
</dbType>

Is there something else I need to do?
Highlighted
Five Stars

Re: java.sql.SQLException: Unable to convert between [B and TIMESTAMP.

Sorry Eric. Check the <talendType> for id_Date under <talendToDbTypes>, you might have to remove the entry for TIMESTAMP there.

	<talendType type="id_Date">
<dbType type="DATETIME" default="true" />
<dbType type="SMALLDATETIME" />
</talendType>

- Brian
Highlighted
One Star

Re: java.sql.SQLException: Unable to convert between [B and TIMESTAMP.

Sorry Eric. Check the <talendType> for id_Date under <talendToDbTypes>, you might have to remove the entry for TIMESTAMP there.

Got it. Thanks.
FYI -- "Reset DB Types" on the Schema in the Repository still didn't work. I actually had to delete the Table Schema from the repository and re-add it from the DB (Retreive Schema).

2019 GARTNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog