20.11.4 Retrieving LOB Locators as streams

To retrieve LOB locators as streams, specify the type of retrieved object as :input-stream or :output-stream in the query. For example:

(sql:select [image :input-stream] :from [mytable] :flatp t)

returns a list of streams.

For example, to print the name of all images that start with some "magic number", that is a sequence of 4 specific bytes (#xf5 #x12 #x4e #x23):

(let ((array (make-array 4 :element-type '(unsigned-byte 8))))
  (sql:do-query ((name lob-stream)
                 [sql:select [name][image :input-stream] 
                             :from [mytable]])
                (when (and (eq (read-sequence array lob-stream ) 4)
                           (eq (aref array 0) #xf5)
                           (eq (aref array 0) #x12)
                           (eq (aref array 0) #x4e)
                           (eq (aref array 0) #x23))
                  (print name))))

Closing the stream also frees the LOB object.

When using :output-stream , it is important to call force-output before trying to commit the changes, because the stream is buffered.

LispWorks User Guide - 11 Mar 2008