Fork me on GitHub
#spacemacs
<
2022-02-09
>
paulspencerwilliams15:02:39

What is the simple and idiomatic way to configure org-babel languages within spacemacs. I’ve tried

(defun dotspacemacs/user-config ()
  (with-eval-after-load 'org
    (org-babel-do-load-languages
      'org-babel-load-languages
        '((sql . t))))
But when I try to execute
#+name: my-query
#+begin_src sql
  select * from people
#+end_src
I’m asked to confirm that I’d like to execute the code block, and when I answer yes, I get
Wrong type argument: stringp, nil

practicalli-johnny16:02:03

I didn't need to add any config for Clojure, although I did need to start a Clojure REPL. https://practical.li/spacemacs/org-mode/literate-programming/

practicalli-johnny16:02:34

I assume the SQL layer is installed and confirmed working already https://develop.spacemacs.org/layers/+lang/sql/README.html

paulspencerwilliams16:02:28

Actually, I'd not installed the sql layer, as native emacs has basic sql support. But subsequently, I've installed the sql layer and testing now.

paulspencerwilliams16:02:13

Em... So, I've installed the sql layer. I've connected to a database with M-x sql-postgres and been able to query tables etc. I've also saved the connection. and connected to that saved session. But once again, when I attempt to run a sql command in a orgmode file, I receive the same error.

paulspencerwilliams16:02:03

It's definitely related to my configuration though.

paulspencerwilliams16:02:48

If I remove my attempted babel config, I can successfully execute the following org file section.

#+name: list files 
#+begin_src bash
  ls
#+end_src
It correctly lists files.

paulspencerwilliams16:02:29

However, if I apply my config, then I can't even execute that bash script, and I receive the same Wrong type argument: stringp, nil. error

paulspencerwilliams16:02:29

Oh, that might be a red herring. I didn't realise , b b prompted and execute every script in the file.

paulspencerwilliams16:02:28

Going to grab some food, and come back to it later. As well as look at the thread in #clojure about configuring Cider to include the :test profile when running tests so I can pick up test-resources

practicalli-johnny16:02:54

The Org code block that was provided only defines a names query, to be used with other code blocks, as you discovered it does not run the SQL query.

practicalli-johnny16:02:17

To run an SQL query, details of the database should be provided in the meta data of the code block, e.g.

#+name: my-query
#+header: :engine mysql
#+header: :dbhost host
#+header: :dbuser user
#+header: :dbpassword pwd
#+header: :database dbname
#+begin_src sql
  SELECT * FROM mytable
  WHERE id > 500
#+end_src
This is from https://www.orgmode.org/worg/org-contrib/babel/languages/ob-doc-sql.html

paulspencerwilliams16:02:27

Oh, that makes sense although I assumed you could define the connection info elsewhere so I wouldn't, for example, commit them to git?

practicalli-johnny16:02:07

I also assume you could just define the connection once in the org file and then just use that, but not sure of the syntax.

paulspencerwilliams17:02:28

Okay, cheers. I'll try these later!

practicalli-johnny17:02:38

I'm interested in how you get on, so feel free to share anything that works. It seems some languages support session-based evaluation. If SQL supports this then perhaps the database information can be shared between code blocks. If SQL doesnt support session-based evaluation, then probably have to include the database connection details for each code block.

practicalli-johnny17:02:09

Or you could define all the SQL statements as their own named code blocks and use those statements in a language block, such as Clojure, and run those queries using next.jdbc. Not something I have tried yet though.

paulspencerwilliams07:02:08

So declaring the parameters fails a bit later in the process. So, proving general postgres connectivity using M-x postgress

db=> select count(*) from locations;
select count(*) from locations;
  count   
----------
 123456789
(1 row)
But valid sql blocks in an org file result in empty result
#+name: my-query
#+header: :engine postgres 
#+header: :dbhost redacted
#+header: :dbuser redacted
#+header: :dbpassword redacted
#+header: :database db 
#+begin_src sql
  SELECT count(*) FROM locations 
#+end_src

#+RESULTS: my-query
|---|
and invalid queries (misspelt table name) don’t returns errors, but once again empty results suggesting it doesn’t actually do anything.
#+name: my-query
#+header: :engine postgres 
#+header: :dbhost redacted
#+header: :dbuser redacted
#+header: :dbpassword redacted
#+header: :database db 
#+begin_src sql
  SELECT count(*) FROM incorrect_locations 
#+end_src

#+RESULTS: my-query
|---|

paulspencerwilliams07:02:03

Time to put this down, and revert back to using .sql files, at least for the day.

practicalli-johnny08:02:21

Sorry to hear it didn't work, thanks for sharing.