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.
The functions |
Here follows type definitions that are used by more than one function in the ODBC API.
The type |
ConnectionReference - as returned by connect/2
TimeOut = Milliseconds | infinity
Milliseconds = integer()
Reason = term() - some kind of explanation of what went wrong
String = list of ASCII characters
ColName = String - Name of column in the result set
ColNames - [ColName] - e.g. a list of the names of the selected columns in the result set.
Row = [Value] - List 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.
commit(ConnectionReference, CommitMode) ->
commit(ConnectionReference, CommitMode, TimeOut) ->
ok | {error, Reason}
CommitMode = commit | rollback
See also common data types.
Commits or rollbacks a transaction. Needed on connections where automatic commit is turned off.
connect(ConnectStr, Options) ->
{ok, ConnectionReference} | {error, Reason}
ConnectionReference - should be used to acess the
connection.
ConnectStr
"DSN=sql-server;UID=alladin;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=alladin;PWD=sesame"
where DBQ is your TNSNAMES.ORA entry name e.g. some Oracle
specific configuration attribute. Options = [] | [Option]
Option = {auto_commit, AutoCommitMode} | {timeout,
Milliseconds} | {trace_driver, TraceMode}
AutoCommitMode = on | off
TraceMode = on | off
See also common data types.
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.
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.
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.
For more information about the |
disconnect(ConnectionReference) -> ok
See common data types.
Closes a connection to a database. This will also terminate all processes that may have been spawned when the connection was opened.
first(ConnectionReference) ->
first(ConnectionReference, Timeout) ->
{selected, ColNames, Rows} | {error, Reason}
See common data types.
Returns the first row of the result set and positions a cursor at this row.
last(ConnectionReference) ->
last(ConnectionReference, TimeOut) ->
{selected, ColNames, Rows} | {error, Reason}
See common data types.
Returns the last row of the result set and positions a cursor at this row.
next(ConnectionReference) ->
next(ConnectionReference, TimeOut) ->
{selected, ColNames, Rows} | {error, Reason}
See common data types.
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.
prev(ConnectionReference) ->
prev(ConnectionReference, TimeOut) ->
{selected, ColNames, Rows} | {error, Reason}
See common data types.
Returns the previous row of the result set relative the current cursor position and positions the cursor at this row.
sql_query(ConnectionReference, SQLQuery) ->
sql_query(ConnectionReference, SQLQuery, TimeOut) ->
{updated, NRows} | {selected, ColNames, Rows} | {error, Reason}
SQLQuery = String
NRows = integer()
See also common data types.
Executes a SQL query. 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.
Some drivers may not have the information of the number
of affected rows available and then the return value may
be The list of colum names is ordered in the same way as the
list of values of a row, e.g. the first |
select_count(ConnectionReference, SelectQuery) ->
select_count(ConnectionReference, SelectQuery, TimeOut) ->
{ok, NrRows} | {error, Reason}
SelectQuery = String
NrRows = integer() | undefined
See also common data types.
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 |
select(ConnectionReference, Position, N) ->
select(ConnectionReference, Position, N, TimeOut)
{selected, ColNames, Rows} | {error, Reason}
Position = next | {relative, Pos} | {absolute, Pos}
Pos = integer()
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. See also common data types.
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.
The following functions are deprecated and will disappear in the next release. They are only kept to maintain a temporary backward compatibility to make the transition to the new interface less abrupt. However you should not mix the use of the old and the new interface. This may lead to an unexpected behavior. The deprecated interface also requires the application programmer to use the include file odbc.hrl, this whole file is now deprecated. Here follows a short description of the deprecated interface, for details see odbc (deprecated) documentation.
start_link(Args, Options) ->
start_link(ServerName, Args, Options) -> Result
Functionality provided by connect/2.
stop(Server) ->
stop(Server, Timeout) -> ok
Functionality provided by disconnect/2.
sqlConnect(Server, DSN, UID, Auth) ->
sqlConnect(Server, DSN, UID, Auth, Timeout) ->
Result | {error, ErrMsg, ErrCode}
Use connect/2 instead.
erl_connect(Server, ConnectStr) ->
erl_connect(Server, ConnectStr, Timeout) ->
erl_connect(Server, DSN, UID, PWD) ->
erl_connect(Server, DSN, UID, PWD, Timeout) ->
ok, | {error, ErrMsg, ErrCode}
Use connect/2 instead.
sqlDisConnect(Server) ->
sqlDisConnect(Server, Timeout) ->
Result | {error, ErrMsg, ErrCode}
Use disconnect/1 instead.
erl_disconnect(Server) ->
erl_disconnect(Server, Timeout) ->
ok | {error, ErrMsg, ErrCode}
Use disconnect/1 instead.
sqlSetConnectAttr(Server, Attr, Value) ->
sqlSetConnectAttr(Server, Attr, Value, Timeout) ->
Result | {error, ErrMsg, ErrCode}
Functionality provided by connect/2.
erl_executeStmt(Server, Stmt) ->
erl_executeStmt(Server, Stmt, Timeout) ->
{updated, NRows} | {selected, ColNames, Rows} |
{error, ErrFunc, ErrMsg}
Use sql_query/[2,3] instead.
sqlEndTran(Server, ComplType) ->
sqlEndTran(Server, ComplType, Timeout) ->
Result | {error, ErrorMsg, errCode}
Use commmit/[2,3] instead.
sqlRowCount(Server) ->
sqlRowCount(Server, Timeout) ->
{Result, RowCount} | {error, ErrMsg, ErrCode}
Functionality provided by sql_query/[2,3] and select_count/[2,3]
sqlDescribeCol(Server, ColNum) ->
sqlDescribeCol(Server, ColNum, Timeout) ->
{Result, ColName, Nullable} | {error, ErrMsg, ErrCode}
Not needed.
sqlNumResultCols(Server) ->
sqlNumResultCols(Server, Timeout) ->
{Result, ColCount} | {error, ErrMsg, ErrCode}
Not needed.
sqlCloseHandle(Server) ->
sqlCloseHandle(Server, Timeout) ->
Result | {error, ErrMsg, ErrCode}
Not needed.
sqlExecDirect(Server, Stmt) ->
sqlExecDirect(Server, Stmt, Timeout) ->
Result | {error, ErrMsg, ErrCode}
columnRef() ->{ok, Ref}
sqlBindColumn(Server, ColNum, Ref) ->
sqlBindColumn(Server, ColNum, Ref, Timeout) ->
Result | {error, ErrMsg, ErrCode}
sqlFetch(Server) ->
sqlFetch(Server, Timeout) ->
readData(Server, Ref) ->
readData(Server, Ref, Timeout) -> {ok, Value}
The semantical benefit of using the above functions can be achived much easier by using select_count/[2,3] followed by next/[1,2].
[1]: Microsoft ODBC 3.0, Programmer's Reference and SDK Guide
See also http://msdn.microsoft.com/