<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html xmlns:fn="http://www.w3.org/2005/02/xpath-functions">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<link rel="stylesheet" href="../../../../doc/otp_doc.css" type="text/css">
<title>Erlang -- Getting started</title>
</head>
<body bgcolor="white" text="#000000" link="#0000ff" vlink="#ff00ff" alink="#ff0000"><div id="container">
<script id="js" type="text/javascript" language="JavaScript" src="../../../../doc/js/flipmenu/flipmenu.js"></script><script id="js2" type="text/javascript" src="../../../../doc/js/erlresolvelinks.js"></script><script language="JavaScript" type="text/javascript">
<!--
function getWinHeight() {
var myHeight = 0;
if( typeof( window.innerHeight ) == 'number' ) {
//Non-IE
myHeight = window.innerHeight;
} else if( document.documentElement && ( document.documentElement.clientWidth ||
document.documentElement.clientHeight ) ) {
//IE 6+ in 'standards compliant mode'
myHeight = document.documentElement.clientHeight;
} else if( document.body && ( document.body.clientWidth || document.body.clientHeight ) ) {
//IE 4 compatible
myHeight = document.body.clientHeight;
}
return myHeight;
}
function setscrollpos() {
var objf=document.getElementById('loadscrollpos');
document.getElementById("leftnav").scrollTop = objf.offsetTop - getWinHeight()/2;
}
function addEvent(obj, evType, fn){
if (obj.addEventListener){
obj.addEventListener(evType, fn, true);
return true;
} else if (obj.attachEvent){
var r = obj.attachEvent("on"+evType, fn);
return r;
} else {
return false;
}
}
addEvent(window, 'load', setscrollpos);
//--></script><div id="leftnav"><div class="innertube">
<img alt="Erlang logo" src="../../../../doc/erlang-logo.png"><br><small><a href="users_guide.html">User's Guide</a><br><a href="index.html">Reference Manual</a><br><a href="release_notes.html">Release Notes</a><br><a href="../pdf/odbc-2.10.13.pdf">PDF</a><br><a href="../../../../doc/index.html">Top</a></small><p><strong>Erlang ODBC</strong><br><strong>User's Guide</strong><br><small>Version 2.10.13</small></p>
<br><a href="javascript:openAllFlips()">Expand All</a><br><a href="javascript:closeAllFlips()">Contract All</a><p><small><strong>Chapters</strong></small></p>
<ul class="flipMenu" imagepath="../../../../doc/js/flipmenu">
<li id="no" title="Introduction" expanded="false">Introduction<ul>
<li><a href="introduction.html">
Top of chapter
</a></li>
<li title="Purpose"><a href="introduction.html#id57521">Purpose</a></li>
<li title="Prerequisites"><a href="introduction.html#id57527">Prerequisites</a></li>
<li title="About ODBC"><a href="introduction.html#id56991">About ODBC</a></li>
<li title="About the Erlang ODBC application"><a href="introduction.html#id58131">About the Erlang ODBC application</a></li>
</ul>
</li>
<li id="loadscrollpos" title="Getting started" expanded="true">Getting started<ul>
<li><a href="getting_started.html">
Top of chapter
</a></li>
<li title="Setting things up "><a href="getting_started.html#id60105">Setting things up </a></li>
<li title="Using the Erlang API"><a href="getting_started.html#id56674">Using the Erlang API</a></li>
</ul>
</li>
<li id="no" title="Databases" expanded="false">Databases<ul>
<li><a href="databases.html">
Top of chapter
</a></li>
<li title="Databases"><a href="databases.html#id57988">Databases</a></li>
<li title="Database independence "><a href="databases.html#id61089">Database independence </a></li>
<li title="Data types "><a href="databases.html#id61509">Data types </a></li>
<li title="Batch handling"><a href="databases.html#id62159">Batch handling</a></li>
</ul>
</li>
<li id="no" title="Error handling" expanded="false">Error handling<ul>
<li><a href="error_handling.html">
Top of chapter
</a></li>
<li title="Strategy "><a href="error_handling.html#id62301">Strategy </a></li>
<li title="The whole picture "><a href="error_handling.html#id62376">The whole picture </a></li>
</ul>
</li>
</ul>
</div></div>
<div id="content">
<div class="innertube">
<h1>2 Getting started</h1>
<h3><a name="id60105">2.1
Setting things up </a></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></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 <span class="code">Erlang</span>
and <span class="code">C</span> code. The <span class="code">C</span> code is delivered as a
precompiled executable for windows, solaris and linux (SLES10) 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>
<div class="note">
<div class="label">Note</div>
<div class="content"><p>
<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.</p>
</p></div>
</div>
<h3><a name="id56674">2.2
Using the Erlang API</a></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 <span class="code">sqlserver 7.0 with servicepack 1</span> as database and
the ODBC driver for <span class="code">sqlserver</span> with version
<span class="code">2000.80.194.00</span>.</p>
<div class="example"><pre>
1 > odbc:start().
ok </pre></div>
<p>Connect to the database </p>
<div class="example"><pre>
2 > {ok, Ref} = odbc:connect("DSN=sql-server;UID=aladdin;PWD=sesame", []).
{ok,<0.342.0>} </pre></div>
<p>Create a table </p>
<div class="example"><pre>
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} </pre></div>
<p>Insert some data </p>
<div class="example"><pre>
4 > odbc:sql_query(Ref, "INSERT INTO EMPLOYEE VALUES(1, 'Jane', 'Doe', 'F')").
{updated,1} </pre></div>
<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.</p>
<div class="example"><pre>
5 > odbc:describe_table(Ref, "EMPLOYEE").
{ok, [{"NR", sql_integer},
{"FIRSTNAME", {sql_varchar, 20}},
{"LASTNAME", {sql_varchar, 20}}
{"GENDER", {sql_char, 1}}]}
</pre></div>
<p> <a name="param_query"></a>
Use a parameterized query
to insert many rows in one go. </p>
<div class="example"><pre>
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}
</pre></div>
<p>Fetch all data in the table employee </p>
<div class="example"><pre>
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"}]]} </pre></div>
<p>Associate a result set containing the whole table
<span class="code">EMPLOYEE</span> to the connection. The number of rows in the
result set is returned.</p>
<div class="example"><pre>
8 > odbc:select_count(Ref, "SELECT * FROM EMPLOYEE").
{ok,8} </pre></div>
<p>You can always traverse the result set sequential by using next</p>
<div class="example"><pre>
9 > odbc:next(Ref).
{selected,["NR","FIRSTNAME","LASTNAME","GENDER"],[{1,"Jane","Doe","F"}]}
</pre></div>
<div class="example"><pre>
10 > odbc:next(Ref).
{selected,["NR","FIRSTNAME","LASTNAME","GENDER"],[{2,"John","Doe","M"}]}
</pre></div>
<p>If your driver supports scrollable cursors you have a little
more freedom, and can do things like this. </p>
<div class="example"><pre>
11 > odbc:last(Ref).
{selected,["NR","FIRSTNAME","LASTNAME","GENDER"],[{8,"Louise","Lane","F"}]} </pre></div>
<div class="example"><pre>
12 > odbc:prev(Ref).
{selected,["NR","FIRSTNAME","LASTNAME","GENDER"],[{7,"Prue","Halliwell","F"}]} </pre></div>
<div class="example"><pre>
13 > odbc:first(Ref).
{selected,["NR","FIRSTNAME","LASTNAME","GENDER"],[{1,"Jane","Doe","F"}]} </pre></div>
<div class="example"><pre>
14 > odbc:next(Ref).
{selected,["NR","FIRSTNAME","LASTNAME","GENDER"],[{2,"John","Doe","M"}]}
</pre></div>
<p>Fetch the fields <span class="code">FIRSTNAME </span> and <span class="code">NR </span> for all female
employees</p>
<div class="example"><pre>
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}]} </pre></div>
<p>Fetch the fields <span class="code">FIRSTNAME </span> and <span class="code">NR </span> for all female
employees and sort them on the field <span class="code">FIRSTNAME </span>. </p>
<div class="example"><pre>
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}]}
</pre></div>
<p>Associate a result set that contains the fields <span class="code">FIRSTNAME</span> and <span class="code">NR </span> for all female employees to the
connection. The number of rows in the result set is
returned.</p>
<div class="example"><pre>
17 > odbc:select_count(Ref, "SELECT FIRSTNAME, NR FROM EMPLOYEE WHERE GENDER = 'F'").
{ok,6} </pre></div>
<p>A few more ways of retrieving parts of the result set when the
driver supports scrollable cursors. Note that next will work even
without support for scrollable cursors. </p>
<div class="example"><pre>
18 > odbc:select(Ref, {relative, 2}, 3).
{selected,["FIRSTNAME","NR"],[{"Monica",3},{"Rachel",5},{"Piper",6}]}
</pre></div>
<div class="example"><pre>
19 > odbc:select(Ref, next, 2).
{selected,["FIRSTNAME","NR"],[{"Prue",7},{"Louise",8}]}
</pre></div>
<div class="example"><pre>
20 > odbc:select(Ref, {absolute, 1}, 2).
{selected,["FIRSTNAME","NR"],[{"Jane",1},{"Monica",3}]}
</pre></div>
<div class="example"><pre>
21 > odbc:select(Ref, next, 2).
{selected,["FIRSTNAME","NR"],[{"Rachel",5},{"Piper",6}]}
</pre></div>
<div class="example"><pre>
22 > odbc:select(Ref, {absolute, 1}, 4).
{selected,["FIRSTNAME","NR"],
[{"Jane",1},{"Monica",3},{"Rachel",5},{"Piper",6}]}
</pre></div>
<p>Select, using a parameterized query. </p>
<div class="example"><pre>
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"}]}
</pre></div>
<p>Delete the table <span class="code">EMPLOYEE</span>.</p>
<div class="example"><pre>
24 > odbc:sql_query(Ref, "DROP TABLE EMPLOYEE").
{updated,undefined}
</pre></div>
<p>Shut down the connection. </p>
<div class="example"><pre>
25 > odbc:disconnect(Ref).
ok
</pre></div>
<p>Shut down the application. </p>
<div class="example"><pre>
26 > odbc:stop().
=INFO REPORT==== 7-Jan-2004::17:00:59 ===
application: odbc
exited: stopped
type: temporary
ok
</pre></div>
</div>
<div class="footer">
<hr>
<p>Copyright © 1999-2012 Ericsson AB. All Rights Reserved.</p>
</div>
</div>
</div></body>
</html>
distrib
>
Fedora
>
17
>
i386
>
media
>
updates
>
by-pkgid
>
675c8c8167236dfcf8d66da674f931e8
>
files
>
1053
