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", "postgresql" and "sqlite". 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 run time 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.
Specifies the filename as is.
This is equivalent to
":memory:" and specifies a private, temporary in-memory database.
This is equivalent to
"" and specifies a private, temporary on-disk database.
The special variable
sql:*sqlite-library-path* contains the FLI shared library name for SQLite. It defaults to
"-lsqlite3" on non-Windows platforms, which should work if SQLite is installed. On Windows, it defaults to
"sqlite3.dll", which requires that DLL to be on the path. Note that 64-bit and 32-bit LispWorks require different DLL files.
The Common SQL SQLite interface assumes that the library is compiled with standard options and that SQLite is not configured in an unusual way. Most importantly, if the threading mode is single-thread (either because the library is compiled as single-thread, or because
sqlite3_config set it to single-thread), then the Common SQL SQLite interface is not thread-safe anymore. This situation is quite unlikely to happen.
By default, the connection is opened as a UTF-8 connection (using the C function
:encoding argument to connect can have the value
:utf-8 which all use the default (that is, have no effect), and
:utf-16-native, which opens the connection using UTF-16 in the native byte order (using 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. When opening as UTF-16, the keywords
:vfs are ignored.
The sqlite-keywords keyword argument to connect allows you to specify several parameters controlling the behavior of the connection. sqlite-keywords is a property list, providing values for the SQLite-specific keywords
:uniform-type-per-column. These keywords affect the connection as follows.
By default, the connection is opened with opening modes create and readwrite, which means that the file is created if it does not exist, and the database can be modified. The SQLite-specific keyword
:open-mode in sqlite-keywords can be used to change this. The value
:readonly specifies that the file must exist (so connect fails if it does not exist) and is opened for reading only (so it is not possible to modify it). The value :
readwrite means that the file must exist and the database can be modified.
By default, the threading mode of the connection is "serialized" (so it is thread-safe). You can change this by the SQLite-specific keyword
:threading-mode in sqlite-keywords, which can take the values
:serialized. When the threading mode is
:multi-thread (rather than
:serialized), it is not actually thread-safe, and you can access it only on one thread at a time (but it can be accessed from different threads over time). The term "multi-thread" means that you can access different connections at the same time on different threads.
:multi-thread is probably more efficient, but we do not know by how much.
By default, connection-spec can be a URI filename, which is a string starting with "file:" (see https://www.sqlite.org/uri.html "URI Filenames In SQLIte" for details). Whether this is allowed is controlled by the SQLite-specific keyword
:uri in sqlite-keywords, which defaults to
t, and can be switched off by passing
By default, the connection cache mode is the system default. The SQLite-specific keyword
:cache-mode in sqlite-keywords can be used to change this to either
:shared. See https://www.sqlite.org/sharedcache.html "SQLite Shared-cache mode" for details.
:Shared mode probably improves performance if you connect multiple times to the same file.
The SQLite-specific keyword
:uniform-type-per-column in sqlite-keywords affects the default type for fields in the results of queries. See Tables containing a uniform type per column.
See Using SQLite for other SQLite-specific features.
LispWorks User Guide and Reference Manual - 20 Sep 2017