This took me a while to figure out so I’m hoping that by posting this, it’ll save others some work. I’ve got a legacy system that I’m working with; one of the initiatives I’ve been pursuing in my spare time is to build a JSON API to wrap this legacy database so that other developers within the firm can get at the data with JSON calls.  It’s not a high-priority to get this done which makes it a great candidate for exactly this sort of thing because I can work on it at my own pace and I don’t have to cut too many corners. This legacy system was pretty well-designed (kudos to the original developers) and one thing they did was to build stored procs to get data from the DB.  So as I’m building out this new API, I also need to call those stored procs; this way I don’t inadvertently miss any logic that is built into the SP’s. The issue I had was trying to figure out how to call a stored proc from Elixir.  I can find examples of calling a parameterized query from Erlang and I can find a few places where people have documented using parameterized queries to call a stored proc with ODBC but I couldn’t quite get the two ideas working together. I made up a dummy SP because I was trying to avoid additional needless complication while I’m trying to figure this out.  So here’s my dummy SP:

CREATE PROCEDURE [dbo].[usp_CheckName] @NameToCheck VARCHAR(100) AS
SELECT DomainName FROM tnm_validdomains WHERE DomainName = @NameToCheck

Anyone familiar with stored procs can see this is about as simple as a stored proc could be.

So this is where it gets a little bit interesting.  First the ODBC code is an Erlang library.  As someone else pointed out, you have to be careful about the difference between Erlang’s list of chars vs. Elixir’s strings–this comes up in this code.

#Test calling SP from Elixir

:odbc.start()
{:ok,_ref} = :odbc.connect('Driver={SQL Server};Server=(local);Database=TNM;Trusted_Connection=True;',[])
spstmt = '{CALL usp_CheckName (?)}' #Call Stored Proc Statement
testval = "GETBACKSTITCH.COM"
{:selected,field_list,_result} = :odbc.param_query(_ref,spstmt,[{{:sql_varchar,100}, [to_char_list(testval)]}])
IO.puts("Fields returned: #{field_list}")
IO.inspect(_result) #Note; in this case _result is a binary not a string.
:odbc.stop()

Couple of things I’d like to call out here:

1.) Note well the line I’ve commented as the “Call Stored Proc Statement” line. I don’t believe I found any reference to passing a “CALL” statement to a param_query in Erlang–anywhere.  Of course my GoogleFu may have failed me.  But the real trick to be aware of here is the need for the literal curly braces at the start and end of the string.  I spent hours with spstmt = ‘CALL usp_CheckName(?)}’ and the error seemed to indicate that the problem was the value that I was passing at the parameter.  I finally spotted the problem by accident in someone’s PHP code sample of all places.

At any rate, that same trick about “{CALL (?)}” is needed in Erlang and Elixir too.

2.) That last parameter to the param_query function is a bit interesting.  It’s a list of tuples of tuples and lists.  Parsing it a bit more finely:

[                                             #List
  {                                           # Of Tuples
    {:sql_varchar,100} ,                      # Of an ODBC Type Tuple
    [to_char_list(testval)]                   # And a list of the values 
                                              # to substitute for the parameters.
  } 
]

I hope this helps anyone else who’s trying to figure out how to code a call to a stored proc from either Erlang or Elixir.