odbc
Erlang ODBC application
This application provides an Erlang interface to communicate with relational 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 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 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_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_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 handling strategy and possible errors sources are described in the Erlang ODBC User's Guide.
Functions
commit(Ref, CommitMode) ->
commit(Ref, CommitMode, TimeOut) -> ok | {error, Reason}
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}
ConnectStr = string()
Options = [] | [option()]
option() = {auto_commit, auto_commit_mode()} | {timeout, milliseconds()} | {tuple_row, tuple_mode()} | {scrollable_cursors, use_scrollable_cursors()} | {trace_driver, trace_mode()}
auto_commit_mode() = on | off
tuple_mode() = on | off
use_scrollable_cursors() = on | off
trace_mode() = on | off
Ref = connection_reference() - should be used to access the connection.
Reason = port_program_executable_not_found | common_reason()
"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 configuration attribute.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 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.
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 scrollable cursor for a connection, limiting the API but gaining speed
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}
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. However you may receive an error message as result if you try to disconnect a connection started by another process.
describe_table(Ref, Table) ->
describe_table(Ref, Table, Timeout) -> {ok, Description} | {error, Reason}
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}
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 | 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}
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 | 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}
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}
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
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 "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 typemapping in the User's Guide.
prev(Ref) ->
prev(ConnectionReference, TimeOut) -> {selected, ColNames, Rows} | {error, Reason}
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 | 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}
Type = permanent | transient | temporary
Starts the odbc application. Default type is temporary. See application(3)
stop() -> ok
Stops the odbc application. See application(3)
sql_query(Ref, SQLQuery) ->
sql_query(Ref, SQLQuery, TimeOut) -> ResultTuple | [ResultTuple] |{error, Reason}
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_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 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 first ColName
is
associated with the first Value
in a Row
.
select_count(Ref, SelectQuery) ->
select_count(Ref, SelectQuery, TimeOut) -> {ok, NrRows} | {error, Reason}
Ref = connection_reference()
SelectQuery = string()
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}
Ref = connection_reference()
Position = next | {relative, Pos} | {absolute, Pos}
Pos = integer()
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 | common_reason()
relative
it will be used as an offset from the current cursor position, when used together with the option absolute
it will be interpreted as a row number.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 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, 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/