NextPrevUpTopContentsIndex

18.5.1.2 SQL strings representing symbolic expressions

There are some SQL operators which may take a single argument (for example any , some , all , not , union , intersect , except , and minus ). These are read as calls to the appropriate SQL operator. For example:

[any '(3 4)] -> #<SQL-VALUE-EXP "(ANY (3,4))">

This causes no conflict, however, as it is illegal to use these reserved words as identifiers in SQL. Similarly with two argument operators:

[> [baz] [beep]] 
     -> #<SQL-RELATIONAL-EXP "(BAZ > BEEP)">

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 an SQL expression, which could be bound to a Lisp variable and later given to query to execute. For example:

[select [foo] [bar *] 
        :from '([baz] [bar])
        :where [or [= [foo] 3] 
                   [> [baz.quux] 10]]]
->
#<SQL-QUERY 
    "(SELECT FOO,BAR.* FROM BAZ,BAR
                      WHERE ((FOO = 3) 
                             OR (BAZ.QUUX > 10)))">

Strings can be inserted in place of database identifiers within a select :

[select [foo bar] [baz] 
        :from '([foo] [quux])
        :where [or [> [baz] 3] 
                   [like [foo bar] "SU%"]]]
->
#<SQL-QUERY: 
   "(SELECT FOO.BAR,BAZ 
         FROM FOO,QUUX
         WHERE ((BAZ > 3) 
                OR (FOO.BAR LIKE 'SU%')))">

Any non-constant included gets filled in at runtime, for example:

[> [foo] x]

when macroexpanded reads as

(SQL-> #<SQL-IDENT "FOO"> X)

which constructs the actual SQL string at runtime.

Any arguments to an SQL operator that are Lisp constants are translated to the matching SQL construct at compile-time, for example:

"foo" -> "'foo'"
3 -> "3" 
'("this" 5 "that") -> "('this', 5, 'that')"
'xyz -> "XYZ"

SQL operators which are supported are null , exists , * , + , / , - , like , substr , and , or , not , in , all , any , some , || , = , < , > , >= , <= , <> , order-by , count , max , min , avg , sum , minus , nvl , distinct , except , intersect , union , slot-value , between and userenv . There's also the pseudo operator function (see Calling database functions).

The general syntax is: [<operator> <operand> ...] , for instance:

(select [count [*]] :from [emp])

The operand can itself be a SQL expression, as in the following example:

(sql:create-table [company] 
                  '(([name] (varchar 20) not-null)))
 
(loop for company in '("LispWorks Ltd"
                       "Harlequin"
                       "Oracle"
                       "Rover"
                       "Microsoft") 
      do 
      (sql:insert-records :into [company]
                          :av-pairs `(([name] ,company))))
 
(sql:create-table [person] 
                  '(([surname] (varchar 20) not-null) 
                    ([firstname] (varchar 20) not-null)))
 
(loop for person in '(("Joe" "Bloggs") 
                      ("Fred" "Smith") 
                      ("Rover" "the Dog")
                      ("Fido" "the Dog")) 
      do (sql:insert-records :into [person] 
                             :av-pairs 
                             `(([firstname] ,(car person)) 
                               ([surname] ,(second person)))))
 
(sql:select [name] 
            :from [company] 
            :where [= [name] 
                      [any [select [surname] 
                                   :from [person]]]])
 
(sql:select [surname] 
            :from [person] 
            :set-operation [union [select [firstname] 
                                          :from [person]]])

LispWorks User Guide - 21 Jul 2006

NextPrevUpTopContentsIndex