MSSQL and Ecto3

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

Thanks for your reply.

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

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.

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

https://github.com/findmypast-oss/mssqlex/issues/22

to see if they need help / additional contributors.

@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

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.

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

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)

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)
• GitHub - tallakt/codepagex: Elixir string encoding conversion - like iconv but pure Elixir · GitHub 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.

Dunno if super related, but since you mention encoding issues, I used the custom type at this bottom of this issue: String with accents are converted to binary · Issue #63 · livehelpnow/tds_ecto · GitHub

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