connect

Description

The function connect opens a connection to a database of type database-type.

The allowed values for database-type are :odbc, :odbc-driver, :mysql, :postgresql, :oracle8 and :oracle, though not all of these are supported on some platforms. See Supported databases for details of per-platform database support.

The default for database-type is the value of *default-database-type*.

connect sets the variable *default-database* to an instance of the database opened, and returns that instance.

If connection-spec is a list it is interpreted as a plist of keywords and values. Some of the keywords are database-type specific: see Connecting to Oracle, Connecting to ODBC, Connecting to MySQL or Connecting to PostgreSQLas appropriate.

General connection-spec keywords are:

:username

User name

:password

Password

:connection

A specification of the connection. In general, this is supposed to be sufficient information (other than the username and password) to open a connection.The precise meaning varies according to the database-type.

If connection-spec is a string, it is interpreted canonically as:


username/password@connection

where connection can be omitted along with the '@' in cases when there is a default connection, password can be omitted along with the preceding '/', and username can be omitted if there is a default user. For example, if you have an Oracle user matching the current Unix username and that does not need a password to connect, you can call


(connect "/")

Specific database-types may allow more elaborate syntax, but conforming to the pattern above. See the section Initialization for details.

Additionally for database-types :odbc and :odbc-driver, if connection-spec does not include the '@' character then the string is interpreted in a special way, for backward compatibility with LispWorks 4.4 and earlier versions. See the section Connecting to ODBC for details.

name can be passed to explicitly specify the name of the connection. If name is supplied then it is used as-is for the connection name. Therefore it can be found by another call to connect and calls to find-database. Connection names are compared with equalp. If name is not supplied, then a unique database name is constructed from connection-spec and a counter.

If name is supplied then existing connections are found by comparing their name with name and then if-exists modifies the behavior of connect as follows:

:new

Makes a new connection even if connections to the same database already exist.

:warn-new

Makes a new connection but warns about existing connections.

:error

Makes a new connection but signals an error for existing connections.

:warn-old

Selects an existing connection if there is one (and warns), or makes a new connection.

:old

Selects an existing connection if there is one, or makes a new one.

The default value of if-exists is the value of *connect-if-exists*.

interface is used if connect needs to display a dialog to ask the user for username and password. If interface is a CAPI element, this is used. If interface is any other value (the default value is nil), and connect is called in a process which is associated with a CAPI interface, then this CAPI interface is used. interface has been added because dialogs asking for passwords can fail otherwise. This depends on the driver that the datasource uses: the problem has only been observed using MS SQL on Microsoft Windows.

encoding specifies the encoding to use in the connection. The value should be a keyword naming an acceptable encoding, or nil (the default). The value :unicode is accepted for all database-types, and this will try to make a connection that can support sending and retrieving double-byte string values. Other values are database-type specific:

:mysql

If encoding is nil or :default then the encoding is chosen according to the default character set of the connection (if available) and if that fails the encoding :utf-8 is used. The other recognized values of encoding are :unicode, :utf-8, :ascii, :latin-1, :euc and :sjis.

:unicode uses :utf-8 internally.

:postgresql

If encoding is nil or :default LispWorks does not set anything in the connection. If the connection character set is SQL_ASCII, LispWorks uses :latin-1 to convert to and from Lisp strings, otherwise it uses :utf-8.

If encoding is one of the keywords listed below, LispWorks uses it as the external format for converting to and from Lisp strings, and LispWorks also sets the connection character set to the corresponding string:

Keyword	character-set	alias
`:utf-8`	UTF-8	`:unicode`
`:latin-1`	SQL_ASCII
`:ascii`	SQL_ASCII
`:gbk`	GBK
`:euc-jp`	EUC_JP	`:euc`
`:sjis`	SJIS	`:shift-jis`

An alias maps to the corresponding keyword.

In addition, encoding can be a string or a cons of a keyword and a string. If it is a string LispWorks uses :utf-8 as the external format, and sets the connection character set to the string. If it is a cons, the keyword (the car) is used as the external format, and the string (cdr) is used to set the character set.

See "character set support" in the PostgreSQL manual for known character sets.

:oracle

The only recognized values of encoding are nil and :unicode.

:oracle8

encoding is ignored.

:odbc or :odbc-driver

The valid values of encoding are :unicode or nil. When encoding is nil it uses the default multibyte encoding.

:sqlite

If encoding is :default, :unicode or :utf-8 then UTF-8 is used (by calling the C function sqlite3_open_v2). If encoding is :utf-16 or :utf-16-native, then UTF-16 in the native byte order is used (by calling the C function sqlite3_open16). It is not obvious in what circumstances UTF-16 is better and it is made available only because the underlying library supports it.

signal-rollback-errors controls what happens when an attempted rollback causes an error, for databases that do not support rollback properly (for example MySQL with the default settings). For database-types other than :mysql signal-rollback-errors is ignored and such an error is always signaled. For database-type :mysql signal-rollback-errors is interpreted as follows:

nil

Ignore the error.

:default

If default-table-type is :support-transactions, "innodb" or "bdb", then rollback errors are signaled. Otherwise rollback errors are not signaled.

Function designator

The function signal-rollback-errors should take two arguments: the database object and a string (for an error message). The function is called when a rollback signaled an error.

The default value of signal-rollback-errors is :default.

default-table-type specifies the default value of the :type argument to create-table. See create-table for details. The default value of default-table-type is nil.

default-table-extra-options specifies the default value of the :extra-options argument to create-table. See create-table for details. The default value of default-table-extra-options is nil.

date-string-format specifies which format to use to represent dates. If the value is a string, it should be appropriate for the database-type. The value :standard means that the standard SQL date format is used. If the value is nil (the default), then the date format is not changed. Currently only database-type :oracle uses the value of date-string-format, and in this case it must be a valid date format string for Oracle.

sql-mode specifies the mode of the SQL connection for database-type :mysql. By default (that is, when sql-mode is not supplied) connect sets the mode of the connection to ANSI, by executing this statement:


    "set sql_mode='ansi'"

sql-mode can be supplied as nil, in which case no statement is executed. Otherwise it should be a string which is a valid setting for sql_mode, and then connect executes the statement:


   set sql_mode='sql-mode'

When database-type is not :mysql, sql-mode is ignored.

prefetch-rows-number and prefetch-memory are used when database-type is :oracle, and specify the amount of data to prefetch when performing queries. prefetch-rows-number is the number of rows to prefetch, with default value 100. prefetch-memory is the maximum number of bytes to prefetch, with default value #x100000. prefetch-rows-number and prefetch-memory can both also have the value :default, which allows the database to choose the amount to prefetch.

sqlite-keywords is used only when connecting to SQLite (database-type is :sqlite) and is ignored otherwise. See SQLite connection keywords for more details.

connect

Function

Summary

Package

Signature

Arguments

Values

Description

Notes

Compatibility notes

Example

See also