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 must be one, two or three Lisp forms inside the square brackets. The first form must be a symbol, string or a recursive database identifier (that is, another square brackets expression). The second form, if present, must be a symbol or a string. The third form, if present, must be a keyword.
The case with a single form that is a string is special, and is interpreted as a direct SQL expression rather than an identifier (see Enclosing a SQL expression directly below).
When a string or a symbol is used to specify all or part of the identifier and the string (or name of the symbol) cannot be used as an identifier (because it contains special characters or matches a SQL reserved word), then it is wrapped with double quotes in the resulting SQL.
If there is more than one form inside the square brackets, and the first form is a symbol that is recognized as a SQL operator or a pseudo-operator, then the expression is interpreted as an operation rather than as an identifier (see the following sections).
The first form is always interpreted as specifying a string that is part or all of the identifier. For a symbol, it is the symbol name and for a recursive identifier it is the string that would be generated for this identifier. In the examples below, the text following the => (and optionally up to the semicolon) shows what is generated for the resulting SQL.
=> W%((jj ; single form string not quoted.
If the second form is a string and the first form is not a string, then the first form specifies the name of the identifier and the second form specifies an alias. In this case there must not be a third form. The alias identifier is useful for giving tables aliases in the
from part of the SQL
=> FOO AA
If there is a third form, or the second form is not keyword, or the first form is a string, then the second form specifies an identifier qualified by the first form, that is they are combined with a period in the middle:
If there are only two forms and the second form is a keyword, or there are three forms, then the second form (in the two form case) or the third form (in the three form case) specifies a type associated with the identifier. The type does not affect the SQL statement that the database sees. It is used when the identifier is part of the selection list, to tell Common SQL what type the value should be. Such identifiers should appear only in the selection list of queries.
When you use a keyword to specify the type of an expression as described in Enclosing database identifiers, you are telling common SQL that the values retrieved for this expression should be of a specific type. For example, if you call:
(sql:select [name :string] :from [TableName])
There are four keywords that are supported by all common SQL backends:
:single-float. For each of these keywords, the values are mapped to the matching Common Lisp type. If this is not possible, the value is returned as
:binary is supported by most of backends (except Microsoft Access and PostgreSQL). The value that is returned for
:binary is an array with element type
(unsigned-byte 8). On Oracle,
:binary can be used only for columns of binary type, so it is only useful when you want to retrieve the contents of a BLOB directly, because for plain RAW columns it is the default anyway. Other backends allow you to retrieve at least strings as binary values.
[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).
(sql:select [count [*]] :from [emp])
(select [sql-function "COS" [age]] :from [EMPLOYEES])
Creating a full query (which can be used as argument to query):
[= [name] "John"]
However, Microsoft SQL Server (which can be used via ODBC) requires the N syntax for string literal that are not entirely ASCII, or contain characters that are not recognized by the server code page. (The N syntax prefixes the string literal by the character N, for example
N'Greek', rather than
'Greek'.) Although this syntax is part of the SQL standard, not all SQL backends accept it (in particular, SQLite and Microsoft Access (via ODBC) do not). Thus the decision whether to use the N syntax needs to be made at run time and requires the SQL backend (which is represented by the database object that connect returns). By default, the symbolic SQL syntax does not use the N syntax, but the special pseudo-operator
string can be used to override this.
string takes a required argument, which must be a string, and an optional argument, a database (which defaults to *default-database*), and produces the appropriate syntax for that database. The example above can be written using
string like this:
The same database must be used for the
string pseudo-operator and the function/macro that uses the resulting expression. In the example above, the function is select and the database not specified at all, so both
string and select will use *default-database*. This restriction means that the
string pseudo-operator cannot be used to generate a pre-existing expression, which is otherwise possible with the symbolic SQL syntax. For example, your code might contain:
(defvar *match-name-starting-with-cf* [like [name] "CF%"])
You can perform approximately what the
string pseudo-operator does by using string-prefix-with-n-if-needed:
Another option is to set the variable *use-n-syntax-for-non-ascii-strings* to
t at compile time, which causes all string literals that are not entirely ASCII to be produced with N syntax. That would generate code that will work with almost all SQL backends, but not with SQLite or Microsoft Access (which do not support the N syntax). The advantage is that, if you have a large number of string literals, then you do not have to change them all: you just need to recompile your code with *use-n-syntax-for-non-ascii-strings* set to
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 - 20 Sep 2017