odbc(3erl) Erlang Module Definition odbc(3erl)NAME
odbc - Erlang ODBC application
DESCRIPTION
This application provides an Erlang interface to communicate with relational SQL-data-
bases. 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 select/[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 func-
tion 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_support_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, infinity). If the timeout
expires the client will exit with the reason timeout.
connection_reference() - as returned by connect/2
time_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
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_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_con-
nection | 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=aladdin;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 connection string, but some drivers have other driver specific
attributes, for example "DSN=Oracle8;DBQ=gandalf;UID=aladdin;PWD=sesame"
where DBQ is your TNSNAMES.ORA entry name e.g. some Oracle specific configu-
ration attribute.
Options = [] | [option()]
All options has default values.
option() = {auto_commit, on | off} | {timeout, milliseconds()} |
{binary_strings, on | off} | {tuple_row, on | off} | {scrollable_cursors, on
| off} | {trace_driver, 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 considered as an individ-
ual 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 com-
mit 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 binary 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 access and then you can disable scrol-
lable cursor for a connection, limiting the API but gaining speed.
Note:
Turning the scrollable_cursors option off is noted to make old odbc-drivers able to con-
nect 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 directory of the erlang emulator. This information
may be useful if you suspect there might be a bug in the erlang ODBC application, 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].
disconnect(Ref) -> ok | {error, Reason}
Types Ref = connection_reference()
Reason = process_not_owner_of_odbc_connection
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 can not be disconnected gracefully it will be brutally killed. How-
ever you may receive an error message as result if you try to disconnect a connec-
tion started by another process.
describe_table(Ref, Table) ->
describe_table(Ref, Table, Timeout) -> {ok, Description} | {error, Reason}
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 columns 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_support_function |
scrollable_cursors_disabled | process_not_owner_of_odbc_connection | com-
mon_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_support_function |
scrollable_cursors_disabled | process_not_owner_of_odbc_connection | com-
mon_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 cursor position and
positions the cursor at this row. If the cursor is positioned at the last row of
the result set when this function is called 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.
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 markers/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 procedures.
TimeOut = time_out()
Values = term() - Must be consistent with the Erlang data type that corre-
sponds to the ODBC data type ODBCDataType
Executes a parameterized SQL query. For an example see the "Using 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} | {error, Reason}
Types Ref = connection_reference()
TimeOut = time_out()
ColNames = col_names()
Rows = rows()
Reason = result_set_does_not_exist | driver_does_not_support_function |
scrollable_cursors_disabled | process_not_owner_of_odbc_connection | com-
mon_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 application(3erl)
stop() -> ok
Stops the odbc application. See application(3erl)
sql_query(Ref, SQLQuery) ->
sql_query(Ref, SQLQuery, TimeOut) -> ResultTuple | [ResultTuple] |{error, Reason}
Types Ref = connection_reference()
SQLQuery = string() - The string may be composed by several SQL-queries sep-
arated by a ";", this is called a batch.
TimeOut = time_out()
ResultTuple = result_tuple()
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, 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 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 first ColName is associated with the first Value in a Row .
select_count(Ref, SelectQuery) ->
select_count(Ref, SelectQuery, TimeOut) -> {ok, NrRows} | {error, Reason}
Types Ref = connection_reference()
SelectQuery = string()
SQL SELECT query.
TimeOut = time_out()
NrRows = n_rows()
Reason = process_not_owner_of_odbc_connection | common_reason()
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} | {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 with the
option relative it will be used as an offset from the current cursor posi-
tion, when used together with the option absolute it will be interpreted as
a row number.
N = integer()
TimeOut = time_out()
Reason = result_set_does_not_exist | driver_does_not_support_function |
scrollable_cursors_disabled | process_not_owner_of_odbc_connection | com-
mon_reason()
Selects N consecutive rows of the result set. If Position is next it is semanti-
cally 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 returned 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, Col-
Names,[]} 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.10.10 odbc(3erl)