18.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%'))">