MSSQL and Ecto3

Questions / Help
,
1

I want to write an elixir application performing some ETL work on an SQL Server database (I can see how to do it with ecto, and other ways I can see doing it are harder).

I have found mssql_ecto which does not seem to work with ecto 3, and tds_ecto also seems to not be up to date (I am aware of a fork of ecto_sql but could not glean much from it). Are there any other things I can try to make it work that I have not seen yet?

2 Likes
2

Welcome to the forum!

I’m relatively new to Elixir as well, but for what I’ve seen Ecto adapters for commercial DBs are not updated as often as open source ones. It seems to be just a matter of the number of people using and maintaining those adapters.

You already found what is available for MS SQL. If you want/have to keep working with that DB, you’ll have to use Ecto 2.

Regards

1 Like
3

Thanks for your reply.

I already thought that was going to be the case, but it never hurts to ask whether I missed something.

2 Likes
4

There are efforts which seem to be almost completed to bring MySQL up to speed. But not sure the core team would tackle MSSQL afterwards (or ever).

I personally think it could help Elixir’s adoption a lot.

3 Likes
5

You might try reaching out to the author of this issue:

to see if they need help / additional contributors.

3 Likes
6

@lindem,

I am developing a Phoenix app using a legacy mssql database with tds_ecto and ecto 2.

Since I have plans to migrate to postgres I switch my app from tds_ecto + ecto 2 to postgrex + ecto 3 several times a week.

I have not found any unsolvable issues yet and everything seems to work fine.

Please let me know if I can help with a particular issue.

Regards

1 Like
7

Thanks for all the replies.

@dimitarvp The company I have intended to do this for firmly believes in MSSQL, so those places exist.

@al2o3cr I am not sure whether the original project team will keep maintaining it, they have stated they don’t do much elixir anymore here, casting doubts on that project as a whole. That was the reason I kept looking.

@hectorsq I think that is what I am going to explore next. Thanks for telling me that that works for you.

8

The tds_ecto library is close to being ready for Ecto 3.0. A summary of the progress can be found here

7 Likes
9

Stoked I watch progress on the Ecto 3 stuff! Though honestly less because of Ecto 3 and more for an adapter with more consistent performance. Tds.Ecto has been great, but every now and then queries are crazy long (similar to findings in this article https://medium.com/asolvi/going-functional-part-iii-using-elixir-and-ecto-with-azure-sql-4e66b677f998)

10

The project is done now.

In case anybody finds this thread – I have tried some things, but the SQL Server database (running on Windows) itself was problematic to work with.

I tried/used

  • mssqlex with and without mssql_ecto
  • tds with and without tds_ecto
  • Ecto in version 2
  • the :unicode.characters_to_binary and :unicode.characters_to_list calls (strings are character lists in erlang)
  • https://github.com/tallakt/codepagex in an attempt to convert the data to utf-8 (I enabled the CP1252 encoding in VENDORS/MICSFT/WINDOWS for this)

I was able to query the database with all adapters and drivers. The encoding issue surprisingly took most of the time. Most of the time I got recognizable text intermixed with garbage. The data could be queried correctly with raw SQL in SSMS (the SQL server GUI). There is another account of this difficulty with no solution here.

I suspect that at least one layer in the chain lied about its encoding.

I was not granted much time to try this. The database was not reachable off-premise, either.

Be that as it may, we ended up not using elixir.

Again, Thanks for everybody’s help.

2 Likes
11

Dunno if super related, but since you mention encoding issues, I used the custom type at this bottom of this issue: https://github.com/livehelpnow/tds_ecto/issues/63

and its been working well in pulling what td_ecto returns as binary data if the varchar field returns any characters. Just use the custom type and it seems to decode okay

field :ProductDescription, EfAPI.Types.VarChar
1 Like
12

I have always worked with windows and SQL Server + Elixir in production for clients.

I just use builtin Erlang ODBC.

Never need to touch ecto.

2 Likes
13

Another thing to consider is that I don’t think the tds package support Windows Authentication, which is a relevant factor for enterprise applications.

1 Like