File: getting_started.html

package info (click to toggle)
erlang-doc-html 1%3A11.b.2-1
links: PTS
area: main
in suites: etch, etch-m68k
size: 23,284 kB
ctags: 10,724
sloc: erlang: 505; ansic: 323; makefile: 62; perl: 61; sh: 45
file content (313 lines) | stat: -rw-r--r-- 10,290 bytes
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<!-- This document was generated using DocBuilder 3.3.3 -->
<HTML>
<HEAD>
  <TITLE>Getting started</TITLE>
  <SCRIPT type="text/javascript" src="../../../../doc/erlresolvelinks.js">
</SCRIPT>
</HEAD>
<BODY BGCOLOR="#FFFFFF" TEXT="#000000" LINK="#0000FF" VLINK="#FF00FF"
      ALINK="#FF0000">
<CENTER>
<A HREF="http://www.erlang.se"><IMG BORDER=0 ALT="[Ericsson AB]" SRC="min_head.gif"></A>
</CENTER>
<A NAME="2"><!-- Empty --></A>
<H2>2 Getting started</H2>
<A NAME="2.1"><!-- Empty --></A>
<H3>2.1  Setting things up </H3>

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

<P>
<UL>

<LI>
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).

</LI>


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

</LI>


<LI>
        The Erlang ODBC application consists of both <CODE>Erlang</CODE>
        and <CODE>C</CODE> code. The <CODE>C</CODE> code is delivered as a
        precompiled executable for windows and solaris in the commercial
        build. In the open source distribution it is built the
        same way as all other application using configure and make.
        You may want to provide the the path to your ODBC libraries
        using --with-odbc=PATH. 

</LI>


</UL>

<P>
<TABLE CELLPADDING=4>
  <TR>
    <TD VALIGN=TOP><IMG ALT="Note!" SRC="note.gif"></TD>
    <TD>

<P>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.    </TD>
  </TR>
</TABLE>
<A NAME="2.2"><!-- Empty --></A>
<H3>2.2 Using the Erlang API</H3>

<P> 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 <CODE>sqlserver 7.0 with servicepack 1</CODE> as database and
the ODBC driver for <CODE>sqlserver</CODE> with version
<CODE>2000.80.194.00</CODE>.

<PRE>
 1 &#62; application:start(odbc).
      ok
</PRE>

<P> Connect to the database 
<PRE>
 2 &#62; {ok, Ref} = odbc:connect(&#34;DSN=sql-server;UID=aladin;PWD=sesame&#34;, []).
      {ok,&#60;0.342.0&#62;}
</PRE>

<P> Create a table 
<PRE>
 3 &#62; odbc:sql_query(Ref, &#34;CREATE TABLE EMPLOYEE (NR integer,
      FIRSTNAME  char varying(20), LASTNAME  char varying(20), GENDER char(1),
      PRIMARY KEY(NR))&#34;).
      {updated,undefined}
</PRE>

<P> Insert some data 
<PRE>
 4 &#62; odbc:sql_query(Ref, &#34;INSERT INTO EMPLOYEE VALUES(1, 'Jane', 'Doe', 'F')&#34;).
      {updated,1}
</PRE>

<P> 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.
<PRE>
 5 &#62; odbc:describe_table(Ref, &#34;EMPLOYEE&#34;).
      {ok, [{&#34;NR&#34;, sql_integer},
            {&#34;FIRSTNAME&#34;, {sql_varchar, 20}},
            {&#34;LASTNAME&#34;, {sql_varchar, 20}}
            {&#34;GENDER&#34;, {sql_char, 1}}]}
    
</PRE>

<P> <A NAME="param_query"><!-- Empty --></A> Use a parameterized query
to insert many rows in one go. 
<PRE>
 6 &#62; odbc:param_query(Ref,&#34;INSERT INTO EMPLOYEE (NR, FIRSTNAME, &#34;
                          &#34;LASTNAME, GENDER) VALUES(?, ?, ?, ?)&#34;,
                           [{sql_integer,[2,3,4,5,6,7,8]},
                            {{sql_varchar, 20},
                             [&#34;John&#34;, &#34;Monica&#34;, &#34;Ross&#34;, &#34;Rachel&#34;,
                             &#34;Piper&#34;, &#34;Prue&#34;, &#34;Louise&#34;]},
                           {{sql_varchar, 20},
                             [&#34;Doe&#34;,&#34;Geller&#34;,&#34;Geller&#34;, &#34;Green&#34;,
                              &#34;Halliwell&#34;, &#34;Halliwell&#34;, &#34;Lane&#34;]},
                           {{sql_char, 1}, [&#34;M&#34;,&#34;F&#34;,&#34;M&#34;,&#34;F&#34;,&#34;F&#34;,&#34;F&#34;,&#34;F&#34;]}]).
      {updated, 7}
   
</PRE>

<P> Fetch all data in the table employee 
<PRE>
 7&#62; odbc:sql_query(Ref, &#34;SELECT * FROM EMPLOYEE&#34;).
    {selected,[&#34;NR&#34;,&#34;FIRSTNAME&#34;,&#34;LASTNAME&#34;,&#34;GENDER&#34;],
          [{1,&#34;Jane&#34;,&#34;Doe&#34;,&#34;F&#34;},
           {2,&#34;John&#34;,&#34;Doe&#34;,&#34;M&#34;},
           {3,&#34;Monica&#34;,&#34;Geller&#34;,&#34;F&#34;},
           {4,&#34;Ross&#34;,&#34;Geller&#34;,&#34;M&#34;},
           {5,&#34;Rachel&#34;,&#34;Green&#34;,&#34;F&#34;},
           {6,&#34;Piper&#34;,&#34;Halliwell&#34;,&#34;F&#34;},
           {7,&#34;Prue&#34;,&#34;Halliwell&#34;,&#34;F&#34;},
           {8,&#34;Louise&#34;,&#34;Lane&#34;,&#34;F&#34;}]]} 
</PRE>

<P> Associate a result set containg the whole table
<CODE>EMPLOYEE</CODE> to the connection. The number of rows in the
result set is returned.
<PRE>
 8 &#62; odbc:select_count(Ref, &#34;SELECT * FROM EMPLOYEE&#34;).
      {ok,8} 
</PRE>

<P> You can always traverse the result set sequential by using next

<PRE>
 9 &#62; odbc:next(Ref).
      {selected,[&#34;NR&#34;,&#34;FIRSTNAME&#34;,&#34;LASTNAME&#34;,&#34;GENDER&#34;],[{1,&#34;Jane&#34;,&#34;Doe&#34;,&#34;F&#34;}]}
    
</PRE>

<PRE>
 10 &#62; odbc:next(Ref).
      {selected,[&#34;NR&#34;,&#34;FIRSTNAME&#34;,&#34;LASTNAME&#34;,&#34;GENDER&#34;],[{2,&#34;John&#34;,&#34;Doe&#34;,&#34;M&#34;}]}
    
</PRE>

<P> If your driver supports scrollable cursors you have a little
more freedom, and can do thigs like this. 
<PRE>
 11 &#62; odbc:last(Ref).
      {selected,[&#34;NR&#34;,&#34;FIRSTNAME&#34;,&#34;LASTNAME&#34;,&#34;GENDER&#34;],[{8,&#34;Louise&#34;,&#34;Lane&#34;,&#34;F&#34;}]}
</PRE>

<PRE>
 12 &#62; odbc:prev(Ref).
      {selected,[&#34;NR&#34;,&#34;FIRSTNAME&#34;,&#34;LASTNAME&#34;,&#34;GENDER&#34;],[{7,&#34;Prue&#34;,&#34;Halliwell&#34;,&#34;F&#34;}]}
</PRE>

<PRE>
 13 &#62; odbc:first(Ref).
      {selected,[&#34;NR&#34;,&#34;FIRSTNAME&#34;,&#34;LASTNAME&#34;,&#34;GENDER&#34;],[{1,&#34;Jane&#34;,&#34;Doe&#34;,&#34;F&#34;}]}    
</PRE>

<PRE>
 14 &#62; odbc:next(Ref).
      {selected,[&#34;NR&#34;,&#34;FIRSTNAME&#34;,&#34;LASTNAME&#34;,&#34;GENDER&#34;],[{2,&#34;John&#34;,&#34;Doe&#34;,&#34;M&#34;}]}
    
</PRE>

<P> Fetch the fields <CODE> FIRSTNAME </CODE> and <CODE> NR </CODE> for all female
employees
<PRE>
 15 &#62; odbc:sql_query(Ref, &#34;SELECT FIRSTNAME, NR FROM EMPLOYEE WHERE GENDER = 'F'&#34;).
     {selected,[&#34;FIRSTNAME&#34;,&#34;NR&#34;],
          [{&#34;Jane&#34;,1},
           {&#34;Monica&#34;,3},
           {&#34;Rachel&#34;,5},
           {&#34;Piper&#34;,6},
           {&#34;Prue&#34;,7},
           {&#34;Louise&#34;,8}]} 
</PRE>

<P> Fetch the fields <CODE> FIRSTNAME </CODE> and <CODE> NR </CODE> for all female
employees and sort them on the field <CODE> FIRSTNAME </CODE>. 
<PRE>
 16 &#62; odbc:sql_query(Ref, &#34;SELECT FIRSTNAME, NR FROM EMPLOYEE WHERE GENDER = 'F'
      ORDER BY FIRSTNAME&#34;).
    {selected,[&#34;FIRSTNAME&#34;,&#34;NR&#34;],
          [{&#34;Jane&#34;,1},
           {&#34;Louise&#34;,8},
           {&#34;Monica&#34;,3},
           {&#34;Piper&#34;,6},
           {&#34;Prue&#34;,7},
           {&#34;Rachel&#34;,5}]}
      
</PRE>

<P> Associate a result set that contains the fields <CODE> FIRSTNAME
</CODE> and <CODE> NR </CODE> for all female employees to the
connection. The number of rows in the result set is
returned.
<PRE>
 17 &#62; odbc:select_count(Ref, &#34;SELECT FIRSTNAME, NR FROM EMPLOYEE WHERE GENDER = 'F'&#34;).
      {ok,6}
</PRE>

<P> 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. 
<PRE>
 18 &#62; odbc:select(Ref, {relative, 2}, 3).
    {selected,[&#34;FIRSTNAME&#34;,&#34;NR&#34;],[{&#34;Monica&#34;,3},{&#34;Rachel&#34;,5},{&#34;Piper&#34;,6}]}
    
</PRE>

<PRE>
 19 &#62; odbc:select(Ref, next, 2).
      {selected,[&#34;FIRSTNAME&#34;,&#34;NR&#34;],[{&#34;Prue&#34;,7},{&#34;Louise&#34;,8}]}
    
</PRE>

<PRE>
 20 &#62; odbc:select(Ref, {absolute, 1}, 2).
      {selected,[&#34;FIRSTNAME&#34;,&#34;NR&#34;],[{&#34;Jane&#34;,1},{&#34;Monica&#34;,3}]}
    
</PRE>

<PRE>
 21 &#62; odbc:select(Ref, next, 2).
    {selected,[&#34;FIRSTNAME&#34;,&#34;NR&#34;],[{&#34;Rachel&#34;,5},{&#34;Piper&#34;,6}]}
    
</PRE>

<PRE>
 22 &#62; odbc:select(Ref, {absolute, 1}, 4). 
      {selected,[&#34;FIRSTNAME&#34;,&#34;NR&#34;],
                [{&#34;Jane&#34;,1},{&#34;Monica&#34;,3},{&#34;Rachel&#34;,5},{&#34;Piper&#34;,6}]}
    
</PRE>

<P>Select, using a parameterized query. 
<PRE>
 23 &#62; odbc:param_query(Ref, &#34;SELECT * FROM EMPLOYEE WHERE GENDER=?&#34;,
      [{{sql_char, 1}, [&#34;M&#34;]}]).
      {selected,[&#34;NR&#34;,&#34;FIRSTNAME&#34;,&#34;LASTNAME&#34;,&#34;GENDER&#34;],
                [{2,&#34;John&#34;, &#34;Doe&#34;, &#34;M&#34;},{4,&#34;Ross&#34;,&#34;Geller&#34;,&#34;M&#34;}]} 
    
    
</PRE>

<P> Delete the table <CODE>EMPLOYEE</CODE>.
<PRE>
 24 &#62; odbc:sql_query(Ref, &#34;DROP TABLE EMPLOYEE&#34;).
      {updated,undefined}
    
</PRE>

<P> Shut down the connection. 
<PRE>
 25 &#62; odbc:disconnect(Ref).
      ok
    
</PRE>

<P> Shut down the application. 
<PRE>
 26 &#62; application:stop(odbc).
    =INFO REPORT==== 7-Jan-2004::17:00:59 ===
    application: odbc
    exited: stopped
    type: temporary

    ok
    
</PRE>
<CENTER>
<HR>
<SMALL>
Copyright &copy; 1991-2006
<A HREF="http://www.erlang.se">Ericsson AB</A><BR>
</SMALL>
</CENTER>
</BODY>
</HTML>