The functional interface provides a full set of Data Manipulation and Data Definition functions. The interface provides a SQL-compatible means of querying and updating the database from Lisp. In particular, the values returned from the database are Lisp values -- thus smoothly integrating user applications with database transactions. An embedded syntax is provided for dynamically constructing sophisticated queries through select. Iteration is also provided via a mapping function and an extension to the loop macro. If necessary, the basic functions query and execute-command can be called with SQL statements expressed as strings. It is also possible to update or query the data dictionary.
The function select returns data from a database matching the constraints specified. The data is returned, by default, as a list of records in which each record is represented as a list of attribute values.
The square bracket syntax assumes that sql symbols are visible. Therefore when using the
 syntax, ensure that the current package either is
sql, or is a package which has the
sql package on its package-use-list.
For a description of the symbolic SQL syntax see Symbolic SQL syntax. For example, the following is a potential query and its result:
:mysql, further control over the values returned from queries is possible as described in Types of values returned from queries.
To output the results of a query in a more easily readable tabulated way, use the function print-query. For example the following call prints two even columns of names and salaries:
Modifications to the database can be done using the following functions;
insert-records creates records in a specified table. The values can be either specified directly with the argument values or in the argument av-pairs, or they can be the result of a query specified in the query argument. The attributes can be specified with the argument attributes or in the argument av-pairs.
Operations which add or modify records sometimes need to perform an internal query to obtain type information for the relevant attributes. In principle it is possible for the database schema to change between update operations, and hence this query is run for each update operation. This can be a significant overhead.
For tables which are guaranteed to have a constant schema, you can optimize performance by adding a cache of these internal query results, using the function
cache-table-queries. This can also be used to reset the cache if the table schema is actually altered. To control the default caching behavior throughout every database connection, you can set the variable
A transaction in SQL is defined as
the connect, or from a commit,
The macro with-transaction executes a body of code and then does a commit, unless the body failed in which case it does a rollback. Using this macro allows your code to cope with the fact that transactions may be handled differently in the different vendor implementations. Any differences are transparent if the update is done within a with-transaction form.
Applications should perform all database update operations in a with-transaction form (or follow them with commit or rollback) in order to safely commit or discard their changes. This applies to operations that modify either the data or the schema.
The following example shows a series of updates to an employee table within a transaction. This example would commit the changes to the database on exit from with-transaction. This example inserts a new record into the
emp table, then changes those employees whose department number is 40 to 50 and finally removes those employees whose salary is more than 300,000.
Common SQL has three iteration constructs: a
do loop, a mapping function, and an extension to the Common Lisp loop macro.
The function map-query maps a function across the results of a query and returns its result in a sequence of a specified type, like the Common Lisp
query-expression can be a string, a SQL expression (a result of the [..] syntax) or a prepared statement (a result of prepare-statement).
Each iteration of the loop assigns the next record of the table to the variable var. The record is represented in Lisp as a list. Destructuring can be used in var to bind variables to specific attributes of the records resulting from query-expression. In conjunction with the panoply of existing clauses available from the
loop macro, the new iteration clause provides an integrated report generation facility.
Suppose the name of everyone in an employee table is required. This simple query is shown below using the different iteration method. The function map-query requires flatp to be specified; otherwise each name would be wrapped in a list.
The following extended loop example binds, on each record returned as a result of the query,
salary, accumulates the salary, and for salaries greater than 2750 increments a count, and prints the details. Finally, the average salary is printed.
Sometimes it is necessary to execute vendor-specific SQL statements and queries. For these occasions Common SQL provides the functions query and execute-command. They can also be used when the exact SQL is known in advance and thus the square bracket syntax is not needed. The query expression can be a string, a SQL expression (a result of the [..] syntax) or a prepared statement (a result of prepare-statement).
(attribute-type [ename] [emp]) -> :char
LispWorks User Guide and Reference Manual - 13 Feb 2015