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 sql package.
The examples in this chapter assume that the sql package is visible.
Three steps are required to initialize the SQL interface:
The remainder of this section describes how you perform these steps.
require with 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.
The same application can use more than one interface, and needs to call require to load each interface that it uses.
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 :database.
When a connection is no longer required, it should be closed by calling disconnect.
The minimal code to initialize a connection is loading the code and connecting. For example, using only Oracle:
(require "oracle")
(sql:connect "scott/tiger")
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:
(handler-case
(sql:initialize-database-type)
(error (cc)
;; tell the user of failure to initialize Oracle
))
Note: This section applies only to Unix-like operating systems.
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.
In order to override the default loading of database library code, you may set *sql-libraries*. To control messages while loading the libraries, set *sql-loading-verbose*.
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 equalp.
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, scott and personnel, and then prints out the connected databases.
(setf *default-database-type* :odbc)
(connect "scott")
(connect "personnel" :database-type :odbc)
(print *connected-databases*)
For database-type :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 tnsnames.ora file.
To connect to Oracle via SQL*Net, connection-spec is of the form username/password@host where host is an Oracle hostname.
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.
For database-type :odbc or :odbc-driver, connection-spec may take the canonical form described for connect, but an additional syntax is also allowed.
connect keyword arguments :encoding, :signal-rollback-errors and :date-string-format are all ignored.
connection-spec should have one of the forms:
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.
Note that this means that "xyz" and "@xyz" are both interpreted to give the same values (username is null, password is null, dsn is "xyz").
For database-type :mysql, connection-spec may be in the canonical form described for connect, but it may also have the extensions described in this section.
In both the string and plist forms of connection-spec described below, any part that is omitted defaults to the MySQL default:
anonymous user
No password
No default database
localhost
3306 (unless using unix-socket).
connection-spec can be a string of the form:
username/password/dbname@hostname:port
where port is a decimal number specifying the port number to use. port can be omitted along with the preceding ':'.
hostname can be omitted. If port is omitted too, the '@' can be omitted as well. If port is supplied and hostname is not supplied, then both the '@' and the ':' are required, for example:
me/my-password/my-db@:3307
hostname may also specify a unix socket name, which must start with the character '/'.
dbname may be omitted along with the preceding '/'.
password may be omitted. If dbname is also omitted, the preceding '/' can be omitted too.
connection-spec can be a plist containing (some of) the keywords :username, :password, :dbname, :hostname, :port, :connection, :unix-socket.
Each of these keywords may be omitted.
If :unix-socket is specified, then none of :hostname, :port and :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:port. If :connection is specified then it can be a string conforming to one of these patterns:
Must start with '/'
That is, the value connection supplied in a plist connection-spec is interpreted just like the part of a string connection-spec following the '@' character.
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.
Download the 32-bit or 64-bit MySQL package to match your LispWorks image.
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.
To build the 32-bit dynamic library:
gcc -dynamiclib -fno-common \
-o /usr/local/mysql/lib/libmysqlclient_r.dylib \
-all_load /usr/local/mysql/lib/libmysqlclient_r.a -lz
To build the 64-bit dynamic library:
gcc -m64 -dynamiclib -fno-common \
-o /usr/local/mysql/lib/libmysqlclient_r.dylib \
-all_load /usr/local/mysql/lib/libmysqlclient_r.a -lz
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.
An alternate way to create a dynamic library is to build MySQL from its source code with the --enable-shared flag.
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")
You can also set *mysql-library-path* to a full path, which avoids the need to set *mysql-library-directories*.
If the environment variable LW_MYSQL_LIBRARY. is set, then its value is used instead of the value of *mysql-library-path*.
For database-type :postgresql, connection-spec must be either a string in the format specified by the PostgreSQL libraries or a plist.
If connection-spec is a string then it should be in the format specified by
www.postgresql.org/docs/9.3/static/libpq-connect.html#LIBPQ-CONNSTRING
dbname=test user=scott password=tiger host=scandium
connection-spec can be a plist containing (some of) the keywords :username (or :user), :password, :dbname, :hostname (or :host), :port, :connection. Each of these keywords may be omitted, but if :connection is specified, then :hostname and :port must not be specified.
The value supplied for :hostname can be a raw hostname or a string of the form hostname:port. The value supplied for :post can be an integer or a string naming a service.
If :connection is specified then it can be a string conforming to one of these patterns:
The values should not be escaped or quoted: LispWorks will escape and quote it as needed before passing it to the PostgreSQL library.
LispWorks sets the PostgreSQL session variable standard_conforming_strings to 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