9 Replies Latest reply on Sep 12, 2014 2:08 PM by shawkins

Any downsides to the implicit conversion of dates to strings when using a dynamic VDB?

lselecky Sep 11, 2014 12:49 PM

Hi,

I deployed a dynamic VDB for a SQL Server database and noticed that the date columns are shown as strings when I check the column type metadata via DB Visualizer.

Data Virtualization performs implicit conversions so queries with date values do succeed, like this one:

SELECT * FROM SomeDW.dbo.SomeTable where lastservicedate >= '2014-01-01' AND lastservicedate <= '2014-03-31';

But are there any downsides to the implicit data type conversion (i.e., performance) or is it perfectly fine to rely on this behavior?

Any compelling reasons to define additional metadata in the dynamic VDB xml file to retain the date columns as dates?

Thanks,

Les

1. Re: Any downsides to the implicit conversion of dates to strings when using a dynamic VDB?

shawkins Sep 11, 2014 4:58 PM (in response to lselecky)

Before getting to the other questions, we need to see why we are using a string mapping. Going directly to SQL Server what is the declared type of those columns and what is reported in DB Visualizer?
Actions
2. Re: Any downsides to the implicit conversion of dates to strings when using a dynamic VDB?

lselecky Sep 11, 2014 5:55 PM (in response to shawkins)

When accessing the table directly via DB Visualizer the TYPE_NAME is "date".

I also connected directly via SQL Server Management Studio and the column in the DDL is defined as:

[LastServiceDate] [date] NOT NULL,
Actions
3. Re: Any downsides to the implicit conversion of dates to strings when using a dynamic VDB?

shawkins Sep 12, 2014 8:28 AM (in response to lselecky)

Any chance you are using an older jdbc client on the server side, such that the date type is not yet supported. Otherwise I don't see a reason for the different handling as we map JDBC columns with a type code of date (91) to the Teiid runtime type of date.
Actions
4. Re: Any downsides to the implicit conversion of dates to strings when using a dynamic VDB?

lselecky Sep 12, 2014 8:56 AM (in response to shawkins)

On the server in the jboss-eap-6.1/dataVirtualization/jdbc directory the jar is:

teiid-8.4.1-redhat-7-jdbc.jar

The teiid download page (http://teiid.jboss.org/downloads/) has a version for 8.8.1 but the jar is named "teiid-8.8.1-jdbc.jar"; should I try that jar?
Does it matter that it's not the Red Hat version?

Thanks.
Actions
5. Re: Any downsides to the implicit conversion of dates to strings when using a dynamic VDB?

shawkins Sep 12, 2014 9:23 AM (in response to lselecky)

No I mean the SQL Server client. SQL Server 2008 added support for the date type and older clients report it as string, rather than date.
Actions
6. Re: Any downsides to the implicit conversion of dates to strings when using a dynamic VDB?

lselecky Sep 12, 2014 9:57 AM (in response to shawkins)

I appreciate the quick replies. Just checked and it's SQL Server 2012.
Actions
7. Re: Any downsides to the implicit conversion of dates to strings when using a dynamic VDB?

shawkins Sep 12, 2014 10:13 AM (in response to lselecky)

If you see that a type code of 91 is being returned and Teiid is mapping to string, then that is an issue. Otherwise my best guess is still that it's a SQL Server client jar issue - see http://technet.microsoft.com/en-us/library/ms379048%28v=sql.110%29.aspx

> But are there any downsides to the implicit data type conversion (i.e., performance) or is it perfectly fine to rely on this behavior?

It is mostly fine, but you may see some inconsistencies with operations evaluated in Teiid vs. the source since they are using different types.
Actions
8. Re: Any downsides to the implicit conversion of dates to strings when using a dynamic VDB?

lselecky Sep 12, 2014 1:49 PM (in response to shawkins)
Thanks Steven, you're right, it's because of the driver.

I looked at the JDBC metadata after accessing the DB directly using the JTDS JDBC driver and Microsoft's driver.

The JTDS driver returned a data_type of 12 but a type_name of date.
The MS driver correctly returned a data_type of 91 and a type_name of date.

The JTDS driver is the problem and I'm using a current version.

Thanks again for the help!
Actions
9. Re: Any downsides to the implicit conversion of dates to strings when using a dynamic VDB?

shawkins Sep 12, 2014 2:08 PM (in response to lselecky)

Is this does introduce issues for JTDS users down the road, then we'll have to look at updating the type mapping logic for the SQL Server importer. Thanks for bringing this to our attention.
Actions

Go to original post