18.9.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:

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.