[Erlang Systems]

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.

Note!

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

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

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 for sqlserver with version 2000.80.194.00.

Connect to the database

 1 > {ok, Ref} = odbc:connect("DSN=sql-server;UID=aladin;PWD=sesame", []).
      {ok,<0.342.0>}

Create a table

 2 > odbc:sql_query(Ref, "CREATE TABLE EMPLOYEE (NR integer,
      FIRSTNAME  char varying(20), LASTNAME  char varying(20), GENDER char(1))").
      {updated,undefined}

Insert some data

 3 > odbc:sql_query(Ref, "INSERT INTO EMPLOYEE VALUES(1, 'Jane', 'Doe', 'F')").
      {updated,1}
 4 >odbc:sql_query(Ref, "INSERT INTO EMPLOYEE VALUES(2, 'John', 'Doe', 'M')").
      {updated,1}
 5 > odbc:sql_query(Ref, "INSERT INTO EMPLOYEE VALUES(3, 'Monica', 'Geller', 'F')").
      {updated,1}
 6 > odbc:sql_query(Ref, "INSERT INTO EMPLOYEE VALUES(4, 'Ross', 'Geller', 'M')").
      {updated,1}
 7 > odbc:sql_query(Ref, "INSERT INTO EMPLOYEE VALUES(5, 'Rachel', 'Green', 'F')").
      {updated,1}
 8 > odbc:sql_query(Ref, "INSERT INTO EMPLOYEE VALUES(6, 'Piper', 'Halliwell', 'F')").
      {updated,1} 
 9 > odbc:sql_query(Ref, "INSERT INTO EMPLOYEE VALUES(7, 'Prue', 'Halliwell', 'F')").
      {updated,1} 
 10 > odbc:sql_query(Ref, "INSERT INTO EMPLOYEE VALUES(8, 'Louise', 'Lane', 'F')").
      {updated,1} 

Fetch all data in the table employee

 11> 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.

 12 > odbc:select_count(Ref, "SELECT * FROM EMPLOYEE").
      {ok,8} 

Fetch certain parts of the result set.

 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"]]}
    
 15 > odbc:last(Ref).
      {selected,["NR","FIRSTNAME","LASTNAME","GENDER"],[[8,"Louise","Lane","F"]]}
 16 > odbc:prev(Ref).
      {selected,["NR","FIRSTNAME","LASTNAME","GENDER"],[[7,"Prue","Halliwell","F"]]}

Fetch the fields FIRSTNAME and NR for all female employees

 17 > 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 .

 18 > 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.

 19 > odbc:select_count(Ref, "SELECT FIRSTNAME, NR FROM EMPLOYEE WHERE GENDER = 'F'").
      {ok,6}

Fetch certain parts of the result set.

 20 > odbc:select(Ref, {relative, 2}, 3).
      {selected,["FIRSTNAME","NR"],[["Monica",3],["Rachel",5],["Piper",6]]}
    
 21 > odbc:select(Ref, next, 2).
      {selected,["FIRSTNAME","NR"],[["Prue",7],["Louise",8]]}
    
 22 > odbc:select(Ref, {absolute, 1}, 2).
      {selected,["FIRSTNAME","NR"],[["Jane",1],["Monica",3]]}
    
 23 > odbc:select(Ref, next, 2).
      {selected,["FIRSTNAME","NR"],[["Rachel",5],["Piper",6]]}
    
 24 > odbc:select(Ref, {absolute, 1}, 4). 
      {selected,["FIRSTNAME","NR"],
                [["Jane",1],["Monica",3],["Rachel",5],["Piper",6]]}
    

Delete the table EMPLOYEE

 25 > odbc:sql_query(Ref, "DROP TABLE EMPLOYEE").
      {updated,undefined}
    

Shout down the connection.

 26 > odbc:disconnect(Ref).
      ok
    

Copyright © 1991-2002 Ericsson Utvecklings AB