Erlang Central

ODBC with Erlang R10B-4 and MySQL

Revision as of 08:47, 30 June 2006 by (Talk)



why ODBC

When I decided to use MySQL with Erlang, I also had to decide how to access the database(s) I created. I could have used one of the MySQL protocol-specific packages such as [1]

However I wanted to use an access method that both Erlang and MySQL AB supported. Best as I could tell, that means ODBC. Any performance penalties for using ODBC were compensated by having a vendor-supported interface that, presumably, the vendors would update as needed.

Follows is what I did to get ODBC working on SuSE Linux release 9.1,2 using Erlang R10B-4 and MySQL v4.0.18 and v4.1.7.

The exact commands may not map over for other Linux distros (for example, not all distros use rpm). However, the following should give a reasonable starting point to install and troubleshoot your ODBC installation.

In the following, the database client is running on the same machine as the database server.

Code listing 1.1: For commands, the following will be used

<br>$ this is a user command (note $ prompt)
<br># this is a root command (note # prompt)

some system information

System: SuSE release 9.1 or 9.2 (some package versions different w/9.2)

Code listing 1.2: show the system type

$ uname -a
Linux fangora 2.6.4-52-default #1 Wed Apr 7 02:08:30 UTC 2004 i686 i686 i386 GNU/Linux

Packages to install using Yast

Code listing 1.3: using the command

# yast  
(and install the following...)

MyODBC-unixODBC-3.51.06-150 unixODBC-devel-2.2.8-55 mysql-devel-4.0.18-32 mysql-4.0.18-32 mysql-client-4.0.18-32 mysql-shared-4.0.18-32 openssl-devel ncurses-devel libtool gnu C/C++


Some of the installed packages will have other requirements that yast will figure out; accept the yast suggestions.


On Gentoo the following seem to suffice: emerge mysql unixODBC myodbc

Packages to install from source code

Note: Using OTP_R10B-6, no file tweaking, as shown below seem to be necessary.

We just did: ./configure --with-odbc ....etc...

Code listing 1.4: Erlang R10B-4

<br>(user has rwx privileges on /opt/src directory and rx on /opt/tar)

$ cd /opt/src
$ tar -xvf /opt/tar/otp_src_R10B-4.tar.gz
$ cd /opt/src/otp_src_R10B-4/
$ export ERL_TOP=${PWD}/
$ export LC_ALL=C
$ export ERLANG_COMMERCIAL_BUILD="Your Message Here"
$ ./configure  --with-odbc --x-includes=/usr/local/include --x-libraries=/usr/lib --with-ssl
**********************  APPLICATIONS DISABLED  **********************

jinterface     : No Java compiler found
odbc           : No odbc library found

$ chmod u+w lib/Makefile
$ echo "From: Mikael Karlsson"
$ emacs lib/Makefile with the following changes...
otp_src_R10B-4/lib> diff Makefile Makefile~
<           pman $(SSL_APP) toolbar tv observer odbc \
>           pman $(SSL_APP) toolbar tv observer \

$ chmod u+w lib/odbc/
$ emacs lib/odbc/ with the following changes ...
On SuSE the odbc root is /usr so this could be added
to the configure(.in) files:
otp_src_R10B-4/lib/odbc> diff
<                for dir in /opt/local/pgm/odbc /usr/local/odbc /usr/odbc /usr
>                for dir in /opt/local/pgm/odbc /usr/local/odbc /usr/odbc
$ make
$ export PATH=$PATH:$ERL_TOP/bin  (for erlc)
$ echo "ODBC_LIB = $(EILIB) in Makefile"
$ echo "and, per Heinrich..."
#ifeq ($(TYPE),debug)
#TYPEMARKER = .debug

LDFLAGS =  $(ODBC_LIB) $(EI_LDFLAGS)  -L/usr/local/lib
LIBS = -lpthread  $(EI_LIB) -lodbc

(copy over my odbc.ini and odbcinst.ini files and change DNS name to expect)
$ (cd lib/odbc ; rm SKIP && emacs c_src/i686-pc-linux-gnu/Makefile && make)
$ sudo make install

Running things

Code listing 1.5: running things

# /etc/init.d/mysql start
# mysqladmin -u root password "secret"

$ mysql -u root -p
Password: secret
mysql> grant all on test.* to test@localhost identified by 'test' ;
mysql> quit ;

$ export ODBCINI=/etc/unixODBC/odbc.ini
$ export ODBCSYSINI=/etc/unixODBC/odbcinst.ini

$ erl
Erlang (BEAM) emulator version 5.4.4 [hipe]

Eshell V5.4.4  (abort with ^G)
1> application:start(odbc).
2> {ok,Ref} = odbc:connect("DSN=myodbc3;UID=test;PWD=test",[{trace_driver,on}]).
{ok, <0.38.0>}
3> odbc:sql_query(Ref, "select version();").
{selected, ["version()"],[{"4.0.21"}]}
4> odbc:disconnect(Ref).
5> q().
6> $

The {trace_driver,on} above is supposed to cause the ODBC driver to write out
to a sql.log file which may help you troubleshoot.  It showed up as
/tmp/sql.log on my system though the Erlang ODBC docs say it will be in the
Erlang interpreter's directory.

I then was able to 'rpm -e' the MySQL* rpms, and then 'rpm -i' MySQL* v4.1.7
rpms to upgrade the MySQL installation .  You may be able to use the v4.1.7
rpms first.

I researched the following WARNINGs from Erlang and apparently they are
benign, though annoying, as they show up on terminal applications.  You
will not necessarily get them.

[MYODBCUtilReadDataSource.c][209][WARNING] Failed to get value for attribute
[MYODBCUtilReadDataSource.c][209][WARNING] Failed to get value for attribute
[MYODBCUtilReadDataSource.c][209][WARNING] Failed to get value for attribute
[MYODBCUtilReadDataSource.c][209][WARNING] Failed to get value for attribute
[MYODBCUtilReadDataSource.c][205][ERROR] Unknown attribute (Trace).
[MYODBCUtilReadDataSource.c][205][ERROR] Unknown attribute (TraceFile).


/etc/unixODBC/odbc* files

Code listing 1.6: odbc.ini

[ODBC Data Sources]

myodbc3     = MySQL ODBC 3.51 Driver DSN 

Driver       = /usr/lib/
Description  = MySQL ODBC 3.51 Driver DSN
SERVER       = localhost
PORT         = 
USER         = test
Password     = 
Database     = 
OPTION       = 3   
SOCKET       = 
Trace		= Yes
TraceFile	= odbc.log

Code listing 1.7: odbcinst.ini

Description	= MySQL
Driver		= /usr/lib/
Setup		= /usr/lib/
FileUsage	= 1


I would like to thank Ingela Anderton for her patience and help to me while getting ODBC working with MySQL on Linux.

Download xml