.TH odbc 3erl "odbc 2.12" "Ericsson AB" "Erlang Module Definition"
.SH NAME
odbc \- Erlang ODBC application
.SH DESCRIPTION
.LP
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\&.
.LP

.RS -4
.B
Note:
.RE
The functions \fIfirst/[1,2]\fR\&, \fIlast/[1,2]\fR\&, \fInext/[1,2]\fR\&, \fIprev[1,2]\fR\& and \fIselect/[3,4]\fR\& assumes there is a result set associated with the connection to work on\&. Calling the function \fIselect_count/[2,3]\fR\& 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 \fIsql_query/[2,3]\fR\&, will remove the current result set association\&.
.LP
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 \fIfirst/[1,2]\fR\&, \fIlast/[1,2]\fR\&, \fIprev[1,2]\fR\&, etc will return \fI{error, driver_does_not_support_function}\fR\&

.SH "COMMON DATA TYPES "

.LP
Here follows type definitions that are used by more than one function in the ODBC API\&.
.LP

.RS -4
.B
Note:
.RE
The type \fITimeOut\fR\& has the default value \fIinfinity\fR\&, so for instance: 
.br
commit(Ref, CommitMode) is the same as commit(Ref, CommitMode, infinity)\&. If the timeout expires the client will exit with the reason timeout\&.

.LP
.nf

 connection_reference() - as returned by connect/2    
.fi
.LP
.nf

 time_out() = milliseconds() | infinity    
.fi
.LP
.nf

 milliseconds() = integer() >= 0     
.fi
.LP
.nf

 common_reason() = connection_closed | extended_error() | term() - some kind of
 explanation of what went wrong    
.fi
.LP
.nf

 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. 
.fi
.LP
.nf

 string() = list of ASCII characters    
.fi
.LP
.nf

 col_name() = string() - Name of column in the result set    
.fi
.LP
.nf

 col_names() - [col_name()] - e.g. a list of the names of the
           selected columns in the result set.     
.fi
.LP
.nf

 row() = {value()} - Tuple of column values e.g. one row of the
           result set.     
.fi
.LP
.nf

 value() = null | term() - A column value.     
.fi
.LP
.nf

 rows() = [row()] - A list of rows from the result set.    
.fi
.LP
.nf

 result_tuple() =
      {updated, n_rows()} | {selected, col_names(), rows()}    
.fi
.LP
.nf

 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.    
.fi
.LP
.nf

 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()
    
.fi
.LP
.nf

 precision() = integer()    
.fi
.LP
.nf

 scale() = integer()    
.fi
.LP
.nf

 size() = integer()    
.fi
.SH "ERROR HANDLING "

.LP
The error handling strategy and possible errors sources are described in the Erlang ODBC \fBUser\&'s Guide\&.\fR\&
.SH EXPORTS
.LP
.B
commit(Ref, CommitMode) ->
.br
.B
commit(Ref, CommitMode, TimeOut) -> ok | {error, Reason} 
.br
.RS
.LP
Types:

.RS 3
Ref = connection_reference() 
.br
CommitMode = commit | rollback
.br
TimeOut = time_out()
.br
Reason = not_an_explicit_commit_connection | process_not_owner_of_odbc_connection | common_reason()
.br
.RE
.RE
.RS
.LP
Commits or rollbacks a transaction\&. Needed on connections where automatic commit is turned off\&.
.RE

.LP
.B
connect(ConnectStr, Options) -> {ok, Ref} | {error, Reason} 
.br
.RS
.LP
Types:

.RS 3
ConnectStr = string()
.br
.RS 2
An example of a connection string: \fI"DSN=sql-server;UID=aladdin;PWD=sesame"\fR\& 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 \fI"DSN=Oracle8;DBQ=gandalf;UID=aladdin;PWD=sesame"\fR\& where DBQ is your TNSNAMES\&.ORA entry name e\&.g\&. some Oracle specific configuration attribute\&.
.RE
Options = [] | [option()]
.br
.RS 2
All options has default values\&. 
.RE
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} 
.br
Ref = connection_reference() - should be used to access the connection\&. 
.br
Reason = port_program_executable_not_found | common_reason()
.br
.RE
.RE
.RS
.LP
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\&.
.LP
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\&.
.LP
The default timeout is infinity
.LP
>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\&.
.LP
As default result sets are returned as a lists of tuples\&. The \fITupleMode\fR\& 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\&.
.LP
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\&.
.LP

.RS -4
.B
Note:
.RE
Turning the scrollable_cursors option off is noted to make old odbc-drivers able to connect that will otherwhise fail\&.

.LP
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\&.
.LP

.RS -4
.B
Note:
.RE
For more information about the \fIConnectStr\fR\& see description of the function SQLDriverConnect in [1]\&.

.LP
The \fIextended_errors\fR\& option enables extended ODBC error information when an operation fails\&. Rather than returning \fI{error, Reason}\fR\&, the failing function will reutrn \fI{error, {ODBCErrorCode, NativeErrorCode, Reason}}\fR\&\&. 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\&.
.RS 2
.TP 2
*
\fIODBCErrorCode\fR\& is the ODBC error string returned by the ODBC driver\&.
.LP
.TP 2
*
\fINativeErrorCode\fR\& is the numberic error code returned by the underlying database\&. The possible values and their meanings are dependent on the database being used\&.
.LP
.TP 2
*
\fIReason\fR\& is as per the \fIReason\fR\& field when extended errors are not enabled\&.
.LP
.RE

.LP

.RS -4
.B
Note:
.RE
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\&.

.RE

.LP
.B
disconnect(Ref) -> ok | {error, Reason} 
.br
.RS
.LP
Types:

.RS 3
Ref = connection_reference()
.br
Reason = process_not_owner_of_odbc_connection | extended_error()
.br
.RE
.RE
.RS
.LP
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\&. 
.RE

.LP
.B
describe_table(Ref, Table) -> 
.br
.B
describe_table(Ref, Table, Timeout) -> {ok, Description} | {error, Reason} 
.br
.RS
.LP
Types:

.RS 3
Ref = connection_reference()
.br
Table = string() - Name of databas table\&.
.br
TimeOut = time_out()
.br
Description = [{col_name(), odbc_data_type()}]
.br
Reason = common_reason()
.br
.RE
.RE
.RS
.LP
Queries the database to find out the ODBC data types of the columns of the table \fITable\fR\&\&.
.RE

.LP
.B
first(Ref) ->
.br
.B
first(Ref, Timeout) -> {selected, ColNames, Rows} | {error, Reason} 
.br
.RS
.LP
Types:

.RS 3
Ref = connection_reference()
.br
TimeOut = time_out()
.br
ColNames = col_names() 
.br
Rows = rows()
.br
Reason = result_set_does_not_exist | driver_does_not_support_function | scrollable_cursors_disabled | process_not_owner_of_odbc_connection | common_reason() 
.br
.RE
.RE
.RS
.LP
Returns the first row of the result set and positions a cursor at this row\&.
.RE

.LP
.B
last(Ref) ->
.br
.B
last(Ref, TimeOut) -> {selected, ColNames, Rows} | {error, Reason} 
.br
.RS
.LP
Types:

.RS 3
Ref = connection_reference()
.br
TimeOut = time_out()
.br
ColNames = col_names() 
.br
Rows = rows()
.br
Reason = result_set_does_not_exist | driver_does_not_support_function | scrollable_cursors_disabled | process_not_owner_of_odbc_connection | common_reason() 
.br
.RE
.RE
.RS
.LP
Returns the last row of the result set and positions a cursor at this row\&.
.RE

.LP
.B
next(Ref) -> 
.br
.B
next(Ref, TimeOut) -> {selected, ColNames, Rows} | {error, Reason} 
.br
.RS
.LP
Types:

.RS 3
Ref = connection_reference()
.br
TimeOut = time_out()
.br
ColNames = col_names() 
.br
Rows = rows()
.br
Reason = result_set_does_not_exist | process_not_owner_of_odbc_connection | common_reason() 
.br
.RE
.RE
.RS
.LP
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 \fI{selected, ColNames,[]}\fR\& e\&.i\&. the list of row values is empty indicating that there is no more data to fetch\&. 
.RE

.LP
.B
param_query(Ref, SQLQuery, Params) -> 
.br
.B
param_query(Ref, SQLQuery, Params, TimeOut) -> ResultTuple | {error, Reason} 
.br
.RS
.LP
Types:

.RS 3
Ref = connection_reference()
.br
SQLQuery = string() - a SQL query with parameter markers/place holders in form of question marks\&.
.br
Params = [{odbc_data_type(), [value()]}] |[{odbc_data_type(), in_or_out(), [value()]}] 
.br
in_or_out = in | out | inout
.br
.RS 2
Defines IN, OUT, and IN OUT Parameter Modes for stored procedures\&.
.RE
TimeOut = time_out()
.br
Values = term() - Must be consistent with the Erlang data type that corresponds to the ODBC data type ODBCDataType
.br
.RE
.RE
.RS
.LP
Executes a parameterized SQL query\&. For an example see the \fB"Using the Erlang API"\fR\& in the Erlang ODBC User\&'s Guide\&.
.LP

.RS -4
.B
Note:
.RE
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 \fBmapping\fR\& in the User\&'s Guide\&.

.RE

.LP
.B
prev(Ref) -> 
.br
.B
prev(ConnectionReference, TimeOut) -> {selected, ColNames, Rows} | {error, Reason} 
.br
.RS
.LP
Types:

.RS 3
Ref = connection_reference()
.br
TimeOut = time_out()
.br
ColNames = col_names() 
.br
Rows = rows()
.br
Reason = result_set_does_not_exist | driver_does_not_support_function | scrollable_cursors_disabled | process_not_owner_of_odbc_connection | common_reason() 
.br
.RE
.RE
.RS
.LP
Returns the previous row of the result set relative the current cursor position and positions the cursor at this row\&.
.RE

.LP
.B
start() -> 
.br
.B
start(Type) -> ok | {error, Reason}
.br
.RS
.LP
Types:

.RS 3
Type = permanent | transient | temporary 
.br
.RE
.RE
.RS
.LP
Starts the odbc application\&. Default type is temporary\&. \fBSee application(3erl)\fR\& 
.RE

.LP
.B
stop() -> ok 
.br
.RS
.LP
Stops the odbc application\&. \fBSee application(3erl)\fR\& 
.RE

.LP
.B
sql_query(Ref, SQLQuery) -> 
.br
.B
sql_query(Ref, SQLQuery, TimeOut) -> ResultTuple | [ResultTuple] |{error, Reason}
.br
.RS
.LP
Types:

.RS 3
Ref = connection_reference()
.br
SQLQuery = string() - The string may be composed by several SQL-queries separated by a ";", this is called a batch\&. 
.br
TimeOut = time_out()
.br
ResultTuple = result_tuple() 
.br
Reason = process_not_owner_of_odbc_connection | common_reason() 
.br
.RE
.RE
.RS
.LP
Executes a SQL query or a batch of SQL queries\&. If it is a SELECT query the result set is returned, on the format \fI{selected, ColNames, Rows}\fR\&\&. For other query types the tuple \fI{updated, NRows}\fR\& is returned, and for batched queries, if the driver supports them, this function can also return a list of result tuples\&.
.LP

.RS -4
.B
Note:
.RE
Some drivers may not have the information of the number of affected rows available and then the return value may be \fI{updated, undefined} \fR\&\&.
.LP
The list of column names is ordered in the same way as the list of values of a row, e\&.g\&. the first \fIColName\fR\& is associated with the first \fIValue\fR\& in a \fIRow\fR\&\&.

.br

.RE

.LP
.B
select_count(Ref, SelectQuery) -> 
.br
.B
select_count(Ref, SelectQuery, TimeOut) -> {ok, NrRows} | {error, Reason} 
.br
.RS
.LP
Types:

.RS 3
Ref = connection_reference()
.br
SelectQuery = string()
.br
.RS 2
SQL SELECT query\&.
.RE
TimeOut = time_out()
.br
NrRows = n_rows()
.br
Reason = process_not_owner_of_odbc_connection | common_reason() 
.br
.RE
.RE
.RS
.LP
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 \fI{ok, NrRows}\fR\& is returned\&.
.LP

.RS -4
.B
Note:
.RE
Some drivers may not have the information of the number of rows in the result set, then \fINrRows\fR\& will have the value \fIundefined\fR\&\&.

.RE

.LP
.B
select(Ref, Position, N) ->
.br
.B
select(Ref, Position, N, TimeOut) -> {selected, ColNames, Rows} | {error, Reason} 
.br
.RS
.LP
Types:

.RS 3
Ref = connection_reference()
.br
Position = next | {relative, Pos} | {absolute, Pos} 
.br
.RS 2
Selection strategy, determines at which row in the result set to start the selection\&.
.RE
Pos = integer() 
.br
.RS 2
Should indicate a row number in the result set\&. When used together with the option \fIrelative\fR\&it will be used as an offset from the current cursor position, when used together with the option \fIabsolute\fR\&it will be interpreted as a row number\&.
.RE
N = integer() 
.br
TimeOut = time_out()
.br
Reason = result_set_does_not_exist | driver_does_not_support_function | scrollable_cursors_disabled | process_not_owner_of_odbc_connection | common_reason() 
.br
.RE
.RE
.RS
.LP
Selects \fIN\fR\& consecutive rows of the result set\&. If \fIPosition\fR\& is \fInext\fR\& it is semantically equivalent of calling \fInext/[1,2]\fR\& \fIN\fR\& times\&. If \fIPosition\fR\& is \fI{relative, Pos}\fR\&, \fIPos\fR\& will be used as an offset from the current cursor position to determine the first selected row\&. If \fIPosition\fR\& is \fI{absolute, Pos}\fR\&, \fIPos\fR\& 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 \fIN\fR\& rows left of the result set the length of \fIRows\fR\& will be less than \fIN\fR\&\&. If the first row to select happens to be beyond the last row of the result set, the returned value will be \fI{selected, ColNames,[]}\fR\& e\&.i\&. the list of row values is empty indicating that there is no more data to fetch\&.
.RE

.SH "REFERENCES"

.LP
[1]: Microsoft ODBC 3\&.0, Programmer\&'s Reference and SDK Guide 
.br
See also http://msdn\&.microsoft\&.com/