emacs-orgmode@gnu.org archives
 help / color / mirror / code / Atom feed
From: Daniel Kraus <daniel@kraus.my>
To: Stefano Rodighiero <stefano.rodighiero@gmail.com>
Cc: Kyle Meyer <kyle@kyleam.com>, emacs-orgmode@gnu.org
Subject: Re: [PATCH] ob-sql: Respect database param when using dbconnection
Date: Mon, 31 Oct 2022 08:02:42 +0100	[thread overview]
Message-ID: <8735b4lbbl.fsf@kraus.my> (raw)
In-Reply-To: <CABuLN4yPhg3a=qdYWG8Dg4gtVqgYn4=4LzSXBKAAcLTeBSX-PQ@mail.gmail.com>

[-- Attachment #1: Type: text/plain, Size: 318 bytes --]


sorry for replying to a 2 year old thread.
Attached is a new patch with the change from Kyle and
I also added a news entry.
Not sure if the text for the news entry is any good,
or if there should be an example etc.
Feedback welcome.

Otherwise, the patch itself is not much and I use it daily.


[-- Warning: decoded text below may be mangled, UTF-8 assumed --]
[-- Attachment #2: 0001-ob-sql.el-Respect-all-params-when-using-dbconnection.patch --]
[-- Type: text/x-patch, Size: 3383 bytes --]

From cd170dd691ba12ec81ef5c71db2868b33cd63ddf Mon Sep 17 00:00:00 2001
From: Daniel Kraus <daniel@kraus.my>
Date: Mon, 31 Oct 2022 07:52:09 +0100
Subject: [PATCH] ob-sql.el: Respect all params when using dbconnection

* etc/ORG-NEWS (Miscellaneous): Document change
* lisp/ob-sql.el (org-babel-find-db-connection-param): Make it
possible to overwrite parameters that are set from :dbconnection
 etc/ORG-NEWS   | 14 ++++++++++++++
 lisp/ob-sql.el | 24 ++++++++++++------------
 2 files changed, 26 insertions(+), 12 deletions(-)

diff --git a/etc/ORG-NEWS b/etc/ORG-NEWS
index 6e875deb6..b542da34b 100644
--- a/etc/ORG-NEWS
+++ b/etc/ORG-NEWS
@@ -457,6 +457,20 @@ The new variable name is =org-plantuml-args=.  It now applies to both
 jar PlantUML file and executable.
 ** Miscellaneous
+*** SQL Babel ~:dbconnection~ parameter can be mixed with other SQL Babel parameters
+Before you could either specify SQL parameters like ~:dbhost~,
+~:dbuser~, ~:database~, etc or a ~:dbconnection~ parameter which looks
+up all other parameters from the ~sql-connection-alist~ variable.  Now
+it's possible to specify a ~:dbconnection~ and additionally other
+parameters that will add or overwrite the parameters coming from
+E.g. if you have a connection in your ~sql-connection-alist~ to a
+server that has many databases, you don't need an entry for every
+database but instead can just specify ~:database~ next to your
+~:dbconnection~ parameter.
 *** Post-processing code blocks can return an empty list
 When the result of a regular code block is nil, then that was already
diff --git a/lisp/ob-sql.el b/lisp/ob-sql.el
index d1256bf83..626d595c9 100644
--- a/lisp/ob-sql.el
+++ b/lisp/ob-sql.el
@@ -222,18 +222,18 @@ then look for the parameter into the corresponding connection
 defined in `sql-connection-alist', otherwise look into PARAMS.
 See `sql-connection-alist' (part of SQL mode) for how to define
 database connections."
-  (if (assq :dbconnection params)
-      (let* ((dbconnection (cdr (assq :dbconnection params)))
-             (name-mapping '((:dbhost . sql-server)
-                             (:dbport . sql-port)
-                             (:dbuser . sql-user)
-                             (:dbpassword . sql-password)
-                             (:dbinstance . sql-dbinstance)
-                             (:database . sql-database)))
-             (mapped-name (cdr (assq name name-mapping))))
-        (cadr (assq mapped-name
-                    (cdr (assoc dbconnection sql-connection-alist)))))
-    (cdr (assq name params))))
+  (or (cdr (assq name params))
+      (and (assq :dbconnection params)
+           (let* ((dbconnection (cdr (assq :dbconnection params)))
+                  (name-mapping '((:dbhost . sql-server)
+                                  (:dbport . sql-port)
+                                  (:dbuser . sql-user)
+                                  (:dbpassword . sql-password)
+                                  (:dbinstance . sql-dbinstance)
+                                  (:database . sql-database)))
+                  (mapped-name (cdr (assq name name-mapping))))
+             (cadr (assq mapped-name
+                         (cdr (assoc dbconnection sql-connection-alist))))))))
 (defun org-babel-execute:sql (body params)
   "Execute a block of Sql code with Babel.

[-- Attachment #3: Type: text/plain, Size: 1349 bytes --]

Stefano Rodighiero <stefano.rodighiero@gmail.com> writes:

> On Mon, Jun 1, 2020 at 4:16 AM Kyle Meyer <kyle@kyleam.com> wrote:
> Daniel Kraus writes:
>> > I use ob-sql with the :dbconnection param so I don't have my username
>> and password in my org file.
>> > But often I don't want to use the default database from the dbconnection
>> alist but
>> > rather specify it explicitly with :database.
>> > Attached is a patch that fixes this.
> Thank you @Daniel
>> […]
>> From what I can gather from your description, this looks reasonable.
>> I'm not an ob-sql user, so perhaps I missing something, but would it
>> make sense for any connection parameter to take precedence if explicitly
>> given in the source block header (i.e. something like the patch below)?
>> [+cc Stefano, who added the :dbconneciton feature.]
> I think it makes sense.
> (I personally handle cases like those described by Daniel differently,
> keeping distinct sql-connection-alist entries for each DB
> param combination I might need, but I can imagine why someone
> would want to "override" the database or the host params.
> For port, user and password I have more difficulties imagining a
> case where combinations of those params would need override,
> but I think @Kyle's generic solution is better)
> s.

  reply	other threads:[~2022-10-31  7:09 UTC|newest]

Thread overview: 8+ messages / expand[flat|nested]  mbox.gz  Atom feed  top
2020-05-28 15:17 [PATCH] ob-sql: Respect database param when using dbconnection Daniel Kraus
2020-06-01  2:16 ` Kyle Meyer
2020-06-01  8:20   ` Stefano Rodighiero
2022-10-31  7:02     ` Daniel Kraus [this message]
2022-11-01  1:58       ` Ihor Radchenko
2022-11-02 15:48         ` Daniel Kraus
2022-11-03  7:15           ` Ihor Radchenko
2022-11-03  8:28             ` Bastien Guerry

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

  Avoid top-posting and favor interleaved quoting:

  List information: https://www.orgmode.org/

* Reply using the --to, --cc, and --in-reply-to
  switches of git-send-email(1):

  git send-email \
    --in-reply-to=8735b4lbbl.fsf@kraus.my \
    --to=daniel@kraus.my \
    --cc=emacs-orgmode@gnu.org \
    --cc=kyle@kyleam.com \
    --cc=stefano.rodighiero@gmail.com \


* If your mail client supports setting the In-Reply-To header
  via mailto: links, try the mailto: link
Be sure your reply has a Subject: header at the top and a blank line before the message body.
Code repositories for project(s) associated with this public inbox


This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox;
as well as URLs for read-only IMAP folder(s) and NNTP newsgroup(s).