2

I am able to execute SQL block with the following:

#+NAME:test-1
#+BEGIN_SRC sql :noweb no-export :tangle :engine mssql :cmdline "-S localhost -U SA -P myPassword"
select * from OtherDW.dbo.Supplier_D
#+END_SRC

#+RESULTS: test-1
| Supplier_Id | Supplier_Name |
|-------------+---------------|
|           1 | Walmart       |
|           2 | Amazon        |

However, then I moved the header arguments to property in the same file below the above example as follow::

* Test
:PROPERTIES:
:engine: mssql
:cmdline: \"-S localhost -U SA -P myPassword\"

#+NAME:using-properties
#+BEGIN_SRC sql :noweb no-export :tangle
select * from OtherDW.dbo.Supplier_D
#+END_SRC

Then when I execute the block (C-c C-C), I got the following error:

intern(nil)
  (let* ((val (intern engine))) (cond ((eq val (quote dbi)) (let nil (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)))) ((eq val (quote monetdb)) (let nil (format "mclient -f tab %s < %s > %s" (or cmdline "") (org-babel-process-file-name in-file) (org-babel-process-file-name out-file)))) ((eq val (quote mssql)) (let nil (format "sqlcmd %s -s \"  \" %s -i %s -o %s" (or cmdline "") (org-babel-sql-dbstring-mssql dbhost dbuser dbpassword database) (org-babel-sql-convert-standard-filename (org-babel-process-file-name in-file)) (org-babel-sql-convert-standard-filename (org-babel-process-file-name out-file))))) ((eq val (quote mysql)) (let nil (format "mysql %s %s %s < %s > %s" (org-babel-sql-dbstring-mysql dbhost dbport dbuser dbpassword database) (if colnames-p "" "-N") (or cmdline "") (org-babel-process-file-name in-file) (org-babel-process-file-name out-file)))) ((eq val (quote postgresql)) (let nil (format "%spsql --set=\"ON_ERROR_STOP=1\" %s -A -P footer=off -F \"   \"  %s -f %s -o %s %s" (if dbpassword (format "PGPASSWORD=%s " dbpassword) "") (if colnames-p "" "-t") (org-babel-sql-dbstring-postgresql dbhost dbport dbuser database) (org-babel-process-file-name in-file) (org-babel-process-file-name out-file) (or cmdline "")))) ((eq val (quote sqsh)) (let nil (format "sqsh %s %s -i %s -o %s -m csv" (or cmdline "") (org-babel-sql-dbstring-sqsh dbhost dbuser dbpassword database) (org-babel-sql-convert-standard-filename (org-babel-process-file-name in-file)) (org-babel-sql-convert-standard-filename (org-babel-process-file-name out-file))))) ((eq val (quote vertica)) (let nil (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 "")))) ((eq val (quote oracle)) (let nil (format "sqlplus -s %s < %s > %s" (org-babel-sql-dbstring-oracle dbhost dbport dbuser dbpassword database) (org-babel-process-file-name in-file) (org-babel-process-file-name out-file)))) (t (let nil (error "No support for the %s SQL engine" engine)))))
  (let* ((result-params (cdr (assq :result-params params))) (cmdline (cdr (assq :cmdline params))) (dbhost (cdr (assq :dbhost params))) (dbport (cdr (assq :dbport params))) (dbuser (cdr (assq :dbuser params))) (dbpassword (cdr (assq :dbpassword params))) (database (cdr (assq :database params))) (engine (cdr (assq :engine params))) (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 (let* ((val (intern engine))) (cond ((eq val (quote dbi)) (let nil (format "dbish --batch %s < %s | sed '%s' > %s" ... ... "/^+/d;s/^|//;s/(NULL)/ /g;$d" ...))) ((eq val (quote monetdb)) (let nil (format "mclient -f tab %s < %s > %s" ... ... ...))) ((eq val (quote mssql)) (let nil (format "sqlcmd %s -s \"   \" %s -i %s -o %s" ... ... ... ...))) ((eq val (quote mysql)) (let nil (format "mysql %s %s %s < %s > %s" ... ... ... ... ...))) ((eq val (quote postgresql)) (let nil (format "%spsql --set=\"ON_ERROR_STOP=1\" %s -A -P footer=off -F \"  \"  %s -f %s -o %s %s" ... ... ... ... ... ...))) ((eq val (quote sqsh)) (let nil (format "sqsh %s %s -i %s -o %s -m csv" ... ... ... ...))) ((eq val (quote vertica)) (let nil (format "vsql %s -f %s -o %s %s" ... ... ... ...))) ((eq val (quote oracle)) (let nil (format "sqlplus -s %s < %s > %s" ... ... ...))) (t (let nil (error "No support for the %s SQL engine" engine))))))) (let ((temp-file in-file) (temp-buffer (get-buffer-create (generate-new-buffer-name " *temp file*")))) (unwind-protect (prog1 (save-current-buffer (set-buffer temp-buffer) (insert (let* (...) (cond ... ... ... ... ...)) (org-babel-expand-body:sql body params) (if (string= engine "sqsh") "\ngo" ""))) (save-current-buffer (set-buffer temp-buffer) (write-region nil nil temp-file nil 0))) (and (buffer-name temp-buffer) (kill-buffer temp-buffer)))) (org-babel-eval command "") (let ((--params result-params)) (if (member "none" --params) nil (if (or (member "scalar" --params) (member "verbatim" --params) (member "html" --params) (member "code" --params) (member "pp" --params) (member "file" --params) (and (or (member "output" --params) (member "raw" --params) (member "org" --params) (member "drawer" --params)) (not (member "table" --params)))) (let ((temp-buffer (generate-new-buffer " *temp*"))) (save-current-buffer (set-buffer temp-buffer) (unwind-protect (progn ...) (and ... ...)))) (let ((temp-buffer (generate-new-buffer " *temp*"))) (save-current-buffer (set-buffer temp-buffer) (unwind-protect (progn ... ... ...) (and ... ...))))))))
  org-babel-execute:sql("select * from OtherDW.dbo.Supplier_D" ((:colname-names) (:rowname-names) (:result-params) (:result-type . value) (:results . "") (:exports . "code") (:session . "none") (:cache . "no") (:hlines . "no") (:noweb . "no-export") (:tangle)))
  org-babel-execute-src-block(nil ("sql" "select * from OtherDW.dbo.Supplier_D" ((:colname-names) (:rowname-names) (:result-params) (:result-type . value) (:results . "") (:exports . "code") (:tangle) (:noweb . "no-export") (:hlines . "no") (:cache . "no") (:session . "none")) "" "using-properties" 11012 "(ref:%s)"))
  org-ctrl-c-ctrl-c(nil)
  funcall-interactively(org-ctrl-c-ctrl-c nil)
  call-interactively(org-ctrl-c-ctrl-c nil nil)

It looks like that the property :engine: took no effect?

What does it take to have the property values effective for header-arguments?

I tried to re-open the file, and do C-c C-c at the top of the file ro refresh the property settings.

But I did found that the following way would work:

#+PROPERTY: header-args:sql :engine mssql :cmdline -S localhost -U SA -P myPassword
2

Using sqsh and org-version 9.1.12, I connect to an mssql server with

#+BEGIN_SRC emacs-lisp
  (require 'ob-sql)
  (org-babel-do-load-languages
   'org-babel-load-languages
   '((emacs-lisp . t)
     (sql . t)))
#+END_SRC

#+PROPERTY: header-args:sql :session mysessionname :engine sqsh
#+PROPERTY: header-args:sql+ :database MyDbName :dbhost MyDbHost :dbuser sa :dbpassword MyPassword
#+PROPERTY: header-args:sql+ :exports results

#+BEGIN_SRC sql
  SELECT top 3 * FROM users;
#+END_SRC

In a property drawer, that would be

* Heading
  :PROPERTIES:
  :header-args:sql: :session mysessionname :engine sqsh
  :header-args:sql+: :database MyDbName :dbhost MyDbHost :dbuser sa :dbpassword MyPassword
  :header-args:sql+: :exports results
  :END:
| improve this answer | |
1

You have to use the prefix :header-args: and the properties termination :END::

* Test
:PROPERTIES:
:header-args: :engine mssql
:header-args+: :cmdline \"-S localhost -U SA -P myPassword\"
:END:

#+NAME:using-properties
#+BEGIN_SRC sql :noweb no-export :tangle
select * from OtherDW.dbo.Supplier_D
#+END_SRC

You can also restrict those to sql blocks if you like:

* Test
:PROPERTIES:
:header-args:sql: :engine mssql
:header-args:sql+: :cmdline \"-S localhost -U SA -P myPassword\"
:END:

#+NAME:using-properties
#+BEGIN_SRC sql :noweb no-export :tangle
select * from OtherDW.dbo.Supplier_D
#+END_SRC

Edit: Added sql+, as suggested by Nick.

| improve this answer | |
  • I tried the above-mentioned methods, it still did not work. – Yu Shen Mar 8 '18 at 17:09
  • 1
    I believe you need ...:sql+:... on the second header-args in order to append it to the first one; otherwise, it overwrites it. See the answer by @unhammer. – NickD May 4 '18 at 16:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.