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
The connection specifications.
A database type.
A displayed CAPI element, or
A Lisp object.
A keyword naming an encoding.
A string, the keyword
A string, or the keyword
A string or
An integer or the keyword
A property list of keywords and values specific to SQLite.
The allowed values for database-type are
: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.
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.
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
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-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.
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 an existing connection if there is one (and warns), or makes a new connection.
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
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:
If encoding is
: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
If encoding is
: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
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:
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.
The only recognized values of encoding are
encoding is ignored.
If encoding is
:utf-8 then UTF-8 is used (by calling the C function
sqlite3_open_v2). If encoding is
: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:
Ignore the error.
If default-table-type is
"bdb", then rollback errors are signaled. Otherwise rollback errors are not signaled.
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:
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.
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 ...)
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 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.
(connect "personnel/admin/secret" :database-type :odbc)
LispWorks User Guide and Reference Manual - 20 Sep 2017