Common SQL supports a symbolic query syntax across both the functional and object-oriented interface layers. It allows SQL and Common Lisp expressions to be mixed together -- with as much processing as possible done at compile-time. Symbolic SQL expressions are read as square-bracketed lists to distinguish them from Lisp expressions. However, each can be nested within the other to achieve the desired result.
By default, this reader syntax is turned off. To turn it on see Utilities.
There are some SQL operators which may take a single argument (for example
minus). These are read as calls to the appropriate SQL operator. For example:
[any '(3 4)] -> #<SQL-VALUE-EXP "(ANY (3,4))">
The select statement itself may be prepared for later query execution using the
 syntax. For example:
[select [person_id] [surname] :from [person]]
This form results in a SQL expression, which could be bound to a Lisp variable and later given to query to execute. For example:
Strings can be inserted in place of database identifiers within a select:
[> [foo] x]
(SQL-> #<SQL-IDENT "FOO"> X)
SQL operators which are supported are
userenv. There are also pseudo operators for calling database functions (see Calling database functions).
(select [count [*]] :from [emp])
(select [sql-function "COS" [age]] :from [EMPLOYEES])
Creating a full query (which can be used as argument to query):
sql-operation returns the SQL expression for an operator applied to its arguments. It also supports building SQL expressions which contain arbitrary SQL functions using the pseudo operators
sql-boolean-operator. For examples see sql-operation.
sql-operator returns the Lisp symbol for a SQL operator.
The function sql makes SQL out of the arguments supplied. Each argument to sql is turned into SQL and then the args are concatenated with a single space between each pair. A Lisp string maps to the same characters enclosed between single quotes (this corresponds to a SQL string constant).
nil maps to
"NULL", that is, a SQL null value. Symbols and numbers map to strings. A list maps to a parenthesised, comma-separated expression. A vector maps to a comma-separated expression, which allows the easy generation of SQL lists that require no parentheses such as table lists in select statements.
The rules for the conversion are fully specified in sql.
The following example function, taken from the object-oriented SQL interface layer, makes a SQL query fragment that finds the records corresponding a CLOS object (using the slots as attributes), when built into the where -clause of an updating form.
Here is another example that produces a SQL select statement:
enable-sql-reader-syntax switches square bracket syntax on and sets the state so that restore-sql-reader-syntax-state restores the syntax again if it is subsequently disabled. The function
disable-sql-reader-syntax switches square bracket syntax off and sets the state so that restore-sql-reader-syntax-state disables the syntax again if it is subsequently enabled.
The functions locally-enable-sql-reader-syntax and locally-disable-sql-reader-syntax switch square bracket syntax on and off, but do not change the state restored by restore-sql-reader-syntax-state. The intended use of these is in a file:
LispWorks User Guide and Reference Manual - 13 Feb 2015