Getting Around ODBC slow connection pooling in Erlang Mon, 10 Aug 2009 10:32:03 -0400

If your like me you use SQL quite a bit, or least for some form of data source. One of the most portable methods for SQL interfacing is to use some form of abstraction, this adds portability as well as some support (sometimes). In this case I'm using Erlang to query a bunch of data out of a database and I want to do this very very quickly and efficiently

I've read only that ODBC v3 is supposed to support connection pooling by default, however on my PC (ubuntu hardy) is slow as heck and has a long latency time for initializing connections. However I was able to come up with a way around this by creating a process that does nothing but SQL functions. The below example should be all you need to start using this yourself.


-module(fetcher).
-author("Brian Smith").
-export([start, loop0/0, loop0/1, query/1]).

-define(DSN, "dsn=myodbc3").

start() ->
    register(fetcher_pid, spawn(?MODULE, loop0, [])).

loop0() ->
    {ok, DbConn} = odbc:connect(?DSN, []),
    loop0(DbConn).
loop0(DbConn) ->
    receive
        {sql, From, SqlStmt} ->
             From ! odbc:sql_query(DbConn, SqlStmt),
             loop0(DbConn);
        quit ->
             odbc:disconnect(DbConn),
             quit
    end.

query(SqlStmt) ->
   fetcher_pid ! {sql, self(), SqlStmt},
   receive
        _Results ->
                 _Results
   end.

To use you just need to run fetcher:start().. I'm still trying to figure out how to block the pid for receiving SQL queries until odbc:connect() is complete, so any ideas on this is appreciated.


Comments:

  texHaiste Thu, 14 Jan 2010 19:43:52 -0500
unsteady answers i like it

Post Comment

  • Disclaimer
  • The ideas and opinions expressed here are mine.
  • I'm a Linux and BSD user, and lean heavily toward the use of OSS vs certain other commercial solutions.

View the Ninja's profile on LinkedIn

:= RSS =: