Are there any plans to add support for mapping SQL_SS_TIMESTAMPOFFSET to/from TIMESTAMP WITH TIME ZONE in the SQL Anywhere ODBC drivers? The MobiLink server .NET API reference says that they "can be used only against Microsoft SQL Server and Oracle databases".

If not, could encouragement from users spark interest? Is there a technical issue that flat out prevents it? Is it (fingers crossed) actually supported, but just not documented?

After all, the documentation (Ways to send dates and times to the database) says "The date and time of day with a time zone offset can be sent to the database as a string only.", but I've found that Entity Framework, at least, happily maps TIMESTAMP WITH TIME ZONE to System.DateTimeOffset.

asked 28 May '13, 21:05

Dan%20Konigsbach's gravatar image

Dan Konigsbach
455101220
accept rate: 0%


Is there a technical issue that flat out prevents it?

Yes there is - maybe not "prevent" it from ever happening, but certainly "makes it more difficult to implement". In general, our ODBC driver targets ODBC version 3.5, due to general compatibility concerns with third-party ODBC driver managers on UNIX operating systems (e.g. unixODBC, and iODBC). From: http://msdn.microsoft.com/en-us/library/bb677267.aspx

Because ODBC does not permit driver defined C types, SQL_C_BINARY is used for time and datetimeoffset as binary structures.

and: http://msdn.microsoft.com/en-us/library/windows/desktop/ms714822%28v=vs.85%29.aspx

In ODBC 3.8, you can specify driver-specific C data types. This enables you to bind a SQL type as a driver-specific C type in ODBC applications when you call SQLBindCol, SQLGetData, or SQLBindParameter. This can be useful for supporting new server types, because existing C data types might not correctly represent the new server data types. Using driver-specific C types can increase the number of conversions that drivers can perform.

For example, suppose a database management system (DBMS) introduced a new SQL type, DATETIMEOFFSET, to represent the date and time with time zone information. There would be no specific C type in ODBC that corresponded to DATETIMEOFFSET. An application would have to bind DATETIMEOFFSET as SQL_C_BINARY and cast it to a user-defined data type. Beginning in ODBC 3.8 with C data type extensibility, a driver can define a new corresponding C type. For example, for the new SQL type DATETIMEOFFSET, the driver can define a new corresponding C type such as SQL_C_DATETIMEOFFSET. Then, an application can bind the new SQL type as a driver-specific C type.

A C data type is defined in the driver as follows:

The ODBC compliance level for an application, ODBC driver, and Driver Manager is 3.8 (or higher).

So we would have to re-target our ODBC compliance level for our ODBC driver and then third-party ODBC managers have to implement ODBC 3.8, in order to make such a change fully work.

I've found that Entity Framework, at least, happily maps TIMESTAMP WITH TIME ZONE to System.DateTimeOffset.

This is a string to native type mapping that happens inside the provider. It's still returned as a string back from the database underneath the covers.


Is there a specific issue are you encountering due to this issue? Most of our customers are able to use the TIMESTAMP WITH TIME ZONE type directly as a string.

permanent link

answered 30 May '13, 10:03

Jeff%20Albion's gravatar image

Jeff Albion
10.7k171174
accept rate: 24%

edited 30 May '13, 10:07

2

Thank you for the excellent, detailed, informative answer.

As part of upgrading from SA 11.0.1 to SA 16, we're planning to convert some of our TIMESTAMPs to TIMESTAMP WITH TIME ZONEs. Among other things, I'm looking into how that affects our C++ ODBC code, which currently uses TIMESTAMP_STRUCT. With your guidance, I'll now plan for the changed fields to bind strings. You've saved me a lot of research and testing. THANK YOU!

(By the way, I'm glad that you deal with the internal intricacies of ODBC drivers, so that we don't have to.)

(30 May '13, 14:01) Dan Konigsbach
Replies hidden
Comment Text Removed
1

"I'm glad that you deal with the internal intricacies of ODBC drivers, so that we don't have to." - amen!

(30 May '13, 16:03) Breck Carter
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×124
×21
×14
×5

question asked: 28 May '13, 21:05

question was seen: 2,346 times

last updated: 30 May '13, 16:03