Nepheles <nepheles@myrealbox.com> wrote:
+---------------
| How does one represent the result of an SQL query as a Lisp object,
| while maintaining as much information as possible? The obvious method
| seems to be to have a list of rows, each containing a list of results,
| but that makes it awkward to refer to column names.
+---------------

Call me simple-minded, perhaps, but I tend to just cons a row of column
names onto the front of the list of result row-lists, and then pass the
resulting single list around, e.g., using Eric Marsden's "pg.lisp":

    > (defun simple-query (query)
	"SIMPLE-QUERY -- Does PostgreSQL query using a shared,
	per-HTTP-request connection.
	Success ==> (values results nil) ; A list of rows+1 lists of strings.
	Fail    ==> (values nil error)   ; A SQL-ERROR condition object."
	(handler-case
	    (with-shared-pg-connection (conn *http-request*)
	      (let* ((result (pg-exec conn query)) ;Single query, no xaction.
		     ;; Pull out just the column names from the attributes.
		     (cols (mapcar #'car (pg-result result :attributes))))
		(values (cons cols (pg-result result :tuples)) nil)))
	  (error (cc)
	    (values nil cc))))

    SIMPLE-QUERY
    > (simple-query "select * from toy limit 4")

    (("season" "media" "title" "upd")
     ("fall" "tape" "My Favorite Thanksgiving" 16)
     ("xmas" "book" "My Favorite Christmas" 2)
     ("xmas" "video" "The Grinch who Stole Christmas" 4)
     ("summer" "book" "Unusual 4ths of July" 17))
    > 

If you just want to output an HTML table, it's already in a reasonably
convenient form. Or if you need to use the column labels more than once,
just peel them off and re-use them:

    > (let* ((results (simple-query "select * from toy limit 4"))
	     (columns (car results))
	     (rows (cdr results)))
	(loop for row in rows do
	  (loop for col in columns	; repeats each time
		and item in row do
	    (format t "~&~a:~10t~a~%" col item))
	(terpri)))

    season:   fall
    media:    tape
    title:    My Favorite Thanksgiving
    upd:      16

    season:   xmas
    media:    book
    title:    My Favorite Christmas
    upd:      2

    season:   xmas
    media:    video
    title:    The Grinch who Stole Christmas
    upd:      4

    season:   summer
    media:    book
    title:    Unusual 4ths of July
    upd:      17

    NIL
    > 


-Rob

-----
Rob Warnock			<rpw3@rpw3.org>
627 26th Avenue			<URL:http://rpw3.org/>
San Mateo, CA 94403		(650)572-2607