Question on usage when yields "extracts" data out of the query

[vindarel]

Hi,

By following SxQL and cl-dbi's doc I have come to this workflow:

(defun my-query () (select …))

(let* ((query (dbi:prepare *connection*
                           (yield my-query)))
       (query (dbi:execute query)))
  (dbi:fetch[-all] query)

When my query ends with a value

    (where (:> :row.quantity 0)))) 

the SxQL object looks similar:

#<SXQL-STATEMENT:  … WHERE (row.quantity > 0)

but I need yield to get the query string, and yield replaces the 0 with a ? placeholder and returns it as a second value:

"… WHERE (row.quantity > ?)""
(0)

then on the call to dbi:execute I have to repeat the 0 argument (which is clearly an issue when the code is split into functions).

I can maybe use multiple-value-bind but I find it unnecessarily tedious. Did I miss something, or could there be a function that simplifies all this?

Thanks

[jnc-nj]

So I actually use this lib in production, and this is what I do:

(dbi:with-connection <connection-params>
	      (multiple-value-bind (string params) (yield <statements>)
		(let* ((prep (dbi:prepare conn string))
		       (exec (dbi:execute prep params))
		       (fetch (loop for row = (dbi:fetch exec)
				    while row
				    collect (alexandria:plist-alist row))))
		 <do stuff>)

I think the original intent is to allow params to be modified prior to being executed...
So I ended up with a macro along the lines of:

(defmacro with-db-query ((connection
			  &key top-result-only first-element-only
			    no-duplicates no-id (no-index t))
			 &body statements)
  `(let ((conn-params (cons 'conn ,connection)))
     (eval `(dbi:with-connection ,conn-params
	      (multiple-value-bind (string params) (yield ,,@statements)
		(let* ((prep (dbi:prepare conn string))
		       (exec (dbi:execute prep params))
		       (fetch (loop for row = (dbi:fetch exec)
				    while row
				    collect (alexandria:plist-alist row))))
		  (when ,,no-id
		    (setf fetch
			  (loop for item in fetch
				collect (remove-if #'(lambda (arg) (string= (car arg) "id"))
						   item))))
		  (when ,,no-index
		    (setf fetch
			  (loop for item in fetch
				collect (mapcar #'cdr item))))
		  (when ,,first-element-only
		    (setf fetch (mapcar #'car fetch)))
		  (when ,,top-result-only
		    (setf fetch (car fetch)))
		  (when ,,no-duplicates
		    (setf fetch (remove-duplicates fetch :test #'equal)))
		  fetch))))))

To act as a wrapper over the original author's api.