process_not_owner_of_odbc_connection :ODBC connection

Question

Has anyone ran across this error when querying with erlang odbc:

{:error, :process_not_owner_of_odbc_connection}

I am writing a connection pool and the :ODBC.connect('conn string) is ran inside the genserver and the returned pid is put into the state... But when i get that PID back out of the gen server, I get the above error when trying to run an :odbc.param_query with that pid... How are we suppose to create a connection pool if we can use pids created in a genserver?

Anyone have any ideas?

GenServer:

defmodule ItemonboardingApi.ConnectionPoolWorker do
    use GenServer

    def start_link([]) do
      IO.puts "Starting Connection Pool"
      GenServer.start_link(__MODULE__, nil, [])
    end

    def init(_) do
        :odbc.start
      {ok, pid} = :odbc.connect('DSN=;UID=;PWD=', [])
      IO.inspect pid
      {:ok, pid}
    end

    def handle_call(:get_connection, _from, state) do
      IO.puts "Inspecting State:"
      IO.inspect state
      IO.puts("process #{inspect(self())} getting odbc connection")
      {:reply, state, state}
    end
  end

Calling Function:

def get_standards_like(standard) do
    :poolboy.transaction(
      :connection_pool,
      fn pid -> 
       connection = GenServer.call(pid, :get_connection) 
       IO.inspect connection

      val =
        String.upcase(standard)
        |> to_char_list

      # Call Stored Proc Statement
      spstmt = '{call invlibr.usp_vm_getStandards (?)}'

      case :odbc.param_query(connection, spstmt, [
             {{:sql_char, 50}, [val]}
           ])
           |> map_to_type(ItemonboardingCore.Standard) 
           do
        {:ok, mapped} ->
          {:ok, mapped}

        {:updated, affected_count} ->
          {:ok, affected_count}

        {:executed, o, a} ->
          {:ok, o, a}

        {:error, _} ->
          {:bad}
      end 
     end
    )
  end

I have confirmed that the pids in the genserver are infect the correct odbc pids and the GenServer.Call returns one of them.

****EDIT**** Here is what I did to fix the issue. I didnt know that the process that created the connection, had to be the process that runs the query. A slight change to my worker to pass the query into has fixed my issue. This is a rough first pass, a few things still need to be done to the worker.

defmodule ItemonboardingApi.ConnectionPoolWorker do
    use GenServer

    def start_link([]) do
      IO.puts "Starting Connection Pool"
      GenServer.start_link(__MODULE__, nil, [])
    end

    def init(_) do
      :odbc.start(:app)
      {ok, pid} = :odbc.connect('DSN=;UID=;PWD=', [])
      IO.inspect pid
      {:ok, pid}
    end

    def handle_call(:get_connection, _from, state) do
      {:reply, state, state}
    end

    def handle_call({:query, %{statement: statement, params: params}}, _from, state) do
      #TODO Check if pid is alive and start if needed.
      {:reply, :odbc.param_query(state, to_charlist(statement), params),  state}
    end
  end

Answer 1

That's going to be a problem, like Kevin pointed out you can't transfer connection ownership for odbc and other drivers like mysql/otp.

If you want to use a connection pool, take a look at this instead https://github.com/mysql-otp/mysql-otp-poolboy

Otherwise, you can use any pool but the process that executes the sql queries has to be the one that opened the connections.

---

example in Erlang

sql_query_priv(Conn, Conn_Params, {SQL, Params}) ->
    lager:debug("~p:sql_query_priv trying to execute query: ~p, with params: ~p, conn = ~p~n", [?MODULE, SQL, Params, Conn]),
    case Conn of 
    null -> try mysql:start_link(Conn_Params) of
            {ok, NewConn} ->  
                  lager:info("~p:sql_query_priv Connection to DB Restored", [?MODULE]),
                  try mysql:query(NewConn, SQL, Params) of
                    ok ->  {ok, NewConn, []};
                    {ok, _Columns, Results} -> {ok, NewConn, Results};
                    {error, Reason} ->  
                        lager:error("~p:sql_query_priv Connection To DB Failed, Reason: ~p~n", [?MODULE, {error, Reason}]),
                       exit(NewConn, normal),
                       {error, null, Reason}                            
                  catch 
                    Exception:Reason -> 
                        lager:error("~p:sql_query_priv Connection To DB Failed, Exception:~p Reason: ~p~n", [?MODULE, Exception, Reason]),
                        {error, null, {Exception, Reason}} 
                  end;
            {error, Reason} ->  
                   lager:error("~p:sql_query_priv Connection To DB Failed, Reason: ~p~n", [?MODULE, {error, Reason}]),
                   {error, Conn, Reason}                            
            catch 
                Exception:Reason -> 
                    lager:error("~p:sql_query_priv Connection To DB Failed, Exception:~p Reason: ~p~n", [?MODULE, Exception, Reason]),
                    {error, Conn, {Exception, Reason}} 
            end;
    Conn -> try mysql:query(Conn, SQL, Params) of
                ok ->  {ok, Conn, []};
                {ok, _Columns, Results} -> {ok, Conn, Results};                      
                {error, Reason} ->  
                        try exit(Conn, normal) of
                         _Any -> ok
                        catch
                         _E:_R -> ok
                        end,
                      lager:error("~p:sql_query_priv Connection To DB Failed, Reason: ~p~n", [?MODULE, {error, Reason}]),
                      {error, null, Reason}                            
            catch 
                Exception:Reason -> 
                        try exit(Conn, normal) of
                         _Any -> ok
                        catch
                         _E:_R -> ok
                        end,
                      lager:error("~p:sql_query_priv Connection To DB Failed, Exception:~p Reason: ~p~n", [?MODULE, Exception, Reason]),
                      {error, null, {Exception, Reason}} 
            end
    end. 

Answer 2

According to the relevant documentation, an odbc connection is private to the Genserver process that created the connection.

Opens a connection to the database. The connection is associated with the process that created it and can only be accessed through it. This function may spawn new processes to handle the connection. These processes will terminate if the process that created the connection dies or if you call disconnect/1.

You can compare this to ETS tables that can be set to private, except that in the case of ETS tables you can make them public. This is not possible with odbc connections, as such you should move your code inside the Genserver