2.2 Connection management

The ODBC interface deals with a single ODBC environment with multiple simultaneous connections. The predicates in this section deal with connection management.

odbc_connect(+DSN, -Connection, +Options)

Create a new ODBC connection to data-source DSN and return a handle to this connection in Connection. The connection handle is either an opaque structure or an atom of the alias option is used. In addition to the options below, options applicable to odbc_set_connection/2 may be provided.

user(User)

Define the user-name for the connection. This option must be present if the database uses authorization.

password(Password)

Provide a password for the connection. Normally used in combination with user(User).

alias(AliasName)

Use AliasName as Connection identifier, making the connection available as a global resource. A good choice is to use the DSN as alias.

open(OpenMode)

If OpenMode is once (default if an alias is provided), a second call to open the same DSN simply returns the existing connection. If multiple (default if there is no alias name), a second connection to the same data-source is opened.

mars(+Bool)

If true, use Microsoft SQL server 2005 mars mode. This is support for multiple concurrent statements on a connection without requiring the dynamic cursor (which incurs an astounding 20-50x slowdown of query execution!!). MARS is a new feature in SQL2k5 apparently, and only works if you use the native driver. For the non-native driver, specifying that it is enabled will have absolutely no effect.

connection_pool_mode(+Bool)

Determines how a connection is chosen from a connection pool if connection pooling is on. See odbc_set_option/1 for enabling pooling. Permitted values are’strict' (Only connections that exactly match the connection options in the call and the connection attributes set by the application are reused. This is the default) and’relaxed' (Connections with matching connection string keywords can be used. Keywords must match, but not all connection attributes must match.)

odbc_version(+Atom)

Select the version of the ODBC connection. Default is '3.0'. The other supported value is '2.0'.

The following example connects to the WordNet^{1An SQL version of WordNet is available from http://wordnet2sql.infocity.cjb.net/} [1] database, using the connection alias wordnet and opening the connection only once:

open_wordnet :-
        odbc_connect('WordNet', _,
                     [ user(jan),
                       password(xxx),
                       alias(wordnet),
                       open(once)
                     ]).

odbc_driver_connect(+DriverString, -Connection, +Options)

Connects to a database using SQLDriverConnect(). This API allows for driver-specific additional options. DriverString is passed without checking. Options should not include user and password.

Whenever possible, applications should use odbc_connect/3. If you need this predicate, please check the documentation for SQLDriverConnect() and the documentation of your driver.^{bugFacilities to deal with prompted completion of the driver options are not yet implemented.}

odbc_disconnect(+Connection)

Close the given Connection. This destroys the connection alias or, if there is no alias, makes further use of the Connection handle illegal.

odbc_current_connection(?Connection, ?DSN)

Enumerate the existing ODBC connections.

odbc_set_connection(+Connection, +Option)

Set options on an existing connection. All options defined here may also be specified with odbc_connect/2 in the option-list. Defined options are:

access_mode(Mode)

If read, tell the driver we only access the database in read mode. If update (default), tell the driver we may execute update commands.

auto_commit(bool)

If true (default), each update statement is committed immediately. If false, an update statement starts a transaction that can be committed or rolled-back. See section 2.4 for details on transaction management.

cursor_type(CursorType)

I haven't found a good description of what this does, but setting it to dynamic makes it possible to have multiple active statements on the same connection with Microsoft SQL server. Other values are static, forwards_only and keyset_driven.

encoding(+Encoding)

Define the encoding used to communicate to the driver. Defined values are given below. The default on MS-Windows is unicode while on other platforms it is utf8. Below, the *A() functions refer to the‘ansi' ODBC functions that exchange bytes and the *W() functions refer to the‘unicode' ODBC functions that exchange UCS-2 characters.

iso_latin_1

Communicate using the *A() functions and pass bytes untranslated.

locale

Communicate using the *A() functions and translated between Prolog Unicode characters and their (possibly) multibyte representation in the current locale.

utf8

Communicate using the *A() functions and translated between Prolog Unicode characters and their UTF-8 encoding.

unicode

Communicate using the *W() functions.

silent(Bool)

If true (default false), statements returning SQL_SUCCESS_WITH_INFO succeed without printing the info. See also section 2.8.1.

null(NullSpecifier)

Defines how the SQL constant NULL is represented. Without specification, the default is the atom $null$. NullSpecifier is an arbitrary Prolog term, though the implementation is optimised for using an unbound variable, atom and functor with one unbound variable. The representation null(_) is a commonly used alternative.

The specified default holds for all statements executed on this connection. Changing the connection default does not affect already prepared or running statements. The null-value can also be specified at the statement level. See the option list of odbc_query/4.

wide_column_threshold(+Length)

If the width of a column exceeds Length, use the API SQLGetData() to get the value incrementally rather than using a (large) buffer allocated with the statement. The default is to use this alternate interface for columns larger than 1024 bytes. There are two cases for using this option. In time critical applications with wide columns it may provide better performance at the cost of a higher memory usage and to work around bugs in SQLGetData(). The latter applies to Microsoft SQL Server fetching the definition of a view.

odbc_get_connection(+Connection, ?Property)

Query for properties of the connection. Property is a term of the format Name(Value). If Property is unbound all defined properties are enumerated on backtracking. Currently the following properties are defined.

database_name(Atom)

Name of the database associated to the connection.

dbms_name(Name)

Name of the database engine. This constant can be used to identify the engine.

dbms_version(Atom)

Version identifier from the database engine.

driver_name(Name)

ODBC Dynamic Link Library providing the interface between ODBC and the database.

driver_odbc_version(Atom)

ODBC version supported by the driver.

driver_version(Atom)

The drivers version identifier.

active_statements(Integer)

Maximum number of statements that can be active at the same time on this connection. Returns 0 (zero) if this is unlimited.^{2Microsoft SQL server can have multiple active statements after setting the option cursor_type to dynamic. See odbc_set_connection/2.}

odbc_data_source(?DSN, ?Description)

Query the defined data sources. It is not required to have any open connections before calling this predicate. DSN is the name of the data source as required by odbc_connect/3. Description is the name of the driver. The driver name may be used to tailor the SQL statements used on the database. Unfortunately this name depends on the local installing details and is therefore not universally useful.