connect

connect Function

Summary

Opens a connection to a database.

Package

sql

Signature

connect connection-spec &key if-exists database-type interface name encoding signal-rollback-errors default-table-type default-table-extra-options date-string-format sql-mode prefetch-rows-number prefetch-memory sqlite-keywords => database

Arguments

`connection-spec`⇩	The connection specifications.
`if-exists`⇩	A keyword.
`database-type`⇩	A database type.
`interface`⇩	A displayed CAPI element, `:none` or `nil`.
`name`⇩	A Lisp object.
`encoding`⇩	A keyword naming an encoding.
`signal-rollback-errors`⇩
	`nil`, the keyword `:default`, or a function designator.
`default-table-type`⇩	A string, the keyword `:support-transactions`, or `nil`.
`default-table-extra-options`⇩
	A string or `nil`.
`date-string-format`⇩	A string, or the keyword `:standard`, or `nil`.
`sql-mode`⇩	A string or `nil`.
`prefetch-rows-number`⇩	An integer or the keyword `:default`.
`prefetch-memory`⇩	An integer or the keyword `:default`.
`sqlite-keywords`⇩	A property list of keywords and values specific to SQLite.

Values

database

A database.

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 23.1.2 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.

connect may signal an error if it cannot open the connection. If it fails to establish a connection, the error if of class sql-failed-to-connect-error. That typically indicates an incorrect connection specification, for example the wrong user or password. If the failure is a failure to configure the connection after making it, the error will of some subclass of sql-database-error. Other errors can be signaled if the arguments to connect are wrong in a way that can be identfied by LispWorks without trying to connect.

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 23.2.4 Connecting to Oracle, 23.2.5 Connecting to ODBC, 23.2.6 Connecting to MySQL or 23.2.7 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 `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 values of database-type may allow more elaborate syntax, but conforming to the pattern above. See the section 23.2 Initialization for details.

Additionally when database-type is :odbc or :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 23.2.5 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.

If name is supplied then if-exists defaults to the value of *connect-if-exists*. If name is not supplied then if-exists must be :new or omitted, otherwise an error is signaled (this is a new requirement in LispWorks 8.0).

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 :none, connect does not raises a dialog, and instead signals an error. If interface is nil (the default), and connect is called in a process that 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 values of database-type, 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 values of database-type 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 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 23.2.8.3 SQLite connection keywords for more details.

Notes

All the Common SQL functions that accept the keyword argument :database use find-database to find the database if the given value is not a database. Therefore these functions can now find only databases that that were opened with an explicit name:

(connect ... :name name ...)

Compatibility notes

LispWorks 4.4 (and previous versions) use connection-spec passed to connect as the database name. connect checks whether a connection with this name already exists (according to the value of if-exists). find-database can be used to find a database using this name.

LispWorks 5.0 (and later versions) does not use connection-spec as the name. Instead, by default it generates a name from connection-spec. The name is intended to be unique (by including a counter). Thus normally connect will not find an existing connection even if it is called again with identical value of connection-spec.

Examples

The following example connects LispWorks to the info database.

(connect "info")

The next example connects to the ODBC database personnel using the username "admin" and the password "secret".

(connect "personnel/admin/secret" :database-type :odbc)

The next example opens a connection to MySQL which treats quotes as in ANSI but does not set other ANSI features:

(sql:connect "me/mypassword/mydb"
             :sql-mode "ANSI_QUOTES")