emacs-orgmode@gnu.org archives
 help / color / mirror / code / Atom feed

* Add support for sqsh sql src blocks
@ 2016-11-24 14:00 MaDhAt2r
  2016-11-28 22:12 ` Nicolas Goaziou
  0 siblings, 1 reply; 9+ messages in thread
From: MaDhAt2r @ 2016-11-24 14:00 UTC (permalink / raw)
  To: emacs-orgmode


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)

^ permalink raw reply	[flat|nested] 9+ messages in thread

• * Re: Add support for sqsh sql src blocks
    2016-11-24 14:00 Add support for sqsh sql src blocks MaDhAt2r
  @ 2016-11-28 22:12 ` Nicolas Goaziou
    2016-11-30  1:07   ` [Patch v2] " MaDhAt2r
    0 siblings, 1 reply; 9+ messages in thread
  From: Nicolas Goaziou @ 2016-11-28 22:12 UTC (permalink / raw)
    To: MaDhAt2r; +Cc: emacs-orgmode
  
  Hello,
  
  MaDhAt2r <madhat2r@dukefoo.com> 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
  
  ^ permalink raw reply	[flat|nested] 9+ messages in thread

• • * Re: [Patch v2] Add support for sqsh sql src blocks
      2016-11-28 22:12 ` Nicolas Goaziou
    @ 2016-11-30  1:07   ` MaDhAt2r
      2016-12-03  8:08     ` Nicolas Goaziou
      2016-12-04 13:01     ` Sebastien Vauban
      0 siblings, 2 replies; 9+ messages in thread
    From: MaDhAt2r @ 2016-11-30  1:07 UTC (permalink / raw)
      To: Nicolas Goaziou; +Cc: emacs-orgmode
    
    
    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 <madhat2r@dukefoo.com> 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
    
    ^ permalink raw reply related	[flat|nested] 9+ messages in thread

  • • * Re: [Patch v2] Add support for sqsh sql src blocks
        2016-11-30  1:07   ` [Patch v2] " MaDhAt2r
      @ 2016-12-03  8:08     ` Nicolas Goaziou
        2016-12-03 15:38       ` MaDhAt2r
        2016-12-04 13:01     ` Sebastien Vauban
        1 sibling, 1 reply; 9+ messages in thread
      From: Nicolas Goaziou @ 2016-12-03  8:08 UTC (permalink / raw)
        To: MaDhAt2r; +Cc: emacs-orgmode
      
      Hello,
      
      MaDhAt2r <madhat2r@dukefoo.com> writes:
      
      > Thanks for your comments Nicolas! I hope this is better.
      
      Thank you. This looks good.
      
      However, I'm not able to apply it on master branch. Could you rebase
      your branch against master and send that patch again?
      
      Also, could you provide an ORG-NEWS entry about the new feature?
      
      > +(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"
      
      `sqsh' -> "sqsh"
      
      > +  (cond
      > +	 ((equal (intern engine) 'sqsh)
      > +    (org-table-import out-file '(4))
      > +    )
      
      The lonesome parenthesis should go to the line above it.
      
      
      Regards,
      
      -- 
      Nicolas Goaziou
      
      ^ permalink raw reply	[flat|nested] 9+ messages in thread

    • • * Re: [Patch v2] Add support for sqsh sql src blocks
          2016-12-03  8:08     ` Nicolas Goaziou
        @ 2016-12-03 15:38       ` MaDhAt2r
          2016-12-03 21:06         ` Nicolas Goaziou
          0 siblings, 1 reply; 9+ messages in thread
        From: MaDhAt2r @ 2016-12-03 15:38 UTC (permalink / raw)
          To: Nicolas Goaziou; +Cc: emacs-orgmode
        
        
        Thanks Nicolas.
        
        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.
        
        https://github.com/madhat2r/org-mode/tree/feature/babel-sqsh-engine
        
        -Micah
        
        On Dec 03 at 09:08 AM, Nicolas Goaziou said thus:
        > Hello,
        >
        > MaDhAt2r <madhat2r@dukefoo.com> writes:
        >
        >> Thanks for your comments Nicolas! I hope this is better.
        >
        > Thank you. This looks good.
        >
        > However, I'm not able to apply it on master branch. Could you rebase
        > your branch against master and send that patch again?
        >
        > Also, could you provide an ORG-NEWS entry about the new feature?
        >
        >> +(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"
        >
        > `sqsh' -> "sqsh"
        >
        >> +  (cond
        >> +	 ((equal (intern engine) 'sqsh)
        >> +    (org-table-import out-file '(4))
        >> +    )
        >
        > The lonesome parenthesis should go to the line above it.
        >
        >
        > Regards,
        >
        > -- 
        > Nicolas Goaziou
        
        ^ permalink raw reply	[flat|nested] 9+ messages in thread

      • • * Re: [Patch v2] Add support for sqsh sql src blocks
            2016-12-03 15:38       ` MaDhAt2r
          @ 2016-12-03 21:06         ` Nicolas Goaziou
            2016-12-03 21:31           ` MaDhAt2r
            0 siblings, 1 reply; 9+ messages in thread
          From: Nicolas Goaziou @ 2016-12-03 21:06 UTC (permalink / raw)
            To: MaDhAt2r; +Cc: emacs-orgmode
          
          Hello,
          
          MaDhAt2r <madhat2r@dukefoo.com> 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
          
          ^ permalink raw reply	[flat|nested] 9+ messages in thread

        • • * Re: [Patch v2] Add support for sqsh sql src blocks
              2016-12-03 21:06         ` Nicolas Goaziou
            @ 2016-12-03 21:31           ` MaDhAt2r
              2016-12-05 21:47             ` Nicolas Goaziou
              0 siblings, 1 reply; 9+ messages in thread
            From: MaDhAt2r @ 2016-12-03 21:31 UTC (permalink / raw)
              To: Nicolas Goaziou; +Cc: emacs-orgmode
            
            [-- Attachment #1: Type: text/plain, Size: 31 bytes --]
            
            
            Absolutely. 
            
            Thanks,
            
            Micah
            
            
            [-- Warning: decoded text below may be mangled, UTF-8 assumed --]
            [-- Attachment #2: 0001-add-sqsh-engine-and-ORG-NEWS-entry.patch --]
            [-- Type: text/x-diff, Size: 7843 bytes --]
            
            From f7c71428981a5f7c0ff5ee44fcecf7003c340813 Mon Sep 17 00:00:00 2001
            From: madhat2r <MaDhAt2r@dukefoo.com>
            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
            
            
            [-- Attachment #3: Type: text/plain, Size: 457 bytes --]
            
            
            On Dec 03 at 10:06 PM, Nicolas Goaziou said thus:
            > Hello,
            >
            > MaDhAt2r <madhat2r@dukefoo.com> 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
            
            ^ permalink raw reply related	[flat|nested] 9+ messages in thread

          • • * Re: [Patch v2] Add support for sqsh sql src blocks
                2016-12-03 21:31           ` MaDhAt2r
              @ 2016-12-05 21:47             ` Nicolas Goaziou
                0 siblings, 0 replies; 9+ messages in thread
              From: Nicolas Goaziou @ 2016-12-05 21:47 UTC (permalink / raw)
                To: MaDhAt2r; +Cc: emacs-orgmode
              
              Hello,
              
              MaDhAt2r <madhat2r@dukefoo.com> writes:
              
              > Absolutely.
              >
              > Thanks,
              >
              > Micah
              >
              > From f7c71428981a5f7c0ff5ee44fcecf7003c340813 Mon Sep 17 00:00:00 2001
              > From: madhat2r <MaDhAt2r@dukefoo.com>
              > Date: Sat, 3 Dec 2016 09:33:13 -0600
              > Subject: [PATCH] add sqsh engine and ORG-NEWS entry
              
              Applied. Thank you.
              
              Regards,
              
              -- 
              Nicolas Goaziou
              
              ^ permalink raw reply	[flat|nested] 9+ messages in thread

    • * Re: [Patch v2] Add support for sqsh sql src blocks
        2016-11-30  1:07   ` [Patch v2] " MaDhAt2r
        2016-12-03  8:08     ` Nicolas Goaziou
      @ 2016-12-04 13:01     ` Sebastien Vauban
        1 sibling, 0 replies; 9+ messages in thread
      From: Sebastien Vauban @ 2016-12-04 13:01 UTC (permalink / raw)
        To: emacs-orgmode-mXXj517/zsQ
      
      Hello all,
      
      A quick question: does someone has a link to a working executable on
      Windows (with or without Cygwin).  I have searched recently, but found
      nothing, or not-working exe.
      
      A pity it's not available in Cygwin packages...
      
      Thanks in advance.
      
      -- 
      Sebastien Vauban
      
      ^ permalink raw reply	[flat|nested] 9+ messages in thread

end of thread, other threads:[~2016-12-05 21:47 UTC | newest]

Thread overview: 9+ messages (download: mbox.gz / follow: Atom feed)
-- links below jump to the message on this page --
2016-11-24 14:00 Add support for sqsh sql src blocks MaDhAt2r
2016-11-28 22:12 ` Nicolas Goaziou
2016-11-30  1:07   ` [Patch v2] " MaDhAt2r
2016-12-03  8:08     ` Nicolas Goaziou
2016-12-03 15:38       ` MaDhAt2r
2016-12-03 21:06         ` Nicolas Goaziou
2016-12-03 21:31           ` MaDhAt2r
2016-12-05 21:47             ` Nicolas Goaziou
2016-12-04 13:01     ` Sebastien Vauban

Code repositories for project(s) associated with this public inbox

	https://git.savannah.gnu.org/cgit/emacs/org-mode.git

This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox;
as well as URLs for read-only IMAP folder(s) and NNTP newsgroup(s).