2 Getting started
2.1 Setting things up
As the Erlang ODBC application is dependent on third party products there are a few administrative things that needs to be done before you can get things up and running.
- The first thing you need to do, is to make sure you have an ODBC driver installed for the database that you want to access. Both the client machine where you plan to run your erlang node and the server machine running the database needs the the ODBC driver. (In some cases the client and the server may be the same machine).
- Secondly you might need to set environment variables and paths to appropriate values. This may differ a lot between different os's, databases and ODBC drivers. This is a configuration problem related to the third party product and hence we can not give you a standard solution in this guide.
- The Erlang ODBC application consists of both
Erlang
andC
code. TheC
code is delivered as a precompiled executable for windows and solaris. If you for any reason need to recompile theC
code or if you are running some other os and want to compile the code there is a include makefile to help you do so.
The Erlang ODBC application should run on all Unix dialects including Linux, Windows 2000, Windows XP and NT. But currently it is only tested for Solaris, Windows 2000, Windows XP and NT.
2.2 Compiling on Windows
On windows compilers are often distributed in some development environment such as
Visual C++
, that is what we use to compile theC
code for windows.If you need to compile the
C
code open a command prompt. Assume that Erlang/OTP is installed at "c:\Program Files\erl<erlang-version>". Change to the directory "c:\Program Files\erl<erlang-version>\lib\odbc-<odbc-version>\c_src" directory. Here you will find a Makefile. There are three variables in this makefile that you may want to override.
- ODBCLIBS - Path to the ODBC library.
- ODBCINCLUDE - Path to the ODBC header files.
- EIROOT - Path to the root directory of the Erlang application erl_interface.
An example of how the make command might look:
nmake EIROOT="..\..\..\lib\erl_interface-3.3.0"2.3 Compiling on Unix
The prefered compiler is gcc version 2.7.2 or higher. Assume that the Erlang/OTP is installed in /usr/local/erlang then the
C
code is located in the /usr/local/erlang/lib/odbc-<odbc-version>/c_src directory. In theC
code directory you will find a Makefile. There are three variables in this make file that you may want to override.
- ODBCROOT - Path to the root directory of the ODBC installation.
- ODBCLIBS - Path to the ODBC library.
- EIROOT - Root directory of the Erlang applicationerl_interface.
An example of how the make command might look:
gmake EIROOT="../../../lib/erl_interface-3.3.0"2.4 Using the Erlang API
The following dialog within the Erlang shell illustrates the functionality of the Erlang ODBC interface. The table used in the example does not have any relevance to anything that exist in reality, it is just a simple example. The example was created using
sqlserver 7.0 with servicepack 1
as database and the ODBC driver forsqlserver
with version2000.80.194.00
.1 > application:start(odbc). okConnect to the database
2 > {ok, Ref} = odbc:connect("DSN=sql-server;UID=aladin;PWD=sesame", []). {ok,<0.342.0>}Create a table
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}Insert some data
4 > odbc:sql_query(Ref, "INSERT INTO EMPLOYEE VALUES(1, 'Jane', 'Doe', 'F')"). {updated,1}5 >odbc:sql_query(Ref, "INSERT INTO EMPLOYEE VALUES(2, 'John', 'Doe', 'M')"). {updated,1}6 > odbc:sql_query(Ref, "INSERT INTO EMPLOYEE VALUES(3, 'Monica', 'Geller', 'F')"). {updated,1}7 > odbc:sql_query(Ref, "INSERT INTO EMPLOYEE VALUES(4, 'Ross', 'Geller', 'M')"). {updated,1}8 > odbc:sql_query(Ref, "INSERT INTO EMPLOYEE VALUES(5, 'Rachel', 'Green', 'F')"). {updated,1}9 > odbc:sql_query(Ref, "INSERT INTO EMPLOYEE VALUES(6, 'Piper', 'Halliwell', 'F')"). {updated,1}10 > odbc:sql_query(Ref, "INSERT INTO EMPLOYEE VALUES(7, 'Prue', 'Halliwell', 'F')"). {updated,1}11 > odbc:sql_query(Ref, "INSERT INTO EMPLOYEE VALUES(8, 'Louise', 'Lane', 'F')"). {updated,1}Fetch all data in the table employee
12> odbc:sql_query(Ref, "SELECT * FROM EMPLOYEE"). {selected,["NR","FIRSTNAME","LASTNAME"," [[1,"Jane","Doe","F"], [2,"John","Doe","M"], [3,"Monica","Geller","F"], [4,"Ross","Geller","M"], [5,"Rachel","Green","F"], [6,"Piper","Halliwell","F"], [7,"Prue","Halliwell","F"], [8,"Louise","Lane","F"]]}Associate a result set containg the whole table
EMPLOYEE
to the connection. The number of rows in the result set is returned.13 > odbc:select_count(Ref, "SELECT * FROM EMPLOYEE"). {ok,8}Fetch certain parts of the result set.
14 > odbc:first(Ref). {selected,["NR","FIRSTNAME","LASTNAME","GENDER"],[[1,"Jane","Doe","F"]]}15 > odbc:next(Ref). {selected,["NR","FIRSTNAME","LASTNAME","GENDER"],[[2,"John","Doe","M"]]}16 > odbc:last(Ref). {selected,["NR","FIRSTNAME","LASTNAME","GENDER"],[[8,"Louise","Lane","F"]]}17 > odbc:prev(Ref). {selected,["NR","FIRSTNAME","LASTNAME","GENDER"],[[7,"Prue","Halliwell","F"]]}Fetch the fields
FIRSTNAME
andNR
for all female employees18 > odbc:sql_query(Ref, "SELECT FIRSTNAME, NR FROM EMPLOYEE WHERE GENDER = 'F'"). {selected,["FIRSTNAME","NR"], [["Jane",1], ["Monica",3], ["Rachel",5], ["Piper",6], ["Prue",7], ["Louise",8]]}Fetch the fields
FIRSTNAME
andNR
for all female employees and sort them on the fieldFIRSTNAME
.19 > odbc:sql_query(Ref, "SELECT FIRSTNAME, NR FROM EMPLOYEE WHERE GENDER = 'F' ORDER BY FIRSTNAME"). {selected,["FIRSTNAME","NR"], [["Jane",1], ["Louise",8], ["Monica",3], ["Piper",6], ["Prue",7], ["Rachel",5]Associate a result set that contains the fields
FIRSTNAME
andNR
for all female employees to the connection. The number of rows in the result set is returned.20 > odbc:select_count(Ref, "SELECT FIRSTNAME, NR FROM EMPLOYEE WHERE GENDER = 'F'"). {ok,6}Fetch certain parts of the result set.
21 > odbc:select(Ref, {relative, 2}, 3). {selected,["FIRSTNAME","NR"],[["Monica",3],["Rachel",5],["Piper",6]]}22 > odbc:select(Ref, next, 2). {selected,["FIRSTNAME","NR"],[["Prue",7],["Louise",8]]}23 > odbc:select(Ref, {absolute, 1}, 2). {selected,["FIRSTNAME","NR"],[["Jane",1],["Monica",3]]}24 > odbc:select(Ref, next, 2). {selected,["FIRSTNAME","NR"],[["Rachel",5],["Piper",6]]}25 > odbc:select(Ref, {absolute, 1}, 4). {selected,["FIRSTNAME","NR"], [["Jane",1],["Monica",3],["Rachel",5],["Piper",6]]}Delete the table
EMPLOYEE
26 > odbc:sql_query(Ref, "DROP TABLE EMPLOYEE"). {updated,undefined}Shout down the connection.
27 > odbc:disconnect(Ref). ok