Subject: bug#76025: [PATCH] ob-sql: session

From: Phil Estival <[email-masked]>
Date: Sun, 15 Jun 2025 17:16:08 +0200

I'm submitting again the patches for ob-sql.el, taking into
considerations the previous review, commented below. I also took good
note of Michael remarks.  The attached series of patches will work
without any modification to sql.el. I'll introduce later a local
`sql-connection' bound to the session name. In the absence of it,
sessions will work, but will ask to confirm provided connection
parameter upon establishing connection.

* [2025-05-17 18:05 +0000] Ihor Radchenko <[email-masked]>:
> Phil Estival <[email-masked]> writes:
>
>>> May you please update your latest patch for ob-sql.el, converting it
>>> into (1) patch to sql.el; (2) patch for ob-sql.el that assumes changes
>>> to sql.el?
>>
>> Here they are
>> 1) the patch of sql.el
>> 2) the list of changes in ob-sql.el against release_9.7.30.
>>    The patch that assumes the change in sql.el is n°13.
>
> Thanks! I will comment on the Org-related part.
>
>> Subject: [PATCH 01/14] ob-sql: session support. Introduces new variables and
>>  functions
>>
>> * lisp/ob-sql.el: new variables and functions for session support for
>> postgres and sqlite. Requires sql.el for a connection to a session.
>> SQL clients are configured by a preamble of commands given to the SQL
>> shell.  Custom variables are declared in a new sub-group ob-babel-sql.
>> The echo of an SQL ANSI comment is to be appended to the source block
>> of SQL commands for comint to detect when the commands terminate,l
>> instead of relying on prompt detection.
>
> This is not a complete changelog of your changes. Please check out
> https://orgmode.org/worg/org-contribute.html#commit-messages and follow
> the format.
>
> In general, because you plan to be the maintainer, please familiarize
> yourself with all the conventions discussed in
> https://orgmode.org/worg/org-contribute.html and
> https://orgmode.org/worg/org-maintenance.html
>
>>  ;; Author: Eric Schulte
>>  ;; Maintainer: Daniel Kraus <[email-masked]>
>> +;; Maintainer: Philippe Estival <[email-masked]>
>>  ;; Keywords: literate programming, reproducible research
>>  ;; URL: https://orgmode.org
>
> I think adding you as a maintainer should be a separate patch to make
> the change distinct.

Patch follows

>
>>  (require 'ob)
>> +(require 'sql)
>
> Do we need to load sql.el early? May we do it dynamically instead?
>

I see that calling `org-require-package' when establishing a new
connection is more rational. Changed in the following patch.

>> +(defvar org-babel-sql-session-start-time)
>
> This variable is unused. What is it for?
>

Starting time of one session block execution, used to exit on timeout.
Fixed in the following patch.

What naming convention to opt for ?  org-babel-[lang]-var ?
or ob-[lang]-var ?

>> +(defvar org-sql-session-preamble
>> +  (list
>> +   'postgres "\\set ON_ERROR_STOP 1
>> +\\pset footer off
>> +\\pset pager off
>> +\\pset format unaligned"	 )
>> +  "Command preamble to run upon shell start.")
>
> Should it be a defcustom?
>

Yes. It is now in the next patch.

>> +(defvar org-sql-session-command-terminated nil)
>
> AFAIU, this is some kind of flag to be used inside sql comit buffer.
> Should it be buffer-local?

Yes, it is now too.

> Also, please add a docstring.
>
>> +(defvar org-sql-session--batch-terminate  "---#"  "To print at the end of a command batch.")
>
> This is a bit weird formatting. In Elisp, the docstring is usually
> placed on a separate line. Also, please check out
> https://www.gnu.org/software/emacs/manual/html_node/elisp/Documentation-Tips.html
> Your docstring here reads confusing.

I hope the formatting and style of the others docstrings is now fine.

>
>> +(defvar org-sql-batch-terminate
>> +  (list 'sqlite (format ".print %s\n" org-sql-session--batch-terminate)
>> +        'postgres (format "\\echo %s\n" org-sql-session--batch-terminate))
>> +  "Print the command batch termination as last command.")
>
> This implies that you treat `org-sql-session--batch-terminate' as a
> constant. If so, maybe declare it as such.
>
> Also, should it be a defcustom?
>
>> +(defvar org-sql-terminal-command-prefix
>> +  (list 'sqlite "\\."
>> +        'postgres "\\\\")
>> +  "Identify a command for the SQL shell.")
>
> This variable is unused.
>

Right, it is no longer in the present mileage.

>> +(defvar org-sql-environment
>> +  (list 'postgres '(("PGPASSWORD" sql-password))))
>
> Also unused.
>

removed.

>> +(defvar org-sql-session-clean-output nil
>> +  "Store the regexp used to clear output (prompt1|termination|prompt2).")
>
> Should it be buffer-local in session buffer?
>

Yes.

>> +(defvar org-sql-session-start-time)
>> +(defvar org-sql-session-command-terminated nil)
>
> Same question.
>
>> +(defvar org-sql-session--batch-terminate  "---#"  "To print at the end of a command batch.")
>
> Duplicate defvar.
>
>> +(defcustom org-sql-run-comint-p 'nil
>
> There is no need to quote nil and numbers.
> They are self-quoting.
>
>> +  "Run non-session SQL commands through comint if not nil."
>> +  :type '(boolean)
>
> :type 'boolean
>
>> +(defcustom org-sql-timeout '5.0
>> +  "Abort on timeout."
>> +  :type '(number)
>
> :type 'number
>
>> +(defcustom org-sql-close-out-temp-buffer-p 'nil
>> +  "To automatically close sql-out-temp buffer."
>
> The docstring is not very clear.
>

Changed to "When non-nil, close the temporary buffer holding output results."

>> +  :type '(boolean)
>
> :type 'boolean
>
>> +(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 org-sql-session-command-terminated t))
>> +
>> +  (with-current-buffer (get-buffer-create "*ob-sql-result*")
>> +    (insert string)))
>
> Using global `org-sql-session-command-terminated',
> `org-sql-session-start-time', and "*ob-sql-result*" buffer will not be
> reliable when there are multiple comint sessions. It will also make it
> impossible to implement async sessions.
> Please use some other approach (for example, buffer-local or
> process-local variables)
>

>> * lisp/ob-sql.el: removal of org-assert-version makes org-macs no
>> longer needed.
>> ...
>> -
>> -(require 'org-macs)
>> -(org-assert-version)
>> -
>
> (org-assert-version) is needed to detect mixed Org versions.
> (require 'org-macs) there is mostly to provide `org-assert-version'.
>

I reintroduced `org-macs to provide `org-require-package in the following
patches.  But shouldn't `org-assert-version be checked only once when
org core loads instead of subsequent modules?

>> Subject: [PATCH 04/14] ob-sql: realign variables for improved readability.
>
> We do not allow whitespace-only commits. See
> https://orgmode.org/worg/org-contribute.html#orgbbd6fd6
>

>> +	  (when colnames-p (with-temp-buffer
>
> nitpick: It is unusual to place
> (when condition body-line1
>                ...)
>
> The common practice is
> (when condition
>   body)
>

Ok, fixed.

>> +        (setq 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 "\\)"))))))
>
> This will be broken when multiple sessions for different engines are
> used in parallel. Please avoid global state.
>

Set as local now.

>> +	     (let ((sql--buffer
>> +		    (org-babel-sql-session-connect in-engine params session)))
>
> Why `sql--bufer'? (double slash) It is not a variable sql.el uses.
>

Replaced by `ob-sql-buffer'.
IIUC, a double dash indicates a variable used by the prefixed package?

>> +	       (with-current-buffer (get-buffer-create "*ob-sql-result*")
>> +		 (erase-buffer))
>> +	       (setq org-sql-session-start-time (current-time))
>> +	       (setq org-sql-session-command-terminated nil)
>> +
>> +	       (with-current-buffer (get-buffer sql--buffer)
>> +		 (process-send-string (current-buffer)
>> +				      (org-sql-session-format-query
>> +                                       (org-babel-expand-body:sql body params)
>> +                                       in-engine))
>> +		 (while (or (not org-sql-session-command-terminated)
>> +                            (> (time-to-seconds
>> +                                (time-subtract (current-time)
>> +                                               org-sql-session-start-time))
>> +                               org-sql-timeout))
>> +		   (sleep-for 0.03))
>> +		 ;; command finished, remove filter
>> +		 (set-process-filter (get-buffer-process sql--buffer) nil)
>
> This looks repetitive with the code in
> `org-sql-session-comint-output-filter'. Could it be possible to avoid
> code duplication?
>

True, the evaluation of the timeout was dup.

>> +		 (when (not session-p)
>> +		   (comint-quit-subjob)
>> +		   ;; despite this quit signal, the process may not be finished yet
>> +		   (let ((kill-buffer-query-functions nil))
>> +		     (kill-this-buffer))))
>
> Maybe we should wait until it is finished then? You can check
> `process-status'.

To be fixed next time

>> +                   (insert-for-yank
>
> Why do you need `insert-for-yank'?

No need. It's rather insert instead.

Cheers,

--
Phil

>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 01/08] ob-sql: add session support

lisp/ob-sql.el: add session support to ob-sql

* ob-sql.el: declare the maintainer of the following changes

---

diff --git a/lisp/ob-sql.el b/lisp/ob-sql.el
index 14ca6bc48..caee4502a 100644
--- a/lisp/ob-sql.el
+++ b/lisp/ob-sql.el
@@ -4,5 +4,6 @@

 ;; Author: Eric Schulte
 ;; Maintainer: Daniel Kraus <[email-masked]>
+;; Maintainer: Philippe Estival <[email-masked]>
 ;; Keywords: literate programming, reproducible research
 ;; URL: https://orgmode.org
>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 02/08] ob-sql: add session support

lisp/ob-sql.el: variables for session support to ob-sql

* ob-sql.el: declare variables and local variables
 for termination of a command batch,
 starting time of command execution for timeout,
 regexp to clear returned output

---

diff --git a/lisp/ob-sql.el b/lisp/ob-sql.el
index 14ca6bc48..caee4502a 100644
--- a/lisp/ob-sql.el
+++ b/lisp/ob-sql.el
@@ -76,6 +77,23 @@

 (require 'ob)

+(defvar org-sql-session--batch-terminate  "---#"
+  "String echoed by the SQL at the end of a command batch.")
+
+(defvar org-sql-batch-terminate
+  (list 'sqlite (format ".print %s\n" org-sql-session--batch-terminate)
+        'postgres (format "\\echo %s\n" org-sql-session--batch-terminate))
+  "Print the command batch termination as last command.")
+
+(defvar-local org-sql-session-command-terminated nil
+  "Non-nil when a command batch has completed.")
+
+(defvar-local org-sql-session-start-time nil
+  "Starting time of a session code block execution, used to exit on timeout.")
+
+(defvar-local org-sql-session-clean-output nil
+  "Store the regexp used to clear output (prompt1|termination|prompt2).")
+
 (declare-function org-table-import "org-table" (file arg))
 (declare-function orgtbl-to-csv "org-table" (table params))
 (declare-function org-table-to-lisp "org-table" (&optional txt))
>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 03/08] ob-sql: add session support

lisp/ob-sql.el: custom variables for session support to ob-sql

* ob-sql.el: declare custom variables
 default header argument per SQL engine,
 if non-session commands should also be run through comint,
 timeout duration of commands in seconds,
 if the temporary buffers of results are closed automatically

---

diff --git a/lisp/ob-sql.el b/lisp/ob-sql.el
index 14ca6bc48..caee4502a 100644
--- a/lisp/ob-sql.el
+++ b/lisp/ob-sql.el

@@ -83,7 +99,49 @@
 (declare-function sql-set-product "sql" (product))

 (defvar sql-connection-alist)
-(defvar org-babel-default-header-args:sql '())
+
+(defcustom org-babel-default-header-args:sql  '((:engine . "unset"))
+  "Default header args."
+  :type '(alist :key-type symbol :value-type string
+                :options ("dbi" "sqlite" "mysql" "postgres"
+                          "sqsh" "mssql" "vertica" "oracle" "saphana" ))
+  :group 'org-babel-sql
+  :safe t)
+
+(defcustom org-sql-session-preamble
+  (list
+   'postgres "
+\\set ON_ERROR_STOP 1
+\\pset footer off
+\\pset pager off
+\\pset format unaligned"
+   'sqlite "
+.header off")
+  "Command preamble to run upon shell start."
+
+  :type '(plist :key-type symbol :value-type string
+                :options ('postgres 'sqlite 'dbi 'mysql
+                          'sqsh 'mssql 'vertica 'oracle 'saphana ))
+  :group 'org-babel-sql
+  :safe t)
+
+(defcustom org-sql-run-comint-p nil
+  "Run non-session SQL commands through comint if not nil."
+  :type 'boolean
+  :group 'org-babel-sql
+  :safe t)
+
+(defcustom org-sql-timeout 5.0
+  "Abort on timeout."
+  :type 'number
+  :group 'org-babel-sql
+  :safe t)
+
+(defcustom org-sql-close-out-temp-buffer-p nil
+  "When non-nil, close the temporary buffer holding output results."
+  :type 'boolean
+  :group 'org-babel-sql
+  :safe t)

 (defconst org-babel-header-args:sql
   '((engine	       . :any)
>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 04/08] ob-sql: clear nil values from prologue and epilogue

---

diff --git a/lisp/ob-sql.el b/lisp/ob-sql.el
index 14ca6bc48..caee4502a 100644
--- a/lisp/ob-sql.el
+++ b/lisp/ob-sql.el

@@ -101,11 +159,11 @@
   (let ((prologue (cdr (assq :prologue params)))
 	(epilogue (cdr (assq :epilogue params))))
     (mapconcat 'identity
-               (list
+     (delq nil (list
                 prologue
                 (org-babel-sql-expand-vars
                  body (org-babel--get-vars params))
-                epilogue)
+                epilogue))
                "\n")))

 (defun org-babel-edit-prep:sql (info)
>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 05/08] ob-sql: add session support

lisp/ob-sql.el: factorize shell quoting of connection parameters to SQL clients

@@ -117,11 +175,10 @@ corresponding :engine source block header argument."

 (defun org-babel-sql-dbstring-mysql (host port user password database)
   "Make MySQL cmd line args for database connection.  Pass nil to omit that arg."
-  (mapconcat
-   #'identity
+  (combine-and-quote-strings
    (delq nil
-	 (list (when host     (concat "-h" (shell-quote-argument host)))
+	 (list (when host     (concat "-h" host))
 	       (when port     (format "-P%d" port))
-	       (when user     (concat "-u" (shell-quote-argument user)))
-	       (when password (concat "-p" (shell-quote-argument password)))
-	       (when database (concat "-D" (shell-quote-argument database))))) " "))
+	       (when user     (concat "-u" user))
+	       (when password (concat "-p" password))
+	       (when database (concat "-D" database))))))

 (defun org-babel-sql-dbstring-postgresql (host port user database)
   "Make PostgreSQL command line args for database connection.
 Pass nil to omit that arg."
-  (mapconcat
-   #'identity
+  (combine-and-quote-strings
    (delq nil
-	 (list (when host (concat "-h" (shell-quote-argument host)))
+	 (list (when host (concat "-h" host))
 	       (when port (format "-p%d" port))
-	       (when user (concat "-U" (shell-quote-argument user)))
-	       (when database (concat "-d" (shell-quote-argument database))))) " "))
+	       (when user (concat "-U" user))
+	       (when database (concat "-d" database))))))

 (defun org-babel-sql-dbstring-oracle (host port user password database)
   "Make Oracle command line arguments for database connection.
>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,6 +508,79 @@ 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 t)))
+
+        (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)
>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 07/08] ob-sql: add session support

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

* ob-sql.el: connect to an SQL shell and execute command block.
Replaces calls to (intern engine) by variable `in-engine',
Replace a unique cond by a when
Close temporary buffer when org-sql-close-out-temp-buffer-p is non-nil.

@@ -245,28 +293,71 @@ database connections."
                          (cdr (assoc-string dbconnection sql-connection-alist t))))))))

 (defun org-babel-execute:sql (body params)
-  "Execute a block of Sql code with Babel.
+  "Execute SQL BODY with PARAMS.
 This function is called by `org-babel-execute-src-block'."
   (let* ((result-params (cdr (assq :result-params params)))
          (cmdline (cdr (assq :cmdline params)))
-         (dbhost (org-babel-find-db-connection-param params :dbhost))
-         (dbport (org-babel-find-db-connection-param params :dbport))
-         (dbuser (org-babel-find-db-connection-param params :dbuser))
+         (dbhost     (org-babel-find-db-connection-param params :dbhost))
+         (dbport     (org-babel-find-db-connection-param params :dbport))
+         (dbuser     (org-babel-find-db-connection-param params :dbuser))
          (dbpassword (org-babel-find-db-connection-param params :dbpassword))
          (dbinstance (org-babel-find-db-connection-param params :dbinstance))
-         (database (org-babel-find-db-connection-param params :database))
+         (database   (org-babel-find-db-connection-param params :database))
          (engine (cdr (assq :engine params)))
+         (in-engine  (intern (or engine (user-error "Missing :engine"))))
          (colnames-p (not (equal "no" (cdr (assq :colnames params)))))
          (in-file (org-babel-temp-file "sql-in-"))
          (out-file (or (cdr (assq :out-file params))
                        (org-babel-temp-file "sql-out-")))
 	 (header-delim "")
-         (command (cl-case (intern engine)
+         (session (cdr (assoc :session params)))
+         (session-p (not (string= session "none"))))
+
+	 (if (or session-p org-sql-run-comint-p) ; run through comint
+	     (let* ((ob-sql-buffer (org-babel-sql-session-connect in-engine params session))
+                    (proc (get-buffer-process ob-sql-buffer)))
+	       (setq-local org-sql-session-start-time (current-time))
+	       (setq-local org-sql-session-command-terminated nil)
+	       (with-current-buffer (get-buffer ob-sql-buffer)
+                 (progn
+                   (goto-char (point-max))
+                   (insert
+                    (format "%s\n;\n%s"
+                            (org-babel-expand-body:sql
+                             (replace-regexp-in-string "[\t]" "" body) params)
+                            (plist-get org-sql-batch-terminate in-engine)))
+                   (comint-send-input nil t))
+		 (while (not org-sql-session-command-terminated)
+		   (sleep-for 0.03))
+		 ;; command finished, remove filter
+		 (set-process-filter proc nil)
+
+		 (when (not session-p)
+		   (comint-quit-subjob)
+                   (while (not (eq 'exit (process-status proc)))
+                     (sleep-for 0.1))
+		   ;; despite this quit signal, the process may not be finished yet
+		   (let ((kill-buffer-query-functions nil))
+		     (kill-this-buffer))))
+
+	       (with-current-buffer (get-buffer-create "*ob-sql-result*")
+		 (goto-char (point-min))
+		 ;; clear the output or prompt and termination
+		 (let ((clean-output (plist-get org-sql-session-clean-output in-engine)))
+		   (while (re-search-forward clean-output nil t)
+		     (replace-match "")))
+		 (write-file out-file)))
+
+	   (let ( ; else run one command line
+         (command (cl-case in-engine
                     (dbi (format "dbish --batch %s < %s | sed '%s' > %s"
 				 (or cmdline "")
 				 (org-babel-process-file-name in-file)
 				 "/^+/d;s/^|//;s/(NULL)/ /g;$d"
 				 (org-babel-process-file-name out-file)))
+                    (sqlite (format "sqlite3 < %s > %s"
+                                   (org-babel-process-file-name in-file)
+                                   (org-babel-process-file-name out-file)))
                     (monetdb (format "mclient -f tab %s < %s > %s"
 				     (or cmdline "")
 				     (org-babel-process-file-name in-file)

@@ -332,7 +421,7 @@ footer=off -F \"\t\"  %s -f %s -o %s %s"
                     (t (user-error "No support for the %s SQL engine" engine)))))
     (with-temp-file in-file
       (insert
-       (pcase (intern engine)
+       (pcase in-engine
 	 (`dbi "/format partbox\n")
          (`oracle "SET PAGESIZE 50000
 SET NEWPAGE 0

@@ -356,23 +445,22 @@ SET COLSEP '|'
        (org-babel-expand-body:sql body params)
        ;; "sqsh" requires "go" inserted at EOF.
        (if (string= engine "sqsh") "\ngo" "")))
-    (org-babel-eval command "")
+    (org-babel-eval command "")))
     (org-babel-result-cond result-params
       (with-temp-buffer
 	(progn (insert-file-contents-literally out-file) (buffer-string)))
       (with-temp-buffer
 	(cond
-	 ((memq (intern engine) '(dbi mysql postgresql postgres saphana sqsh vertica))
+	 ((memq in-engine '(dbi mysql postgresql postgres saphana sqsh vertica))
 	  ;; Add header row delimiter after column-names header in first line
-	  (cond
-	   (colnames-p
-	    (with-temp-buffer
+	  (when colnames-p
+            (with-temp-buffer
 	      (insert-file-contents out-file)
 	      (goto-char (point-min))
 	      (forward-line 1)
 	      (insert "-\n")
 	      (setq header-delim "-")
-	      (write-file out-file)))))
+	      (write-file out-file))))
 	 (t
 	  ;; Need to figure out the delimiter for the header row
 	  (with-temp-buffer
@@ -388,6 +476,8 @@ SET COLSEP '|'
 	      (forward-char -1))
 	    (write-file out-file))))
 	(org-table-import out-file (if (string= engine "sqsh") '(4) '(16)))
+	(when org-sql-close-out-temp-buffer-p
+	  (kill-buffer (get-file-buffer out-file)))
 	(org-babel-reassemble-table
 	 (mapcar (lambda (x)
 		   (if (string= (car x) header-delim)
>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 08/08] ob-sql: add session support

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

@@ -36,6 +37,7 @@
 ;; Header args used:
 ;; - engine
 ;; - cmdline
+;; - sessions
 ;; - dbhost
 ;; - dbport
 ;; - dbuser
@@ -58,13 +60,20 @@
 ;; - mssql
 ;; - sqsh
 ;; - postgresql (postgres)
+;; - sqlite
 ;; - oracle
 ;; - vertica
 ;; - saphana
 ;;
+;; Limitation:
+;; - sessions:
+;;   - engines configured: sqlite, postgres
+;;   - no error line number (stays as LINE 1)
+;;   - default port number only
+;;
 ;; TODO:
 ;;
-;; - support for sessions
 ;; - support for more engines
-;; - what's a reasonable way to drop table data into SQL?
-;;
+;; - babel tables as input
+;; - raw replace result
+;; - port number configuration for sessions

 ;;; Code: