.TH odbc 3 "odbc  2.10.2" "Ericsson AB" "ERLANG MODULE DEFINITION"
.SH MODULE
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\&.
.SS Note:
.LP
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\&. 
.SS Note:
.LP
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\&. 


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

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

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

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

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

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

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

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

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

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

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

.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

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

.nf
 precision() = integer()    
.fi

.nf
 scale() = integer()    
.fi

.nf
 size() = integer()    
.fi
.SH ERROR HANDLING 
.LP
The error handling strategy and possible errors sources are described in the Erlang ODBC User\&'s Guide\&.
.SH EXPORTS
.LP
.B
commit(Ref, CommitMode) ->
.br
.B
commit(Ref, CommitMode, TimeOut) -> ok | {error, Reason} 
.br
.RS
.TP
Types
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
.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
.TP
Types
ConnectStr = string()
.br
  An example of a connection string:\fI"DSN=sql-server;UID=alladin;PWD=sesame"\fRwhere 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=alladin;PWD=sesame"\fRwhere DBQ is your TNSNAMES\&.ORA entry name e\&.g\&. some Oracle specific configuration attribute\&.
.br
Options = [] | [option()]
.br
  All options has default values\&. 
.br
option() = {auto_commit, auto_commit_mode()} | {timeout, milliseconds()} | {tuple_row, tuple_mode()} | {scrollable_cursors, use_srollable_cursors()} | {trace_driver, trace_mode()} 
.br
  The default timeout is infinity 
.br
auto_commit_mode() = on | off 
.br
  Default is on\&.
.br
tuple_mode() = on | off 
.br
  Default is on\&. The option is deprecated and should not be used in new code\&.
.br
use_srollable_cursors() = on | off 
.br
  Default is on\&.
.br
trace_mode() = on | off 
.br
  Default is off\&.
.br
Ref = connection_reference() - should be used to acess the connection\&. 
.br
Reason = port_program_executable_not_found | common_reason()
.br
.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 funtion 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 automaticly commited 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
As default result sets are returned as a lists of tuples\&. The \fITupleMode\fR option still exists to keep some degree of backwards compatiblity\&. 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
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\&.
.SS Note:
.LP
For more information about the \fIConnectStr\fR see description of the function SQLDriverConnect in [1]\&.

.RE
.LP
.B
disconnect(Ref) -> ok | {error, Reason} 
.br
.RS
.TP
Types
Ref = connection_reference()
.br
Reason = process_not_owner_of_odbc_connection
.br
.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
.TP
Types
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
.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
.TP
Types
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
.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
.TP
Types
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
.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
.TP
Types
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
.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
.TP
Types
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
  Defines IN, OUT, and IN OUT Parameter Modes for stored procedures\&.
.br
TimeOut = time_out()
.br
Values = term() - Must be consistent with the Erlang data type that corresponds to the ODBC data type ODBCDataType
.br
.RE
.RS
.LP
Executes a parameterized SQL query\&. For an example see the "Using the Erlang API" in the Erlang ODBC User\&'s Guide\&.
.SS Note:
.LP
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\&.

.RE
.LP
.B
prev(Ref) -> 
.br
.B
prev(ConnectionReference, TimeOut) -> {selected, ColNames, Rows} | {error, Reason} 
.br
.RS
.TP
Types
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
.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
.TP
Types
Type = permanent | transient | temporary 
.br
.RE
.RS
.LP
Starts the odbc application\&. Default type is temporary\&. See application(3) 
.RE
.LP
.B
stop() -> ok 
.br
.RS
.LP
Stops the odbc application\&. See application(3) 
.RE
.LP
.B
sql_query(Ref, SQLQuery) -> 
.br
.B
sql_query(Ref, SQLQuery, TimeOut) -> ResultTuple | [ResultTuple] |{error, Reason}
.br
.RS
.TP
Types
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
.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\&.
.SS Note:
.LP
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
.TP
Types
Ref = connection_reference()
.br
SelectQuery = string()
.br
  SQL SELECT query\&.
.br
TimeOut = time_out()
.br
NrRows = n_rows()
.br
Reason = process_not_owner_of_odbc_connection | common_reason() 
.br
.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\&. 
.SS Note:
.LP
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
.TP
Types
Ref = connection_reference()
.br
Position = next | {relative, Pos} | {absolute, Pos} 
.br
  Selection strategy, determines at which row in the result set to start the selection\&.
.br
Pos = integer() 
.br
  Should indicate a row number in the result set\&. When used together with the option \fIrelative\fRit will be used as an offset from the current cursor position, when used together with the option \fIabsolute\fRit will be interpreted as a row number\&.
.br
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
.RS
.LP
Selects \fIN\fR consecutive rows of the result set\&. If \fIPosition\fR is \fInext\fR it is semanticly 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/