Support DATETIMEOFFSET

[ploeh]

This seems like a great library, but some version (I forget which one) of SQL Server introduced the DATETIMEOFFSET column time, and if one tries to query a table with such a column, an exception is thrown.

Steps to reproduce

Create a tabel as follows:

CREATE TABLE [dbo].[DateTimeOffsetRepro] ([Date] DATETIMEOFFSET NOT NULL)

Insert some data into it; e.g.

INSERT INTO DateTimeOffsetRepro VALUES ('2019-05-08T18:24:41-06:00')

In GHCi, create a connection conn to the database, and attempt to use query to read the data:

Prelude Database.ODBC.SQLServer> query conn "SELECT * FROM DateTimeOffsetRepro"
*** Exception: UnknownDataType "getData" (-155)

Expected result

The data from the database is printed in GHCi.

Actual result

An exception is thrown, as shown above.

[ploeh]

I poked around in the code base a little, to see if I could offer a pull request with a fix, but it's 20 years ago I did any C++ programming, and I've no experience with ODBC, so it was a bit too hard for me. (I do realise that this isn't written in C++, but there's too much stuff about interop with C++ that I don't understand. I can, on the other hand, write Haskell code, FWIW.)

If you've oceans of patience, I'd be up for attempting a pull request, but I'm going to need a lot of hand-holding if I should make this work.

Alternatively, I'd also be happy if anyone else creates a fix.

[chrisdone]

It's just C code, not C++. But I think to implement this you don't have to touch any C. I think it'd probably be easier if I work on this, but I'm not likely to have time for a while. But here're some links relevant to the problem:

https://docs.microsoft.com/en-us/sql/odbc/reference/develop-app/c-data-types-in-odbc?view=sql-server-2017

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.

If you want to take a crack at it, you could start in here, it seems that you have to go via binary; so using getBinaryData, as done here.

In this section you'd add a constant for -155 as from your output above, and call it e.g. sql_datetimeoffset.

Once you've got a BinaryValue (Binary bs) you'll have a 10-byte value that you can inspect. Details here. You can get access to the individual bytes via regular bytestring operations. I don't know how the bytes are structured, where the date, time or timezone are. But I guess you can find it somewhere.

You can reasonably add a constructor to the Value type such as ZonedTimeValue !ZonedTime which consists of a LocalTime and a TimeZone, which is what I understand datetimeoffset to correspond to.

[ploeh]

I'm not likely to have time for a while

I completely understand. If I try to tackle this myself, I still think I'm going to need a bit more help than the above fine introduction, so I'm going to ask some more questions. Some of these are, I believe, questions that will need to be answered regardless of who implements the feature, but some of them will be the result of my ignorance. If you don't have time to answer, I completely understand.

You can reasonably add a constructor to the Value type such as ZonedTimeValue !ZonedTime which consists of a LocalTime and a TimeZone, which is what I understand datetimeoffset to correspond to.

Yes. One issue, however, is that ZonedTime doesn't have an Eq instance. Value, on the other hand, does have a (derived) Eq instance, but that's not going to work if we add a ZonedTimeValue data constructor. How would you prefer that we handle that?

One option is to wrap the ZonedTime value in an newtype with an Eq instance. Another is to explicitly write the Eq instance for Value, instead of deriving it.

I think we're going to have the same issue with Ord.

I'd favour using a newtype, but I wanted to hear your preference first.

[chrisdone]

Yes. One issue, however, is that ZonedTime doesn't have an Eq instance. Value, on the other hand, does have a (derived) Eq instance, but that's not going to work if we add a ZonedTimeValue data constructor. How would you prefer that we handle that?

Good catch. I read up and it's intentional to avoid people thinking that 12:00UTC==13:00UTC-1 for example. That's fine. Makes sense to avoid people making mistakes.

How about we decompose it into the two fields as ZonedTimeValue !LocalTime !TimeZone, so that we have Ord and Eq again. Then, like the Datetime2 and Smalldatetime types, we can have a Datetimeoffset data type that contains a ZonedTime but no Eq/Ord instance? 🤔

[chrisdone]

Explicitly writing the Eq instance is also attractive...

[ploeh]

I'm making some progress, but I'm having trouble figuring out how to extract the various parts from the data. I've been looking at the code that handles SQL_SS_TIME2, and that one uses the functions odbc_TIME_STRUCT_hour, odbc_TIME_STRUCT_minute, and odbc_TIME_STRUCT_second.

These seem to call some external C APIs defined in odbc.c. That file, again, seems to import some header files that, I suppose, are assumed to 'just be available'. (I've never done any C programming, so how these things work aren't entirely clear to me.)

I've found this article that discusses both SQL_SS_TIME2 and SQL_SS_TIMESTAMPOFFSET, but it seems as though the code that handles SQL_SS_TIME2 'cheats' and uses odbc_TIME_STRUCT_hour et al, which seems to be using a data structure called TIME_STRUCT. I've tried using some of those functions, like odbc_TIME_STRUCT_hour, but they don't seem to work for SQL_SS_TIMESTAMPOFFSET.

Is there some header file I can import into to odbc.c to enable access to the constituent values of the datePtr, or am I barking up the wrong tree?

[ploeh]

FYI, it seems I'm making some progress 😅