Previously on this series, we’d used the Erlang :odbc application to establish a DBConnection connection to Microsoft SQL Server. Unfortunately, the way that DBConnection callbacks work conflicts with the :odbc restriction of only being called from the process where the connection was established or its children.

We need a process that holds the connection to :odbc and passes messages on to it. Fortunately, Elixir makes the construction of processes really easy with GenServer.

Our GenServer

We’ll build a GenServer that wraps the three :odbc calls we’ll need to start with, connect/2, disconnect/1, and sql_query/2. Connection and disconnection will be mapped to GenServer.init/1 and terminate/2 respectively while sql_query/2 will be our sole handle_call/3:

defmodule Mssqlex.ODBC do
  use GenServer
  
  def start_link(conn_str, opts) do
    GenServer.start_link(__MODULE__, {conn_str, opts})
  end
  
  def stop(pid) do
    GenServer.stop(pid, :normal)
  end
  
  def query(pid, statement) do
    GenServer.call(pid, {:query, statement})
  end
  
  # Callbacks
  
  def init({conn_str, opts}) do
    case :odbc.connect(to_charlist(conn_str), opts) do
      {:ok, odbc_pid} -> {:ok, odbc_pid}
      {:error, reason} -> {:stop, reason} # init should return :stop on errors
    end
  end
  
  def terminate(_, odbc_pid) do
    :odbc.disconnect(odbc_pid)
  end
  
  def handle_call({:query, statement}, _, odbc_pid) do
    {:reply, :odbc.sql_query(odbc_pid, to_charlist(statement)), odbc_pid}
  end
end

That code should be pretty self-explanatory, although a glance through the GenServer docs is never amiss. The server starts up an :odbc process and maintains a reference to it in state. This way, no matter where Mssqlex.ODBC.query/2 is called from, :odbc.sql_query/2 will get called from the same process (with ref pid) that established the connection.

We’ve also taken advantage of the fact that the calls to Erlang functions are all in one place to make the conversion from Elixir to Erlang strings there so they don’t clutter up our main logic.

The implementation of DBConnection that we began writing in the previous post can then be expanded to become:

defmodule Mssqlex.Protocol do
  use DBConnection
  
  alias Mssqlex.ODBC
  
  def connect(opts) do
    conn_opts = opts
    |> Keyword.put_new(:tuple_row, :off)
    |> Keyword.put_new(:extended_errors, :on)

    conn_str = [
      {"DRIVER", opts[:odbc_driver] || "{ODBC Driver 13 for SQL Server}"},
      {"SERVER", opts[:hostname] || System.get_env("MSSQL_HST") || "localhost"},
      {"DATABASE", opts[:database] || System.get_env("MSSQL_DB")},
      {"UID", opts[:username] || System.get_env("MSSQL_UID")},
      {"PWD", opts[:password] || System.get_env("MSSQL_PWD")}
    ]
    |> Enum.map_join(fn {key, value} ->
      "#{key}=#{value};" end)

    ODBC.start_link(conn_str, conn_opts)
  end
  
  def disconnect(_, state) do
    case ODBC.disconnect(state) do
      :ok -> :ok
      {:error, reason} -> {:error, reason, state}
    end
  end
  
  # State checking and checkout to connection process
  
  def checkout(state) do
    {:ok, state}
  end

  def checkin(state) do
    {:ok, state}
  end
  
  # Query preparation
  
  def handle_prepare(query, opts, state) do
    {:ok, query, state}
  end
  
  # Finishing a prepared query
  
  def handle_close(_query, _opts, state) do
    {:ok, nil, state}
  end
  
  # Actually execute the query
  
  def handle_execute(query, _params, _opts, state) do
    ODBC.query(state, query)
  end
end

We’ve introduced a number of new callbacks, which are required to be able to execute a query. Let’s run through them:

• checkout and checkin are there to handle the process of copying state to and from the process that calls our implementation and our implementation’s process itself. If your state object needs to be different in each of those execution environments, you can use these callbacks to implement the necessary transformations. Our state object is for now just a reference to the ODBC process, so no transformations are needed.
• handle_prepare and handle_close handle the process of preparing a query for later executions and releasing that prepared query respectively. This would be useful if the process of translating a query into a form readable by the database wasparticularly expensive or if performance could be gained by caching queries for execution in the database. There’s no gain there for our purposes, so we leave them as no-ops.
• handle_execute is the callback that actually runs the query when the user calls an execution DBConnection function (execute, prepare_execute, or their raising (!) versions). Its return value becomes the return value of those functions.

Let’s try it out:

iex> {:ok, conn} = DBConnection.start_link(Mssqlex.Protocol, [])
iex> DBConnection.execute!(conn, "Select 12345", [])
(Protocol.UndefinedError) protocol DBConnection.Query not implemented for "Select 12345"

Aw!

OK, we’ve overlooked a few things:

• DBConnection requires that queries be structs implementing the DBConnection.Query protocol.
• handle_execute should return an {:ok, result} tuple, but :odbc.sql_query somewhat unhelpfully returns either {:updated, number_of_rows_updated} or {:selected, column_names, result}.
• We really need to support parametrised queries via :odbc.param_query unless we expect users of the adapter to interpolate values into their query strings (which is a bad idea.

But for the sake of checking that our little GenServer architecture actually works we can directly call inner functions for a completely artificial test:

iex> {:ok, conn} = Mssqlex.Protocol.connect([])
iex> Mssqlex.ODBC.query(conn, "Select 12345")
{:selected, [[]], [[12345]]}

It lives! (Note the :odbc result structure I mentioned earlier).

In the next post, we’ll work through those three items to produce an API that actually works for real-world parametrised queries.