Deprecated odbc

MODULE

odbc (deprecated)

MODULE SUMMARY

Deprecated version of the Erlang ODBC application

DESCRIPTION

The Erlang ODBC interface is divided into three parts:

• Start and Stop
  Start and stop the Erlang ODBC gen_server process.


• Basic API
  Consist of most ODBC functions.


• Utility API
  Consists of functions that are easier to use than the Basic API. These functions are on a higher level, do more of the job, but allow less control to the application programmer.

General information

Erlang ODBC is an Erlang application. An Erlang application allows noramlly code change in a running Erlang system. Erlang ODBC application does not allow code upgrade in a running Erlang system.
Erlang ODBC functions are synchronous.
Erlang ODBC functions supports all ODBC defined SQL data types. SQL data types are mapped to nearest Erlang type, except binaries which are mapped into string(). The type string() is a list() of integers representing ASCII codes.
When a column in a row has no value then columns is called to be null or contain a null. The null is mapped to a Erlang atom null.
A SQL question in Erlang is a string. The string is decoded in the Erlang ODBC C-program as a string. If the SQL questions contains a null character "\0" then the decode functions think that the string ends there. Do not use null characters in the SQL question.
In some databases has data in string format been filled out with space until column length.
The default Timeout for all functions is 5000 ms, unless otherwise stated.

Error handle

Erlang ODBC application may fail for following reasons:
Bad arguments to an Erlang ODBC function.
Failure from ODBC-driver.
Bad arguments to an Erlang ODBC function returns the tuple {'EXIT',Reason}.
When an Erlang ODBC function receive faliure from ODBC driver, the functions returns the tuple {error, ErrMsg, ErrCode} .

Start and Stop

EXPORTS

start_link(Args, Options) ->
start_link(ServerName, Args, Options) -> Result

Types:
Args = []
Options = [Opt]
Opt = This are options which are used by the gen_server module.

For information see the module documentation gen_server and sys.
ServerName = {local, atom()} | {global, atom()}

When supplied, causes the server to be registered locally or globally. If the server is started without a name it can only be called using the returned pid.
Result = {ok, pid()} | {error, Reason}

The pid of the server or an error tuple.
Reason = {already_started, pid()} | timeout | {no_c_node, Info}

The server was already started, a timeout has expired, or the C node could not be started (the program may not have been found or may not have been executable e.g.).
Info = string()

More information.

Starts a new ODBC server process, registers it with the supervisor, and links it to the calling process. Opens a port to a new C node on the local host, using the same cookie as is used by the node of the calling process. Links to the process on the C node.

There is no default timeout value. Not using the timeout option is equivalent to having an infinite timeout value. An expired timeout is reported as an error here, not an exception. The debug options are described in the sys module documentation.

stop(Server) ->
stop(Server, Timeout) -> ok

Types:
Server = pid() | Name | {global, Name} | {Name, Node}

The pid of the server process, a registered name, a globally registered name, or a registered name on a remote node.
Timeout = integer() | infinity

Max time (ms) for serving the request.

Stops the ODBC server process as soon as all already submitted requests have been processed. The C node is also stopped.

Basic API

To use the Basic API it is necessary to gain a comprehensive understanding of ODBC by studying [1].
Erlang ODBC application Basic API function allocate and deallocate memory automatic and therefore have the ODBC function which allocate or deallocate memory been excluded.

EXPORTS

sqlBindColumn(Server, ColNum, Ref) ->
sqlBindColumn(Server, ColNum, Ref, Timeout) -> Result | {error, ErrMsg, ErrCode}

Types:
Server = pid() | Name | {global, Name} | {Name, Node}

The pid of the server process, a registered name, a globally registered name, or a registered name on a remote node.
ColNum = integer()

Column number from left to right starting at 1.
Ref = term()

A reference.
Timeout = integer() | infinity

Maximum time (ms) for serving the request.
Result = ?SQL_SUCCESS | ?SQL_SUCCESS_WITH_INFO

ErrMsg = string()

Error message.
ErrCode = ?SQL_INVALID_HANDLE | ?SQL_ERROR

Assigns a reference to the column with the number ColNum.

Differences from the ODBC Function:


The parameters Server and Timeout have been added. The input parameters TargetType, TargetValuePtr, BufferLength, and StrLen_or_IndPtr of the ODBC function have been replaced with the Ref parameter.

sqlCloseCursor(Server) ->
sqlCloseCursor(Server, Timeout) -> Result | {error, ErrMsg, ErrCode}

Types:
Server = pid() | Name | {global, Name} | {Name, Node}

The pid of the server process, a registered name, a globally registered name, or a registered name on a remote node.
Timeout = integer() | infinity

Maximum time (ms) for serving the request.
Result = ?SQL_SUCCESS | ?SQL_SUCCESS_WITH_INFO

ErrMsg = string()

Error message.
ErrCode = ?SQL_INVALID_HANDLE | ?SQL_ERROR

Closes a cursor that has been opened on a statement and discards pending results. See SQLCloseCursor in [1].

Differences from the ODBC Function:

The parameters Server and Timeout have been added.

sqlConnect(Server, DSN, UID, Auth) ->
sqlConnect(Server, DSN, UID, Auth, Timeout) -> Result | {error, ErrMsg, ErrCode}

Types:
Server = pid() | Name | {global, Name} | {Name, Node}

The pid of the server process, a registered name, a globally registered name, or a registered name on a remote node.
DSN = string()

The name of the database.
UID = string()

The user ID
Auth = string()

The user's password for the database.
Timeout = integer() | infinity

Maximum time (ms) for serving the request.
Result = ?SQL_SUCCESS | ?SQL_SUCCESS_WITH_INFO

ErrMsg -> string()

Error message.
ErrCode -> ?SQL_INVALID_HANDLE | ?SQL_ERROR

Establishes a connection to a driver and a data source. See SQLConnect in [1].
Differences from the ODBC Function:

Connection pooling is not supported. The parameters Server and Timeout have been added. The input parameters NameLength1, NameLength2, and NameLength3 of the ODBC function have been excluded.

sqlDescribeCol(Server, ColNum) ->
sqlDescribeCol(Server, ColNum, Timeout) -> {Result, ColName, Nullable} | {error, ErrMsg, ErrCode}

Types:
Server = pid() | Name | {global, Name} | {Name, Node}

The pid of the server process, a registered name, a globally registered name, or a registered name on a remote node.
ColNum = integer()

The column number from left to right, starting at 1.
Timeout = integer() | infinity

Maximum time (ms) for serving the request.
Result = ?SQL_SUCCESS | ?SQL_SUCCESS_WITH_INFO

ColName = string()

The column name.
Nullable = ?SQL_NO_NULLS | ?SQL_NULLABLE | ?SQL_NULLABLE_UNKNOWN

Indicates whether the column allows null values or not.
ErrMsg -> string()

Error message.
ErrCode -> ?SQL_INVALID_HANDLE | ?SQL_ERROR

Returns the result descriptor -- column name, and nullability for one column in the result set. See SQLDescribeCol in [1].
Differences from the ODBC Function:

The function does not support retrieval of bookmark column data. The parameters Server and Timeout have been added. The output parameters ColumnName and NullablePtr of the ODBC function have been changed into the returned values ColName and Nullable. The output parameters BufferLength, NameLengthPtr, DataTypePtr, ColumnSizePtr, and DecimalDigitsPtr of the ODBC function hsve been excluded.

sqlDisConnect(Server) ->
sqlDisConnect(Server, Timeout) -> Result | {error, ErrMsg, ErrCode}

Types:
Server = pid() | Name | {global, Name} | {Name, Node}

The pid of the server process, a registered name, a globally registered name, or a registered name on a remote node.
Timeout = integer() | infinity

Maximum time (ms) for serving the request.
Result = ?SQL_SUCCESS | ?SQL_SUCCESS_WITH_INFO

ErrMsg -> string()

Error message.
ErrCode -> ?SQL_INVALID_HANDLE | ?SQL_ERROR

Closes the connection associated with a specific server. See SQLDisconnect in [1].

Differences from the ODBC Function:

Connection pooling is not supported. The parameters Server and Timeout have been added.

sqlEndTran(Server, ComplType) ->
sqlEndTran(Server, ComplType, Timeout) -> Result | {error, ErrorMsg, errCode}

Types:
Server = pid() | Name | {global, Name} | {Name, Node}

The pid of the server process, a registered name, a globally registered name, or a registered name on a remote node.
ComplType = ?SQL_COMMIT | ?SQL_ROLLBACK

Commit operation or rollback operation.
Timeout = integer() | infinity

Maximum time (ms) for serving the request.
Result = ?SQL_SUCCESS | ?SQL_SUCCESS_WITH_INFO

ErrMsg -> string()

Error message.
ErrCode -> ?SQL_INVALID_HANDLE | ?SQL_ERROR

Requests a commit or rollback operation for all active operations on all statement handles associated with a connection. See SQLEndTran in [1].

Rollback of transactions may be unsupported by core level drivers.




Differences from the ODBC Function:



The parameter HandleType and Handle of the ODBC function has been excluded. The parameters Server and Timeout have been added.

sqlExecDirect(Server, Stmt) ->
sqlExecDirect(Server, Stmt, Timeout) -> Result | {error, ErrMsg, ErrCode}

Types:
Server = pid() | Name | {global, Name} | {Name, Node}

The pid of the server process, a registered name, a globally registered name, or a registered name on a remote node.
Stmt = string()

An SQL statement.
Timeout = integer() | infinity

Maximum time (ms) for serving the request.
Result = ?SQL_SUCCESS | ?SQL_SUCCESS_WITH_INFO | ?SQL_NEED_DATA | ?SQL_NO_DATA

ErrMsg -> string()

Error message.
ErrCode -> ?SQL_INVALID_HANDLE | ?SQL_ERROR

Executes a statement. See SQLExecDirect in [1].

Differences from the ODBC Function:

?SQL_NO_DATA is returned only in connection with positioned updates, which are not supported. The parameters Server and Timeout have been added. The input parameter StatementHandle and TextLength of the ODBC function has been excluded.

sqlFetch(Server) ->
sqlFetch(Server, Timeout) -> Result | {error, ErrMsg, ErrCode}

Types:
Server = pid() | Name | {global, Name} | {Name, Node}

The pid of the server process, a registered name, a globally registered name, or a registered name on a remote node.
Timeout = integer() | infinity

Maximum time (ms) for serving the request.
Result = ?SQL_SUCCESS | ?SQL_SUCCESS_WITH_INFO | ?SQL_NO_DATA

ErrMsg -> string()

Error message.
ErrCode -> ?SQL_INVALID_HANDLE | ?SQL_ERROR

Fetches a row of data from a result set. The driver returns data for all columns that were bound to storage locations with sqlBindCol/[3, 4]. See SQLFetch in [1].

Differences from the ODBC Function:

The parameter StatementHandle of the ODBC function has been excluded. The parameters Server and Timeout have been added.

sqlNumResultCols(Server) ->
sqlNumResultCols(Server, Timeout) -> {Result, ColCount} | {error, ErrMsg, ErrCode}

Types:
Server = pid() | Name | {global, Name} | {Name, Node}

The pid of the server process, a registered name, a globally registered name, or a registered name on a remote node.
Timeout = integer() | infinity

Maximum time (ms) for serving the request.
Result = ?SQL_SUCCESS | ?SQL_SUCCESS_WITH_INFO

ColCount = integer()

The number of columns in the result set.
ErrMsg -> string()

Error message.
ErrCode -> ?SQL_INVALID_HANDLE | ?SQL_ERROR

Returns the number of columns in a result set. See SQLNumResultCols in [1].

Differences from the ODBC Function:

The parameter StatementHandle of the ODBC function has been excluded. The parameters Server and Timeout have been added. The output parameter ColumnCountPtr of the ODBC function has been changed into the returned value ColCount.

sqlRowCount(Server) ->
sqlRowCount(Server, Timeout) -> {Result, RowCount} | {error, ErrMsg, ErrCode}

Types:
Server = pid() | Name | {global, Name} | {Name, Node}

The pid of the server process, a registered name, a globally registered name, or a registered name on a remote node.
Timeout = integer() | infinity

Maximum time (ms) for serving the request.
Result = ?SQL_SUCCESS | ?SQL_SUCCESS_WITH_INFO

Result macro.
RowCount = integer()

The number of affected rows. If the number of affected rows is not available -1 is returned. For exceptions, see SQLRowCount in [1].
ErrMsg -> string()

Error message.
ErrCode -> ?SQL_INVALID_HANDLE | ?SQL_ERROR

Returns the number of rows affected by an UPDATE, INSERT, or DELETE statement. See SQLRowCount in [1].

Differences from the ODBC Function:

The parameter StatementHandle has been excluded from ODBC function. The parameters Server and Timeout have been added. The output parameter RowCountPtr of the ODBC function has been changed into the returned value RowCount.

sqlSetConnectAttr(Server, Attr, Value) ->
sqlSetConnectAttr(Server, Attr, Value, Timeout) -> Result | {error, ErrMsg, ErrCode}

Types:
Server = pid() | Name | {global, Name} | {Name, Node}

The pid of the server process, a registered name, a globally registered name, or a registered name on a remote node.
Attr = integer()

One of the attributes described further down are supported. The attributes defined by ODBC are supplied through macros, but driver-specific attributes are not.
Value = string() | integer()

The new attribute value.
Timeout = integer() | infinity

Maximum time (ms) for serving the request.
Result = ?SQL_SUCCESS | ?SQL_SUCCESS_WITH_INFO

ErrMsg -> string()

Error message.
ErrCode -> ?SQL_INVALID_HANDLE | ?SQL_ERROR

Sets attributes that govern aspects of connections. The following attributes, and their possible values, are supported (through macros):
?SQL_ATTR_AUTOCOMMIT
?SQL_ATTR_TRACE
?SQL_ATTR_TRACEFILE
These attributes can only be set after a connection. More information can be found under SQLSetConnectAttr in [1]. Driver-specific attributes are not supported through macros, but can be retrieved, if they are of character or signed/unsigned long integer types.


Differences from the ODBC Function:

Only character and signed/unsigned long integer attribute types are supported. The parameters Server and Timeout have been added. The input parameter ConnectionHandle and StringLength of the ODBC function has been excluded.

readData(Server, Ref) ->
readData(Server, Ref, Timeout) -> {ok, Value}

Types:
Server = pid() | Name | {global, Name} | {Name, Node}

The pid of the server process, a registered name, a globally registered name, or a registered name on a remote node.
Ref

A reference to the column.
Timeout = integer() | infinity

Maximum time (ms) for serving the request.
Value = string()

Contents of the column associated with Ref.

Returns the contents of a deferred data buffer and its associated length/indicator buffer. Used in connection with sqlFetch/[1, 2].

columnRef() -> {ok, Ref}

Types:
Ref

A reference.

Returns a reference. The reference is assigned to a column in the function sqlBindColumn/[3,4].

Utility API

Erlang ODBC application Utility API has a few easy-to-use function. The reported errors are tuples with arity 3.

EXPORTS

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}

Types:
Server = pid() | Name | {global, Name} | {Name, Node}

The pid of the server process, a registered name, a globally registered name, or a registered name on a remote node.
ConnectStr = string()

Connection string. For syntax see SQLDriverConnect in [1].
DSN = string()

Name of the database.
UID = string()

User ID.
PWD = string()

Password.
Timeout = integer() | infinity

Maximum time (ms) for serving the request.
ErrMsg = string()

Error message.
ErrCode = ?SQL_INVALID | ?SQL_ERROR

Opens a connection to a database. There can be only one open connections to a database and per server. connect/[2, 3] is used when the information that can be supplied through connect/[4, 5] does not suffice.

The syntax to be used for ConnectStr is described under SQLDriverConnect in [1]. The ConnectStr must be complete.

erl_disconnect(Server) ->
erl_disconnect(Server, Timeout) -> ok | {error, ErrMsg, ErrCode}

Types:
Server = pid() | Name | {global, Name} | {Name, Node}

The pid of the server process, a registered name, a globally registered name, or a registered name on a remote node.
Timeout = integer() | infinity

Maximum time (ms) for serving the request.
ErrMsg = string()

Error message.
ErrCode = ?SQL_INVALID_HANDLE | ?SQL_ERROR

Closes the connection to a database.

erl_executeStmt(Server, Stmt) ->
erl_executeStmt(Server, Stmt, Timeout) -> {updated, NRows} | {selected, [ColName], [Row]} | {error, ErrMsg}

Types:
Server = pid() | Name | {global, Name} | {Name, Node}

The pid of the server process, a registered name, a globally registered name, or a registered name on a remote node.
Stmt = string()

SQL statement to execute.
Timeout = integer() | infinity

Maximum time (ms) for serving the request.
NRows = integer()

The number of updated rows for UPDATE, INSERT, or DELETE statements, or -1 if the number is not available. For other statement types the value is driver defined, see [1].
ColName = string()

The name of a column in the resulting table.
Row = [Value]

One row of the resulting table.
Value = string() | null

One value in a row.
ErrMsg = string()

Error message.

Executes a single SQL statement. All changes to the data source are, by default, automatically committed if successful.

{updated, 0} or {updated, -1} is returned when a statement that does not select or update any rows is successfully executed. The ColNames are ordered the same way as the Values in the Rows (the first ColName is associated with the first Value of each Row etc.). The Rows have no defined order since they represent a set.

AUTHORS

Ingvar Meyer - support@erlang.ericsson.se