Erlang ODBC

Continuing my Erlang journey, I decided to try with Erlang ODBC by porting one of my toy applications. Perhaps not a wise choice since working with database (especially relational database) is not a strength of Erlang, at least for now.

After almost 10 years and being spoiled by myriad of Object-Relational Mapping (ORM) frameworks, using ODBC again feels like a giant leap backward - 3 generations back to be exact (ADO.Net, ADO, ODBC). So the overall development experience is not that great. This is not to say that Erlang is not a great language, but Erlang as a platform is quite narrow in scope (this makes sense considering Erlang's strong telco heritage).

So here is my porting exercise. Note that the code below is my feeble attempt at using/learning Erlang by reading the man-pages alone, so if you have suggestions to improve it, please drop a comment. Also, it is not robust since it does not handle error conditions.

Background

The application (or component) that I am working on is a Data Access Objects (DAO) package which I originally developed in Java using various ORM frameworks and then C# using various 3rd-party ORM and ADO.NET frameworks. The data model is quite simple, consisting 3 entities: AddressFieldType (AFT for short), AddressField (AF for short) and Address. The relationships among them are as following:

• AFT - AF has a one-to-many relationship
• Address - AF has a many-to-many relationship

The partial entity relationship diagram (ERD) can be found in my previous blog.

Here, I attempt to implement some of the interfaces from the DAO package using Erlang ODBC.

The Design

Since Erlang is not an object oriented language, I cannot call my module DAO, so I call it DAL (Data Access Layer) instead borrowing from Microsoft terminology. I have two simple modules:

1. address_dal: implementation of some of the interfaces retrieving records from the Address database (using MySQL 5.x with ODBC driver 5.1.5 for win32 downloaded from MySQL).
2. orm: ORM here means ODBC-Record Mapping. There are two types of functions in this module: those that provide ODBC template functions; and those that convert ODBC returned data into Erlang Records defined in address.hrl file.

Records

I want to work with Erlang records rather than tuples returned by ODBC calls. So I created these records to represent the domain model. From the address.hrl file:

-record(address_field_type, {id,
 name,
 locale_country,
 default_value,
 hierarchy_order,
 display_order,
 validation_rules,
 suffix} ).
 
-record(address_field, {id,
 value,
 address_field_type_id,
 location_code}).
 
-record(address, {id,
 status,
 location_code,
 address_fields = [#address_field{}]}).

Notice the address record has a list of address_field records, representing half of the many-to-many relationship.

ODBC Template

I need to surround my SQL queries with database connection and disconnection so that these boiler plate codes do not get scattered everywhere in my business logic. Also, if the SQL queries results in error, Erlang breaks the ODBC connection (I am not sure if this is Erlang's fault or ODBC's). So I have to reconnect before my next SQL query anyway.

So in my orm.erl I have these ODBC template/boiler-plate functions:

-define(CONNECTION_STRING, "DSN=Address-MySQL;UID=root").

connect() ->
 case proplists:is_defined(odbc,application:which_applications()) of
  false ->
   application:start(odbc); % pre R12 way of starting odbc;
  _Else ->
   false % do nothing
 end,
 odbc:connect(?CONNECTION_STRING, [
  {auto_commit, off}, 
  {scrollable_cursor, off} 
 ]).
 
sql_query(Query) ->
 {ok, Ref}=connect(),
 ResultTuple = odbc:sql_query(Ref, Query),
 odbc:disconnect(Ref),
 ResultTuple.
 
param_query(Query, Params) ->
 {ok, Ref}=connect(),
 ResultTuple = odbc:param_query(Ref, Query, Params),
 odbc:disconnect(Ref),
 ResultTuple.

Note that Erlang ODBC is based on ODBC v3 which supports connection pooling which has been turned on. So surrounding the SQL queries with connection and disconnection should not incur performance penalty (although I have not explicitly tested it).

One thing I hate about Erlang's odbc:connect() is that it does not give any comprehensive error reasons if the connection fails - it always says "No SQL-driver information available. Connection to database failed." Again, I am not sure if this is Erlang's or ODBC's fault.

Converting into Records

The ODBC query functions return the results as tuples or list of tuples. I want to convert them into recdords so that I can access the fields of the records more easily - e.g. #address.id.

I tried two approaches implementing the record constructor functions. The first one is more portable since it does not rely on the field position but uses column name as a clue to map to the record's fields. This is shown below from the orm.erl file mapping the AddressFieldType query results to their corresponding address_field_type record type:

construct_aft_records(ColNames, Rows) when erlang:is_list(ColNames) and (length(ColNames)>0)
 and erlang:is_list(Rows) ->
 if (length(Rows)>0) ->
  lists:foldl(fun(R, AftRecords) ->
    lists:append(AftRecords, [construct_aft_record(ColNames, R)])
   end,
   [], % initial value of AftRecords list
   Rows
  );
 true->
  []
 end.

   
construct_aft_record(ColNames, Row) when erlang:is_list(ColNames) and (length(ColNames)>0)
 and erlang:is_tuple(Row) and (size(Row) == length(ColNames)) ->
 Map=lists:foldl(fun(N, Map) ->
  lists:append(Map, 
   [{string:to_upper(lists:nth(N, ColNames)), 
   element(N,Row)}]
  )
  end,
  [], % initial value of Map is []
  lists:seq(1, length(ColNames))
 ),
 #address_field_type{
  id=extract_map_value(Map, "ADDRESSFIELDTYPEID"),
  name=extract_map_value(Map, "NAME"),
  locale_country=extract_map_value(Map, "LOCALECOUNTRY"),
  default_value=extract_map_value(Map, "DEFAULTVALUE"),
  hierarchy_order=extract_map_value(Map, "HIERARCHYORDER"),
  display_order=extract_map_value(Map, "DISPLAYORDER"),
  validation_rules=extract_map_value(Map, "VALIDATIONRULES"),
  suffix=extract_map_value(Map, "SUFFIX")
 }.

 
extract_map_value(Map, Key) ->
  element(2, element(2,lists:keysearch(Key, 1, Map))).

The second approach is lazier as it relies on the field position as specified in the SELECT SQL statement. This is shown below from the orm.erl file.

find_addresses(Query, Params) ->
 {ok, Ref}=connect(),
 {selected, _, Rows} = odbc:param_query(Ref, Query, Params),
 % now for each address Row fetch its addressField records
 AddressRecords = lists:foldl(fun(A, Records) ->
   AddressId=element(1,A),
   AddressStatus=element(2,A),
   AddressLocationCode=element(3,A),
   {selected, _, AfRows}=odbc:param_query(Ref,
    "SELECT af.addressFieldId, af.value, af.addressFieldTypeId,"
    "       af.locationCode, aft.hierarchyOrder"
    " FROM Address.addressField as af, Address.AddressFieldType as aft,"
         "      Address.address_addressField as aaf"
    " WHERE aaf.addressId=?"
      "   AND af.addressFieldId=aaf.addressFieldId"
      "   AND aft.addressFieldTypeId=af.addressFieldTypeId"
            " ORDER BY aft.hierarchyOrder",
            [{sql_integer, [AddressId]}]
           ),
           AfRecords = lists:foldl(fun(Af, AddressFieldRecords) ->
             AfRecord=#address_field{
              id=element(1, Af),
      value=element(2, Af),
      address_field_type_id=element(3, Af),
      location_code=element(4, Af)
             },
             lists:append(AddressFieldRecords, [AfRecord])
            end,
            [],
            AfRows
           ),
           AddressRecord= #address{
            id=AddressId,
    status=AddressStatus,
    location_code=AddressLocationCode,
    address_fields=AfRecords
   },
   lists:append(Records, [AddressRecord])
          end,
  [],
  Rows
 ),
 odbc:disconnect(Ref),
 AddressRecords.

Implementing the DAL Interfaces

Now that the boiler-plate code is done, I can implement the DAL interface methods, oops! I mean functions. Here is the address_dal.erl file.

%% Implementation of the Address Data Access Layer (DAL) interfaces.
-module(address_dal).
-export([find_aft/0, find_aft/1]).
-export([find_addresses_in_af/1, find_addresses_in_location/1]).
-include("address.hrl").
 
find_aft() ->
 {selected, ColNames, Rows} = orm:sql_query("SELECT * from Address.AddressFieldType"),
 orm:construct_aft_records(ColNames, Rows).
 
find_aft(LocaleCountry) ->
 {selected, ColNames, Rows} = orm:param_query("SELECT * from Address.AddressFieldType "
  "WHERE localeCountry=?", 
  [{{sql_varchar, 64}, [LocaleCountry]} ]
 ),
 orm:construct_aft_records(ColNames, Rows).


find_addresses_in_af(AddressFieldId) ->
 orm:find_addresses( 
  "SELECT a.addressID, a.status, a.locationCode"
  " FROM Address.Address as a, Address.Address_AddressField as aaf"
  " WHERE aaf.ADDRESSFIELDID = ?"
  " AND a.ADDRESSID=aaf.ADDRESSID",
  [{sql_integer, [AddressFieldId]}] 
 ).
 
% the input parameter LocationCode needs to be a string due to the ODBC 
% to Erlang datatype mapping - hence the test for is_list().
find_addresses_in_location(LocationCode) when is_list(LocationCode) ->
 orm:find_addresses("SELECT a.addressID, a.status, a.locationCode"
  " FROM Address.Address as a, Address.AddressField as af, Address.Address_AddressField as aaf"
  " WHERE af.LOCATIONCODE = ?"
  "   AND a.ADDRESSID=aaf.ADDRESSID"
         "   AND af.ADDRESSFIELDID=aaf.ADDRESSFIELDID",
  [{{sql_numeric, 32, 0}, [LocationCode]}] 
 ).

Notice that in the find_aft functions I can use 'SELECT *...' because the orm:construct_aft_records/2 function uses the column names to map to the address_field_type record; on the other hand, the find_addresses... functions have to use 'SELECT a.addressID, a.status, a.locationCode...' and in that strict order because the orm:find_addresses/2 function expects the columns to be in those positions.