An example of using server-side cursor with fukamachi/cl-dbi

cl-dbi-server-side-cursor.lisp

(defmacro do-rows ((row sql &key params) &body body)
  (with-gensyms (prepared cursor-name)
    
    (let ((cursor-sql (fmt "DECLARE ~A CURSOR FOR ~A"
                           cursor-name
                           sql))
          (fetch-sql (fmt "FETCH FROM ~A"
                          cursor-name)))
      `(progn
          (dbi:do-sql *connection* ,cursor-sql ,params)
          
          (loop with ,prepared = (dbi:prepare *connection*
                                              ,fetch-sql)
                for ,row = (dbi:fetch (dbi:execute ,prepared))
                while ,row
                do (progn ,@body))))))

;;; Example usage:

FINANCES/DB> (with-transaction
               (do-rows (row "SELECT *
                                FROM ods_simplywall_companies
                               WHERE price < price_target_low
                                 AND total_score > ?"
                         :params '(19))
                 (format t "ROW: ~S~%" row)))
ROW: (:|ticker| "NBIX" :|company_id| "DB5A2CD9-5845-45EF-8FE1-6176C911FCEA"
      :|price_target_high| 163 :|price_target_low| 101 :|price_target| 6363/50
      :|total_score| 20 :|price| 4829/50 :|etl_updated| 3827657640)
ROW: (:|ticker| "NBIX" :|company_id| "DB5A2CD9-5845-45EF-8FE1-6176C911FCEA"
      :|price_target_high| 163 :|price_target_low| 101 :|price_target| 6363/50
      :|total_score| 20 :|price| 4829/50 :|etl_updated| 3827657640)