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}

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, 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()

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. 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}

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 | 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_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}

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()]}]

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.

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 | common_reason()

Returns the previous row of the result set relative the current cursor position and positions the cursor at this row.

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 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.

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.

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 position, 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 | common_reason()

Selects N consecutive rows of the result set. If Position is next it is semanticly 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.