From mboxrd@z Thu Jan 1 00:00:00 1970 From: MaDhAt2r Subject: Add support for sqsh sql src blocks Date: Thu, 24 Nov 2016 08:00:43 -0600 Message-ID: <87vavdkmes.fsf@ArchNemesis.i-did-not-set--mail-host-address--so-tickle-me> Mime-Version: 1.0 Content-Type: text/plain Return-path: Received: from eggs.gnu.org ([2001:4830:134:3::10]:52888) by lists.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1c9uZz-0006on-IW for emacs-orgmode@gnu.org; Thu, 24 Nov 2016 09:00:48 -0500 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71) (envelope-from ) id 1c9uZy-00010V-Ax for emacs-orgmode@gnu.org; Thu, 24 Nov 2016 09:00:47 -0500 Received: from mail-oi0-x22c.google.com ([2607:f8b0:4003:c06::22c]:34198) by eggs.gnu.org with esmtps (TLS1.0:RSA_AES_128_CBC_SHA1:16) (Exim 4.71) (envelope-from ) id 1c9uZy-0000zE-1W for emacs-orgmode@gnu.org; Thu, 24 Nov 2016 09:00:46 -0500 Received: by mail-oi0-x22c.google.com with SMTP id y198so51974959oia.1 for ; Thu, 24 Nov 2016 06:00:45 -0800 (PST) Received: from localhost ([2602:30a:c025:da20:3617:ebff:fecc:a65b]) by smtp.gmail.com with ESMTPSA id 17sm11658839oty.17.2016.11.24.06.00.43 for (version=TLS1_2 cipher=ECDHE-RSA-CHACHA20-POLY1305 bits=256/256); Thu, 24 Nov 2016 06:00:43 -0800 (PST) List-Id: "General discussions about Org-mode." List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org Sender: "Emacs-orgmode" To: emacs-orgmode@gnu.org ob-sql-el: Add support for `sqsh' as an SQL engine TINYCHANGE 1 file changed, 41 insertions(+), 10 deletions(-) ob-sql.el | 51 +++++++++++++++++++++++++++++++++++++++++---------- modified ob-sql.el @@ -116,6 +116,17 @@ SQL Server on Windows and Linux platform." (when database (format "-d \"%s\"" database)))) " ")) +(defun org-babel-sql-dbstring-sqsh (host user password database) + "Make sqlcmd commmand line args for database connection. +`sqsh' is one method to access Sybase or MS SQL via Linux platform" + (mapconcat #'identity + (delq nil + (list (when host (format "-S \"%s\"" host)) + (when user (format "-U \"%s\"" user)) + (when password (format "-P \"%s\"" password)) + (when database (format "-D \"%s\"" database)))) + " ")) + (defun org-babel-sql-convert-standard-filename (file) "Convert the file name to OS standard. If in Cygwin environment, uses Cygwin specific function to @@ -141,14 +152,14 @@ This function is called by `org-babel-execute-src-block'." (in-file (org-babel-temp-file "sql-in-")) (out-file (or (cdr (assq :out-file params)) (org-babel-temp-file "sql-out-"))) - (header-delim "") + (header-delim "") (command (pcase (intern engine) - (`dbi (format "dbish --batch %s < %s | sed '%s' > %s" + (`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" + (`monetdb (format "mclient -f tab %s < %s > %s" (or cmdline "") (org-babel-process-file-name in-file) (org-babel-process-file-name out-file))) @@ -160,7 +171,15 @@ This function is called by `org-babel-execute-src-block'." (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" + (`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)))) + (`mysql (format "mysql %s %s %s < %s > %s" (org-babel-sql-dbstring-mysql dbhost dbport dbuser dbpassword database) (if colnames-p "" "-N") @@ -179,13 +198,13 @@ footer=off -F \"\t\" %s -f %s -o %s %s" (org-babel-process-file-name in-file) (org-babel-process-file-name out-file) (or cmdline ""))) - (`oracle (format + (`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))) - (_ (error "No support for the %s SQL engine" engine))))) + (_ (error "No support for the %s SQL engine" engine))))) (with-temp-file in-file (insert (pcase (intern engine) @@ -203,18 +222,23 @@ SET MARKUP HTML OFF SPOOL OFF SET COLSEP '|' ") - (`mssql "SET NOCOUNT ON + ((or `mssql `sqsh ) "SET NOCOUNT ON ") (_ "")) - (org-babel-expand-body:sql body params))) + (org-babel-expand-body:sql body params) + ;; sqsh requires `go' inserted at EOF + (if (equal (intern 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)) + ((memq (intern engine) '(dbi mysql postgresql sqsh)) ;; Add header row delimiter after column-names header in first line (cond (colnames-p @@ -239,7 +263,14 @@ SET COLSEP '|' (goto-char (point-max)) (forward-char -1)) (write-file out-file)))) - (org-table-import out-file '(16)) + (cond + ((equal (intern engine) 'sqsh) + (org-table-import out-file '(4)) + ) + (t + (org-table-import out-file '(16)) + ) + ) (org-babel-reassemble-table (mapcar (lambda (x) (if (string= (car x) header-delim)