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

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

Cloud Free Trial

Try Talend Cloud free for 30 days.

Tutorial

Introduction to Talend Open Studio for Data Integration.

Definitive Guide to Data Integration

Practical steps to developing your data integration strategy.

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.