MSSQL stored procedures

Loopers97

Still really new to elixir, but I’m trying to get some experience with it. I’m working on trying to hook a new api up to an existing legacy mssql db using sso, that’s filled with stored procedures. I’m currently using ecto 3.4.6 and TDS 2.0. I’m able to pattern match a pid and start link, and query the db in iex.

I’m not quite sure what the code in the module is going to look like, and figured I’d ask for help from the community.

Do I have to do the
{:ok, pid} = Tds.start_link(“sso vars”)
Tds.query(pid, “[customer].gt_customer #{id}”, )
Everytime I want to get something out of the db?

Does Ecto.Repo not do any of start link for me? Should I even be using Repo, if all my interactions with the data store are through stored procs?

Are there any examples anywhere of table type parameters to Tds.query()?

I’ve scoured the internet and not found much guidance, but I doubt I’m the only person that has had to deal with this. Is there some resources somewhere that’d help? I own ecto and absinthe graphql books. Their material primarily focuses on built in adapters.

4 comments

#ecto #tds

1 1421 4

2020-10-09 20:58:40 UTC

First 4 of 4 Posts

Loopers97

For future people like me. I know this seems ridiculous, it felt ridiculous writing it. I welcome any advice anyone has to clean this up.

Put the start link in repo
{:ok, pid} = Tds.start_link([db connection string vars])
pid

Account module
{:ok, res} = Repo.query(“[schema].[sp_name] #{id}”)
res
|> result_to_maps #produce a list of maps
|> Enum.map(fn r → Repo.load(MODULE, r) end) load list of maps into repo

def result_to_maps(%Tds.Result{columns: col_numbers, rows: rows}) do
Enum.map(rows, fn row → row_to_map(col_numbers, row) end)
end

def row_to_map(col_numbers, values) do
Stream.zip(col_numbers, values)
|> Enum.into(, & &1)
end

Also I used embedded_schema because I don’t have access to the table and they’re not snake_casing because I ask them nicely.

embedded_schema do
field :first_name, :string, source: :FirstName
end

Post #1

chouzar

My approach for doing MSSQL stored procdure queries was to skip Ecto and use the Tds driver directly.

I would load the SQL files from the priv directory and pass parameters to it by transforming a Map or KeywordList into the required format and appending to the file. So far has worked ok

I’m not an expert on Ecto.Repo but your approach looks solid (not sure what Repo.load does); libraries like yesql or ayesql might also help you but haven’t tried them for stored procedures.

As for process startup it looks quite fine; not sure if you already know about Supervision Trees or Applications but it is also one of the ways to start long-running systems in erlang-otp.

Post #2

Loopers97

Thanks for the advice. I’ll try to reimplement using the libraries you suggested. I was hoping to get use out of Ecto.Changeset, but… you might be right to just skip over Ecto entirely.

Post #3

chouzar

Hi @Loopers97 sorry for taking so long.

The suggested libraries are very cool, but keep in mind that your approach is totally ok.

Changesets will help you with casting, data validation and readable errors; but if you only want to validate passed params to the stored procedure you could give a fair shot to the Norm library.

Post #4

— All posts loaded —