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