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 => database

Arguments

connection-spec

The connection specifications.

if-exists

A keyword.

database-type

A database type.

interface

A displayed CAPI element, 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 .

Values

database

A database.

Description

The connect function 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 the section "Supported databases" in the LispWorks User Guide 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 the documentation for each database. General 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-type s may allow more elaborate syntax, but conforming to the pattern above. See the section "Initialization" in the LispWorks User Guide for details.

Addtionally for database-type s :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" in the LispWorks User Guide for details.

The argument 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 old connection if one exists (and warns) or makes a new one.

:old

Selects old connection if one exists 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 Windows.

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.

Note: 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 ...)

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-type s, 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 recognised values of encoding are :unicode , :utf-8 , :ascii , :latin-1 , :euc and :sjis .

:unicode uses :utf-8 internally.

:postgresql

encoding is ignored.

:oracle

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

:oracle8

encoding is ignored.

:odbc

encoding is ignored.

:odbc-driver

encoding is ignored.

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-type s other than :mysql signal-rollback-errors is ignored and such an error is always signalled. 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 signalled. Otherwise rollback errors are not signalled.

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

Compatibility Note

LispWorks 4.4 (and previous versions) use connection-spec passed to connect as the database name. connect checks if 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 the 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 .

Example

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")

See also

*default-database*
*default-database-type*
connected-databases
*connect-if-exists*
database-name
disconnect
find-database
reconnect
status