From mboxrd@z Thu Jan 1 00:00:00 1970 From: MaDhAt2r Subject: Re: [Patch v2] Add support for sqsh sql src blocks Date: Tue, 29 Nov 2016 19:07:25 -0600 Message-ID: <87vav5zsfm.fsf@ArchNemesis.i-did-not-set--mail-host-address--so-tickle-me> References: <87vavdkmes.fsf@ArchNemesis.i-did-not-set--mail-host-address--so-tickle-me> <87a8cjfe4c.fsf@nicolasgoaziou.fr> Mime-Version: 1.0 Content-Type: text/plain Return-path: Received: from eggs.gnu.org ([2001:4830:134:3::10]:48846) by lists.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1cBtMx-0004D7-WB for emacs-orgmode@gnu.org; Tue, 29 Nov 2016 20:07:33 -0500 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71) (envelope-from ) id 1cBtMw-0001n9-Fu for emacs-orgmode@gnu.org; Tue, 29 Nov 2016 20:07:31 -0500 Received: from mail-pg0-x235.google.com ([2607:f8b0:400e:c05::235]:34831) by eggs.gnu.org with esmtps (TLS1.0:RSA_AES_128_CBC_SHA1:16) (Exim 4.71) (envelope-from ) id 1cBtMw-0001m7-3B for emacs-orgmode@gnu.org; Tue, 29 Nov 2016 20:07:30 -0500 Received: by mail-pg0-x235.google.com with SMTP id p66so75424855pga.2 for ; Tue, 29 Nov 2016 17:07:28 -0800 (PST) In-Reply-To: <87a8cjfe4c.fsf@nicolasgoaziou.fr> 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: Nicolas Goaziou Cc: emacs-orgmode@gnu.org Thanks for your comments Nicolas! I hope this is better. -Micah * lisp/ob-sql.el (org-babel-sql-dbstring-sqsh): New Function. Make sqsh command line args for databse connection. (org-babel-execute:sql): Add support for sqsh engine TINYCHANGE ob-sql.el | 52 ++++++++++++++++++++++++++++++++++++++++------------ 1 file changed, 40 insertions(+), 12 deletions(-) diff --git a/ob-sql.el b/ob-sql.el index ec94c35..f9935a4 100644 --- a/ob-sql.el +++ b/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,17 +198,17 @@ 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) - (`dbi "/format partbox\n") + (`dbi "/format partbox\n") (`oracle "SET PAGESIZE 50000 SET NEWPAGE 0 SET TAB OFF @@ -203,18 +222,22 @@ 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 +262,12 @@ 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) -- 2.10.2 On Nov 28 at 11:12 PM, Nicolas Goaziou said thus: > Hello, > > MaDhAt2r writes: > >> ob-sql-el: Add support for `sqsh' as an SQL engine > > Thank you for the patch. Some comments follow. > >> - (`mssql "SET NOCOUNT ON >> + ((or `mssql `sqsh ) "SET NOCOUNT ON > > There is a spurious white space above. > >> - (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" >> + "") >> + )) > > ;; sqsh requires "go" inserted at EOF. > > also, do not leave dangling parenthesis at the end of the expression > above. > >> + (cond >> + ((equal (intern engine) 'sqsh) >> + (org-table-import out-file '(4)) >> + ) >> + (t >> + (org-table-import out-file '(16)) >> + ) >> + ) > > See above about parenthesis. > > Eventually, could you write a proper commit message, e.g. > > * lisp/ob-sql.el (org-babel-sql-dbstring-sqsh): New function > (....): Added .... > > Regards, > > -- > Nicolas Goaziou