14.5 Symbolic SQL syntax

14.5.2 Programmatic interface

In some cases it is necessary to build SQL-expressions dynamically under program control. The following functions are provided to this end:

sql-operation

Function

sql-operation op &rest args

: Returns the SQL for an operator applied to its arguments. That is, this is equivalent to the second use of the[] syntax above where SQL strings represent symbolic expressions. This function is shorthand for

(apply (sql-operator op) args)

sql-expression

Function

sql-expression &key string table alias attribute type

Makes an SQL expression from the given keywords. This is equivalent to the first and third uses of the[] syntax as discussed in Section 14.5.1 on page 133. Valid combinations are:

: string
: table
: table and alias
: table and attribute
: table, attribute, and type
: table-alias and attribute
: table-alias, attribute, and type
: attribute
: attribute and type

sql-operator

Function

sql-operator symbol

: Returns the symbol for an SQL operator (that is,sql-symbol).

sql

Function

sql &rest args

: Makes SQL out of args. Each argument tosql is turned into SQL and then the args are concatenated with a single space between each pair. The rules for translation into SQL are as follows (based on the type of each individual argumentx):

string --> (format nil "'~A'" x)

: That is, the characters ofx between single quotes (this corresponds to an SQL string constant);

(sql null) --> "NULL"

: That is, an SQL null value;

symbol --> (symbol-name x)
number --> (princ-to-string x)
list --> (format nil "(~{~A~^,~})" (mapcar #'sql x))

: That is, the elements ofx in SQL, between parentheses separated by commas.

vector --> (format nil "~{~A~^,~}" (map 'list #'sql x))

: That is, the elements ofx in SQL, comma-separated, without parentheses. This is to allow the easy generation of SQL lists that require no parentheses such as table lists in select statements.

sql-expression --> x
otherwise --> (error)