From: Xi Shen <davidshen84@gmail.com>
To: "Emacs-orgmode@gnu.org" <Emacs-orgmode@gnu.org>
Subject: Re: [PATCH] ob-sql.el: Support sqlcmd and cygwin environment
Date: Thu, 16 Jun 2016 06:04:26 +0000 [thread overview]
Message-ID: <CANO68EOe37t7r2HS7CXpTshDKLG8LYysLVQw2OQDtYbfygsQTQ@mail.gmail.com> (raw)
In-Reply-To: <87inxaz9b9.fsf@saiph.selenimh>
[-- Attachment #1.1: Type: text/plain, Size: 2412 bytes --]
Hi Nicolas,
Please take a look at the updated patch. Changes:
- add ORG-NEWS entry
- add function declaration
- add input file template for `mssql' engine to remove the "affected rows"
tail
On Thu, Jun 16, 2016 at 12:49 AM Nicolas Goaziou <mail@nicolasgoaziou.fr>
wrote:
> Hello,
>
> Xi Shen <davidshen84@gmail.com> writes:
>
> > I suppose I should put the news entry to ./etc/ORG-NEWS file, but into
> > which version? I created below entry, please take look and let me know
> > where do you want me to put it.
>
> I'd say
>
> Version 9.0 > New features > Babel
>
> or
>
> Version 9.0 > Miscellaneous
>
> > *** Improved support to Microsoft SQL Server in =ob-sql.el=
> > =ob-sql.el= library removes support to the ~msosql~ engine which uses
> > the deprecated =osql= command line tool, and replaces it with ~mssql~
> > engine which uses the =sqlcmd= command line tool. Use with properties
> > like this:
> >
> > #+BEGIN_EXAMPLE
> > :engine mssql
> > :dbhost <host.com>
> > :dbuser <username>
> > :dbpassword <secret>
> > :database <database>
> > #+END_EXAMPLE
> >
> > If you want to use the *trusted connection* feature, omit *both* the
> > =dbuser= and =dbpassword= properties and add =cmdline -E= to the
> > properties.
> >
> > If your Emacs is running in a Cygwin environment, the =ob-sql.el=
> > library can pass the converted path to the =sqlcmd= tool.
>
> It looks good.
>
> > I checked the code and it does not quote the arguments for me. It is a
> safe
> > manner in Windows to always quote the path. So I will keep it.
>
> Fair enough.
>
> > I have a question. Currently the table generated by mssql engine has the
> > "affected rows" append to the end, like this.
> >
> > | memberid | username | xx | flags |
> > |-------------------+----------+------+-------|
> > | 1 | GPL | Indo | NULL |
> > | 2 | GPL | Indo | NULL |
> > | | | | |
> > | (2 rows affected) | | | |
> >
> > I personally prefer to remove it. Do you or the org community has a
> > preference about this? Maybe I should keep the behavior align with other
> > engines?
>
> I lean towards removing it, too. I doesn't give useful feedback. We can
> always insert it back later if it introduces unwanted side-effects.
>
>
> Thank you.
>
>
> Regards,
>
> --
> Nicolas Goaziou
>
--
Thanks,
David S.
[-- Attachment #1.2: Type: text/html, Size: 3447 bytes --]
[-- Attachment #2: 0001-ob-sql.el-Support-sqlcmd-in-Cygwin-environment.patch --]
[-- Type: application/octet-stream, Size: 5066 bytes --]
From e689bb17dc7f644f71e884d0b06013359d2f7a70 Mon Sep 17 00:00:00 2001
From: Xi Shen <davidshen84@gmail.com>
Date: Wed, 8 Jun 2016 13:49:54 +0800
Subject: [PATCH] ob-sql.el: Support sqlcmd in Cygwin environment
* etc/ORG-NEWS: Add "Improved support to Microsoft SQL Server via
~sqlcmd~" section to addresss this change.
* lisp/ob-sql.el (org-babel-sql-dbstring-mssql): Format Microsoft
`sqlcmd' command line args.
(org-babel-sql-convert-standard-filename): Convert a Posix path to
Windows long path in Cygwin environment, or do nothing.
(org-babel-execute:sql): Add `mssql' engine support and remove support
for `msosql' engine.
The `osql' command line tool was last updated in 2004,
https://technet.microsoft.com/en-us/library/aa214012(v=sql.80).aspx, and
could not output the query result in a way that morden `org-table.el'
expects. The `sqlcmd' is the preferred command line tool to connect the
Microsoft SQL Server and it also has a Linux version,
https://msdn.microsoft.com/en-us/library/hh568447(v=sql.110).aspx.
TINYCHANGE
---
etc/ORG-NEWS | 20 ++++++++++++++++++++
lisp/ob-sql.el | 36 +++++++++++++++++++++++++++++++++---
2 files changed, 53 insertions(+), 3 deletions(-)
diff --git a/etc/ORG-NEWS b/etc/ORG-NEWS
index ed44a21..8af7423 100644
--- a/etc/ORG-NEWS
+++ b/etc/ORG-NEWS
@@ -210,6 +210,26 @@ mandatory):
:database <database>
:dbpassword <secret>
#+END_EXAMPLE
+**** Improved support to Microsoft SQL Server via ~sqlcmd~
+=ob-sql= library removes support to the ~msosql~ engine which uses the
+deprecated ~osql~ command line tool, and replaces it with ~mssql~
+engine which uses the ~sqlcmd~ command line tool. Use with properties
+like this:
+
+#+BEGIN_EXAMPLE
+ :engine mssql
+ :dbhost <host.com>
+ :dbuser <username>
+ :dbpassword <secret>
+ :database <database>
+#+END_EXAMPLE
+
+If you want to use the *trusted connection* feature, omit *both* the
+=dbuser= and =dbpassword= properties and add =cmdline -E= to the
+properties.
+
+If your Emacs is running in a Cygwin environment, the =ob-sql= library
+can pass the converted path to the =sqlcmd= tool.
**** Support for additional plantuml output formats
The support for output formats of [[http://plantuml.com/][plantuml]] has been extended to now
include:
diff --git a/lisp/ob-sql.el b/lisp/ob-sql.el
index 6488afe..fdf73a0 100644
--- a/lisp/ob-sql.el
+++ b/lisp/ob-sql.el
@@ -62,6 +62,7 @@
(declare-function org-table-import "org-table" (file arg))
(declare-function orgtbl-to-csv "org-table" (table params))
(declare-function org-table-to-lisp "org-table" (&optional txt))
+(declare-function cygwin-convert-file-name-to-windows "cygw32.c" (file &optional absolute-p))
(defvar org-babel-default-header-args:sql '())
@@ -103,6 +104,28 @@ Pass nil to omit that arg."
"Make Oracle command line args for database connection."
(format "%s/%s@%s:%s/%s" user password host port database))
+(defun org-babel-sql-dbstring-mssql (host user password database)
+ "Make sqlcmd commmand line args for database connection.
+`sqlcmd' is the preferred command line tool to access Microsoft
+SQL Server on Windows and 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
+convert the file name. Otherwise, uses Emacs' standard conversion
+function."
+ (format "\"%s\""
+ (if (fboundp 'cygwin-convert-file-name-to-windows)
+ (cygwin-convert-file-name-to-windows file)
+ (convert-standard-filename file))))
+
(defun org-babel-execute:sql (body params)
"Execute a block of Sql code with Babel.
This function is called by `org-babel-execute-src-block'."
@@ -129,10 +152,14 @@ This function is called by `org-babel-execute-src-block'."
(or cmdline "")
(org-babel-process-file-name in-file)
(org-babel-process-file-name out-file)))
- (`msosql (format "osql %s -s \"\t\" -i %s -o %s"
+ (`mssql (format "sqlcmd %s -s \"\t\" %s -i %s -o %s"
(or cmdline "")
- (org-babel-process-file-name in-file)
- (org-babel-process-file-name out-file)))
+ (org-babel-sql-dbstring-mssql
+ 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)
@@ -173,6 +200,9 @@ SET MARKUP HTML OFF SPOOL OFF
SET COLSEP '|'
")
+ (`mssql "SET NOCOUNT ON
+
+")
(_ ""))
(org-babel-expand-body:sql body params)))
(org-babel-eval command "")
--
2.8.3
next prev parent reply other threads:[~2016-06-16 6:04 UTC|newest]
Thread overview: 17+ messages / expand[flat|nested] mbox.gz Atom feed top
2016-06-08 10:24 [PATCH] ob-sql.el: Support sqlcmd and cygwin environment Xi Shen
2016-06-10 22:06 ` Nicolas Goaziou
2016-06-11 2:17 ` Xi Shen
2016-06-11 8:40 ` Nicolas Goaziou
2016-06-12 2:12 ` Xi Shen
2016-06-12 10:37 ` Xi Shen
2016-06-13 5:36 ` Xi Shen
2016-06-13 6:30 ` Xi Shen
2016-06-14 11:52 ` Nicolas Goaziou
2016-06-14 13:02 ` Xi Shen
2016-06-15 4:01 ` Xi Shen
2016-06-15 16:49 ` Nicolas Goaziou
2016-06-16 6:04 ` Xi Shen [this message]
2016-06-16 8:56 ` tumashu
2016-06-16 22:29 ` Nicolas Goaziou
2016-06-20 12:34 ` Xi Shen
2016-07-04 8:11 ` Xi Shen
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
Avoid top-posting and favor interleaved quoting:
https://en.wikipedia.org/wiki/Posting_style#Interleaved_style
List information: https://www.orgmode.org/
* Reply using the --to, --cc, and --in-reply-to
switches of git-send-email(1):
git send-email \
--in-reply-to=CANO68EOe37t7r2HS7CXpTshDKLG8LYysLVQw2OQDtYbfygsQTQ@mail.gmail.com \
--to=davidshen84@gmail.com \
--cc=Emacs-orgmode@gnu.org \
/path/to/YOUR_REPLY
https://kernel.org/pub/software/scm/git/docs/git-send-email.html
* If your mail client supports setting the In-Reply-To header
via mailto: links, try the mailto: link
Be sure your reply has a Subject: header at the top and a blank line
before the message body.
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).