23.1 Introduction

This chapter covers the following areas:

Initialization and Connection.
The Functional SQL Interface.
The Object-Oriented (CLOS) SQL Interface.
The Symbolic SQL Syntax.
SQL I/O Recording.
SQL Interface Errors.

The LispWorks SQL interface uses the following database terminology:

Data Definition Language (DDL)
	The language used to specify and interrogate the structure of the database schema.
Data Manipulation Language (DML)
	The language used for retrieving and modifying data. Also known as query language.
table	A set of records. Also known as relation.
attribute	A field of information in the table. Also known as column.
record	A complete set of attribute values in the table. Also known as tuple, or row.
view	A display of a table configured to your own needs. Also known as virtual table.

23.1.1 Overview

Common SQL is designed to provide both embedded and transparent access to relational databases from the LispWorks environment. That is, SQL/relational data can be directly manipulated from within Lisp, and also used as necessary when instantiating or accessing particular Lisp objects.

The SQL interface allows the following:

Direct use of standard SQL statements as strings.
Mixed symbolic SQL and Common Lisp expressions.
Implicit SQL invocation when instantiating or accessing CLOS objects.

The SQL interface provides these features through two complementary layers:

A functional SQL interface.
An object-oriented SQL interface.

The functional interface provides users with Lisp functions which map onto standard SQL DML and DDL commands. Special iteration constructs which utilize these functions are also provided. The object-oriented interface allows users to manipulate database views as CLOS classes via def-view-class. The two interfaces may be flexibly combined in accordance with system requirements and user preference. For example, a select query can be used to initialize slots in a CLOS instance; conversely, accessing a CLOS slot may trigger an implicit functional query.

23.1.2 Supported databases

Common SQL supports connections to various databases using the driver/client libraries for each interface-platform combination as indicated below in Supported driver/client libraries for each interface-platform combination.

Common SQL may work, but is currently untested, with driver/interface/platform combinations shown as "None tested". We would be pleased to hear of your experience with these other driver/interface/platform combinations, at lisp-support@lispworks.com.

Supported driver/client libraries for each interface-platform combination
`interface` (module name)	"odbc"	"oracle"	"postgresql"	"mysql"	"sqlite"
Default database type	`:odbc-driver`	`:oracle`	`:postgresql`	`:mysql`	`:sqlite`
Other database type	`:odbc`	`:oracle8`	None	None	None
Windows	Microsoft SQL Server Oracle Postgres	Oracle 9i(r2) and later	Postgres	MySQL	SQLite 3.6.12 or later
macOS	MySQL Postgres	Oracle 10g and later	Postgres	MySQL	SQLite 3.6.12 or later
Linux	MySQL Postgres	Oracle 9i(r2) and later	Postgres	MySQL	SQLite 3.6.12 or later
FreeBSD	None tested	Not supported	Postgres	MySQL	SQLite 3.6.12 or later
Solaris/Intel	None tested	Oracle 10g and later	Postgres	MySQL	None tested

The keyword shown in the second and third rows is the corresponding value of the database-type argument to connect. When a client library version is shown, it is the earliest version that was tested successfully: later versions should work too, and in many cases earlier versions may work too.

Note: MySQL versions prior to 4.1.1 should be run in ANSI mode to work with Common SQL. That is, mysqld must be started with --ansi or the ansi option must appear in the [mysqld] section of its configuration file.

Note: To use PostgreSQL on any non-Microsoft Windows platform, LispWorks/Common SQL requires PostgreSQL version >= 8.x built with --enable-thread-safety.