Loopers97
MSSQL stored procedures
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.
Marked As Solved
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
Also Liked
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.
Popular in Questions
Other popular topics
Latest on Elixir Forum
Sponsor Spotlight
Producing high quality Elixir screencasts since 2017.
Categories:
Sub Categories:
Forums
Our Sponsors
Build Elixir applications with speed and confidence.
Supporting innovation across the BEAM ecosystem.
We build reliable cloud platforms for business-critical systems.
Catch errors, track performance, monitor hosts and more.
Practical resources that improve the lives of professional developers.
Develop your skills with books, videos, and courses.
Enabling companies to succeed by building software people love.
Courses that'll move you from confusion to "Aha, now I get it!"