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 => database
The connection specifications.
A keyword.
A database type.
A displayed CAPI element, or
nil
.
A Lisp object.
A keyword naming an encoding.
nil
, the keyword
:default
, or a function designator.
A string, the keyword
:support-transactions
, or
nil
.
A string, or the keyword
:standard
, or
nil
.
A string or
nil
.
An integer or the keyword
:default
.
An integer or the keyword
:default
.
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 the documentation for each database. General keywords are:
User name
Password
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 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 for details.
The argument
if-exists
modifies the behavior of
connect
as follows:
Makes a new connection even if connections to the same database already exist.
Makes a new connection but warns about existing connections.
Makes a new connection but signals an error for existing connections.
Selects old connection if one exists (and warns) or makes a new one.
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 Microsoft 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:
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.
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:
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.
The only recognised values of
encoding
are
nil
and
:unicode
.
encoding is ignored.
The valid values of
encoding
are
:unicode
or
nil
. When
encoding
is
nil
it uses the default multibyte encoding.
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:
Ignore the error.
If
default-table-type
is
:support-transactions
,
"innodb"
or
"bdb"
, then rollback errors are signalled. Otherwise rollback errors are not signalled.
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.
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.
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
.
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")
*default-database*
*default-database-type*
connected-databases
*connect-if-exists*
database-name
disconnect
find-database
reconnect
status
LispWorks User Guide and Reference Manual - 21 Dec 2011