<!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 -- Databases</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="no" title="Getting started" expanded="false">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="loadscrollpos" title="Databases" expanded="true">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>3 Databases</h1>
<h3><a name="id57988">3.1
Databases</a></h3>
<p>If you need to access a relational database such as
<span class="code">sqlserver</span>, <span class="code">mysql</span>, <span class="code">postgres</span>, <span class="code">oracle</span>,
<span class="code">cybase</span> etc. from your erlang application using the Erlang
ODBC interface is a good way to go about it.</p>
<p></p>
<p>The Erlang ODBC application should work for any relational
database that has an ODBC driver. But currently it is only
regularly tested for <span class="code">sqlserver</span> and <span class="code">postgres</span>.</p>
<h3><a name="id61089">3.2
Database independence </a></h3>
<p>The Erlang ODBC interface is in principal database
independent, e.i. an erlang program using the interface could be
run without changes towards different databases. But as SQL is
used it is alas possible to write database dependent
programs. Even though SQL is an ANSI-standard meant to be
database independent, different databases have proprietary
extensions to SQL defining their own data types. If you keep to
the ANSI data types you will minimize the problem. But
unfortunately there is no guarantee that all databases actually
treats the ANSI data types equivalently. For instance an
installation of <span class="code">Oracle Enterprise release 8.0.5.0.0 for unix</span> will accept that you create a table column with the
ANSI data type <span class="code">integer</span>, but when retrieving values from
this column the driver reports that it is of type
<span class="code">SQL_DECIMAL(0, 38)</span> and not <span class="code">SQL_INTEGER</span> as you may have
expected. </p>
<p>Another obstacle is that some drivers do not support scrollable
cursors which has the effect that the only way to traverse the
result set is sequentially, with next, from the first row to the
last, and once you pass a row you can not go back. This means
that some functions in the interface will not work together with
certain drivers. A similar problem is that not all drivers
support "row count" for select queries, hence resulting in that
the function <span class="code">select_count/[3,4]</span> will return <span class="code">{ok, undefined}</span> instead of <span class="code">{ok, NrRows}</span> where
<span class="code">NrRows</span> is the number of rows in the result set.</p>
<h3><a name="id61509">3.3
Data types </a></h3>
<p>The following is a list of the ANSI data types. For details
turn to the ANSI standard documentation. Usage of other data types
is of course possible, but you should be aware that this makes your
application dependent on the database you are using at the moment.</p>
<ul>
<li>CHARACTER (size), CHAR (size)</li>
<li>NUMERIC (precision, scale), DECIMAL (precision, scale), DEC
(precision, scale ) precision - total number of digits, scale
- total number of decimal places</li>
<li>INTEGER, INT, SMALLINT</li>
<li>FLOAT (precision)</li>
<li>REAL</li>
<li>DOUBLE PRECISION</li>
<li>CHARACTER VARYING(size), CHAR VARYING(size)</li>
</ul>
<p>When inputting data using sql_query/[2,3] the values will
always be in string format as they are part of an SQL-query.
Example:</p>
<div class="example"><pre>
odbc:sql_query(Ref, "INSERT INTO TEST VALUES(1, 2, 3)").
</pre></div>
<div class="note">
<div class="label">Note</div>
<div class="content"><p>
<p>Note that when the value of the data to input is a string, it
has to be quoted with <span class="code">'</span>. Example: </p>
<div class="example"><pre>
odbc:sql_query(Ref, "INSERT INTO EMPLOYEE VALUES(1, 'Jane', 'Doe', 'F')").
</pre></div>
</p></div>
</div>
<p>You may also input data using <span class="bold_code"><a href="odbc.html#param_query">param_query/[3,4]</a></span> and then
the input data will have the Erlang type corresponding to the
ODBC type of the column.<span class="bold_code"><a href="#type">See ODBC to Erlang mapping</a></span></p>
<p> <a name="type"></a>
When selecting data from a table, all data
types are returned from the database to the ODBC driver as an
ODBC data type. The tables below shows the mapping between those
data types and what is returned by the Erlang API.</p>
<table border="1" cellpadding="2" cellspacing="0">
<tr>
<td align="left" valign="middle">ODBC Data Type </td>
<td align="left" valign="middle">Erlang Data Type </td>
</tr>
<tr>
<td align="left" valign="middle">SQL_CHAR(size)</td>
<td align="left" valign="middle">String | Binary (configurable)</td>
</tr>
<tr>
<td align="left" valign="middle">SQL_WCHAR(size) </td>
<td align="left" valign="middle">Unicode binary encoded as UTF16 little endian.</td>
</tr>
<tr>
<td align="left" valign="middle">SQL_NUMERIC(p,s) <br>
when (p >= 0 and p <= 9 and s == 0) </td>
<td align="left" valign="middle">Integer </td>
</tr>
<tr>
<td align="left" valign="middle">SQL_NUMERIC(p,s) <br>
when (p >= 10 and p <= 15 and s == 0) or (s <= 15 and s > 0)</td>
<td align="left" valign="middle">Float </td>
</tr>
<tr>
<td align="left" valign="middle">SQL_NUMERIC(p,s) <br>
when p >= 16 </td>
<td align="left" valign="middle">String </td>
</tr>
<tr>
<td align="left" valign="middle">SQL_DECIMAL(p,s) <br>
when (p >= 0 and p <= 9 and s == 0) </td>
<td align="left" valign="middle">Integer </td>
</tr>
<tr>
<td align="left" valign="middle">SQL_DECIMAL(p,s) <br>
when (p >= 10 and p <= 15 and s == 0) or (s <= 15 and s > 0)</td>
<td align="left" valign="middle">Float </td>
</tr>
<tr>
<td align="left" valign="middle">SQL_DECIMAL(p,s) <br>
when p >= 16 </td>
<td align="left" valign="middle">String </td>
</tr>
<tr>
<td align="left" valign="middle">SQL_INTEGER </td>
<td align="left" valign="middle">Integer </td>
</tr>
<tr>
<td align="left" valign="middle">SQL_SMALLINT </td>
<td align="left" valign="middle">Integer </td>
</tr>
<tr>
<td align="left" valign="middle">SQL_FLOAT </td>
<td align="left" valign="middle">Float </td>
</tr>
<tr>
<td align="left" valign="middle">SQL_REAL </td>
<td align="left" valign="middle">Float </td>
</tr>
<tr>
<td align="left" valign="middle">SQL_DOUBLE</td>
<td align="left" valign="middle">Float</td>
</tr>
<tr>
<td align="left" valign="middle">SQL_VARCHAR(size) </td>
<td align="left" valign="middle">String | Binary (configurable)</td>
</tr>
<tr>
<td align="left" valign="middle">SQL_WVARCHAR(size) </td>
<td align="left" valign="middle">Unicode binary encoded as UTF16 little endian.</td>
</tr>
</table>
<em>Table
3.1:
Mapping of ODBC data types to the Erlang data types returned to the Erlang application.</em>
<table border="1" cellpadding="2" cellspacing="0">
<tr>
<td align="left" valign="middle">ODBC Data Type </td>
<td align="left" valign="middle">Erlang Data Type </td>
</tr>
<tr>
<td align="left" valign="middle">SQL_TYPE_DATE </td>
<td align="left" valign="middle">String </td>
</tr>
<tr>
<td align="left" valign="middle">SQL_TYPE_TIME </td>
<td align="left" valign="middle">String </td>
</tr>
<tr>
<td align="left" valign="middle">SQL_TYPE_TIMESTAMP </td>
<td align="left" valign="middle"> {{YY, MM, DD}, {HH, MM, SS}} </td>
</tr>
<tr>
<td align="left" valign="middle">SQL_LONGVARCHAR </td>
<td align="left" valign="middle">String | Binary (configurable)</td>
</tr>
<tr>
<td align="left" valign="middle">SQL_WLONGVARCHAR(size) </td>
<td align="left" valign="middle">Unicode binary encoded as UTF16 little endian.</td>
</tr>
<tr>
<td align="left" valign="middle">SQL_BINARY</td>
<td align="left" valign="middle">String | Binary (configurable)</td>
</tr>
<tr>
<td align="left" valign="middle">SQL_VARBINARY</td>
<td align="left" valign="middle">String | Binary (configurable)</td>
</tr>
<tr>
<td align="left" valign="middle">SQL_LONGVARBINARY</td>
<td align="left" valign="middle">String | Binary (configurable)</td>
</tr>
<tr>
<td align="left" valign="middle">SQL_TINYINT </td>
<td align="left" valign="middle">Integer </td>
</tr>
<tr>
<td align="left" valign="middle">SQL_BIT</td>
<td align="left" valign="middle">Boolean </td>
</tr>
</table>
<em>Table
3.2:
Mapping of extended ODBC data types to the Erlang data types returned to the Erlang application.</em>
<div class="note">
<div class="label">Note</div>
<div class="content"><p>
<p>To find out which data types will be returned for the
columns in a table use the function <span class="bold_code"><a href="odbc.html#describe_table">describe_table/[2,3]</a></span></p>
</p></div>
</div>
<h3><a name="id62159">3.4
Batch handling</a></h3>
<p>Grouping of SQL queries can be desirable in order to reduce
network traffic. Another benefit can be that the data source
sometimes can optimize execution of a batch of SQL queries.</p>
<p>Explicit batches an procedures described below will result
in multiple results being returned from sql_query/[2,3].
while with parameterized queries only one result will be returned
from param_query/[2,3].</p>
<h4>Explicit batches</h4>
<p>The most basic form of a batch is created by semicolons
separated SQL queries, for example:</p>
<div class="example"><pre>
"SELECT * FROM FOO; SELECT * FROM BAR" or
"INSERT INTO FOO VALUES(1,'bar'); SELECT * FROM FOO"
</pre></div>
<h4>Procedures </h4>
<p>Different databases may also support creating of procedures
that contains more than one SQL query. For example, the
following SQLServer-specific statement creates a procedure that
returns a result set containing information about employees
that work at the department and a result set listing the
customers of that department. </p>
<div class="example"><pre>
CREATE PROCEDURE DepartmentInfo (@DepartmentID INT) AS
SELECT * FROM Employee WHERE department = @DepartmentID
SELECT * FROM Customers WHERE department = @DepartmentID
</pre></div>
<h4>Parameterized queries</h4>
<p>To effectively perform a batch of similar queries, you can use
parameterized queries. This means that you in your SQL query
string will mark the places that usually would contain values
with question marks and then provide lists of values for each
parameter. For instance you can use this to insert multiple
rows into the <span class="code">EMPLOYEE</span> table while executing only a
single SQL statement, for example code see <span class="bold_code"><a href="getting_started.html#param_query">"Using the Erlang API"</a></span> section in the "Getting Started" chapter.</p>
</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
>
1051
