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.
Erlang
and C
code. The C
code is delivered as a
precompiled executable for windows and solaris. If you for
any reason need to recompile the C
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. |
On windows compilers are often distributed in some
development environment such as Visual C++
, that is
what we use to compile the C
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.
An example of how the make command might look:
nmake EIROOT="..\..\..\lib\erl_interface-3.3.0"
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 the C
code directory you will find a
Makefile. There are three variables in this make file that you
may want to override.
An example of how the make command might look:
gmake EIROOT="../../../lib/erl_interface-3.3.0"
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 for sqlserver
with version
2000.80.194.00
.
1 > application:start(odbc). ok
Connect 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}
Check what data types the database assigned for the columns. Hopefully this is not a surprise, some times it can be! These are the data types that you should use if you want to do a parameterized query.
5 > odbc:describe_table(Ref, "EMPLOYEE"). {ok, [{"NR", sql_integer}, {"FIRSTNAME", {sql_varchar, 20}}, {"LASTNAME", {sql_varchar, 20}} {"GENDER", {sql_char, 1}}]}
Use a parameterized query to insert many rows in one go.
6 > odbc:param_query(Ref,"INSERT INTO EMPLOYEE (NR, FIRSTNAME, " "LASTNAME, GENDER) VALUES(?, ?, ?, ?)", [{sql_integer,[2,3,4,5,6,7,8]}, {{sql_varchar, 20}, ["John", "Monica", "Ross", "Rachel", "Piper", "Prue", "Louise"]}, {{sql_varchar, 20}, ["Doe","Geller","Geller", "Green", "Halliwell", "Halliwell", "Lane"]}, {{sql_char, 1}, ["M","F","M","F","F","F","F"]}]). {updated, 7}
Fetch all data in the table employee
7> odbc:sql_query(Ref, "SELECT * FROM EMPLOYEE"). {selected,["NR","FIRSTNAME","LASTNAME","GENDER"], [{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.
8 > odbc:select_count(Ref, "SELECT * FROM EMPLOYEE"). {ok,8}
You can always traverse the result set sequential by using next
9 > odbc:next(Ref). {selected,["NR","FIRSTNAME","LASTNAME","GENDER"],[{1,"Jane","Doe","F"}]}
10 > odbc:next(Ref). {selected,["NR","FIRSTNAME","LASTNAME","GENDER"],[{2,"John","Doe","M"}]}
If your driver supports scrollable cursors you have a little more freedom, and can do thigs like this.
11 > odbc:last(Ref). {selected,["NR","FIRSTNAME","LASTNAME","GENDER"],[{8,"Louise","Lane","F"}]}
12 > odbc:prev(Ref). {selected,["NR","FIRSTNAME","LASTNAME","GENDER"],[{7,"Prue","Halliwell","F"}]}
13 > odbc:first(Ref). {selected,["NR","FIRSTNAME","LASTNAME","GENDER"],[{1,"Jane","Doe","F"}]}
14 > odbc:next(Ref). {selected,["NR","FIRSTNAME","LASTNAME","GENDER"],[{2,"John","Doe","M"}]}
Fetch the fields FIRSTNAME
and NR
for all female
employees
15 > 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
and NR
for all female
employees and sort them on the field FIRSTNAME
.
16 > 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
and NR
for all female employees to the
connection. The number of rows in the result set is
returned.
17 > odbc:select_count(Ref, "SELECT FIRSTNAME, NR FROM EMPLOYEE WHERE GENDER = 'F'"). {ok,6}
A few more ways of retriving parts of the result set when the driver supports scrollable cursors. Note that next will work even without support for scrollable cursors.
18 > odbc:select(Ref, {relative, 2}, 3). {selected,["FIRSTNAME","NR"],[{"Monica",3},{"Rachel",5},{"Piper",6}]}
19 > odbc:select(Ref, next, 2). {selected,["FIRSTNAME","NR"],[{"Prue",7},{"Louise",8}]}
20 > odbc:select(Ref, {absolute, 1}, 2). {selected,["FIRSTNAME","NR"],[{"Jane",1},{"Monica",3}]}
21 > odbc:select(Ref, next, 2). {selected,["FIRSTNAME","NR"],[{"Rachel",5},{"Piper",6}]}
22 > odbc:select(Ref, {absolute, 1}, 4). {selected,["FIRSTNAME","NR"], [{"Jane",1},{"Monica",3},{"Rachel",5},{"Piper",6}]}
Select, using a parameterized query.
23 > odbc:param_query(Ref, "SELECT * FROM EMPLOYEE WHERE GENDER=?", [{{sql_char, 1}, ["M"]}]). {selected,["NR","FIRSTNAME","LASTNAME","GENDER"], [{2,"John", "Doe", "M"},{4,"Ross","Geller","M"}]}
Delete the table EMPLOYEE
.
24 > odbc:sql_query(Ref, "DROP TABLE EMPLOYEE"). {updated,undefined}
Shut down the connection.
25 > odbc:disconnect(Ref). ok
Shut down the application.
26 > application:stop(odbc). =INFO REPORT==== 7-Jan-2004::17:00:59 === application: odbc exited: stopped type: temporary ok