search

findmypast-oss / mssql_ecto

Ecto adapter for Mssqlex
Apache License 2.0
49 stars 20 forks source link

Support for uniqueidentifier / UUIDs #20

Open timbuchwaldt opened 7 years ago

timbuchwaldt commented 7 years ago

I would love to see support for the native MS-SQL uniqueidentifier datatype.

Expected Behavior

Mapping :binary_id to uniqueidentifier

Current Behavior

Currently data returned with a uniqueidentifier datatype raises an error: 

** (Mssqlex.Error) Column type not supported | ODBC_CODE  | SQL_SERVER_CODE 0

Possible Solution

Something like https://github.com/elixir-ecto/postgrex/blob/b1c0d7e4fd517b56fbd3bcdb7f2ee21e107c2665/lib/postgrex/extensions/uuid.ex##

Context

Using UUID is pretty standard behaviour I would assume.

jbachhardie commented 7 years ago

Unfortunately supporting additional column types is quite challenging since it would require changes to the driver being used, either in the form of updates to Erlang's ODBC driver or the development of a new driver for Elixir/Erlang.

I heard @toddharding might be working on patching Erlang ODBC in this way but there's no ETA since it's work beyond what we can justify doing on paid company time. Do contact him if you know C and/or the OTP codebase and want to help get it done.

cdesch commented 6 years ago

The uniqueidentifer is a bit of a show stopper. @toddharding did the Erlang ODBC ever get this update? I don't see anything 2.12.1 release notes. I'm not sure how I would go about patching this in the ODBC either.

reference issue #31

shdblowers commented 6 years ago

I had a go at doing a similar issue to this myself a few months ago, around adding a datetimeoffset column https://github.com/findmypast-oss/mssqlex/issues/7.

That code base was just far too complex for me to understand, reason about and then modify it correctly. It was also very difficult to get it set-up correctly on my machine.

I ended up losing motivating and abandoning it.

Anyone who has the motivation to give it a go, there are a few useful resources in that issue and the bug board it links to has some helpful people on it.

cdesch commented 6 years ago

Not having some of these native data types fields supported really hurts any elixir/erlang + MS SQL Server development or applications. I would hope that some of the folks from Microsoft would help out a little for their product. Since I can't get it to work with MS SQL Server, I'll migrate the data to PostgreSQL and work with it from there. As a Long shot, maybe some of the folks at MSSQL-docker can refer us to someone that can help @LuisBosquez

noizu commented 4 years ago

@cdesch It's easy enough to patch the odbc to handle guid types (https://github.com/noizu/otp) by basically treating them as another char type column. For example with the above quick and dirty fork I can work with MSSQL if changing binary_id columns to :string or modifying the ecto.uuid.load method to accept the sql_guid string in addition to raw binary input format.

I need this functionality as well so I'll be trying to get something accepted upstream but it while take a bit of time to line up all the details.