Re: [O] [Patch v2] Add support for sqsh sql src blocks

[Nicolas Goaziou]

Hello,

MaDhAt2r  writes:

> Absolutely.
>
> Thanks,
>
> Micah
>
> 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

Applied. Thank you.

Regards,

-- 
Nicolas Goaziou

Re: [O] [Patch v2] Add support for sqsh sql src blocks

[Sebastien Vauban]

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

Re: [O] [Patch v2] Add support for sqsh sql src blocks

[MaDhAt2r]

Absolutely. 

Thanks,

Micah

>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'."
  

Re: [O] [Patch v2] Add support for sqsh sql src blocks

[Nicolas Goaziou]

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

Re: [O] [Patch v2] Add support for sqsh sql src blocks

[MaDhAt2r]

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  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

Re: [O] [Patch v2] Add support for sqsh sql src blocks

[Nicolas Goaziou]

Hello,

MaDhAt2r  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

Re: [O] [Patch v2] Add support for sqsh sql src blocks

[MaDhAt2r]

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 5
 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 "")