Connect to odbc data source from erlang

Today we are going to talk about how to connect to an odbc data source from Erlang.

Odbc is a Microsoft standard for connecting to a relational database.By using odbc you can have access to different databases example mssql,oracle,mysql,excel,dbase,Access as well as some others. The design of odbc aimed to make it independent of the database management system.

ODBC accomplishes DBMS independence by using an ODBC driver as a translation layer between the application and the DBMS. The application uses ODBC functions through an ODBC driver manager with which it is linked, and the driver passes the query to the DBMS.

We are now going to learn how to make erlang and odbc talk together like a happy couple :).

Lets hit the road!!.

Setups needed

I am using an Ubuntu Linux environment specifically trusty on an amd64 box so this tutorial is geared towards that platform.

The general things listed below to check are however quite similar for other platforms (windows,Solaris,UNIX,etc..)

• Install and setup driver for the database
• Setup environmental variables,paths,configurations
• Use erlang odbc application to talk to db !!

Install and setup driver for the database

You first need to make sure you have the header files which are necessary for compiling your driver. They are

• sql.h
• sqlext.h
• sqltypes.h

Ubuntu by default has the odbc library files in the unixodbc package but not the header file required for compilation,development etc…

You can install both header files plus library files using the below bash command.You will need sudo rights to do the below

sudo apt-get update
sudo apt-get install unixodbc unixodbc-dev

After this you can now install the odbc driver of your choice. Two popular ones are iodbc and freetds.

Freetds in particular enables you to connect to sybase and mssql databases.

We will be using freetds in this tutorial .

We will now install freedtds,freetds drivers and freetds command line utilities with the below command

sudo apt-get install freetds-dev freetds-bin tdsodbc

After the installation we are now ready to configure freedtds for use as an odbc data source.

Setup environmental variables,paths,configurations

Open /etc/odbcinst.ini using an editor of your choice.

You now have to create an entry for the driver you just installed like below.

##details for freetds driver
[freetds_driver]
Description = odbc connection driver
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
UsageCount = 1

Depending on your platform architecture (x86,x84,etc…) the driver and setup path may change . They will most likely look like with /usr/lib/x86|x84|amd64|etc../odbc/...

You next have to create an entry for your database you want to connect to in the freetds configuration file Open /etc/freetds/freetds.conf using an editor of your choice. There are examples in the file which will direct you on the format of the configuration. You now create an entry like below for your database at the bottom of the file.

# Details for database server
[mssql_server]
        host = 192.168.0.5
        port = 1433
        tds version = 4.2

Last but not the least you now create a dns entry that applications can now use as a data source name in your odbc.ini file Open /etc/odbc.ini using an editor of your choice. Add an entry for your database(my_db) with freedts as the driver with a specific server name

##details for odbc dns entry
[mssql]
Description             = MSSQL Server
Driver                  = freetds_driver 
Database                = my_db
ServerName              = mssql_server
TDS_Version             = 4.2

The driver version must also match what is in the freedtds.conf file

To test connection use isql.You should see the isql repl.

isql mssql sa 'weakpass'
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> 

Use erlang odbc application to talk to db !!

We are now ready to use erlang with odbc but a few things first ): ..

From the erlang docs for odbc it says

The odbc is made up of c code and erlang. The C code is delivered as a precompiled executable for windows, solaris and linux (SLES10) in the commercial build. In the open source distribution it is built the same way as all other application using configure and make.

So you should have configured erlang succesful with odbc or have the precompile binary before you can use it . An example below for configuration on my box

  ##where path could be /usr/include/ or anywhere where sql.h and sqlext.h header file are found.
  ./configure --with-odbc=/usr/include/
  

Assuming odbc was successfully installed with erlang you can now start the odbc application ,make connections to your db and execute queries.

%%sample to make connections,execute statements in repl
1 > odbc:start().
ok
2 > {ok, Ref} = odbc:connect("DSN=mssql;UID=sa;PWD=weakpass", []).
{ok,<0.342.0>}
3 > odbc:sql_query(Ref, "CREATE TABLE EMPLOYEE (NR integer,
FIRSTNAME char varying(20), LASTNAME char varying(20), GENDER char(1),
PRIMARY KEY(NR))").
{updated,undefined}
4 > odbc:sql_query(Ref, "INSERT INTO EMPLOYEE VALUES(1, 'Jane', 'Doe', 'F')").
{updated,1}
5> odbc:sql_query(Ref, "SELECT * FROM EMPLOYEE").
{selected,["NR","FIRSTNAME","LASTNAME","GENDER"],
[{1,"Jane","Doe","F"}
]}

You may experience some surprises based on the capabilities of the specific odbc driver you are using . some may be support/no support for paging,scrollable cursors etc..

Some other gotchas are also well documented here also Odbc Tips.

Till next time have fun with erlang and odbc !!!!!