MySQL Interface

Michael McDaniel <>
Thu Apr 21 09:09:20 CEST 2005


On Thu, Apr 21, 2005 at 08:36:10AM +0200, Torbjorn Tornkvist wrote:
> 
> BTW: It would be nice if someone could write a little
> HowTo on how to setup and use ODBC from Erlang.
> You'll find the HowTo framework here:
> 
> http://www.trapexit.org/docs/howto/
> 
> Cheers, Tobbe
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
<smile>

I am not particularly pleased with my instructions for getting
odbc running.  It still feels too much like 'poking and prodding'
to get it to work.  However, I have got it working on a few 
of my machines, so I tried to upload some instructions...

I could not figure out how to get authenticated to upload a 'howto'
so here is (one of) my 'odbc-install.txt' file.  I think odbc-2.0.3
is the one included with R10B-4.  All of the following is on Linux.

Feel free to distill what works for you and maybe upload a 'howto'
based on these findings.  There are sone 'NEWER INSTRUCTIONS' 
further below that may be a bit more streamlined.

Erlang odbc using odbc-2.0.3

System: SuSE release 9.1 or 9.2 (some package versions different w/9.2)
uname -a
Linux fangora 2.6.4-52-default #1 Wed Apr 7 02:08:30 UTC 2004 i686 i686 i386 GNU/Linux

1) install the following from Yast
   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
   C/C++

2) using R10-B3
$ cd /opt/src/otp_src_R10B-3/lib
$ rm -fr odbc
$ tar -zxf odbc-2.0.3.tar.gz
$ cd ..
$ export ERL_TOP=${PWD}/
$ export LC_ALL=C
$ export ERLANG_COMMERCIAL_BUILD="Automated Systems"
$ ./configure  --with-odbc --with-ssl

(the EIROOT directory does not yet exist, it will)
$ export EIROOT=/opt/src/otp_src_R10B-3/lib/erl_interface/obj/i686-pc-linux-gnu/
$ emacs lib/odbc_c_src-2.0.3/c_src/i686-pc-linux-gnu/Makefile
ODBC_LIB = $(EIROOT)     (could put entire string here but I like EIROOT; 
			  remove @ODBC_LIB@)
(per Heinrich Ventor)
#ifeq ($(TYPE),debug)
# TYPEMARKER = .debug
# else
TYPEMARKER =
# endif

$ 
$ make
$ sudo make install
$ cd lib/odbc_c_src-2.0.3/
$ make
$ sudo mkdir /usr/local/lib/erlang/lib/odbc-2.0.3
$ cp priv/bin/i686-pc-linux-gnu/odbcserver priv/bin/
$ sudo cp -R * /usr/local/lib/erlang/lib/odbc-2.0.3
$ export ODBCINI=/etc/unixODBC/odbc.ini
$ export ODBCSYSINI=/etc/unixODBC/odbcinst.ini

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

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


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

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

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

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

%% end
*** ---------------------------------------------------------- ***
*** -------------------- NEWER INSTRUCTIONS ------------------ ***
*** ---------------------------------------------------------- ***

and here's another series of operations that worked for me...

unixODBC-2.2.11.tar.gz
./configure --enable-gui=no --with-gnu-ld=yes "CFLAGS=-I/usr/include/mysql"
make
sudo make install
which results in the following msg (there was more but this seemed most
pertinent)
"
----------------------------------------------------------------------
Libraries have been installed in:
   /usr/local/lib

If you ever happen to want to link against installed libraries
in a given directory, LIBDIR, you must either use libtool, and
specify the full pathname of the library, or use the `-LLIBDIR'
flag during linking and do at least one of the following:
   - add LIBDIR to the `LD_LIBRARY_PATH' environment variable
     during execution
   - add LIBDIR to the `LD_RUN_PATH' environment variable
     during linking
   - use the `-Wl,--rpath -Wl,LIBDIR' linker flag
   - have your system administrator add LIBDIR to `/etc/ld.so.conf'

See any operating system documentation about shared libraries for
more information, such as the ld(1) and ld.so(8) manual pages.
----------------------------------------------------------------------
touch /usr/local/etc/odbcinst.ini
touch /usr/local/etc/odbc.ini
mkdir -p /usr/local/etc/ODBCDataSources
"

# rpm --nodeps -i /opt/tar/MyODBC-3.51.11-2.i586.rpm
w/o --nodeps it complains about libodbcinst.so.1 and libodbc.so.1
but they are already installed

erlang from source
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="Automated Systems"
$ ./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 <>"
$ then emacs lib/Makefile w/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 ...
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)
$ (cd lib/odbc ; rm SKIP && emacs c_src/i686-pc-linux-gnu/Makefile && make)
$
$ sudo make install
$ export ODBCINI=/etc/unixODBC/odbc.ini
$ export ODBCSYSINI=/etc/unixODBC/odbcinst.ini
$ erl
Erlang (BEAM) emulator version 5.4.5 [hipe]

Eshell V5.4.5  (abort with ^G)
1> application:start(odbc).
ok
2>

FINE, but 
2>{ok, Ref} = odbc:connect("DSN=callinfo;UID=me;PWD=secret", [{trace_driver,on}]).
wouldn't work.  I wound up copying fangora:/usr/lib/libmyodbc3-3.51.11.so
over to sftp:/usr/lib  and then, on sftp,
rm /usr/local/lib/libmyodbc3.so
ln -s /usr/lib/libmyodbc3-3.51.11.so /usr/local/lib/libmyodbc3.so
and then the erl odbc:connect(...) worked, though with warnings.
The warnings were
[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).

**NOTE** I researched the above WARNING and apparently it is benign, though
annoying, as it shows up on terminal applications.

The Ref worked with sql_query(Ref, "select version();")
{selected,["version()"],[{"4.1.7-standard"}]}
and other queries worked fine also, so I don't yet know what the [WARNING]s
are about (though may be related to having copied over the latest libmyodbc3
and maybe something else is still missing or misconfigured.

SO, I did the following (again because, apparently, I removed it ~!#$%?)
#  rpm -i /opt/tar/MyODBC-3.51.11-2.i586.rpm
and now /usr/lib/libmyodbc3* is fully populated;
change /etc/unixODBC/odbc.ini and odbcinst.ini for path /usr/lib/libmyodbc3.so
(instead of /usr/lib/unixODBC/libmyodbc3.so) and no more errors - hurray!
AND remote access to mxml works.

%% end

------------------------------------------------------------------------
here's my /etc/unixODBC/odbc.ini  (I'm pretty sure only the 'callinfo' section
is needed though I haven't cleaned it out to verify).

;
;  odbc.ini configuration for MyODBC and MyODBC 3.51 Drivers
;

[ODBC Data Sources]

myodbc      = MySQL ODBC 2.50 Driver DSN 
myodbc3     = MySQL ODBC 3.51 Driver DSN 


[myodbc]
Driver       = /usr/lib/libmyodbc3.so
Description  = MySQL ODBC 2.50 Driver DSN
SERVER       = localhost
PORT         = 
USER         = test
Password     = test
Database     = test
OPTION       = 3
SOCKET       = 
Trace		= Yes
TraceFile	= odbc.log


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

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


[default]
Driver       = /usr/lib/libmyodbc3.so
Description  = MySQL ODBC 3.51 Driver DSN
SERVER       = cougora.autosys.us
PORT         = 
USER         = erl
Password     = erl
Database     = world
OPTION       = 3
SOCKET       = 
Trace		= Yes
TraceFile	= odbc.log

------------------------------------------------------------------------
and here's my /etc/unixODBC/odbcinst.ini

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

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

[MySQL ODBC 3.51 Driver]
DRIVER		= /usr/lib/libmyodbc3.so
SETUP		= /usr/lib/libmyodbc3S.so
FileUsage		= 1


Hope that helps.  Oh, yeah, here's some information from one of my machines
where odbc is working.

$ rpm -qa | egrep -i "mysql|odbc"
MySQL-bench-4.1.7-0
MySQL-server-4.1.7-0
unixODBC-devel-2.2.8-55
MyODBC-unixODBC-3.51.06-150
MySQL-client-4.1.7-0
MySQL-devel-4.1.7-0
MySQL-shared-4.1.7-0
unixODBC-2.2.8-55
mysql-shared-4.0.18-32
MyODBC-3.51.11-2

I would like to thank Ingela Anderton of the Ericsson OTP Team
who helped my quite a bit to get odbc working on Linux.

I hope this helps, and I will try to answer questions people 
may have.

~Michael


> 
> 
> Michael McDaniel wrote:
> 
> >On Wed, Apr 20, 2005 at 04:35:57AM -0400, Alexander Williams wrote:
> > 
> >
> >>Evening, folk:
> >>
> >> I'm  trying to put a Yaws front-end on a number of interfaces to the
> >> underlying MySQL database within my SAM3 music broadcast system. The
> >> Yaws  part of the beast is actually no problem at all, truth be told
> >> ...  its  tying to that pesky MySQL back-end that's the bugger right
> >> now.
> >>
> >> I've  been  using  the MySQL interface listed on the Erlang projects
> >> page  (by  Magnus  Ahltorp), but it seems to get a bit confused when
> >> multiple  requests  get  issued to the database in a short time, and
> >> the  initialization  of  the  database channel itself is kind of ...
> >> awkward.
> >>
> >> Undoubtedly, folks are doing more MySQL than I've been, so I thought
> >> I'd ask here. Sensible, really.
> >>
> >> Many thanks in advance.
> >>
> >>-- 
> >>Alexander Williams  ()
> >>The Squid's Redoubt: http//chancel.org:8000/Redoubt
> >> Currently Playing: Audra and the Antidote - Strange & Beautiful
> >>             Album: Hello?
> >>
> >>   
> >>
> >^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
> >I am using R10B-4 odbc on Linux.
> >$ uname -a
> >Linux sftp 2.6.4-52-smp #1 SMP Wed Apr 7 02:11:20 UTC 2004 i686 i686 i386 
> >GNU/Linux
> >
> >I have not stress tested it so do not know how it will work with
> >multiple requests using multiple connections.
> >
> >~~Michael
> >
> >
> > 
> >
> 



More information about the erlang-questions mailing list