LispWorks User Guide and Reference Manual > 19 Common SQL > 19.5 Symbolic SQL syntax > 19.5.2 Programmatic interface

NextPrevUpTopContentsIndex

19.5.2.1 Examples

The following example function, taken from the object-oriented SQL interface layer, makes an 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.

(let* ((class (class-of object))
          (key-slots (db-class-keyfields class)))
   (loop
     for key in key-slots
     for slot-name = (slot-definition-name key)
     for slot-type = (db-slot-definition-type key)
     collect
     [= (make-field-name class key)
        (lisp-to-sql-format
           (slot-value object slot-name)
           (if (listp slot-type)
               (car slot-type)
               slot-type))]
     into cols
     finally (apply (sql-operator 'and) cols)))
->
#<SQL-RELATIONAL-EXP "(EMP.EMPNO = 7369">

Here is another example that produces an SQL select statement:

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

LispWorks User Guide and Reference Manual - 22 Dec 2009

NextPrevUpTopContentsIndex