[postmodern-devel] Can't nest QUERY within prepared-statement QUERY

[J.P. Larocque]

Hi again,

Thanks for the fix and the new release, Marijn.  I think I found another
problem.

When I try to query with a prepared statement with the QUERY macro, and
one of the arguments to the statement is the result of another QUERY
macro (whether directly or from the result of a function), strange
things crop up.  In one instance, the inner QUERY was returning nil
where it would return the expected integer otherwise.  Here's an
isolated case:

(postmodern:query "select cast ($1 as int)"
                  (let ((foo (postmodern:query "select 12" :single)))
                    (format t "result: ~S~%" foo)
                    foo))

This gives me the expected message "result: 12", but also signals a
condition, "Database error: Incorrect number of parameters given for
prepared statement ."

Below is the complete macroexpansion of the above form (without LET and
FORMAT).

(PROGN
 (CL-POSTGRES:PREPARE-QUERY POSTMODERN:*DATABASE* "" "select cast ($1 as int)")
 (CL-POSTGRES:EXEC-PREPARED POSTMODERN:*DATABASE* ""
                            (MAPCAR 'S-SQL:SQL-IZE
                                    (LIST
                                     (CAR
                                      (CL-POSTGRES:EXEC-QUERY
                                       POSTMODERN:*DATABASE* "select 12"
                                       'POSTMODERN::COLUMN-ROW-READER))))
                            'CL-POSTGRES:LIST-ROW-READER))

The sequence of evaluation is PREPARE-QUERY, then EXEC-QUERY (for the
inner "select 12"), then EXEC-PREPARED.  I guessed that doing things
with the database in-between PREPARE-QUERY and EXEC-PREPARED might be
the problem, so I modified QUERY to evaluate prepared-statement
arguments before PREPARE-QUERY.  This allows the inner query to complete
before the outer one actually starts.

diff -urNx '*~' postmodern-1.02/postmodern/query.lisp postmodern-1.02-modified/postmodern/query.lisp
--- postmodern-1.02/postmodern/query.lisp       2007-07-25 04:10:42.000000000 -0700
+++ postmodern-1.02-modified/postmodern/query.lisp      2007-07-27 23:03:16.000000000 -0700
@@ -55,9 +55,10 @@
                      :else :collect arg)))
     (destructuring-bind (reader single-row) (cdr (assoc format *result-styles*))
       (let ((base (if args
-                      `(progn
-                        (prepare-query *database* "" ,(real-query query))
-                        (exec-prepared *database* "" (mapcar 'sql-ize (list , at args)) ',reader))
+                      (let ((arg-values (gensym)))
+                        `(let ((,arg-values (mapcar 'sql-ize (list , at args))))
+                          (prepare-query *database* "" ,(real-query query))
+                          (exec-prepared *database* "" ,arg-values ',reader)))
                       `(exec-query *database* ,(real-query query) ',reader))))
         (if single-row
             `(multiple-value-call 'car-of-first-value ,base)

Now my first example works, as does the result of one prepared-statement
QUERY's being indirectly nested in another prepared-statement QUERY.

-- 
J.P. Larocque: <piranha at thoughtcrime.us>, <piranha at ely.ath.cx>