Function: org-babel-execute:sql

org-babel-execute:sql is a byte-compiled function defined in ob-sql.el.gz.

Signature

(org-babel-execute:sql BODY PARAMS)

Documentation

Execute a block of Sql code with Babel.

This function is called by org-babel-execute-src-block.

Source Code

;; Defined in /usr/src/emacs/lisp/org/ob-sql.el.gz
(defun org-babel-execute:sql (body params)
  "Execute a block of Sql code with Babel.
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))
         (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))
         (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 (cl-case (intern 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)))
                    (monetdb (format "mclient -f tab %s < %s > %s"
				     (or cmdline "")
				     (org-babel-process-file-name in-file)
				     (org-babel-process-file-name out-file)))
		    (mssql (format "sqlcmd %s -s \"\t\" %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))))
                    (mysql (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)))
		    ((postgresql postgres)
                     (format
		      "%s%s --set=\"ON_ERROR_STOP=1\" %s -A -P \
footer=off -F \"\t\"  %s -f %s -o %s %s"
		      (if dbpassword
			  (format "PGPASSWORD=%s "
                                  (shell-quote-argument dbpassword))
			"")
                      (or (bound-and-true-p
                           sql-postgres-program)
                          "psql")
		      (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 "")))
		    (sqsh (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))))
		    (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
			      dbhost dbport dbuser dbpassword database)
			     (org-babel-process-file-name in-file)
			     (org-babel-process-file-name out-file)))
		    (saphana (format "hdbsql %s -I %s -o %s %s"
				     (org-babel-sql-dbstring-saphana
				      dbhost dbport dbinstance dbuser dbpassword database)
				     (org-babel-process-file-name in-file)
				     (org-babel-process-file-name out-file)
				     (or cmdline "")))
                    (t (user-error "No support for the %s SQL engine" engine)))))
    (with-temp-file in-file
      (insert
       (pcase (intern engine)
	 (`dbi "/format partbox\n")
         (`oracle "SET PAGESIZE 50000
SET NEWPAGE 0
SET TAB OFF
SET SPACE 0
SET LINESIZE 9999
SET TRIMOUT ON TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING ON
SET MARKUP HTML OFF SPOOL OFF
SET COLSEP '|'

")
	 ((or `mssql `sqsh) "SET NOCOUNT ON

")
	 (`vertica "\\a\n")
	 (_ ""))
       (org-babel-expand-body:sql body params)
       ;; "sqsh" requires "go" inserted at EOF.
       (if (string= engine "sqsh") "\ngo" "")))
    (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))
	  ;; Add header row delimiter after column-names header in first line
	  (cond
	   (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)))))
	 (t
	  ;; Need to figure out the delimiter for the header row
	  (with-temp-buffer
	    (insert-file-contents out-file)
	    (goto-char (point-min))
	    (when (re-search-forward "^\\(-+\\)[^-]" nil t)
	      (setq header-delim (match-string-no-properties 1)))
	    (goto-char (point-max))
	    (forward-char -1)
	    (while (looking-at "\n")
	      (delete-char 1)
	      (goto-char (point-max))
	      (forward-char -1))
	    (write-file out-file))))
	(org-table-import out-file (if (memq (intern engine) '(saphana sqsh)) '(4) '(16)))
	(org-babel-reassemble-table
	 (mapcar (lambda (x)
		   (if (string= (car x) header-delim)
		       'hline
		     x))
		 (org-table-to-lisp))
	 (org-babel-pick-name (cdr (assq :colname-names params))
			      (cdr (assq :colnames params)))
	 (org-babel-pick-name (cdr (assq :rowname-names params))
			      (cdr (assq :rownames params))))))))