- Reference manual
- SWI-Prolog ODBC Interface
- The ODBC layer
- Global configuration
- Connection management
- Running SQL queries
- Transaction management
- Accessing the database dictionary
- Getting more information
- Representing SQL data in Prolog
- Errors and warnings
- ODBC implementations
- Remaining issues
- The ODBC layer
- SWI-Prolog ODBC Interface
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
aliasoption is used. In addition to the options below, options applicable to odbc_set_connection/2 may be provided.
- Define the user-name for the connection. This option must be present if the database uses authorization.
- Provide a password for the connection. Normally used in combination with
- Use AliasName as Connection identifier, making the connection available as a global resource. A good choice is to use the DSN as alias.
- If OpenMode is
once(default if an
aliasis 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.
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.
- 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.)
- Select the version of the ODBC connection. Default is
'3.0'. The other supported value is
The following example connects to the WordNet1An SQL version of WordNet is available from http://wordnet2sql.infocity.cjb.net/  database, using the connection alias
wordnetand 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
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.
- 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:
read, tell the driver we only access the database in read mode. If
update(default), tell the driver we may execute update commands.
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.
- I haven't found a good description of what this does, but setting it to
dynamicmakes it possible to have multiple active statements on the same connection with Microsoft SQL server. Other values are
- Define the encoding used to communicate to the driver. Defined values
are given below. The default on MS-Windows is
unicodewhile 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.
- Communicate using the *A() functions and pass bytes untranslated.
- Communicate using the *A() functions and translated between Prolog Unicode characters and their (possibly) multibyte representation in the current locale.
- Communicate using the *A() functions and translated between Prolog Unicode characters and their UTF-8 encoding.
- Communicate using the *W() functions.
false), statements returning
SQL_SUCCESS_WITH_INFOsucceed without printing the info. See also section 2.8.1.
- 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.
- 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
Name(Value). If Property is unbound all defined properties are enumerated on backtracking. Currently the following properties are defined.
- Name of the database associated to the connection.
- Name of the database engine. This constant can be used to identify the engine.
- Version identifier from the database engine.
- ODBC Dynamic Link Library providing the interface between ODBC and the database.
- ODBC version supported by the driver.
- The drivers version identifier.
- 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
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.