Erlang Central

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

From ErlangCentral Wiki

 
(6 intermediate revisions by 5 users not shown)
Line 1: Line 1:
 +
==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==
 
==Introduction==
 
===why ODBC===
 
===why ODBC===
Line 11: Line 16:
 
In the following, the database client is running on the same machine as the database server.  
 
In the following, the database client is running on the same machine as the database server.  
  
<table class="ntable" width="100%" cellspacing="0" cellpadding="0" border="0">
+
{{CodeSnippet|Code listing 1.1: For commands, the following will be used|<pre>
<tr><td class="infohead" bgcolor="#7a5ada"><p class="caption">
+
        $ this is a user command (note $ prompt)
            Code listing 1.1: For commands, the following will be used</p></td></tr>
+
        # this is a root command (note # prompt)
<tr><td bgcolor="#ddddff"><pre>
+
</pre>}}
<br>
+
<br>$ this is a user command (note $ prompt)
+
<br># this is a root command (note # prompt)
+
</pre></td></tr>
+
</table>
+
  
 
===some system information===
 
===some system information===
 
System: SuSE release 9.1 or 9.2 (some package versions different w/9.2)
 
System: SuSE release 9.1 or 9.2 (some package versions different w/9.2)
  
<table class="ntable" width="100%" cellspacing="0" cellpadding="0" border="0">
+
{{CodeSnippet|Code listing 1.2: show the system type|<pre>
<tr><td class="infohead" bgcolor="#7a5ada"><p class="caption">
+
            Code listing 1.2: show the system type</p></td></tr>
+
<tr><td bgcolor="#ddddff"><pre>
+
 
$ uname -a
 
$ uname -a
 
Linux fangora 2.6.4-52-default #1 Wed Apr 7 02:08:30 UTC 2004 i686 i686 i386 GNU/Linux
 
Linux fangora 2.6.4-52-default #1 Wed Apr 7 02:08:30 UTC 2004 i686 i686 i386 GNU/Linux
</pre></td></tr>
+
</pre>}}
</table>
+
  
===Packages to install using Yast===
+
{{CodeSnippet|Code listing 1.3: using the command|<pre>
<table class="ntable" width="100%" cellspacing="0" cellpadding="0" border="0">
+
<tr><td class="infohead" bgcolor="#7a5ada"><p class="caption">
+
            Code listing 1.3: using the command</p></td></tr>
+
<tr><td bgcolor="#ddddff"><pre>
+
 
# yast   
 
# yast   
 
(and install the following...)
 
(and install the following...)
   </pre></td></tr>
+
   </pre>}}
</table>
+
  
 
MyODBC-unixODBC-3.51.06-150  
 
MyODBC-unixODBC-3.51.06-150  
Line 68: Line 59:
 
     </p></td></tr></table>
 
     </p></td></tr></table>
  
<table class="ntable" width="100%" cellspacing="0" cellpadding="0" border="0">
+
{{CodeSnippet|Code listing 1.4: Erlang R10B-4|<pre>
<tr><td class="infohead" bgcolor="#7a5ada"><p class="caption">
+
            Code listing 1.4: Erlang R10B-4</p></td></tr>
+
<tr><td bgcolor="#ddddff"><pre>
+
  
<br>(user has rwx privileges on /opt/src directory and rx on /opt/tar)
+
    (user has rwx privileges on /opt/src directory and rx on /opt/tar)
  
 
$ cd /opt/src
 
$ cd /opt/src
Line 129: Line 117:
 
$
 
$
 
$ sudo make install
 
$ sudo make install
   </pre></td></tr>
+
   </pre>}}
</table>
+
  
===Running things===
+
{{CodeSnippet|Code listing 1.5: running things|<pre>
<table class="ntable" width="100%" cellspacing="0" cellpadding="0" border="0">
+
<tr><td class="infohead" bgcolor="#7a5ada"><p class="caption">
+
            Code listing 1.5: running things</p></td></tr>
+
<tr><td bgcolor="#ddddff"><pre>
+
  
 
# /etc/init.d/mysql start
 
# /etc/init.d/mysql start
Line 190: Line 173:
 
[MYODBCUtilReadDataSource.c][205][ERROR] Unknown attribute (TraceFile).
 
[MYODBCUtilReadDataSource.c][205][ERROR] Unknown attribute (TraceFile).
  
   </pre></td></tr>
+
   </pre>}}
</table>
+
  
 
===/etc/unixODBC/odbc* files===
 
===/etc/unixODBC/odbc* files===
<table class="ntable" width="100%" cellspacing="0" cellpadding="0" border="0">
+
{{CodeSnippet|Code listing 1.6: odbc.ini|<pre>
<tr><td class="infohead" bgcolor="#7a5ada"><p class="caption">
+
            Code listing 1.6: odbc.ini</p></td></tr>
+
<tr><td bgcolor="#ddddff"><pre>
+
  
 
[ODBC Data Sources]
 
[ODBC Data Sources]
Line 215: Line 194:
 
Trace = Yes
 
Trace = Yes
 
TraceFile = odbc.log
 
TraceFile = odbc.log
</pre></td></tr>
+
</pre>}}
</table>
+
  
<table class="ntable" width="100%" cellspacing="0" cellpadding="0" border="0">
+
{{CodeSnippet|Code listing 1.7: odbcinst.ini|<pre>
<tr><td class="infohead" bgcolor="#7a5ada"><p class="caption">
+
            Code listing 1.7: odbcinst.ini</p></td></tr>
+
<tr><td bgcolor="#ddddff"><pre>
+
  
 
[MySQL]
 
[MySQL]
Line 228: Line 203:
 
Setup = /usr/lib/libmyodbc3S.so
 
Setup = /usr/lib/libmyodbc3S.so
 
FileUsage = 1
 
FileUsage = 1
</pre></td></tr>
+
</pre>}}
</table>
+
  
 
<table class="ncontent" width="100%" border="0" cellspacing="0" cellpadding="0"><tr><td bgcolor="#bbffbb"><p class="note"><b>Note: </b>
 
<table class="ncontent" width="100%" border="0" cellspacing="0" cellpadding="0"><tr><td bgcolor="#bbffbb"><p class="note"><b>Note: </b>
Line 235: Line 209:
 
to me while getting ODBC working with MySQL on Linux.
 
to me while getting ODBC working with MySQL on Linux.
 
</p></td></tr></table>
 
</p></td></tr></table>
 +
 +
==Download xml==
 +
[http://wiki.trapexit.erlang-consulting.com/upload/howto/odbc-howto.xml odbc-howto.xml]
 +
 
[[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