The initialization of Common SQL involves three stages. Firstly the SQL interface is loaded. Next, the database type (actually class) to be used is initialized. Finally, Common SQL is used to connect to a database. These stages are explained in more detail in this section.
The Lisp symbols introduced in this chapter are exported from the
sql package. Application packages requiring convenient access to these facilities should therefore use the
The examples in this chapter assume that the
sql package is visible.
requirewith the name of a database interface.
Currently implemented interfaces are "oracle", "mysql", "odbc" and "postgresql". However, not all platforms support all interfaces, see Supported driver/client libraries for each interface-platform combination for details.
Loading is done at load time. In particular, if you are building an application, loading needs to be done before calling deliver.
Every connection has a database type, which defines the functionality to use when performing operations on it. Each interface defines one or more database types that can used as the database type. The database type must be initialized, which can be done either when connecting, or by explicitly calling initialize-database-type. Initializing a database type must be done at run time, in other words you should not save an image (by save-image or deliver) with an initialized database type.
Initializing a database type typically means that the system finds the library that implements the client, loads and initializes it. (Actually, there may be several libraries.) It is possible to delay the initialization until making the connection, but it is useful to do the initialization explicitly first as this allows you to catch errors in the initialization and report them.
The variable *default-database-type* holds the value of the default type, which is used when a database type is not supplied. The first database interface that is loaded sets *default-database-type* to its default database type. Therefore in a typical setup using one interface you do not need to specify the database type.
The database types currently supported are shown in Supported database types:
The main argument to connect is a connection-spec, which is interpreted in a database type specific way. See the entry for connect for details. By default, connect uses the database type in *default-database-type*, but it can be specified explicitly by the keyword argument :database-type. If the database type was not initialized yet, connect initializes it.
The result of connect is an object which is referred to as "database object", but it is really a connection object representing a connection to the server. It is possible to have multiple database objects connected independently to the same database server.
The database object is used by all the other Common SQL interface functions. connect sets the value of *default-database* to the result each time it is called, so a call to a SQL interface function without specifying the database always acts on the last connected database. That allows simpler code when there is only one connection. When there is more than one connection, you need to pass the database object to the interface function via the keyword argument
When a connection is no longer required, it should be closed by calling disconnect.
However, if you deliver an application then the
require call needs to happen at load time (before calling deliver), while the connect call must happen at runtime after the delivered application started. So your code should have two parts:
To get better error handling, you may want to add a call to initialize-database-type, in the startup function:
To use a database, LispWorks needs to load foreign libraries, which is done when initializing the database type. To find the right libraries and initialize them, there may be vendor-specific environment variable(s) that need to be set, for example
ORACLE_HOME for Oracle. Typically one of these will point to a directory where the database code is installed. You may need to ensure that these variables are set properly when your application is used.
Database connections can be named by passing the
:name argument to connect, allowing you to have more than one connection to a given database. If this is omitted, then a unique database name is constructed from connection-spec and a counter. Connection names are compared with
To find all the database connection instances, call the function connected-databases. To retrieve the name for a connection instance, call database-name, and to find a connection instance with a given name use find-database. To print status information about the existing connections, call status.
To close a connection to a database, use disconnect.
To reestablish a connection to a database, use reconnect.
The following example assumes that the
:odbc database type has been initialized as described in Initialization steps. It connects to two databases,
personnel, and then prints out the connected databases.
:oracle, connection-spec conforms to the canonical form described for connect. The connection part is the string used to establish the connection. When connecting to a local server, it may be the SID, otherwise it is an alias recognized by the names server, or in the
Common SQL uses the Oracle Call Interface internally where this is available. For Oracle version 8, Common SQL automatically uses the same API as in LispWorks 4.4. On some platforms, this can also be obtained by using database-type
:oracle8. Note that the
:oracle8 database type is restricted because it cannot access or manipulate LOBs and all connections must use the same character set.
:odbc-driver, connection-spec may take the canonical form described for connect, but an additional syntax is also allowed.
connect keyword arguments
:date-string-format are all ignored.
The general form.
For backward compatibility.
The two forms of strings are distinguished by the presence (or absence) of the '
@' character. In both forms, password can be omitted along with the preceding '
/'. Also, username can simply be omitted.
:mysql, connection-spec may be in the canonical form described for connect, but it may also have the extensions described in this section.
No default database
3306 (unless using unix-socket).
:unix-socket is specified, then none of
:connection can be specified. If
:hostname is specified then
:connection must not be specified. The value supplied for
:hostname can be a raw hostname, or a string of the form hostname
:connection is specified then it can be a string conforming to one of these patterns:
Must start with '
The MySQL interface to initialize, it must find the appropriate MySQL client library. The special variables *mysql-library-path*, *mysql-library-directories* and *mysql-library-sub-directories* give you control over this.
The downloadable packages from the MySQL web site contain only static client libraries, but LispWorks needs a dynamic library. You need to create the dynamic library, for example by using the following shell command.
This command should be executed as the root user, or some other user with write permission to the
/usr/local/mysql/lib directory and assumes that MySQL was installed in
/usr/local/mysql, which is the location used by the prepackaged downloads.
By default, LispWorks expects to find the library either in
/usr/local/mysql/lib or on the shared library path. This can be overridden by setting the special variable *mysql-library-directories*.
By default, LispWorks expects the library to be called
libmysqlclient.*.dylib and it searches for a library that matches that pattern, where
* is any version number. This search can be avoided by setting *mysql-library-path* to something other than the default (
"-lmysqlclient"), for example, it is possible to force LispWorks to look for version 12 by evaluating
(setq *mysql-library-path* "libmysqlclient.12")
If the environment variable
LW_MYSQL_LIBRARY. is set, then its value is used instead of the value of *mysql-library-path*.
dbname=test user=scott password=tiger host=scandium
connection-spec can be a plist containing (some of) the keywords
:connection. Each of these keywords may be omitted, but if
:connection is specified, then
:port must not be specified.
LispWorks sets the PostgreSQL session variable
on to match the escaping that Common SQL uses. Note that this variable is only available in PostgreSQL 8.2 and later, so escaping will not work correctly in older versions of PostgreSQL.
LispWorks User Guide and Reference Manual - 13 Feb 2015