Re: ob-sql dbconnection engine

[Andreas Gerler]

Hi Ihor,

I use only MySQL, PostgreSQL and Vertica.
However for me sql-product postgres in my sql-connection-alist works both in 
ob-sql as well as SQLi.
In the end you are still able to overwrite in your src block parameters via 
:engine

Andy

> On Feb 5, 2023, at 11:58 AM, Ihor Radchenko  wrote:
> 
> Andreas Gerler  writes:
> 
>> without the patch your org source block has to look like:
>> 
>> #+begin_src sql :engine mysql :dbconnection mysqldb1
>>  SELECT * FROM nodes;
>> #+end_src
>> 
>> with that patch you can just type
>> 
>> #+begin_src sql :dbconnection mysqldb1
>>  SELECT * FROM nodes;
>> #+end_src
>> 
>> because your engine is mapped to sql-product in your sql-connection-alist:
>> 
>> (setq sql-connection-alist
>>  '((mysqldb1 (sql-product 'mysql)
>>(sql-server "10.10.42.1")
>>(sql-user "dbadmin")
>>(sql-password "foo")
>>(sql-database "testdb"
> 
> Thanks for the explanation!
> This logic assumes that engine names in ob-sql are the same with
> products in `sql-product-alist'. Which is not true. For example, ob-sql
> has postgresql while `sql-product-alist' has postgres.
> 
> Further, some engines are in ob-sql but not in sql.el and vice versa.
> 
> In order to provide auto-detection of sql engine we should first
> synchronize the engine names.
> 
> -- 
> Ihor Radchenko // yantar92,
> Org mode contributor,
> Learn more about Org mode at <https://orgmode.org/>.
> Support Org development at <https://liberapay.com/org-mode>,
> or support my work at <https://liberapay.com/yantar92>
> 

Re: [O] [PATCH] ob-eval: display error fix

[Andreas Gerler]

Hi Ihor,

way too busy right now.
As soon as I find some time I’ll rework that change. Might take a week or two.

so long...

Andreas Gerler

> On Jan 27, 2023, at 2:11 PM, Ihor Radchenko  wrote:
> 
> Andreas Gerler  writes:
> 
>> +(defcustom org-babel-eval-error-display-notify nil
>> +  "Display org-babel-eval-errors always or only if exit code is not 0."
> 
> This docstring is confusing. What will happen if the value is nil?
> non-nil? I cannot answer these questions by reading the docstring.
> 
> Also, what is "org-babel-eval-errors"? Symbol?
> 
> Finally, I do not think that "nil" is a good default here. It is better
> to display warnings by default and let people disable them only
> consciously, when needed. Ideally, we may allow the value to be a list
> of backends where to display/not display the warnings.
> 
>> +  :group 'org-babel
>> +  :version "29.1"
> 
> Please use :package-version instead.
> 
> And add the new customization to etc/ORG-NEWS.
> 
> -- 
> Ihor Radchenko // yantar92,
> Org mode contributor,
> Learn more about Org mode at <https://orgmode.org/>.
> Support Org development at <https://liberapay.com/org-mode>,
> or support my work at <https://liberapay.com/yantar92>
> 

Re: ob-sql dbconnection engine

[Andreas Gerler]

Hi!

and a new patch with proper commit message.



0002-lisp-ob-sql.el-sql-connection-alist-engine.patch
Description: Binary data


> On 28. Jan 2023, at 13:57, Andreas Gerler  
> wrote:
> 
> Hi!
> 
> without the patch your org source block has to look like:
> 
> #+begin_src sql :engine mysql :dbconnection mysqldb1
>  SELECT * FROM nodes;
> #+end_src
> 
> with that patch you can just type
> 
> #+begin_src sql :dbconnection mysqldb1
>  SELECT * FROM nodes;
> #+end_src
> 
> because your engine is mapped to sql-product in your sql-connection-alist:
> 
> (setq sql-connection-alist
>  '((mysqldb1 (sql-product 'mysql)
>(sql-server "10.10.42.1")
>(sql-user "dbadmin")
>(sql-password "foo")
>    (sql-database "testdb"
> 
> 
>> On 27. Jan 2023, at 14:15, Ihor Radchenko  wrote:
>> 
>> Andreas Gerler  writes:
>> 
>>> as I am still learning more elisp it took me some try and error but I can 
>>> use the sql-product now within dbconnection.
>>> Using :engine still works as well.
>>> Still wondering if there is a more elegant way for the if clause.
>> 
>> Thanks for the patch!
>> Could you please explain in more details what the patch does?
>> 
>> --
>> Ihor Radchenko // yantar92,
>> Org mode contributor,
>> Learn more about Org mode at <https://orgmode.org/>.
>> Support Org development at <https://liberapay.com/org-mode>,
>> or support my work at <https://liberapay.com/yantar92>
>> 
> 
> 
> 
> so long…
> 
> Andreas Gerler
> 
> —
> 
> http://www.bundesbrandschatzamt.de/~baron



signature.asc
Description: Message signed with OpenPGP

Re: [BUG] ob-sql sql-connection-alist

[Andreas Gerler]

Hi!

I added the missing changelog entry.



0001-lisp-ob-sql.el-allow-string-in-sql-connection-alist.patch
Description: Binary data


> On 27. Jan 2023, at 17:58, Andreas Gerler  
> wrote:
> 
> Hi!
> 
> I can prepare a new commit this weekend.
> 




so long…

Andreas Gerler
—

http://www.bundesbrandschatzamt.de/~baron


signature.asc
Description: Message signed with OpenPGP

Re: ob-sql dbconnection engine

[Andreas Gerler]

Hi!

without the patch your org source block has to look like:

#+begin_src sql :engine mysql :dbconnection mysqldb1
  SELECT * FROM nodes;
#+end_src

with that patch you can just type

#+begin_src sql :dbconnection mysqldb1
  SELECT * FROM nodes;
#+end_src

because your engine is mapped to sql-product in your sql-connection-alist:

(setq sql-connection-alist
  '((mysqldb1 (sql-product 'mysql)
(sql-server "10.10.42.1")
(sql-user "dbadmin")
(sql-password "foo")
(sql-database "testdb"


> On 27. Jan 2023, at 14:15, Ihor Radchenko  wrote:
> 
> Andreas Gerler  writes:
> 
>> as I am still learning more elisp it took me some try and error but I can 
>> use the sql-product now within dbconnection.
>> Using :engine still works as well.
>> Still wondering if there is a more elegant way for the if clause.
> 
> Thanks for the patch!
> Could you please explain in more details what the patch does?
> 
> --
> Ihor Radchenko // yantar92,
> Org mode contributor,
> Learn more about Org mode at <https://orgmode.org/>.
> Support Org development at <https://liberapay.com/org-mode>,
> or support my work at <https://liberapay.com/yantar92>
> 



so long…

Andreas Gerler

—

http://www.bundesbrandschatzamt.de/~baron


signature.asc
Description: Message signed with OpenPGP

Re: [BUG] ob-sql sql-connection-alist

[Andreas Gerler]

Hi!

I can prepare a new commit this weekend.

so long...

Andreas

> On Jan 27, 2023, at 2:18 PM, Ihor Radchenko  wrote:
> 
> Bastien Guerry  writes:
> 
>> It was missing in the FSF copyright.list file, but it has been fixed
>> and Andreas can be added as a regular contributor.
> 
> Done.
> https://git.sr.ht/~bzg/worg/commit/3dbeb2db
> 
> -- 
> Ihor Radchenko // yantar92,
> Org mode contributor,
> Learn more about Org mode at .
> Support Org development at ,
> or support my work at 

ob-sql dbconnection engine

[Andreas Gerler]

Hi!

as I am still learning more elisp it took me some try and error but I can use 
the sql-product now within dbconnection.
Using :engine still works as well.
Still wondering if there is a more elegant way for the if clause.



ob-sql.el.diff
Description: Binary data


so long…

Andreas Gerler

—

http://www.bundesbrandschatzamt.de/~baron



signature.asc
Description: Message signed with OpenPGP

Re: [BUG] ob-sql sql-connection-alist

[Andreas Gerler]

The copyright assignment was in April 2017.
gnu.org #1203873 if that helps.

> On 21. Jan 2023, at 09:48, Ihor Radchenko  wrote:
> 
> Daniel Kraus  writes:
> 
>> @Ihor, since this is the first patch I install from another contributor,
>> is there anything I should look out for?
> 
>> E.g. does this need a TINYCHANGE entry or something?
> 
> 1. General checks for tests passing and sanity of the patches
> 2. Ensuring copyright status, when total contributed nuber of LOC is
>   around 15
> 
> See https://orgmode.org/worg/org-maintenance.html and
> https://www.gnu.org/prep/maintain/
> 
> Also, make sure that you are familiar with general contribution
> guidelines listed at https://orgmode.org/worg/org-contribute.html
> 
>> Or do you have already copyright assignments filled out, Andreas and
>> then it's not necessary?
>> Can I see somewhere who I need to ask for copyright assignments or not?
> 
> We list all (well, most of) the past contributors and their copyright
> assignment status at https://orgmode.org/worg/contributors.html
> 
> I also have a small hook for my Emacs mail client that automatically
> checks the contributor status and number of contributed LOCs for Org
> mailing list:
> https://github.com/yantar92/emacs-config/blob/master/config.org#detect-fsf-contribution-status-of-sender-for-org-ml
> 
> Bastien has access to official FSF records. We need to ask him when
> copyright assignment status cannot be determined from the Org
> contributors page.
> 
> In this case, Andreas is not listed on
> https://orgmode.org/worg/contributors.html
> I also cannot find anything relevant in the mailing list archives.
> (well, there is
> https://orgmode.org/list/b664b5e8-b6a8-4b20-b0cb-f6149c720...@bundesbrandschatzamt.de,
> but I do not see any confirmation of the copyright assignment status).
> 
> Bastien, could you please confirm the copyright status of Andreas Gerler?
> 
> --
> Ihor Radchenko // yantar92,
> Org mode contributor,
> Learn more about Org mode at <https://orgmode.org/>.
> Support Org development at <https://liberapay.com/org-mode>,
> or support my work at <https://liberapay.com/yantar92>
> 



so long…
Andreas Gerler
—

http://www.bundesbrandschatzamt.de/~baron



signature.asc
Description: Message signed with OpenPGP

Re: [BUG] ob-sql sql-connection-alist

[Andreas Gerler]

Hi Daniel,

I already contributed to Orgmode a few years ago for Vertica support and filled 
the copyright assignment back then.

Andreas

> On Jan 20, 2023, at 9:47 PM, Daniel Kraus  wrote:
> 
> Thanks.
> 
> @Ihor, since this is the first patch I install from another contributor,
> is there anything I should look out for?
> 
> E.g. does this need a TINYCHANGE entry or something?
> Or do you have already copyright assignments filled out, Andreas and
> then it's not necessary?
> Can I see somewhere who I need to ask for copyright assignments or not?
> 
> Thanks,
>  Daniel
> 

Re: [BUG] ob-sql sql-connection-alist

[Andreas Gerler]

Sorry. That was the wrong patch.



0001-lisp-ob-sql.el-allow-string-in-sql-connection-alist.patch
Description: Binary data


> On 20. Jan 2023, at 18:24, Andreas Gerler  
> wrote:
> 
> <0001-lisp-ob-eval.el-Display-error-fix.patch>
> 
>> On 16. Jan 2023, at 11:25, Daniel Kraus  wrote:
>> 
>> Hi!
>> 
>> Andreas Gerler  writes:
>> 
>>> Last week I heard about using ob-sql with credentials stored in the 
>>> variable used by isql.
>>> However I had to modify ob-sql to get it actually working.
>>> Can somebody test the pach before I send in a commit?
>>> 
>>> #+begin_src sql :engine mysql :dbconnection testdb
>>> show tables;
>>> #+end_src
>> 
>> I actually use this feature daily.
>> You have to quote the dbconnection. So this works currently:
>> 
>>> #+begin_src sql :engine mysql :dbconnection 'testdb
>> 
>> but I would agree that not needing the quote makes sense.
>> And since `assoc-string` works with symbol and string (i.e. it's backwards 
>> compatible)
>> I would install the patch if you send it.
>> 
>>> I was considering writing another patch to map the sql-product to engine.
>>> That way we could get rid of another parameter in the src block.
>>> Opinions?
>> 
>> I agree. Specifying :engine when it's already in the connection-alist is 
>> unnecessary.
>> 
>> Thanks,
>> Daniel
> 



signature.asc
Description: Message signed with OpenPGP

Re: [BUG] ob-sql sql-connection-alist

[Andreas Gerler]

0001-lisp-ob-eval.el-Display-error-fix.patch
Description: Binary data


> On 16. Jan 2023, at 11:25, Daniel Kraus  wrote:
> 
> Hi!
> 
> Andreas Gerler  writes:
> 
>> Last week I heard about using ob-sql with credentials stored in the variable 
>> used by isql.
>> However I had to modify ob-sql to get it actually working.
>> Can somebody test the pach before I send in a commit?
>> 
>> #+begin_src sql :engine mysql :dbconnection testdb
>> show tables;
>> #+end_src
> 
> I actually use this feature daily.
> You have to quote the dbconnection. So this works currently:
> 
>> #+begin_src sql :engine mysql :dbconnection 'testdb
> 
> but I would agree that not needing the quote makes sense.
> And since `assoc-string` works with symbol and string (i.e. it's backwards 
> compatible)
> I would install the patch if you send it.
> 
>> I was considering writing another patch to map the sql-product to engine.
>> That way we could get rid of another parameter in the src block.
>> Opinions?
> 
> I agree. Specifying :engine when it's already in the connection-alist is 
> unnecessary.
> 
> Thanks,
>  Daniel



signature.asc
Description: Message signed with OpenPGP

[BUG] ob-sql sql-connection-alist

[Andreas Gerler]

HI!

Last week I heard about using ob-sql with credentials stored in the variable 
used by isql.
However I had to modify ob-sql to get it actually working.
Can somebody test the pach before I send in a commit?

#+begin_src sql :engine mysql :dbconnection testdb
show tables;
#+end_src

(setq sql-connection-alist
  '((testdb (sql-product 'mysql)
(sql-server "127.0.0.1")
(sql-user "mysql”)
(sql-port 3306)
(sql-password “foo")
(sql-database "mysql"

I was considering writing another patch to map the sql-product to engine.
That way we could get rid of another parameter in the src block.
Opinions?



ob-sql.el.diff
Description: Binary data



Thanks in advance…


Andreas Gerler

—

http://www.bundesbrandschatzamt.de/~baron



signature.asc
Description: Message signed with OpenPGP

Re: [O] [PATCH] ob-eval: display error fix

[Andreas Gerler]

0001-lisp-ob-eval.el-Display-error-fix.patch
Description: Binary data


Hi Ruijie,

thanks for asking.
I combined them now and tested again all variants.

Andreas

> On 7. Jan 2023, at 22:33, Ruijie Yu via General discussions about Org-mode. 
>  wrote:
> 
> 
> Hi Andreas,
> 
> -(display-buffer buf))
> +(if org-babel-eval-error-display-notify
> +(display-buffer buf)
> +  (if (or (not (numberp exit-code))
> +  (> exit-code 0))
> +  (display-buffer buf
> 
> Quick question, does it help or hurt readability if we change this
> nested `if' into a `cond' expression?
> 
> Or, alternatively, since both the outer then branch and the inner then
> branch are the same expression, maybe we can just combine all three
> conditions into a single `or' call?
> 
> Something like
> 
> (when (or org-babel-eval-error-display-notify
>  (not (numberp exit-code))
>  (> exit-code 0))
>  (display-buffer buf))
> 
> Best,
> 
> 
> RY
> 



signature.asc
Description: Message signed with OpenPGP

[O] [PATCH] ob-eval: display error fix

[Andreas Gerler]

0001-lisp-ob-eval.el-Display-error-fix.patch
Description: Binary data


Dear all,

since commit

f7b16402e6a694d592210f766544f6114056b4b0

every sql code block is displaying the *Org-Babel Error Output* for me due to 
warning messages from the database.
I added a check to display the buffer only if non zero exit.
The buffer still gets written.
Additionally I added defcustom org-babel-eval-error-display-notify
If anybody is in need to display everything he can toggle it.

I signed my FSF papers in 2017 for an ob-sql patch.

so long…

Andreas Gerler

—

http://www.bundesbrandschatzamt.de/~baron




signature.asc
Description: Message signed with OpenPGP

Re: [O] [PATCH] ob-sql: Add vertica engine

[Andreas Gerler]

Hi Nicolas,

actually I send my request to FSF after sending the patch. So I guess it's 
easier to ignore the patch until I finished signing the papers. I have another 
change sitting currently in my config.

So long 

Andy

> On 30. Mar 2017, at 12:44 PM, Nicolas Goaziou <m...@nicolasgoaziou.fr> wrote:
> 
> Hello,
> 
> Andreas Gerler <ba...@bundesbrandschatzamt.de> writes:
> 
>> * lisp/ob-sql.el (org-babel-sql-dbstring-vertica): New function.
>> (org-babel-execute:sql): Add `vertica' engine.
> 
> Thank you.
> 
> If you haven't signed FSF papers yet, could you add TINYCHANGE at the
> end of the commit message?
> 
> Also, would you mind providing an entry in ORG-NEWS file?
> 
> Regards,
> 
> -- 
> Nicolas Goaziou
> 

[O] [PATCH] ob-sql: Add vertica engine

[Andreas Gerler]

* lisp/ob-sql.el (org-babel-sql-dbstring-vertica): New function.
(org-babel-execute:sql): Add `vertica' engine.

Signed-off-by: Andreas Gerler <ba...@bundesbrandschatzamt.de>
---
 lisp/ob-sql.el | 20 +++-
 1 file changed, 19 insertions(+), 1 deletion(-)

diff --git a/lisp/ob-sql.el b/lisp/ob-sql.el
index 15c50ad..ec0427f 100644
--- a/lisp/ob-sql.el
+++ b/lisp/ob-sql.el
@@ -56,6 +56,7 @@
 ;; - sqsh
 ;; - postgresql
 ;; - oracle
+;; - vertica
 ;;
 ;; TODO:
 ;;
@@ -136,6 +137,16 @@ SQL Server on Windows and Linux platform."
   (when database (format "-D \"%s\"" database
  " "))

+(defun org-babel-sql-dbstring-vertica (host port user password database)
+  "Make Vertica command line args for database connection. Pass nil to omit 
that arg."
+  (mapconcat #'identity
+ (delq nil
+   (list (when host (format "-h %s" host))
+ (when port (format "-p %d" port))
+ (when user (format "-U %s" user))
+ (when password (format "-w %s" (shell-quote-argument 
password) ))
+ (when database (format "-d %s" database
+ " "))

 (defun org-babel-sql-convert-standard-filename (file)
   "Convert the file name to OS standard.
@@ -208,6 +219,12 @@ footer=off -F \"\t\"  %s -f %s -o %s %s"
(org-babel-process-file-name in-file))
   (org-babel-sql-convert-standard-filename
(org-babel-process-file-name out-file
+   (`vertica (format "vsql %s -f %s -o %s %s"
+   (org-babel-sql-dbstring-vertica
+dbhost dbport dbuser dbpassword database)
+   (org-babel-process-file-name in-file)
+   (org-babel-process-file-name out-file)
+   (or cmdline "")))
 (`oracle (format
  "sqlplus -s %s < %s > %s"
  (org-babel-sql-dbstring-oracle
@@ -235,6 +252,7 @@ SET COLSEP '|'
 ((or `mssql `sqsh) "SET NOCOUNT ON

 ")
+(`vertica "\\a\n")
 (_ ""))
(org-babel-expand-body:sql body params)
;; "sqsh" requires "go" inserted at EOF.
@@ -245,7 +263,7 @@ SET COLSEP '|'
(progn (insert-file-contents-literally out-file) (buffer-string)))
   (with-temp-buffer
(cond
-((memq (intern engine) '(dbi mysql postgresql sqsh))
+((memq (intern engine) '(dbi mysql postgresql sqsh vertica))
  ;; Add header row delimiter after column-names header in first line
  (cond
   (colnames-p
--
2.10.1 (Apple Git-78)



signature.asc
Description: Message signed with OpenPGP