[PATCH] ob-sql.el: Improve Oracle connection and usage for ob-sql.

[PATCH] ob-sql.el: Improve Oracle connection and usage for ob-sql.

[Pierre Téchoueyres]

[-- Attachment #1: Type: text/plain, Size: 346 bytes --]

Hello all Org's developpers,
I would like to provide a small improvement to ob-sql : the possibility to
connect to an oracle database by using the alias defined in TNSNAMES file.

The patch joined here try to achive this. It also improve (slightly) the
data fetching by removing unwanted trailing whitespaces provided by the
LINESIZE directive.


[-- Warning: decoded text below may be mangled, UTF-8 assumed --]
[-- Attachment #2: ob-sql.el: Improve Oracle connection and usage for ob-sql. --]
[-- Type: text/x-patch, Size: 1602 bytes --]

> From d3e27d1c833e7f262a30bd0e370a077b6f57c97b Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Pierre=20T=C3=A9choueyres?= <pierre.techoueyres@free.fr>
Date: Thu, 8 Mar 2018 23:37:29 +0100
Subject: [PATCH] ob-sql.el: Improve Oracle connection and usage for ob-sql.

* lisp/ob-sql.el  (org-babel-sql-dbstring-oracle): don't use empty
args. This allow use of alias defined in Oracle's TNSNAMES files.
(org-babel-execute:sql): don't feed lines with trailing spaces. This
also improve speed for retrieving data.
---
 lisp/ob-sql.el | 11 +++++++++--
 1 file changed, 9 insertions(+), 2 deletions(-)

diff --git a/lisp/ob-sql.el b/lisp/ob-sql.el
index 959ede3de..3ad7906cf 100644
--- a/lisp/ob-sql.el
+++ b/lisp/ob-sql.el
@@ -111,8 +111,14 @@ Pass nil to omit that arg."
 	       (when database (concat "-d" database))))))
 
 (defun org-babel-sql-dbstring-oracle (host port user password database)
-  "Make Oracle command line args for database connection."
-  (format "%s/%s@%s:%s/%s" user password host port database))
+  "Make Oracle command line args for database connection.
+
+If PORT and DATABASE are nil then don't pass them. This allow you to
+use names defined in your TNSNAMES file."
+  (format "%s/%s@%s%s" user password host
+	  (if (and port database)
+	      (format ":%s/%s" port database)
+	    "")))
 
 (defun org-babel-sql-dbstring-mssql (host user password database)
   "Make sqlcmd command line args for database connection.
@@ -241,6 +247,7 @@ SET NEWPAGE 0
 SET TAB OFF
 SET SPACE 0
 SET LINESIZE 9999
+SET TRIMOUT ON TRIMSPOOL ON
 SET ECHO OFF
 SET FEEDBACK OFF
 SET VERIFY OFF
-- 
2.14.3

Re: [PATCH] ob-sql.el: Improve Oracle connection and usage for ob-sql.

[Nicolas Goaziou]

Hello,

pierre.techoueyres@free.fr (Pierre Téchoueyres) writes:

> Hello all Org's developpers,
> I would like to provide a small improvement to ob-sql : the possibility to
> connect to an oracle database by using the alias defined in TNSNAMES file.
>
> The patch joined here try to achive this. It also improve (slightly) the
> data fetching by removing unwanted trailing whitespaces provided by the
> LINESIZE directive.

Thank you.

Have you signed FSF papers? Otherwise, you need to add TINYCHANGE at the
end of your message?

Otherwise, it looks good. Would you mind adding an entry in ORG-NEWS
about it?


Regards,

-- 
Nicolas Goaziou

Re: [PATCH] ob-sql.el: Improve Oracle connection and usage for ob-sql.

[pierre.techoueyres]

> Hello,
Hello Nicolas,
> 
> pierre.techoueyres@free.fr (Pierre Téchoueyres) writes:
> 
>> Hello all Org's developpers,
>> I would like to provide a small improvement to ob-sql : the 
>> possibility to
>> connect to an oracle database by using the alias defined in TNSNAMES 
>> file.
>> 
>> The patch joined here try to achive this. It also improve (slightly) 
>> the
>> data fetching by removing unwanted trailing whitespaces provided by 
>> the
>> LINESIZE directive.
> 
> Thank you.
> 
> Have you signed FSF papers? Otherwise, you need to add TINYCHANGE at 
> the
> end of your message?

Normally, I've done this some time ago for TRAMP and EMACS core (I 
think).
> 
> Otherwise, it looks good. Would you mind adding an entry in ORG-NEWS
> about it?
> 
Yes, I'll try to send a new patch soon.

> 
> Regards,

Re: [PATCH] ob-sql.el: Improve Oracle connection and usage for ob-sql.

[Pierre Téchoueyres]

[-- Attachment #1: Type: text/plain, Size: 201 bytes --]

Nicolas Goaziou <mail@nicolasgoaziou.fr> writes:
> ...
> Otherwise, it looks good. Would you mind adding an entry in ORG-NEWS
> about it?
Hello Nicolas,

What dou you think about the attached patch ?


[-- Warning: decoded text below may be mangled, UTF-8 assumed --]
[-- Attachment #2: Use Oracle's alias in ob-sql patch --]
[-- Type: text/x-patch, Size: 2678 bytes --]

From 0103a07b10915ce7c919b8a6858beff3dbd7e45f Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Pierre=20T=C3=A9choueyres?= <pierre.techoueyres@free.fr>
Date: Thu, 8 Mar 2018 23:37:29 +0100
Subject: [PATCH] ob-sql.el: Improve Oracle connection and usage for ob-sql.

* lisp/ob-sql.el  (org-babel-sql-dbstring-oracle): don't use empty
args. This allow use of alias defined in Oracle's TNSNAMES files.
(org-babel-execute:sql): don't feed lines with trailing spaces. This
also improve speed for retrieving data.
---
 etc/ORG-NEWS   | 18 ++++++++++++++++++
 lisp/ob-sql.el | 11 +++++++++--
 2 files changed, 27 insertions(+), 2 deletions(-)

diff --git a/etc/ORG-NEWS b/etc/ORG-NEWS
index 77373d442..11fe4395d 100644
--- a/etc/ORG-NEWS
+++ b/etc/ORG-NEWS
@@ -199,6 +199,24 @@ you should expect to see something like:
 #+BEGIN_EXAMPLE
   ,#+STARTUP: shrink
 #+END_EXAMPLE
+*** Add support for Oracle's database alias in Babel blocks
+=ob-sql= library already support running SQL blocks against an Oracle
+database using ~sqlplus~.  Now it's possible to use alias names
+defined in TNSNAMES file instead of specifying full connection
+parameters.  See example bellow.
+
+#+BEGIN_SRC org
+  you can use the previous full connection parameters
+  ,#+BEGIN_SRC sql :engine oracle :dbuser me :dbpassword my_insecure_password :database my_db_name :dbhost my_db_host :dbport 1521
+  select sysdate from dual;
+  ,#+END_SRC
+
+  or the alias defined in your TNSNAMES file
+  ,#+BEGIN_SRC sql :engine oracle :dbuser me :dbpassword my_insecure_password :database my_tns_alias
+  select sysdate from dual;
+  ,#+END_SRC
+#+END_SRC
+
 ** New functions
 *** ~org-insert-structure-template~
 
diff --git a/lisp/ob-sql.el b/lisp/ob-sql.el
index 959ede3de..3ad7906cf 100644
--- a/lisp/ob-sql.el
+++ b/lisp/ob-sql.el
@@ -111,8 +111,14 @@ Pass nil to omit that arg."
 	       (when database (concat "-d" database))))))
 
 (defun org-babel-sql-dbstring-oracle (host port user password database)
-  "Make Oracle command line args for database connection."
-  (format "%s/%s@%s:%s/%s" user password host port database))
+  "Make Oracle command line args for database connection.
+
+If PORT and DATABASE are nil then don't pass them. This allow you to
+use names defined in your TNSNAMES file."
+  (format "%s/%s@%s%s" user password host
+	  (if (and port database)
+	      (format ":%s/%s" port database)
+	    "")))
 
 (defun org-babel-sql-dbstring-mssql (host user password database)
   "Make sqlcmd command line args for database connection.
@@ -241,6 +247,7 @@ SET NEWPAGE 0
 SET TAB OFF
 SET SPACE 0
 SET LINESIZE 9999
+SET TRIMOUT ON TRIMSPOOL ON
 SET ECHO OFF
 SET FEEDBACK OFF
 SET VERIFY OFF
-- 
2.14.3

Re: [PATCH] ob-sql.el: Improve Oracle connection and usage for ob-sql.

[Nicolas Goaziou]

Hello,

pierre.techoueyres@free.fr (Pierre Téchoueyres) writes:

> Nicolas Goaziou <mail@nicolasgoaziou.fr> writes:
>> ...
>> Otherwise, it looks good. Would you mind adding an entry in ORG-NEWS
>> about it?
> Hello Nicolas,
>
> What dou you think about the attached patch ?

It looks good.

I added two spaces at the end of sentences and pushed it.

Thank you!

Regards,

-- 
Nicolas Goaziou                                                0x80A93738

Re: [PATCH] ob-sql.el: Improve Oracle connection and usage for ob-sql.

[Pierre Téchoueyres]

[-- Attachment #1: Type: text/plain, Size: 713 bytes --]

Nicolas Goaziou <mail@nicolasgoaziou.fr> writes:
Hi Nicolas,
> ...
> It looks good.
>
> I added two spaces at the end of sentences and pushed it.
> ...

I'm sorry I did a mistake in the previous patch. There is an
inconsistency between  the code in `org-babel-sql-dbstring-oracle' and
the example provided in the ORG-NEWS file.

So I attached two patches:
- the first (0001-ORG-NEWS-...) correct the example in order to match to the code,
- the second (0001-ob-sql.el...) correct the codein order to match to the example.

Personnaly I prefer the last as it match better with the habits of
thoses who uses Oracle's products (We talk almost ever in term of
database than in term of servers).

But it's up to you.


[-- Warning: decoded text below may be mangled, UTF-8 assumed --]
[-- Attachment #2: 0001-ORG-NEWS-Correct-Oracle-connection-and-usage-for-ob.patch --]
[-- Type: text/x-patch, Size: 923 bytes --]

From e04cbea0ac40cd54f3973c3824b7c82c4d4246f3 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Pierre=20T=C3=A9choueyres?= <pierre.techoueyres@free.fr>
Date: Thu, 15 Mar 2018 19:20:23 +0100
Subject: [PATCH] ORG-NEWS: Correct Oracle connection and usage for ob-sql.

* etc/ORG-NEWS: match exemple with code in lisp/ob-sql.el
Use :host instead of :database in call.
---
 etc/ORG-NEWS | 2 +-
 1 file changed, 1 insertion(+), 1 deletion(-)

diff --git a/etc/ORG-NEWS b/etc/ORG-NEWS
index 01a9361df..c19a0dfe1 100644
--- a/etc/ORG-NEWS
+++ b/etc/ORG-NEWS
@@ -212,7 +212,7 @@ parameters.  See example bellow.
   ,#+END_SRC
 
   or the alias defined in your TNSNAMES file
-  ,#+BEGIN_SRC sql :engine oracle :dbuser me :dbpassword my_insecure_password :database my_tns_alias
+  ,#+BEGIN_SRC sql :engine oracle :dbuser me :dbpassword my_insecure_password :host my_tns_alias
   select sysdate from dual;
   ,#+END_SRC
 #+END_SRC
-- 
2.14.3


[-- Warning: decoded text below may be mangled, UTF-8 assumed --]
[-- Attachment #3: 0001-ob-sql.el-Improve-Oracle-connection-and-usage-for-ob.patch --]
[-- Type: text/x-patch, Size: 1788 bytes --]

From fd4ae337ac3a38bc9c9a422628482d6f46599bac Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Pierre=20T=C3=A9choueyres?= <pierre.techoueyres@free.fr>
Date: Thu, 15 Mar 2018 19:15:14 +0100
Subject: [PATCH] ob-sql.el: Improve Oracle connection and usage for ob-sql.

* lisp/ob-sql.el (org-babel-sql-dbstring-oracle): Permit to omit host
and port to allow use of alias defined in Oracle's TNSNAMES files.
This now allow two way calling it :
<user>/<password>@<host>:<port>/<database ID>
or
<user>/<password>@<database alias>
---
 lisp/ob-sql.el | 11 +++++++----
 1 file changed, 7 insertions(+), 4 deletions(-)

diff --git a/lisp/ob-sql.el b/lisp/ob-sql.el
index f44bf5674..d030cf528 100644
--- a/lisp/ob-sql.el
+++ b/lisp/ob-sql.el
@@ -112,10 +112,13 @@ Pass nil to omit that arg."
 
 (defun org-babel-sql-dbstring-oracle (host port user password database)
   "Make Oracle command line arguments for database connection.
-If PORT and DATABASE are nil then don't pass them.  This allows
-you to use names defined in your \"TNSNAMES\" file."
-  (concat (format "%s/%s@%s" user password host)
-	  (and port database (format ":%s/%s" port database))))
+If HOST and PORT are nil then don't pass them.  This allows
+you to use names defined in your \"TNSNAMES\" file.
+So you can connect with <user>/<password>@<host>:<port>/<database> or
+<user>/<password>@<database> using it's alias."
+  (cond ((and user password database (not (and host port))) (format "%s/%s@%s" user password database))
+	((and user password database host port) (format "%s/%s@%s:%s/%s" user password host port database))
+	(t (user-error "Missing information to connect to database."))))
 
 (defun org-babel-sql-dbstring-mssql (host user password database)
   "Make sqlcmd command line args for database connection.
-- 
2.14.3

Re: [PATCH] ob-sql.el: Improve Oracle connection and usage for ob-sql.

[Nicolas Goaziou]

Hello,

pierre.techoueyres@free.fr (Pierre Téchoueyres) writes:

> So I attached two patches:
> - the first (0001-ORG-NEWS-...) correct the example in order to match to the code,
> - the second (0001-ob-sql.el...) correct the codein order to match to the example.
>
> Personnaly I prefer the last as it match better with the habits of
> thoses who uses Oracle's products (We talk almost ever in term of
> database than in term of servers).
>
> But it's up to you.

I applied the second patch, with a slight refactoring.

Thank you.

Regards,

-- 
Nicolas Goaziou