The conversion from int to TIMESTAMP is unsupported

[mathangi]

Hi,

I get the following error when I execute a query against a vdb-

Error Code:0 Message:Remote org.teiid.core.TeiidProcessingException: Error Code:0 Message:Error Code:0 Message:Unexpected exception while translating results: The conversion from int to TIMESTAMP is unsupported.

This is the query i am trying to execute-

SELECT

  concat2(concat2(concat2(CONCAT2('7', '-'), 'DSTR'), '-'), INTERNAL_ID) AS OBJECTuid, convert(INTERNAL_ID, string) AS ObjectId, APPL_SYS_NAME AS ObjectName, 'DSTR' AS ObjectType, convert(null,timestamp) AS CreatedDate, '' AS CreatedBy, convert(null,timestamp)  AS LastUpdatedDate, MAINTAINED_BY AS LastUpdatedBy, 7 AS RepositoryId, '' AS Path

FROM

  SQLMappingNew.Metaverse.dbo.E_APLSYS AS AP

Previuosly I had the columns CreatedDate and lastUpdatedDate mapped to null, so I was assuming that that could be the issue. So, I used convert function to change null into timestamp datatype since in my view CreatedDate and lastUpdatedDate are of datatype timestamp.

But even then i see this error. I know this is an SQL server error, but atleast after using convert I should not be getting this error? Is there any default cache setting that is enabled in Teiid or any idea of why i would get this error?

Thanks,

Mathangi

[rareddy]

Mathangi,

For valid implicit/explicit data type conversion that are allowed please see http://docs.jboss.org/teiid/7.4.0.Final/reference/en-US/html_single/#type_conversions

The error is indicating that, the source is returning a "int" column data, for a column that is mapped as "TIMESTAMP" in the Teiid. Please check you source schema to model mapping in Teiid VDB. Also, remove the columns one by one, and execute in the above SQL to see which column is causing the issue.

Ramesh..

[mathangi]

This error was resolved using jtds driver for SQL instead of sqljdbc.jar. In fact so many issues are getting resolved if we use jtds driver.

Thanks,

Mathangi

[rareddy]

Thanks for the update Mathangi. We have also found JTDS driver much better performing under JBoss AS VM than native one. Also solved couple other issues we faced with native driver.

Ramesh..