erlang odbc2.0
TRANSCRIPT
8/13/2019 Erlang Odbc2.0
http://slidepdf.com/reader/full/erlang-odbc20 1/32
ErlangODBCapplication
version2.0
8/13/2019 Erlang Odbc2.0
http://slidepdf.com/reader/full/erlang-odbc20 2/32
Typ eset in LATEX from SG ML source using the D ocBuilder-0.9.7 D ocument Sy stem.
8/13/2019 Erlang Odbc2.0
http://slidepdf.com/reader/full/erlang-odbc20 3/32
Contents
1 ErlangODBC User’sGuide 1
1.1 Int roduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
1.1.1 P urpose . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
1.1.2 P rereq uisit es . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
1.1.3 About O D BC . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11.1.4 Ab out t he Erlang O D B C applicat io n . . . . . . . . . . . . . . . . . . . . . . . . 1
1.2 G et ting st art ed . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
1.2.1 Set ting things up . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
1.2.2 U sing t he Erlang AP I . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
1.3 D at abases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
1.3.1 D at abases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
1.3.2 D a tabase independence . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
1.3.3 D at a t ypes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
1.3.4 Batch handling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
1.4 Error handling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
1.4.1 Strat egy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
1.4.2 The w hole pict ure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
2 ErlangODBC ReferenceManual 13
2.1 odbc . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
List of Figures 23
List of Tables 25
iiiErlang O D BC application
8/13/2019 Erlang Odbc2.0
http://slidepdf.com/reader/full/erlang-odbc20 4/32
iv Erlang O D BC application
8/13/2019 Erlang Odbc2.0
http://slidepdf.com/reader/full/erlang-odbc20 5/32
Chapter 1
Erlang ODBC User’s Guide
The ErlangODBC Application provides an interface for accessing relationa l SQ L-dat abases from Erlang.
1.1 Introduction
1.1.1 Purpose
The purpose of the Erlang O D BC application is to provide the programm er with an O D BC interface
tha t ha s a Erlang/O TP t ouch and feel. So t hat the program mer may concentrat e on solving his/her
actual problem instead of struggling with pointers and m emory allocation w hich is not very relevant for
Erlang. This user guide will give you some information about technical issues and provide some
examples of how to use the Erlang ODBC interface.
1.1.2 Prerequisites
It is assumed that the reader is familiar w ith t he Erlang programm ing language, concepts of OTP and
has a ba sic understanding of relational databa ses and SQ L.
1.1.3 About ODBC
O pen D atabase C onnectivity (OD BC ) is a Microsoft standard for accessing relational databases that has
become w idely used. The O D BC standard provides a c-level application programming interface (API)
for da tabase access. It uses Structured Q uery Language (SQL ) as its datab ase access language.
1.1.4 About the Erlang ODBC application
Provides an Erlang interface to com municate w ith relational SQ L-datab ases. It is built o n top of
Microsofts O D BC interface and therefore requires that you have an OD BC driver to the datab ase that
you w ant t o connect t o. The Erlang OD BC application is designed using the version 3.0 of the
O D BC -standard, how ever using the option
scrollable cursors, off
for a connection has been
know n to make it w ork for at least some 2.X drivers.
1Erlang O D BC application
8/13/2019 Erlang Odbc2.0
http://slidepdf.com/reader/full/erlang-odbc20 6/32
Chapter 1: Erlang ODBC User’s Guide
1.2 Getting started
1.2.1 Setting things up
As the Erlang O D BC application is dependent on t hird party products there are a few ad ministrative
things that needs to be done before you can get t hings up and running.
The first t hing you need to do, is to m ake sure you have an O D BC driver installed for t he databa se
that you want to access. Both the client machine where you plan to run your erlang node and the
server machine running the da tabase needs the the O D BC driver. (In some cases the client and
the server may be the same machine).
Secondly you m ight need to set environment variables and pa ths to appropriate values. This may
differ a lot betw een different os’s, dat abases and O D BC drivers. This is a confi guration problem
related t o t he third party product and hence we can not give you a standard solution in this guide.
The Erlang O D BC application consists of both Erlang and C code. The C code is delivered as a
precompiled executable for w indows and solaris in the commercial build. In th e open source
distribution it is built the same w ay as all ot her application using configure and make. You m ay
w ant t o provide the the pat h t o yo ur O D BC libraries using –w ith-odbc= PATH .
Note:The Erlang O D BC application should run on all Unix dialects including Linux, Window s 2000,
Window s X P and N T. But currently it is only t ested f or Solaris, Windows 2000, Windows X P and
NT.
1.2.2 Using the Erlang API
The follow ing dialog w ithin the Erlang shell illustrates the functionality o f t he Erlang O D BC 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 w ith version 2000.80.194.00.
1 > application:start(odbc).
ok
C onnect to the database
2 > {ok, Ref} = odbc:connect("DSN=sql-server;UID=aladin;PWD=sesame", []).
{ok,<0.342.0>}
C reate 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
2 Erlang O D BC application
8/13/2019 Erlang Odbc2.0
http://slidepdf.com/reader/full/erlang-odbc20 7/32
1.2: Getting started
4 > odbc:sql_query(Ref, "INSERT INTO EMPLOYEE VALUES(1, ’Jane’, ’Doe’, ’F’)").
{updated,1}
C heck what data types the dat abase assigned for t he columns. H opefully t his is not a surprise, some
times it can be! These are the data types that y ou should use if you w ant t o 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 row s 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 tab le 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 t he w hole table EMPLOYEE to t he connection. The number of row s 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"}]}
3Erlang O D BC application
8/13/2019 Erlang Odbc2.0
http://slidepdf.com/reader/full/erlang-odbc20 8/32
Chapter 1: Erlang ODBC User’s Guide
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 th e fi elds FIRSTNAME an d 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 th e fi elds FIRSTNAME an d NR for all female employees and sort them on t he 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 t hat contains the fi elds FIRSTNAME and NR for all female employees to t he
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 w ays of retriving parts of t he result set w hen the driver supports scrollable cursors. Not e
that next w ill w ork even w ithout support fo r 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}]}
4 Erlang O D BC application
8/13/2019 Erlang Odbc2.0
http://slidepdf.com/reader/full/erlang-odbc20 9/32
1.3: Databases
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"}]}
D elete 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
1.3 Databases
1.3.1 Databases
If you need t o a ccess a relational database such as sqlserver, mysql, postgres, oracle, cybase etc.
from your erlang application using the Erlang O D BC interface is a good w ay to go about it.
The Erlang O D BC application should w ork for any relational database that ha s an O D BC driver. But
currently it is only regularly tested for sqlserver and postgres.
5Erlang O D BC application
8/13/2019 Erlang Odbc2.0
http://slidepdf.com/reader/full/erlang-odbc20 10/32
Chapter 1: Erlang ODBC User’s Guide
1.3.2 Database independence
The Erlang OD BC interface is in principal dat abase independent, e.i. a n erlang program using the
interface could be run w ithout changes towa rds different da tabases. But as SQ L is used it is alas
possible to w rite database dependent programs. Even though SQ L is an ANSI-standard meant to be
datab ase independent, different da tabases have proprietary extensions to SQ L d efining their ow n dat a
types. If you keep to the ANSI data types you will minimize the problem. But unfortunately there is noguarantee that all databa ses actually t reats the ANSI data types equivalently. For instance an installation
of Oracle Enterprise release 8.0.5.0.0 for unix will accept that you create a table column with
the ANSI data type integer, but w hen retrinving values from th is column t he driver reports that it is
of type SQL DECIMAL(0, 38) and not SQL INTEGER as you may have expected.
Another obstacle is that some drivers do not support scrollable cursors w hich has the effect t hat 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 w ill not w ork together
with certain drivers. A similar problem is that not all drivers support “row count” for select queries,
hence resulting in that the function select count/[3,4] w ill return
ok, undefined
instead of
ok,
NrRows
where NrRows is the number of row s in the result set.
1.3.3 Data types
The following is a list o f the ANSI dat a t ypes. For details turn to t he ANSI standard d ocumentation.
Usage of ot her data types is of course possible, but you should be aw are that this makes your
application dependent o n the da tabase you a re using at the mom ent.
CHARACTER (size), CHAR (size)
NU MERIC (precision, scale), D EC IMAL (precision, scale), D EC (precision, scale ) precision -
tot al number o f d igits, scale - total number of decimal places
INTEG ER, INT, S MALLIN T
FLOAT (precision)
REAL
D O U B L E P RECIS IO N
C H ARAC TER VARYING (size), C H AR VARYING (size)
When inputing data using sql query/[2,3] the values w ill alway s be in string format as they are part o f
an SQL-query. Example:
odbc:sql_query(Ref, "INSERT INTO TEST VALUES(1, 2, 3)").
Note:Note that w hen the value of the data to input is a string, it has to be q uoted w ith ’. Example:
odbc:sql_query(Ref, "INSERT INTO EMPLOYEE VALUES(1, ’Jane’, ’Doe’, ’F’)").
You may also input dat a using param query/[3,4] [page 19] and t hen the input dat a w ill have the
Erlang type corresponding to t he OD BC type of the column.See O D BC to Erlang mapping [page 6]
6 Erlang O D BC application
8/13/2019 Erlang Odbc2.0
http://slidepdf.com/reader/full/erlang-odbc20 11/32
8/13/2019 Erlang Odbc2.0
http://slidepdf.com/reader/full/erlang-odbc20 12/32
Chapter 1: Erlang ODBC User’s Guide
1.3.4 Batch handling
G rouping of SQ L q ueries can be d esirable in order to reduce netw ork traffic. Another benefit can be
that the data source sometimes can optimize execution of a batch of SQL queries.
Explicit batches an procedures described below will result in multiple results being returned from
sql query/[2,3]. w hile w ith parameterized q ueries only one result w ill be returned from
param q uery/[2,3].
Explicit batches
The mo st basic form of a b atch is created by semicolons separated SQ L q ueries, for exam ple:
"SELECT * FROM FOO; SELECT * FROM BAR" or
"INSERT INTO FOO VALUES(1,’bar’); SELECT * FROM FOO"
Procedures
D ifferent dat abases may also support creating of procedures that contains more than one SQ L q uery.For exam ple, th e follow ing SQ LServer-specific sta tem ent creates a procedure tha t ret urns a result set
containing information about employees that w ork at t he department and and a result set listing the
customers of that department.
CREATE PROCEDURE DepartmentInfo (@DepartmentID INT) AS
SELECT * FROM Employee WHERE department = @DepartmentID
SELECT * FROM Customers WHERE department = @DepartmentID
Parameterized queries
To effectively perform a ba tch of similar q ueries, you ca n use parameterized q ueries. This means that
you in your SQ L q uery string will mark the places that usually wo uld 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 EMPLOYEE table w hile executing only a single SQ L stat ement, for example code
see ”Using the Erlang API” [page 3] section in the “G etting Started” chapter.
1.4 Error handling
1.4.1 Strategy
O n a conceptual level starting a datab ase connection using the Erlang O D BC API is a basic client server
application. The client process uses the API to start and communicate w ith t he server process that
manages the connection. The strategy of the Erlang O D BC application is that programm ing faults in
the application itself will cause the connection process to terminate abnormally.(When a process
terminates abnormally its supervisor will log relevant error reports.) Calls to API functions during or
after t ermination of the connection process, w ill return error, connection closed . Contextual
errors on the other hand w ill not t erminate t he connection it w ill only return error, Reason to the
client, w here Reason may be any erlang term.
8 Erlang O D BC application
8/13/2019 Erlang Odbc2.0
http://slidepdf.com/reader/full/erlang-odbc20 13/32
1.4: Error handling
Clients
The connection is associated with the process that created it and can only be accessed through it. The
reason for th is is to preserve the semantics of result sets and t ransactions w hen select count /[2,3] is
called or auto commit is turned off. Att empts to use the connection from another process will fail. This
w ill not effect t he connection. O n the o ther hand, if the client process dies the connection w ill be
terminated.
Timeouts
All request made b y t he client to the connection are synchronous. If th e timeout is used and expires the
client process will exit w ith reason timeout. Proably t he right thing to do is let t he client die and
perhaps be restarted by its supervisor. But if the client chooses to catch this timeout, it is a good idea to
wait a little while before trying again. If there are too many consecutive timeouts that are caught the
connection process w ill conclude t hat there is something radically w rong and t erminate the connection.
Gaurds
All API-functions are guarded and if you pass an argument of t he w rong type a runtime error will occur.
All input parameters to internal functions are trusted t o be correct. It is a good programm ing practise to
only distrust input from truly external sources. You are not supposed to catch these errors, it will only
make the code very messy and much m ore complex, w hich introduces more bugs and in t he w orst case
also covers up the actual faults. Put your effort on t esting instead, y ou should trust y our ow n input.
1.4.2 The whole picture
As the Erlang O D BC application relies on third party products and communicates with a d ataba se that
proably runs on an other comput er in the netw ork there are plenty of t hings that might go w rong. To
fully understand t he things that might happen it facilitat e to know the design of the Erlang OD BC
application, hence here follows a short description of the current design.
Note:Please note that design is something, that not necessarily will, but might change in future releases.
While the semantics of the API w ill not change as it is independent of the implementation.
9Erlang O D BC application
8/13/2019 Erlang Odbc2.0
http://slidepdf.com/reader/full/erlang-odbc20 14/32
Chapter 1: Erlang ODBC User’s Guide
E r l a n g c o
n t r o l p r o c e s s
C - p r o c e s s
E r l a n g c l i e n t
L i n k
C o n n e c t
i o n r e f e r e n c e
r e q u e s t
r e s p o n s e
m
o n i t o r
C o n n e c t r e q
u e s t
S u p e r v i s o r t h r e a d
D a t a b a s e h a n d l e r t h r e a d
r e q u e s t
r e s p o n s e
E r l a n g p o
r t
E x i t s i g n a l p r o p a g a t i o n s h
u t d o w n
E x i t s i g n a l p r o p a g a t i o n
E r l a n g S u p e r v i s o r
Figure 1.1: Architecture of the Erlang odbc application
When you d o application:start(odbc) the o nly thing t hat happens is that a supervisor process is started.
For each call to the API function connect/2 a process is spawned and added a s a child to the Erlang
O D BC supervisor. The supervisors only ta sks are to provide error-log reports, if a child process should
die abnormally, and the possibility to do a cod e change. O nly the client process has the knowledge to
decide if this connection managing process should be restarted.
The erlang connection process spawned by co nnect/2, w ill open a po rt t o a c-process tha t h andles the
communication w ith the data base through Microsoft’s OD BC API. The erlang port w ill be kept open
for exit signal propagation, if something goes wrong in t he c-process and it exits we w ant know as mush
as possible about the reason. The main communication with the c-process is done through sockets. The
C -process consists of tw o threads, t he supervisor thread and the d atabase handler th read. Thesupervisor thread checks for shutdown messages on the supervisor socket and the database handler
thread receives requests and sends answ ers on the d atabase socket. If t he dat abase thread seems to hang
on some data base call, the erlang control process w ill send a shutdow n m essage on the supervisor
socket, in t his case the c-process will exit. If t he c-process crashes/exits it w ill bring the erlang-process
dow n to o and vice versa i.e. t he connection is terminated.
10 Erlang O D BC application
8/13/2019 Erlang Odbc2.0
http://slidepdf.com/reader/full/erlang-odbc20 15/32
1.4: Error handling
Note:The function connect/2 w ill start the o dbc application if t hat is not already d one. In this case a
supervisor information log w ill be produced stating that the o dbc application w as started as a
temporary application. It is really t he responsibility of the application tha t uses the API too make
sure it is started in the desired way.
Error types
The ty pes of errors that may occur can b e divide into the f ollow ing categories.
Configuration problems - Everything from that the database was not set up right to that the
c-program that should be run th rough the erlang port w as not compiled for your platform.
Errors discovered by t he O D BC driver - If calls to t he O D BC -driver fails due to circumstances
that can not be controlled by t he Erlang O D BC application programm er, an error string will be
dug up from the driver. This string will be the Reason in the
error, Reason
return value.
H ow good t his error message is will of course be driver dependent. Examples of suchcircumstances are trying to insert t he same key tw ice, invalid SQ L-queries and that the d atabase
has gone off line.
C onnection t ermination - If a connection is terminated in an a bnormal w ay, or if yo u try t o use a
connection t hat you h ave already terminated in a normal w ay by calling disconnect/1, th e return
value will be
error, connection closed
. A connection could end abnormally because of an
programming error in the Erlang O D BC application, but also if the O D BC driver crashes.
C ontextual errors - If API functions are used in the w rong context, th e Reason in the error t uple
w ill be a d escriptive atom. For instance if you t ry to call the function last/[1,2] without first
calling select count/[2,3] to associate a result set w ith the connection. If t he OD BC -driver
does not support some functions, or if you d isabled some functionality for a connection and then
try to use it.
11Erlang O D BC application
8/13/2019 Erlang Odbc2.0
http://slidepdf.com/reader/full/erlang-odbc20 16/32
Chapter 1: Erlang ODBC User’s Guide
12 Erlang O D BC application
8/13/2019 Erlang Odbc2.0
http://slidepdf.com/reader/full/erlang-odbc20 17/32
Erlang ODBC Reference Manual
Short Summaries
Erlang Module odbc [page 15] – Erlang ODBC application
odbc
The following functions are exported:
commit(Ref, CommitMode) -
[page 16] C omm its or rollbacks a transaction.
commit(Ref, CommitMode, TimeOut) -
ok |
error, Reason
[page 16] C omm its or rollbacks a transaction.
connect(ConnectStr, Options) -
ok, Ref
|
error, Reason
[page 16] O pens a connection to the dat abase.
disconnect(Ref) -
ok |
error, Reason
[page 18] C loses a connection t o a datab ase.
describe table(Ref, Table) -
[page 18] Queries the database to find out the data types of the columns of the
table Table.
describe table(Ref, Table, Timeout) -
ok, Description
|
error,
Reason
[page 18] Queries the database to find out the data types of the columns of the
table Table.
first(Ref) -
[page 18] Returns the first row of t he result set and positions a cursor at this row.
first(Ref, Timeout) -
selected, ColNames, Rows
|
error, Reason
[page 18] Returns the first row of t he result set and positions a cursor at this row.
last(Ref) -
[page 18] Returns the last row of t he result set and positions a cursor at this row.
last(Ref, TimeOut) -
selected, ColNames, Rows
|
error, Reason
[page 18] Returns the last row of t he result set and positions a cursor at this row.
next(Ref) -
[page 19] Returns the next row of the result set relative the current cursor position
and positions the cursor at this row.
next(Ref, TimeOut) -
selected, ColNames, Rows
|
error, Reason
[page 19] Returns the next row of the result set relative the current cursor position
and positions the cursor at this row.
13Erlang O D BC application
8/13/2019 Erlang Odbc2.0
http://slidepdf.com/reader/full/erlang-odbc20 18/32
Erlang ODBC Reference Manual
param query(Ref, SQLQuery, Params) -
[page 19] Executes a parameterized SQ L q uery.
param query(Ref, SQLQuery, Params, TimeOut) - ResultTuple | error,
Reason
[page 19] Executes a parameterized SQ L q uery.
prev(Ref) -
[page 19] Returns the previous row of the result set relative the current cursor
position and positions the cursor at this row.
prev(ConnectionReference, TimeOut) -
selected, ColNames, Rows
|
error, Reason
[page 19] Returns the previous row of the result set relative the current cursor
position and positions the cursor at this row.
start() -
[page 20] St arts the o db application.
start(Type) -
ok |
error, Reason
[page 20] St arts the o db application.
stop() -
ok[page 20] Stops the od bc application.
sql query(Ref, SQLQuery) -
[page 20] Executes a SQL query or a batch of SQL queries. If it is a SELECT
query t he result set is returned, on t he format
selected, ColNames, Rows
. For
other q uery types the tuple
updated, NRows
is returned, and for batched queries,
if the driver supports them, this function can also return a list of result tuples.
sql query(Ref, SQLQuery, TimeOut) -
ResultTuple | [ResultTuple]
|
error, Reason
[page 20] Executes a SQL query or a batch of SQL queries. If it is a SELECT
query t he result set is returned, on t he format
selected, ColNames, Rows
. For
other q uery types the tuple
updated, NRows
is returned, and for batched queries,
if the driver supports them, this function can also return a list of result tuples.
select count(Ref, SelectQuery) -
[page 20] Executes a SQ L SELEC T query and associates the result set w ith th e
connection. A cursor is positioned before the fi rst row in the result set and the
tuple
ok, NrRows
is returned.
select count(Ref, SelectQuery, TimeOut) -
ok, NrRows
|
error,
Reason
[page 20] Executes a SQ L SELEC T query and associates the result set w ith th e
connection. A cursor is positioned before the fi rst row in the result set and the
tuple
ok, NrRows
is returned.
select(Ref, Position, N) -
[page 21] Selects Nconsecutive rows of the result set.
select(Ref, Position, N, TimeOut) -
selected, ColNames, Rows
|
error, Reason
[page 21] Selects Nconsecutive rows of the result set.
14 Erlang O D BC application
8/13/2019 Erlang Odbc2.0
http://slidepdf.com/reader/full/erlang-odbc20 19/32
Erlang ODBC Reference Manual odbc
odbc
Erlang Module
This application provides an Erlang interface to communicate w ith relational
SQ L-databa ses. It is built on top o f Microsofts O D BC interface and th erefore requires
that you have an OD BC driver to the database that you w ant to connect to.
Note:The functions first/[1,2], last/[1,2], next/[1,2], prev[1,2] and
select/[3,4] assumes there is a result set associated w ith t he connection to w ork
on. Calling the function select count/[2,3] associates such a result set with the
connection. Calling select count again will remove the current result set association
and create a new one. C alling a function which do se not operate on an a ssociated
result sets, such as sql query/[2,3], will remove the current result set association.
Alas some drivers only support sequential traversal of the result set, e.i. they do not
support w hat in the O D BC w orld is know n as scrollable cursors. This will have the
effect that functions such as first/[1,2], last/[1,2], prev[1,2], etc w ill return
error, driver does not support function
COMMON DATA TYPESHere follows type definitions that are used by more than one function in the O D BC
API.
Note:The type TimeOut has the default value infinity, so for instance:
commit(Ref, CommitMode) is the same as commit(Ref, CommitMode, infinity). If
the t imeout expires the client w ill exit w ith t he reason timeout.
connection_reference() - as returned by connect/2time_out() = milliseconds() | infinity
milliseconds() = integer() >= 0
common_reason() = connection_closed | term() - some kind of
explanation of what went wrong
string() = list of ASCII characters
col_name() = string() - Name of column in the result set
15Erlang O D BC application
8/13/2019 Erlang Odbc2.0
http://slidepdf.com/reader/full/erlang-odbc20 20/32
odbc Erlang ODBC Reference Manual
col_names() - [col_name()] - e.g. a list of the names of the
selected columns in the result set.
row() = {value()} - Tuple of column values e.g. one row of the
result set.
value() = null | term() - A column value.
rows() = [row()] - A list of rows from the result set.
result_tuple() =
{updated, n_rows()} | {selected, col_names(), rows()}
n_rows() = integer() - The number of affected rows for UPDATE,
INSERT, or DELETE queries. For other query types the value
is driver defined, and hence should be ignored.
odbc_data_type() = sql_integer | sql_smallint | sql_tinyint |
{sql_decimal, precison(), scale()} |
{sql_numeric, precison(), scale()} |
{sql_char, size()} | {sql_varchar, size()} | {sql_float, precision()} |
{sql_float, precision()} | sql_real | sql_double | sql_bit | atom()
precision() = integer()
scale() = integer()
size() = integer()
ERROR HANDLING
The error h andling strat egy and possible errors sources are described in t he Erlang
O D BC User’s G uide. [page 8]
Exports
commit(Ref, CommitMode) -
commit(Ref, CommitMode, TimeOut) - ok | error, Reason
Types:
Ref = connection reference()
C ommitMode = commit | rollback
TimeO ut = time out()
Reason = not an explicit commit connection |
process not owner of odbc connection | common reason()
Commits or rollbacks a transaction. Needed on connections where automatic commit is
turned off.
connect(ConnectStr, Options) -
ok, Ref
|
error, Reason
Types:
16 Erlang O D BC application
8/13/2019 Erlang Odbc2.0
http://slidepdf.com/reader/full/erlang-odbc20 21/32
Erlang ODBC Reference Manual odbc
ConnectStr = string()
An example of a connection
string:"DSN=sql-server;UID=alladin;PWD=sesame"where DSN is your OD BC
D ata So urce Nam e, U ID is a dat abase user id and P WD is the passw ord for that user.
These are usually the attributes required in the connection string, but some drivers
have oth er driver specific attributes, for
example"DSN=Oracle8;DBQ=gandalf;UID=alladin;PWD=sesame"where DBQ is
your TNSN AMES.O RA entry name e.g. some O racle specific confi guration at tribute.
Options = [] | [opt ion()]
All options has default values.
option() =
auto commit, auto commit mode()
|
timeout, milliseconds()
|
tuple row, tuple mode()
|
scrollable cursors, use srollable cursors()
|
trace driver, trace mode()
The default timeout is infinity
auto commit mode() = on | o ff
D efault is on.
tuple mode() = on | o ff
D efault is on. The option is deprecated and should not be used in new code.
use srollable cursors() = on | o ff
D efault is on.
trace mode() = on | o ff
D efault is off.
Ref = connection reference() - should be used to acess the connection.
Reason = port program executable not found | common reason()
O pens a connection to the dat abase. The connection is associated w ith t he process that
created it and can only be accessed t hrough it. This funtion may spaw n new processes
to handle the connection. These processes w ill terminate if the process that created the
connection dies or if y ou call disconnect/1.
If auto mat ic commit mode is turned on, each q uery will be considered as an individual
transaction and will be automaticly commited after it has been executed. If you want
more than one q uery t o be part of t he same transaction the automatic commit m ode
should be turned of f. Then you w ill have to ca ll commit /3 explicitly t o end a
transaction.
As default result sets are returned as a lists of tuples. The TupleMode option still exists
to keep some degree of backwards com patiblity. If t he option is set to off, result sets will
be returned as a lists of lists instead of a lists of tuples.
Scrollable cursors are nice but causes some overhead. For some connections speed
might be m ore important t han fl exible data access and th en you can d isable scrollable
cursor for a connection, limiting the API but gaining speed
If trace mode is turned on this tells the O D BC driver to w rite a trace log to the fi le
SQ L.LO G that is placed in the current directory of t he erlang emulator. Thisinformation may be useful if you suspect there might be a bug in th e erlang OD BC
application, and it might be relevant for you to send this file to our support. Otherwise
you w ill probably not have much use of this.
Note:For more information about the ConnectStr see description of the function
SQ LD riverConnect in [1].
17Erlang O D BC application
8/13/2019 Erlang Odbc2.0
http://slidepdf.com/reader/full/erlang-odbc20 22/32
8/13/2019 Erlang Odbc2.0
http://slidepdf.com/reader/full/erlang-odbc20 23/32
Erlang ODBC Reference Manual odbc
next(Ref) -
next(Ref, TimeOut) - selected, ColNames, Rows | error, Reason
Types:
Ref = connection reference()
TimeO ut = time out()
C olNames = col names()
Rows = rows()
Reason = result set does not exist | process not owner of odbc connection |
common reason()
Returns the next row of the result set relative the current cursor position and positions
the cursor at this row. If the cursor is positioned at the last row of t he result set w hen
this function is called the returned value w ill be
selected, ColNames,[]
e.i. the list
of row values is empty indicating that there is no more data to fetch.
param query(Ref, SQLQuery, Params) -
param query(Ref, SQLQuery, Params, TimeOut) - ResultTuple | error, Reason
Types:
Ref = connection reference()
SQ LQ uery = string() - a SQ L q uery w ith parameter ma rkers/place holders in form of
question marks.
Params = [
odbc data type(), [value()]
]
TimeO ut = time out()
Values = term() - Must b e consistent w ith th e Erlang data type tha t corresponds to
the OD BC data type OD BC D ataType
Executes a parameterized SQ L q uery. For an example see the ”U sing the Erlang API”
[page 3] in the Erlang O D BC User’s G uide.
Note:Use the function describe t able/[2,3] to find out w hich OD BC data t ype that is
expected for each column of t hat table. If a column has a da ta type tha t is described
w ith capital letters, alas it is not currently supported by the param query function.
Too know w hich Erlang data ty pe corresponds to an O D BC data type see the Erlang
to O D BC data typemapping [page 6] in the U ser’s G uide.
prev(Ref) -
prev(ConnectionReference, TimeOut) -
selected, ColNames, Rows
|
error, Reason
Types:
Ref = connection reference()
TimeO ut = time out()
C olNames = col names()
Rows = rows()
Reason = result set does not exist | driver does not support function |
scrollable cursors disabled | process not owner of odbc connection |
common reason()
19Erlang O D BC application
8/13/2019 Erlang Odbc2.0
http://slidepdf.com/reader/full/erlang-odbc20 24/32
odbc Erlang ODBC Reference Manual
Returns the previous row of the result set relative the current cursor position and
positions the cursor at this row.
start() -
start(Type) - ok | error, Reason
Types:
Type = permanent | t ransient | temporary
Starts the odbc application. D efault type is temporary. [application(3)]
stop() -
ok
Stops th e odbc application. [application(3)]
sql query(Ref, SQLQuery) -
sql query(Ref, SQLQuery, TimeOut) - ResultTuple | [ResultTuple] | error, Reason
Types:
Ref = connection reference()
SQ LQ uery = string() - The string may be compo sed b y several SQ L-queries
separated by a ”;”, this is called a batch.
TimeO ut = time out()
ResultTuple = result tu ple()
Reason = process not owner of odbc connection | common reason()
Executes a SQL query or a batch of SQL queries. If it is a SELECT query the result set
is returned, o n th e format
selected, ColNames, Rows
. For other query types the
tuple
updated, NRows
is returned, and fo r batch ed q ueries, if the driver supports
them, this function can also return a list of result tuples.
Note:Some drivers may not have the information of the number of affected rows available
and then the return value may be
updated, undefined
.
The list of column names is ordered in the same way as the list of values of a row, e.g.
the fi rst ColName is associated w ith t he fi rst Value in a Row.
select count(Ref, SelectQuery) -
select count(Ref, SelectQuery, TimeOut) -
ok, NrRows
|
error, Reason
Types:
Ref = connection reference()
SelectQ uery = string()
SQ L SELECT query.
TimeO ut = time out()
NrRows = n rows()
Reason = process not owner of odbc connection | common reason()
20 Erlang O D BC application
8/13/2019 Erlang Odbc2.0
http://slidepdf.com/reader/full/erlang-odbc20 25/32
Erlang ODBC Reference Manual odbc
Executes a SQ L SELEC T query and associates the result set w ith th e connection. A
cursor is positioned before the first row in the result set and the t uple ok, NrRows
is
returned.
Note:Some drivers may not h ave the information of the number of row s in th e result set,
then NrRows w ill have the value undefined.
select(Ref, Position, N) -
select(Ref, Position, N, TimeOut) -
selected, ColNames, Rows
|
error, Reason
Types:
Ref = connection reference()
Position = next |
relative, Pos
|
absolute, Pos
Selection strategy, determines at which row in the result set to start the selection.
Pos = integer()Should indicate a row number in the result set. When used together w ith the o ption
relativeit w ill be used as an offset from the current cursor position, w hen used
together w ith the option absoluteit w ill be interpreted as a row number.
N = integer()
TimeO ut = time out()
Reason = result set does not exist | driver does not support function |
scrollable cursors disabled | process not owner of odbc connection |
common reason()
Selects N consecutive rows of the result set. If Position is next it is semant icly
equivalent of calling next/[1,2]N times. If Position is
relative, Pos
, Pos will be
used as an offset from t he current cursor position to determine the fi rst selected row. IfPosition is
absolute, Pos
, Pos will be the number of the first row selected. After
this function has returned the cursor is positioned at the last selected row. If there is less
then N rows left of t he result set the length of Rows w ill be less than N. If the first row to
select h appens to be beyond the last row of t he result set, the returned value will be
selected, ColNames,[]
e.i. t he list of row values is empty indicating that there is
no more data to fetch.
REFERENCES
[1]: Microsoft O D BC 3.0, Programm er’s Reference and SD K G uide
See also htt p://msdn.microsoft .com /
21Erlang O D BC application
8/13/2019 Erlang Odbc2.0
http://slidepdf.com/reader/full/erlang-odbc20 26/32
odbc Erlang ODBC Reference Manual
22 Erlang O D BC application
8/13/2019 Erlang Odbc2.0
http://slidepdf.com/reader/full/erlang-odbc20 27/32
List of Figures
1. 1 Arch it ect ure o f t he Erla ng o db c a pp lica tio n . . . . . . . . . . . . . . . . . . . . . . . . . . 10
23Erlang O D BC application
8/13/2019 Erlang Odbc2.0
http://slidepdf.com/reader/full/erlang-odbc20 28/32
List of Figures
24 Erlang O D BC application
8/13/2019 Erlang Odbc2.0
http://slidepdf.com/reader/full/erlang-odbc20 29/32
List of Tables
1.1 Mapping of OD BC data types to the Erlang data types returned to the Erlang application. 7
1.2 Mapping of extended O D BC data t ypes to the Erlang data types returned to the Erlang
applicat ion. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
25Erlang O D BC application
8/13/2019 Erlang Odbc2.0
http://slidepdf.com/reader/full/erlang-odbc20 30/32
List of Tables
26 Erlang O D BC application
8/13/2019 Erlang Odbc2.0
http://slidepdf.com/reader/full/erlang-odbc20 31/32
Index of M odules and Functions
Modules are t yped in this way .
Functions are typed in this way.
commit/2
odbc , 16
commit/3
odbc , 16
connect/2
odbc , 16
describe_table/2
odbc , 18
describe_table/3
odbc , 18
disconnect/1
odbc , 18
first/1
odbc , 18
first/2
odbc , 18
last/1
odbc , 18
last/2
odbc , 18
next/1
odbc , 19
next/2odbc , 19
odbc
commit/2, 16
commit/3, 16
connect/2, 16
describe_table/2, 18
describe_table/3, 18
disconnect/1, 18
first/1, 18
first/2, 18
last/1, 18
last/2, 18
next/1, 19
next/2, 19
param_query/3, 19param_query/4, 19
prev/1, 19
prev/2, 19
select/3, 21
select/4, 21
select_count/2, 20
select_count/3, 20
sql_query/2, 20
sql_query/3, 20
start/0, 20
start/1, 20
stop/0, 20
param_query/3
odbc , 19
param_query/4
odbc , 19
prev/1
odbc , 19
prev/2
odbc , 19
select/3odbc , 21
select/4
odbc , 21
select_count/2
odbc , 20
select_count/3
odbc , 20
27Erlang O D BC application
8/13/2019 Erlang Odbc2.0
http://slidepdf.com/reader/full/erlang-odbc20 32/32
Index of Modules and Functions
sql_query/2
odbc , 20
sql_query/3
odbc , 20
start/0
odbc , 20
start/1
odbc , 20
stop/0
odbc , 20