How to select data via ODBC from Elixir?

Question

I cannot find any documentation or examples for this. So far, I can connect successfully, though, I do not know the syntax for selecting and returning data.

:odbc.start()
{:ok, conn} = :odbc.connect('DSN=mydsn;UID=myuid;PWD=mypwd', [])
:odbc.execute_stmt(conn, 'select count(*) from mytable')
:odbc.stop()

** (UndefinedFunctionError) undefined function: :odbc.execute_stmt/2 (odbc) :odbc.execute_stmt(#PID<0.85.0>, 'select count(*) from mytable')

---

Update 10/6/2015

I found how to do this. I believe I couldn't find any documentation on this for Elixir, because Elixir simply does a pass-through to the Erlang library.

In any case it's sql_query, or select_count:

{:selected, colNames, rows} = :odbc.sql_query(conn, 'select count(*) from mytable')

{:selected, ['COUNT'], [{'182'}]}

{:ok, count} = :odbc.select_count(conn, 'select * from mytable')

{:ok, 182}

---

Update 10/7/2015

Here's my final code, which I believe is more idiomatic:

:odbc.start()
case :odbc.connect('DSN=mydsn;UID=#{System.get_env("MY_UID")};PWD=#{System.get_env("MY_PASSWORD")}', []) do
  {:ok, conn} ->
    case :odbc.sql_query(conn, 'select count(*) from mytable') do
      {:selected, _colNames, [{count}]} -> IO.puts "count = #{count}"
      {:error, err} -> {:error, err}
    end
  {:error, err} -> {:error, err} 
end
:odbc.stop()

Answer 1

There are a couple of things here:

1.) I think you want :odbc.sql_query/2. I built a fairly substantial application last year using ODBC to communicate with SQLServer and I used sql_query pretty much everywhere. I'd share the source but I cannot.

2.) I can't find any documentation on execute_stmt in the official Erlang ODBC docs (v2.11.1) hence I think it may be deprecated. I did find an example that uses execute_stmt but the arity was four not two. And it looks like the version of ODBC involved (judging by the link itself) is rather old--pre v1.0.

3.) In Elixir we tend to shy away from rebuilding things which are already built in Erlang unless there's a compelling need to do so. Since ODBC is already present in Erlang and it works pretty well, I'd guess the general guidance is call the Erlang lib from Elixir. Elixir was designed to make it easy to call Erlang code.