* patch: Oracle engine for SQL blocks
@ 2014-06-11 5:10 John Dell'Aquila
2014-06-12 20:00 ` Eric Schulte
0 siblings, 1 reply; 3+ messages in thread
From: John Dell'Aquila @ 2014-06-11 5:10 UTC (permalink / raw
To: emacs-orgmode
[-- Attachment #1: Type: text/plain, Size: 613 bytes --]
Hi,
This patch makes Oracle a supported database for SQL source code
blocks:
- separate or unified logins, take your pick of:
:dbuser user :dbpassword password :dbhost connect_id
:dbuser user/password@connect_id
:cmdline user/password@connect_id
- support for :colnames on/off
- helpful error messages in minibuffer if execution fails
- good looking result block (most of the time)
I've tested this extensively on Windows 7. It only needs the sqlplus
executable and < > file redirection, so it should work everywhere.
I am willing to execute an FSF copyright assignment.
Regards,
John
[-- Attachment #2: 0001-ob-sql.el-Implement-Oracle-engine.patch --]
[-- Type: application/octet-stream, Size: 2985 bytes --]
From 08e01124a7f65fbdeec8d14f0e7b555bb1565ad9 Mon Sep 17 00:00:00 2001
From: John Dell'Aquila <dellaq@ml1.net>
Date: Tue, 10 Jun 2014 20:33:31 -0500
Subject: [PATCH] ob-sql.el: Implement Oracle engine
* lisp/ob-sql.el (org-babel-execute:sql): Add Oracle support with
proper error handling.
---
lisp/ob-sql.el | 41 +++++++++++++++++++++++++++++++++++++++++
1 file changed, 41 insertions(+)
diff --git a/lisp/ob-sql.el b/lisp/ob-sql.el
index 7b85df8..42a03a1 100644
--- a/lisp/ob-sql.el
+++ b/lisp/ob-sql.el
@@ -128,11 +128,31 @@ This function is called by `org-babel-execute-src-block'."
(org-babel-process-file-name in-file)
(org-babel-process-file-name out-file)
(or cmdline "")))
+ ('oracle (format
+ "sqlplus -S -L %s %s <%s >%s"
+ (if (and dbuser dbpassword dbhost) ; build login
+ (format "%s/%s@%s" dbuser dbpassword dbhost)
+ (or dbuser "")) ; or just pass dbuser
+ (or cmdline "")
+ (org-babel-process-file-name in-file)
+ (org-babel-process-file-name out-file)))
(t (error "No support for the %s SQL engine" engine)))))
(with-temp-file in-file
(insert
(case (intern engine)
('dbi "/format partbox\n")
+ ('oracle
+ ;; Inject suitable SQL*Plus settings
+ (concat
+ (if colnames-p
+ "set heading on underline '-'\n" ; column headings
+ "set heading off\n") ; no headings
+ "set colsep '|' tab off\n" ; "|" between columns, no tabs
+ "set linesize 150\n" ; Oracle default width
+ "set newpage none\n" ; no page breaks
+ "set feedback off\n" ; no informational messages
+ "prompt ~_SentineL~!\n" ; marker for post-processing
+ ))
(t ""))
(org-babel-expand-body:sql body params)))
(message command)
@@ -155,6 +175,27 @@ This function is called by `org-babel-execute-src-block'."
(insert "-\n")
(setq header-delim "-")
(write-file out-file)))))
+ ((eq (intern engine) 'oracle)
+ (with-temp-buffer
+ (insert-file-contents out-file)
+ ;; Delete up to and including sentinel (anything before
+ ;; sentinel is garbage from user's login.sql script)
+ (goto-char (point-min))
+ (if (re-search-forward "~_SentineL~!\n" nil t)
+ (delete-region (point-min) (point)))
+ ;; Anything left?
+ (if (eq (buffer-size) 0)
+ (if (string-match ";" body)
+ (error "No rows selected")
+ (error "No rows selected (need final ; perhaps?)")))
+ ;; Scan for *last* Oracle or SQL*Plus error, include up
+ ;; to 5 preceding lines in error message
+ (goto-char (point-max))
+ (if (re-search-backward "^\\(ORA\\|SP2\\)-[0-9]+: " nil t)
+ (let ((p (line-end-position)))
+ (forward-line -5)
+ (error (buffer-substring-no-properties (point) p))))
+ (write-file out-file)))
(t
;; Need to figure out the delimiter for the header row
(with-temp-buffer
--
1.9.2.msysgit.0
^ permalink raw reply related [flat|nested] 3+ messages in thread
* Re: patch: Oracle engine for SQL blocks
2014-06-11 5:10 patch: Oracle engine for SQL blocks John Dell'Aquila
@ 2014-06-12 20:00 ` Eric Schulte
[not found] ` <1402632199.9139.128293873.498FDE9C@webmail.messagingengine.com>
0 siblings, 1 reply; 3+ messages in thread
From: Eric Schulte @ 2014-06-12 20:00 UTC (permalink / raw
To: John Dell'Aquila; +Cc: emacs-orgmode
Hi John,
This patch looks great. Given it's length FSF copyright assignment will
be required so please begin that process and when it is complete I'll be
very happy to apply this patch.
Thanks,
Eric
"John Dell'Aquila" <dellaq@ml1.net> writes:
> Hi,
>
> This patch makes Oracle a supported database for SQL source code
> blocks:
>
> - separate or unified logins, take your pick of:
> :dbuser user :dbpassword password :dbhost connect_id
> :dbuser user/password@connect_id
> :cmdline user/password@connect_id
>
> - support for :colnames on/off
>
> - helpful error messages in minibuffer if execution fails
>
> - good looking result block (most of the time)
>
> I've tested this extensively on Windows 7. It only needs the sqlplus
> executable and < > file redirection, so it should work everywhere.
>
> I am willing to execute an FSF copyright assignment.
>
> Regards,
> John
>
> From 08e01124a7f65fbdeec8d14f0e7b555bb1565ad9 Mon Sep 17 00:00:00 2001
> From: John Dell'Aquila <dellaq@ml1.net>
> Date: Tue, 10 Jun 2014 20:33:31 -0500
> Subject: [PATCH] ob-sql.el: Implement Oracle engine
>
> * lisp/ob-sql.el (org-babel-execute:sql): Add Oracle support with
> proper error handling.
> ---
> lisp/ob-sql.el | 41 +++++++++++++++++++++++++++++++++++++++++
> 1 file changed, 41 insertions(+)
>
> diff --git a/lisp/ob-sql.el b/lisp/ob-sql.el
> index 7b85df8..42a03a1 100644
> --- a/lisp/ob-sql.el
> +++ b/lisp/ob-sql.el
> @@ -128,11 +128,31 @@ This function is called by `org-babel-execute-src-block'."
> (org-babel-process-file-name in-file)
> (org-babel-process-file-name out-file)
> (or cmdline "")))
> + ('oracle (format
> + "sqlplus -S -L %s %s <%s >%s"
> + (if (and dbuser dbpassword dbhost) ; build login
> + (format "%s/%s@%s" dbuser dbpassword dbhost)
> + (or dbuser "")) ; or just pass dbuser
> + (or cmdline "")
> + (org-babel-process-file-name in-file)
> + (org-babel-process-file-name out-file)))
> (t (error "No support for the %s SQL engine" engine)))))
> (with-temp-file in-file
> (insert
> (case (intern engine)
> ('dbi "/format partbox\n")
> + ('oracle
> + ;; Inject suitable SQL*Plus settings
> + (concat
> + (if colnames-p
> + "set heading on underline '-'\n" ; column headings
> + "set heading off\n") ; no headings
> + "set colsep '|' tab off\n" ; "|" between columns, no tabs
> + "set linesize 150\n" ; Oracle default width
> + "set newpage none\n" ; no page breaks
> + "set feedback off\n" ; no informational messages
> + "prompt ~_SentineL~!\n" ; marker for post-processing
> + ))
> (t ""))
> (org-babel-expand-body:sql body params)))
> (message command)
> @@ -155,6 +175,27 @@ This function is called by `org-babel-execute-src-block'."
> (insert "-\n")
> (setq header-delim "-")
> (write-file out-file)))))
> + ((eq (intern engine) 'oracle)
> + (with-temp-buffer
> + (insert-file-contents out-file)
> + ;; Delete up to and including sentinel (anything before
> + ;; sentinel is garbage from user's login.sql script)
> + (goto-char (point-min))
> + (if (re-search-forward "~_SentineL~!\n" nil t)
> + (delete-region (point-min) (point)))
> + ;; Anything left?
> + (if (eq (buffer-size) 0)
> + (if (string-match ";" body)
> + (error "No rows selected")
> + (error "No rows selected (need final ; perhaps?)")))
> + ;; Scan for *last* Oracle or SQL*Plus error, include up
> + ;; to 5 preceding lines in error message
> + (goto-char (point-max))
> + (if (re-search-backward "^\\(ORA\\|SP2\\)-[0-9]+: " nil t)
> + (let ((p (line-end-position)))
> + (forward-line -5)
> + (error (buffer-substring-no-properties (point) p))))
> + (write-file out-file)))
> (t
> ;; Need to figure out the delimiter for the header row
> (with-temp-buffer
--
Eric Schulte
https://cs.unm.edu/~eschulte
PGP: 0x614CA05D (see https://u.fsf.org/yw)
^ permalink raw reply [flat|nested] 3+ messages in thread
* Re: patch: Oracle engine for SQL blocks
[not found] ` <1402632199.9139.128293873.498FDE9C@webmail.messagingengine.com>
@ 2014-06-13 11:44 ` Eric Schulte
0 siblings, 0 replies; 3+ messages in thread
From: Eric Schulte @ 2014-06-13 11:44 UTC (permalink / raw
To: John Dell'Aquila; +Cc: emacs-orgmode
"John Dell'Aquila" <dellaq@ml1.net> writes:
> Thanks, Eric. I've started the FSF assignment process.
>
> I have a question about the header row delimiter in the results table. I
> didn't set the header-delim variable in the code, becasue the user can
> change the Oracle underline character, possibly multiple times, so it is
> difficult to work out a proper setting.
>
> The results appeared OK with header-delim left at "". I now realize that
> this causes the table to be mis-parsed when the first column is blank,
> even though it has a proper header.
>
> I put in a fix to scan for the first "set underline" command and use
> that character, otherwise set header-delim to "-". In the process of
> testing I found I could always set header-delim to '-' and the table
> parsed correctly even when the actual Oracle delimiter was set
> differently.
>
> I'm wondering how this is supposed to work. I'd rather not add
> unecessary complexity, but I don't want on rely on a bug or misfeature
> either.
>
I'm not familiar with this portion of ob-sql, so I'll defer to your best
judgment.
Best,
--
Eric Schulte
https://cs.unm.edu/~eschulte
PGP: 0x614CA05D (see https://u.fsf.org/yw)
^ permalink raw reply [flat|nested] 3+ messages in thread
end of thread, other threads:[~2014-06-13 12:01 UTC | newest]
Thread overview: 3+ messages (download: mbox.gz follow: Atom feed
-- links below jump to the message on this page --
2014-06-11 5:10 patch: Oracle engine for SQL blocks John Dell'Aquila
2014-06-12 20:00 ` Eric Schulte
[not found] ` <1402632199.9139.128293873.498FDE9C@webmail.messagingengine.com>
2014-06-13 11:44 ` Eric Schulte
Code repositories for project(s) associated with this external index
https://git.savannah.gnu.org/cgit/emacs.git
https://git.savannah.gnu.org/cgit/emacs/org-mode.git
This is an external index of several public inboxes,
see mirroring instructions on how to clone and mirror
all data and code used by this external index.