* 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
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 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).