odbc(3erl) Erlang Module Definition odbc(3erl)
NAME
odbc - Erlang ODBC application
DESCRIPTION
This application provides an Erlang interface to communicate with rela-
tional SQL-databases. It is built on top of Microsofts ODBC interface
and therefore requires that you have an ODBC driver to the database
that you want to connect to.
Note:
The functions first/[1,2], last/[1,2], next/[1,2], prev[1,2] and se-
lect/[3,4] assumes there is a result set associated with the connection
to work 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. Calling a
function which dose not operate on an associated 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 what in the ODBC world is known as scrollable
cursors. This will have the effect that functions such as first/[1,2],
last/[1,2], prev[1,2], etc will return {error, driver_does_not_sup-
port_function}
COMMON DATA TYPES
Here follows type definitions that are used by more than one function
in the ODBC API.
Note:
The type TimeOut has the default value infinity, so for instance:
commit(Ref, CommitMode) is the same as commit(Ref, CommitMode, infin-
ity). If the timeout expires the client will exit with the reason time-
out.
connection_reference() - as returned by connect/2
time_out() = milliseconds() | infinity
milliseconds() = integer() >= 0
common_reason() = connection_closed | extended_error() | term() - some kind of
explanation of what went wrong
extended_error() = {string(), integer(), Reason} - extended error type with ODBC
and native database error codes, as well as the base reason that would have been
returned had extended_errors not been enabled.
string() = list of ASCII characters
col_name() = string() - Name of column in the result set
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, precision(), scale()} |
{sql_numeric, precision(), scale()} |
{sql_char, size()} |
{sql_wchar, size()} |
{sql_varchar, size()} |
{sql_wvarchar, size()}|
{sql_float, precision()} |
{sql_wlongvarchar, size()} |
{sql_float, precision()} |
sql_real | sql_double | sql_bit | atom()
precision() = integer()
scale() = integer()
size() = integer()
ERROR HANDLING
The error handling strategy and possible errors sources are described
in the Erlang ODBC User's Guide.
EXPORTS
commit(Ref, CommitMode) ->
commit(Ref, CommitMode, TimeOut) -> ok | {error, Reason}
Types:
Ref = connection_reference()
CommitMode = commit | rollback
TimeOut = 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:
ConnectStr = string()
An example of a connection string: "DSN=sql-server;UID=al-
addin;PWD=sesame" where DSN is your ODBC Data Source Name,
UID is a database user id and PWD is the password for that
user. These are usually the attributes required in the con-
nection string, but some drivers have other driver specific
attributes, for example "DSN=Oracle8;DBQ=gandalf;UID=al-
addin;PWD=sesame" where DBQ is your TNSNAMES.ORA entry name
e.g. some Oracle specific configuration attribute.
Options = [] | [option()]
All options has default values.
option() = {auto_commit, on | off} | {timeout, millisec-
onds()} | {binary_strings, on | off} | {tuple_row, on | off}
| {scrollable_cursors, on | off} | {trace_driver, on | off} |
{extended_errors, on | off}
Ref = connection_reference() - should be used to access the
connection.
Reason = port_program_executable_not_found | common_reason()
Opens a connection to the database. The connection is associated
with the process that created it and can only be accessed
through it. This function may spawn new processes to handle the
connection. These processes will terminate if the process that
created the connection dies or if you call disconnect/1.
If automatic commit mode is turned on, each query will be con-
sidered as an individual transaction and will be automatically
committed after it has been executed. If you want more than one
query to be part of the same transaction the automatic commit
mode should be turned off. Then you will have to call commit/3
explicitly to end a transaction.
The default timeout is infinity
>If the option binary_strings is turned on all strings will be
returned as binaries and strings inputed to param_query will be
expected to be binaries. The user needs to ensure that the bi-
nary is in an encoding that the database expects. By default
this option is turned off.
As default result sets are returned as a lists of tuples. The
TupleMode option still exists to keep some degree of backwards
compatibility. If the 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 more important than flexible data ac-
cess and then you can disable scrollable cursor for a connec-
tion, limiting the API but gaining speed.
Note:
Turning the scrollable_cursors option off is noted to make old
odbc-drivers able to connect that will otherwhise fail.
If trace mode is turned on this tells the ODBC driver to write a
trace log to the file SQL.LOG that is placed in the current di-
rectory of the erlang emulator. This information may be useful
if you suspect there might be a bug in the erlang ODBC applica-
tion, and it might be relevant for you to send this file to our
support. Otherwise you will probably not have much use of this.
Note:
For more information about the ConnectStr see description of the
function SQLDriverConnect in [1].
The extended_errors option enables extended ODBC error informa-
tion when an operation fails. Rather than returning {error, Rea-
son}, the failing function will reutrn {error, {ODBCErrorCode,
NativeErrorCode, Reason}}. Note that this information is proba-
bly of little use when writing database-independent code, but
can be of assistance in providing more sophisticated error han-
dling when dealing with a known underlying database.
* ODBCErrorCode is the ODBC error string returned by the ODBC
driver.
* NativeErrorCode is the numberic error code returned by the
underlying database. The possible values and their meanings
are dependent on the database being used.
* Reason is as per the Reason field when extended errors are
not enabled.
Note:
The current implementation spawns a port programm written in C
that utilizes the actual ODBC driver. There is a default timeout
of 5000 msec for this port programm to connect to the Erlang
ODBC application. This timeout can be changed by setting an ap-
plication specific environment variable 'port_timeout' with the
number of milliseconds for the ODBC application. E.g.: [{odbc,
[{port_timeout, 60000}]}] to set it to 60 seconds.
disconnect(Ref) -> ok | {error, Reason}
Types:
Ref = connection_reference()
Reason = process_not_owner_of_odbc_connection | extended_er-
ror()
Closes a connection to a database. This will also terminate all
processes that may have been spawned when the connection was
opened. This call will always succeed. If the connection cannot
be disconnected gracefully it will be brutally killed. However
you may receive an error message as result if you try to discon-
nect a connection started by another process.
describe_table(Ref, Table) ->
describe_table(Ref, Table, Timeout) -> {ok, Description} | {error, Rea-
son}
Types:
Ref = connection_reference()
Table = string() - Name of databas table.
TimeOut = time_out()
Description = [{col_name(), odbc_data_type()}]
Reason = common_reason()
Queries the database to find out the ODBC data types of the col-
umns of the table Table.
first(Ref) ->
first(Ref, Timeout) -> {selected, ColNames, Rows} | {error, Reason}
Types:
Ref = connection_reference()
TimeOut = time_out()
ColNames = col_names()
Rows = rows()
Reason = result_set_does_not_exist | driver_does_not_sup-
port_function | scrollable_cursors_disabled |
process_not_owner_of_odbc_connection | common_reason()
Returns the first row of the result set and positions a cursor
at this row.
last(Ref) ->
last(Ref, TimeOut) -> {selected, ColNames, Rows} | {error, Reason}
Types:
Ref = connection_reference()
TimeOut = time_out()
ColNames = col_names()
Rows = rows()
Reason = result_set_does_not_exist | driver_does_not_sup-
port_function | scrollable_cursors_disabled |
process_not_owner_of_odbc_connection | common_reason()
Returns the last row of the result set and positions a cursor at
this row.
next(Ref) ->
next(Ref, TimeOut) -> {selected, ColNames, Rows} | {error, Reason}
Types:
Ref = connection_reference()
TimeOut = time_out()
ColNames = 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 cur-
sor position and positions the cursor at this row. If the cursor
is positioned at the last row of the result set when this func-
tion is called the returned value will be {selected, Col-
Names,[]} 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()
SQLQuery = string() - a SQL query with parameter mark-
ers/place holders in form of question marks.
Params = [{odbc_data_type(), [value()]}] |[{odbc_data_type(),
in_or_out(), [value()]}]
in_or_out = in | out | inout
Defines IN, OUT, and IN OUT Parameter Modes for stored pro-
cedures.
TimeOut = time_out()
Values = term() - Must be consistent with the Erlang data
type that corresponds to the ODBC data type ODBCDataType
Executes a parameterized SQL query. For an example see the "Us-
ing the Erlang API" in the Erlang ODBC User's Guide.
Note:
Use the function describe_table/[2,3] to find out which ODBC
data type that is expected for each column of that table. If a
column has a data type that is described with capital letters,
alas it is not currently supported by the param_query function.
Too know which Erlang data type corresponds to an ODBC data type
see the Erlang to ODBC data type mapping in the User's Guide.
prev(Ref) ->
prev(ConnectionReference, TimeOut) -> {selected, ColNames, Rows} | {er-
ror, Reason}
Types:
Ref = connection_reference()
TimeOut = time_out()
ColNames = col_names()
Rows = rows()
Reason = result_set_does_not_exist | driver_does_not_sup-
port_function | scrollable_cursors_disabled |
process_not_owner_of_odbc_connection | common_reason()
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 | transient | temporary
Starts the odbc application. Default type is temporary. See ap-
plication(3erl)
stop() -> ok
Stops the odbc application. See application(3erl)
sql_query(Ref, SQLQuery) ->
sql_query(Ref, SQLQuery, TimeOut) -> ResultTuple | [ResultTuple] |{er-
ror, Reason}
Types:
Ref = connection_reference()
SQLQuery = string() - The string may be composed by several
SQL-queries separated by a ";", this is called a batch.
TimeOut = time_out()
ResultTuple = result_tuple()
Reason = process_not_owner_of_odbc_connection | common_rea-
son()
Executes a SQL query or a batch of SQL queries. If it is a SE-
LECT query the result set is returned, on the format {selected,
ColNames, Rows}. For other query types the tuple {updated,
NRows} is returned, and for batched queries, if the driver sup-
ports them, this function can also return a list of result tu-
ples.
Note:
Some drivers may not have the information of the number of af-
fected 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 first ColName is associated with
the first Value in a Row.
select_count(Ref, SelectQuery) ->
select_count(Ref, SelectQuery, TimeOut) -> {ok, NrRows} | {error, Rea-
son}
Types:
Ref = connection_reference()
SelectQuery = string()
SQL SELECT query.
TimeOut = time_out()
NrRows = n_rows()
Reason = process_not_owner_of_odbc_connection | common_rea-
son()
Executes a SQL SELECT query and associates the result set with
the connection. A cursor is positioned before the first row in
the result set and the tuple {ok, NrRows} is returned.
Note:
Some drivers may not have the information of the number of rows
in the result set, then NrRows will have the value undefined.
select(Ref, Position, N) ->
select(Ref, Position, N, TimeOut) -> {selected, ColNames, Rows} | {er-
ror, 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 with the option relativeit will be used as an off-
set from the current cursor position, when used together
with the option absoluteit will be interpreted as a row
number.
N = integer()
TimeOut = time_out()
Reason = result_set_does_not_exist | driver_does_not_sup-
port_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 semantically equivalent of calling next/[1,2] N
times. If Position is {relative, Pos}, Pos will be used as an
offset from the current cursor position to determine the first
selected row. If Position is {absolute, Pos}, Pos will be the
number of the first row selected. After this function has re-
turned the cursor is positioned at the last selected row. If
there is less then N rows left of the result set the length of
Rows will be less than N. If the first row to select happens to
be beyond the last row of the result set, the returned value
will be {selected, ColNames,[]} e.i. the list of row values is
empty indicating that there is no more data to fetch.
REFERENCES
[1]: Microsoft ODBC 3.0, Programmer's Reference and SDK Guide
See also http://msdn.microsoft.com/
Ericsson AB odbc 2.13 odbc(3erl)