Subject: Re: [PATCH] ob-sql: session + sql.el w/o prompt

From: Phil Estival <[email-masked]>
Date: Mon, 16 Jun 2025 14:26:13 +0200

This replaces the previous patch.  The `sql-product-interactive' call
was still using the modified signature and does no longer.

>From e27f8231c2f97e6f7f84b6acb793eec7060b8396 Mon Sep 17 00:00:00 2001
From: Phil Estival <[email-masked]>
Date: Wed, 13 June 2025 17:00:00 +0200
Subject: [PATCH 06/08] ob-sql: add session support

lisp/ob-sql.el: preparation of session support to ob-sql

* ob-sql.el: add `org-babel-sql-session-connect' and `org-sql-session-comint-output-filter'

@@ -418,4 +508,77 @@ argument mechanism."

-(defun org-babel-prep-session:sql (_session _params)
-  "Raise an error because Sql sessions aren't implemented."
-  (error "SQL sessions not yet implemented"))
+(defun org-babel-sql-session-connect (in-engine params session)
+  "Start the SQL client of IN-ENGINE if it has not.
+PARAMS provides the sql connection parameters for a new or
+existing SESSION.  Clear the intermediate buffer from previous
+output, and set the process filter.  Return the comint process
+buffer."
+  (let* ((buffer-name (format "%s" (if (string= session "none") ""
+                                     (format "[%s]" session))))
+         (ob-sql-buffer (format "*SQL: %s*" buffer-name)))
+    (org-require-package 'sql)
+    ;; initiate a new connection
+    (when (not (org-babel-comint-buffer-livep ob-sql-buffer))
+      ;; store the regexp used to clear output (prompt1|indicator|prompt2)
+      (let ((prompt-regexp (sql-get-product-feature in-engine :prompt-regexp ))
+            (prompt-cont-regexp (sql-get-product-feature in-engine :prompt-cont-regexp)))
+        (setq-local org-sql-session-clean-output
+              (plist-put org-sql-session-clean-output in-engine
+                         (concat "\\(" prompt-regexp "\\)"
+                                 "\\|\\(" org-sql-session--batch-terminate "\n\\)"
+                                 (when prompt-cont-regexp
+                                   (concat "\\|\\(" prompt-cont-regexp "\\)"))))))
+
+      (let ((sql-server   (cdr (assoc :dbhost params)))
+            (port      (cdr (assoc :port params)))
+            (sql-database (cdr (assoc :database params)))
+            (sql-user     (cdr (assoc :dbuser params)))
+            (sql-password (cdr (assoc :dbpassword params))))
+        (when port (setq-local sql-port port))
+        (save-window-excursion
+          ;; provides environment expressions to the comint service
+          (let ((process-environment (copy-sequence process-environment))
+                (variables (sql-get-product-feature in-engine :sql-environment)))
+            (mapc (lambda (elem) ; evaluate environment expressions
+                    (setenv (car elem) (eval (cadr elem))))
+                  variables)
+            (sql-product-interactive in-engine buffer-name)))
+
+        (let ((sql-term-proc (get-buffer-process ob-sql-buffer)))
+          (unless sql-term-proc
+            (user-error (format "SQL %s didn't start" in-engine)))
+
+          (with-current-buffer (get-buffer ob-sql-buffer)
+            ;; preamble commands
+            (let ((preamble (plist-get org-sql-session-preamble in-engine)))
+              (when preamble
+                (process-send-string ob-sql-buffer preamble)
+                (comint-send-input))))
+          ;; let the preamble execution finish and be filtered
+          (sleep-for 0.1))))
+
+    ;; set the redirection filter and return the SQL client buffer
+    (set-process-filter (get-buffer-process ob-sql-buffer)
+                        #'org-sql-session-comint-output-filter)
+    (get-buffer ob-sql-buffer)))
+
+(defun org-sql-session-comint-output-filter (_proc string)
+  "Process output STRING of PROC gets redirected to a temporary buffer.
+It is called several times consecutively as the shell outputs and flush
+its message buffer"
+
+  ;; Inserting a result in the sql process buffer (to read it as a
+  ;; regular prompt log) inserts it to the terminal, and as a result the
+  ;; ouput would get passed as input onto the next command line; See
+  ;; `comint-redirect-setup' to possibly fix that,
+  ;; (with-current-buffer (process-buffer proc) (insert output))
+
+  (when (or (string-match org-sql-session--batch-terminate string)
+            (> (time-to-seconds
+                (time-subtract (current-time)
+                               org-sql-session-start-time))
+               org-sql-timeout))
+    (setq-local org-sql-session-command-terminated t))
+
+  (with-current-buffer (get-buffer-create "*ob-sql-result*")
+    (insert string)))
+

 (provide 'ob-sql)

--
Phil Estival