From mboxrd@z Thu Jan 1 00:00:00 1970 From: MaDhAt2r Subject: Re: [Patch v2] Add support for sqsh sql src blocks Date: Sat, 03 Dec 2016 15:31:48 -0600 Message-ID: <87twakwvgb.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> <87vav5zsfm.fsf@ArchNemesis.i-did-not-set--mail-host-address--so-tickle-me> <87eg1pcu5a.fsf@nicolasgoaziou.fr> <8760n1xbtf.fsf@ArchNemesis.i-did-not-set--mail-host-address--so-tickle-me> <8760n0d8ok.fsf@nicolasgoaziou.fr> Mime-Version: 1.0 Content-Type: multipart/mixed; boundary="=-=-=" Return-path: Received: from eggs.gnu.org ([2001:4830:134:3::10]:37388) by lists.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1cDHuT-0006Ln-ID for emacs-orgmode@gnu.org; Sat, 03 Dec 2016 16:31:55 -0500 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71) (envelope-from ) id 1cDHuR-0001Le-PI for emacs-orgmode@gnu.org; Sat, 03 Dec 2016 16:31:53 -0500 Received: from mail-oi0-x230.google.com ([2607:f8b0:4003:c06::230]:33266) by eggs.gnu.org with esmtps (TLS1.0:RSA_AES_128_CBC_SHA1:16) (Exim 4.71) (envelope-from ) id 1cDHuR-0001J6-DT for emacs-orgmode@gnu.org; Sat, 03 Dec 2016 16:31:51 -0500 Received: by mail-oi0-x230.google.com with SMTP id w63so303150088oiw.0 for ; Sat, 03 Dec 2016 13:31:50 -0800 (PST) In-Reply-To: <8760n0d8ok.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 --=-=-= Content-Type: text/plain Absolutely. Thanks, Micah --=-=-= Content-Type: text/x-diff Content-Disposition: inline; filename=0001-add-sqsh-engine-and-ORG-NEWS-entry.patch >From f7c71428981a5f7c0ff5ee44fcecf7003c340813 Mon Sep 17 00:00:00 2001 From: madhat2r Date: Sat, 3 Dec 2016 09:33:13 -0600 Subject: [PATCH] add sqsh engine and ORG-NEWS entry --- etc/ORG-NEWS | 17 ++++++++++++ lisp/ob-sql.el | 85 +++++++++++++++++++++++++++++++++++++++++----------------- 2 files changed, 77 insertions(+), 25 deletions(-) diff --git a/etc/ORG-NEWS b/etc/ORG-NEWS index 9be2443..37832a8 100644 --- a/etc/ORG-NEWS +++ b/etc/ORG-NEWS @@ -64,6 +64,23 @@ is specified then all the output from the window will appears in the results section. If =value= is specified, then only the last returned value of the code will be displayed in the results section. +**** SQL: new engine added ~sqsh~ + +A new engine was added to support ~sqsh~ command line utility for use +against Microsoft SQL Server or Sybase SQL server. + +More information on ~sqsh~ can be found here: [[https://sourceforge.net/projects/sqsh/][sourceforge/sqsh]] + +To use ~sqsh~ in an *sql* =SRC_BLK= set the =:engine= like this: + +#+begin_example +,#+BEGIN_SRC sql :engine sqsh :dbhost my_host :dbuser master :dbpassword pass :database support +Select * From Users +Where clue > 0 +,#+END_SRC +#+end_example + + *** Horizontal rules are no longer ignored in LaTeX table math mode * Version 9.0 diff --git a/lisp/ob-sql.el b/lisp/ob-sql.el index ec94c35..9293329 100644 --- a/lisp/ob-sql.el +++ b/lisp/ob-sql.el @@ -48,10 +48,19 @@ ;; - rownames ;; - rowname-names ;; +;; +;; Engines supported: +;; - mysql +;; - dbi +;; - mssql +;; - sqsh +;; - postgresql +;; - oracle +;; ;; TODO: ;; ;; - support for sessions -;; - support for more engines (currently only supports mysql) +;; - support for more engines ;; - what's a reasonable way to drop table data into SQL? ;; @@ -66,13 +75,13 @@ (defvar org-babel-default-header-args:sql '()) (defconst org-babel-header-args:sql - '((engine . :any) - (out-file . :any) - (dbhost . :any) - (dbport . :any) - (dbuser . :any) - (dbpassword . :any) - (database . :any)) + '((engine . :any) + (out-file . :any) + (dbhost . :any) + (dbport . :any) + (dbuser . :any) + (dbpassword . :any) + (database . :any)) "SQL-specific header arguments.") (defun org-babel-expand-body:sql (body params) @@ -95,9 +104,9 @@ Pass nil to omit that arg." (combine-and-quote-strings (delq nil - (list (when host (concat "-h" host)) - (when port (format "-p%d" port)) - (when user (concat "-U" user)) + (list (when host (concat "-h" host)) + (when port (format "-p%d" port)) + (when user (concat "-U" user)) (when database (concat "-d" database)))))) (defun org-babel-sql-dbstring-oracle (host port user password database) @@ -110,12 +119,23 @@ Pass nil to omit that arg." SQL Server on Windows and Linux platform." (mapconcat #'identity (delq nil - (list (when host (format "-S \"%s\"" host)) - (when user (format "-U \"%s\"" user)) + (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-dbstring-sqsh (host user password database) + "Make sqsh 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 +161,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 +180,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 +207,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 +231,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 +271,10 @@ 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 --=-=-= Content-Type: text/plain On Dec 03 at 10:06 PM, Nicolas Goaziou said thus: > Hello, > > MaDhAt2r writes: > >> I went ahead and set up a public branch for these changes. >> >> I added ORG-NEWS entry, and some other minor changes in the commentary >> of ob-sql.el. > > Great. > >> https://github.com/madhat2r/org-mode/tree/feature/babel-sqsh-engine > > Would you mind sending me the patch through this ML? > > Thank you. > > Regards, > > -- > Nicolas Goaziou --=-=-=--