table of contents
other versions
- jessie 1:17.3-dfsg-4+deb8u2
- jessie-backports 1:19.2.1+dfsg-2+deb9u1~bpo8+1
- stretch 1:19.2.1+dfsg-2+deb9u2
- testing 1:21.2.5+dfsg-1
- unstable 1:21.2.6+dfsg-1
- experimental 1:22.0~rc1+dfsg-1
odbc(3erl) | Erlang Module Definition | odbc(3erl) |
NAME¶
odbc - Erlang ODBC applicationDESCRIPTION¶
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:
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) ->
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()
Ref = connection_reference() - should be used to access the connection.
Reason = port_program_executable_not_found | common_reason()
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 configuration
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} | {extended_errors, on | off}
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.
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
scrollable cursor for a connection, limiting the API but gaining speed.
disconnect(Ref) -> ok | {error, Reason}
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 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].
The extended_errors option enables extended ODBC error information when
an operation fails. Rather than returning {error, Reason}, the failing
function will reutrn {error, {ODBCErrorCode, NativeErrorCode, Reason}}.
Note that this information is probably of little use when writing
database-independent code, but can be of assistance in providing more
sophisticated error handling 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 application 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.
Types:
Ref = connection_reference()
Reason = process_not_owner_of_odbc_connection | extended_error()
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) ->
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) ->
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 |
common_reason()
Returns the first row of the result set and positions a cursor at this
row.
last(Ref) ->
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 |
common_reason()
Returns the last row of the result set and positions a cursor at this row.
next(Ref) ->
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) ->
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
Values = term() - Must be consistent with the Erlang data type that corresponds
to the ODBC data type ODBCDataType
Defines IN, OUT, and IN OUT Parameter Modes for stored
procedures.
TimeOut = time_out()
Executes a parameterized SQL query. For an example see the "Using the
Erlang API" in the Erlang ODBC User's Guide.
prev(Ref) ->
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.
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 |
common_reason()
Returns the previous row of the result set relative the current cursor position
and positions the cursor at this row.
start() ->
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) ->
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_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.
select_count(Ref, SelectQuery) ->
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.
Types:
Ref = connection_reference()
SelectQuery = string()
NrRows = n_rows()
Reason = process_not_owner_of_odbc_connection | common_reason()
SQL SELECT query.
TimeOut = time_out()
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.
select(Ref, Position, N) ->
Note:
Some drivers may not have the information of the number of rows in the result
set, then NrRows will have the value undefined.
Types:
Ref = connection_reference()
Position = next | {relative, Pos} | {absolute, Pos}
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()
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 offset from the
current cursor position, when used together with the option absoluteit
will be interpreted as a row number.
N = integer()
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 Guideodbc 2.12 | Ericsson AB |