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
  • About The Author

  • This is the definitive blog for the musings of Brian Smith. I've been a programmer / sys admin for most of my life. I don't have much to say, I work, I enjoy my family, and every once in awhile I'll dump a little something I want to keep track of here.

    Currently I am the CTO of DNS.com where we provide geolocation based authoritative DNS services for the masses. I also have been working on drafts that are submitted to the IETF to the dnsext working group.

    In the past I have been involved with organizing various user groups, including an off and on again 2600 group. Also I have been involved with the development of Seclude, an open source secure instant messaging platform as well as Sliker, a project that later developed into plasma for the KDE desktop environment.

    Additionally I am an avid home brewer making my own beer. Everything else should be below or to the left.

  • 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.

:= RSS =: