Erlang Central

Difference between revisions of "ODBC with Erlang R10B-4 and MySQL"

From ErlangCentral Wiki

(3 intermediate revisions by 2 users not shown)
Line 1:Line 1:
 
==Author==
 
==Author==
 
Michael McDaniel
 
Michael McDaniel
 +
 +
Note that ODBC is well-integrated with current versions of Erlang (R13 will be released soon).  Perhaps some of the following information will be of interest for historical reasons.  This note added 2009/03/15.
  
 
==Introduction==
 
==Introduction==
Line 212:Line 214:
  
 
[[Category:HowTo]]
 
[[Category:HowTo]]
 +
[[Category:Interfacing Erlang|ODBC]]
 +
[[Category:Interfacing Erlang|MySQL]]

Revision as of 01:55, 16 March 2009

Contents

Author

Michael McDaniel

Note that ODBC is well-integrated with current versions of Erlang (R13 will be released soon). Perhaps some of the following information will be of interest for historical reasons. This note added 2009/03/15.

Introduction

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

        $ this is a user command (note $ prompt)
        # 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

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++

Note:

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

Note:

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


    (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~
68c68
<           pman $(SSL_APP) toolbar tv observer odbc \
---
>           pman $(SSL_APP) toolbar tv observer \


$ chmod u+w lib/odbc/configure.in
$ emacs lib/odbc/configure.in 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 configure.in configure.in~
67c67
<                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
#TYPEFLAGS = -g
#else
TYPEMARKER =
#TYPEFLAGS =
#endif

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
  

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).
ok
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).
ok
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
(PORT).
[MYODBCUtilReadDataSource.c][209][WARNING] Failed to get value for attribute
(USER).
[MYODBCUtilReadDataSource.c][209][WARNING] Failed to get value for attribute
(Password).
[MYODBCUtilReadDataSource.c][209][WARNING] Failed to get value for attribute
(SOCKET).
[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 

[myodbc3]
Driver       = /usr/lib/libmyodbc3.so
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


[MySQL]
Description	= MySQL
Driver		= /usr/lib/libmyodbc3.so
Setup		= /usr/lib/libmyodbc3S.so
FileUsage	= 1

Note:

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

Download xml

odbc-howto.xml