Last edit
Changed:
< ;; Copyright (C) 2011 孤峰独秀
< ;; Last Updated: Joseph 2011-10-03 14:03:54 星期一
to
> ;; Copyright (C) 2011~2012 纪秀峰(Joseph)
> ;; Last Updated: Joseph 2012-01-15 21:33:39 星期日
Changed:
< ;; Author: 孤峰独秀 jixiuf@gmail.com
to
> ;; Author: 纪秀峰(Joseph) jixiuf@gmail.com
Changed:
< ;; (unless (and sqlplus-connection
< ;; (equal (process-status (nth 0 sqlplus-connection)) 'run))
< ;; (setq sqlplus-connection (call-interactively 'oracle-query-create-connection)))
to
> ;; (unless (oracle-query-connection-alive-p c)
> ;; (setq sqlplus-connection (call-interactively 'oracle-query-create-connection)))
> ;;
Deleted:
< (defvar oracle-query-heading nil
< "when set heading on ,the result of heading will be stored in the variable")
Changed:
< (let (result row index-of-result)
to
> (let (result row index-of-result oracle-query-heading)
Changed:
< )result))
to
> )(cons oracle-query-heading result)))
Added:
> (defun oracle-query-connection-alive-p(connection)
> "test whether the connection is alive."
> (and connection
> (listp connection)
> (processp (car connection))
> (bufferp (nth 1 connection))
> (buffer-live-p (nth 1 connection))
> (equal (process-status (car connection)) 'run)))
Changed:
< (defun oracle-query-close-connection(sqlplus-connection)
< "close connection.kill sqlplus process and buffer ."
< (kill-process (nth 0 sqlplus-connection))
< (kill-buffer (nth 1 sqlplus-connection))
< (setq sqlplus-connection nil)
to
> (defun oracle-query-close-connection(connection)
> "close connection.kill oracle process and buffer ."
> (when (oracle-query-connection-alive-p connection)
> (process-send-string (car connection) "exit\n"))
> (sleep-for 0.1)
> (when (oracle-query-connection-alive-p connection)
> (kill-process (car connection))
> (kill-buffer (nth 1 connection)))
Added:
> (cdr (oracle-query-with-heading sql oracle-query-connection)))
> (defun oracle-query-with-heading (sql &optional oracle-query-connection)
> "execute sql using `sqlplus' ,and return the result of it."
Deleted:
< (defun oracle-query-with-heading (sql &optional oracle-query-connection)
< "execute sql using `sqlplus' ,and return the result of it."
< (let ((result (oracle-query sql oracle-query-connection)))
< (cons oracle-query-heading result)))
;;; oracle-query.el --- execute sql select using sqlplus. -*- coding:utf-8 -*- ;; Copyright (C) 2011~2012 纪秀峰(Joseph) ;; Last Updated: Joseph 2012-01-15 21:33:39 星期日 ;; Created: 2011-7-31 ;; Version: 0.1.4 ;; Author: 纪秀峰(Joseph) jixiuf@gmail.com ;; Keywords: oracle sql emacs ;; Filename: oracle-query.el ;; Description:execute sql select using sqlplus. ;; URL:http://www.emacswiki.org/emacs/download/oracle-query.el ;; https://github.com/jixiuf/sqlparser ;; Compatibility: Test on Linux ;; This program is free software; you can redistribute it and/or modify ;; it under the terms of the GNU General Public License as published by ;; the Free Software Foundation, either version 3 of the License, or ;; (at your option) any later version. ;; This program is distributed in the hope that it will be useful, ;; but WITHOUT ANY WARRANTY; without even the implied warranty of ;; MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the ;; GNU General Public License for more details. ;; You should have received a copy of the GNU General Public License ;; along with this program. If not, see <http://www.gnu.org/licenses/>. ;;; Commentary: ;; execute sql using sqlplus and return as list . ;; ;; (oracle-query "select empno,ename from emp where empno<=7499") ;; got : (("7369" "SMITH") ("7499" "ALLEN")) ;; or (oracle-query-with-heading) ;; got : (("EMPNO" "ENAME") ("7369" "SMITH") ("7499" "ALLEN")) ;; using default connection ,not recommended. ;; ;; (defvar connection (oracle-query-create-connection "scott/tiger")) ;; (oracle-query "select empno from emp" connection) ;; recommended ;; ;; the normal way to use oracle-query.el is : ;; 1: ;; (defvar sqlplus-connection nil) ;; (unless (oracle-query-connection-alive-p c) ;; (setq sqlplus-connection (call-interactively 'oracle-query-create-connection))) ;; ;; 2: ;; (oracle-query "select empno from emp" sqlplus-connection) ;; 3: ;; (oracle-query-close-connection sqlplus-connection) ;; ;;; Commands: ;; ;; Below are complete command list: ;; ;; `oracle-query-create-connection' ;; create a connection to oracle using sqlplus ,and return the ;; ;;; Customizable Options: ;; ;; Below are customizable option list: ;; ;;; Code: (defvar oracle-query-default-connection nil) (defvar oq-timeout-wait-for-result 300 "waiting 300s for sql result returned.") (defvar oq-linesize 20000 "Default linesize for sqlplus") (defvar oq-pagesize 50000 "Default pagesize for sqlplus") ;; (oracle-query-fetch-username-from-connect-string "scott/tiger") (defun oracle-query-fetch-username-from-connect-string(connect-string) (when (string-match "\\(.*\\)/.*" connect-string) (match-string 1 connect-string))) (defun oq-parse-result-as-list (raw-result) (let (result row index-of-result oracle-query-heading) (with-temp-buffer (insert raw-result) (goto-char (point-min)) (while (re-search-forward "[ \t\n]* [ \t\n]*" nil t) (replace-match " " nil nil)) (goto-char (point-min)) (while (re-search-forward "^[ \t]+" nil t) (replace-match "" nil nil)) (goto-char (point-min)) (forward-line 1) (setq oracle-query-heading (split-string (buffer-substring-no-properties (point-at-bol) (point-at-eol)) " " t)) (forward-line 2) (setq index-of-result 3) (while (not (= (point-at-eol) (point-max))) (unless (or (= 1 (% index-of-result oq-pagesize)) (= 2 (% index-of-result oq-pagesize)) (= 0 (% index-of-result oq-pagesize))) (setq row (split-string (buffer-substring-no-properties (point-at-bol) (point-at-eol)) " " t)) (setq result (append result (list row)))) (forward-line) (beginning-of-line) (setq index-of-result (1+ index-of-result))) )(cons oracle-query-heading result))) ;; (defun oq-build-connection-string() ;; " default:sqlplus scott/tiger@localhost:1521/orcl" ;; (format "sqlplus %s/%s@%s:%s/%s %s" ;; oq-username oq-password oq-server oq-port oq-dbname (if oq-as-sysdba "as sysdba" ""))) (defun oracle-query-read-connect-string () (let( (connection-string (read-string "SqlPlus Connect String:" "" nil))) (list connection-string))) ;; (oracle-query-create-connection "scott/tiger") ;; (oracle-query-create-connection "scott/tiger@localhost:1521/orcl") ;; (oracle-query-create-connection "system/root as sysdba") ;;;###autoload (defun oracle-query-create-connection(connect-string) "create a connection to oracle using sqlplus ,and return the created process" (interactive (oracle-query-read-connect-string)) (let ((oracle-query-process (start-process "sqlplus" (concat " *oracle-query-" (oracle-query-fetch-username-from-connect-string connect-string) "-" (number-to-string (random)) "*") "sqlplus" connect-string))) (process-send-string oracle-query-process "set heading on;\n") (process-send-string oracle-query-process (format "set linesize %d;\n" oq-linesize)) (process-send-string oracle-query-process "set colsep ' ';\n");;column separater (process-send-string oracle-query-process "set null 'NULL';\n");; (process-send-string oracle-query-process "set wrap off;\n") (process-send-string oracle-query-process (format "set pagesize %s;\n" oq-pagesize)) (process-send-string oracle-query-process "set feedback on;\n") (process-send-string oracle-query-process "set serveroutput on;\n") (set-process-query-on-exit-flag oracle-query-process nil) (set-process-sentinel oracle-query-process (lambda (proc change) (when (string-match "\\(finished\\|exited\\|exited abnormally with code\\)" change) (message (concat (process-name proc) "closed")) (kill-buffer (process-buffer proc)) ))) ;; (set-process-filter oracle-query-process 'oq-filter-fun) (list oracle-query-process (process-buffer oracle-query-process) (oracle-query-fetch-username-from-connect-string connect-string)) )) (defun oracle-query-connection-alive-p(connection) "test whether the connection is alive." (and connection (listp connection) (processp (car connection)) (bufferp (nth 1 connection)) (buffer-live-p (nth 1 connection)) (equal (process-status (car connection)) 'run))) ;;;###autoload (defun oracle-query-close-connection(connection) "close connection.kill oracle process and buffer ." (when (oracle-query-connection-alive-p connection) (process-send-string (car connection) "exit\n")) (sleep-for 0.1) (when (oracle-query-connection-alive-p connection) (kill-process (car connection)) (kill-buffer (nth 1 connection))) ) ;;(oracle-query "select empno from emp") ;; (oracle-query "select empno from emp" (oracle-query-create-connection "scott/tiger")) ;;;###autoload (defun oracle-query (sql &optional oracle-query-connection) "execute sql using `sqlplus' ,and return the result of it." (cdr (oracle-query-with-heading sql oracle-query-connection))) (defun oracle-query-with-heading (sql &optional oracle-query-connection) "execute sql using `sqlplus' ,and return the result of it." (let( (connection oracle-query-connection) process) (unless connection (unless (and oracle-query-default-connection (buffer-live-p (nth 1 oracle-query-default-connection)) (equal (process-status (nth 0 oracle-query-default-connection)) 'run) ) (setq oracle-query-default-connection (call-interactively 'oracle-query-create-connection))) (setq connection oracle-query-default-connection) ) (setq process (car connection)) (when (string-match "\\(.*\\);[ \t\n\r]*" sql) (setq sql (match-string 1 sql))) (setq sql (replace-regexp-in-string "[\n\r]" " " sql)) (with-current-buffer (process-buffer process) (delete-region (point-min) (point-max)) (let ((start (point-min)) end) (goto-char (point-max)) (process-send-string process (format "%s ;\n" sql)) (goto-char (point-min)) (while (not (re-search-forward "^\\([0-9]+\\|no\\) rows? \\(selected\\|updated\\|deleted\\)" nil t 1)) (when (accept-process-output process oq-timeout-wait-for-result 0 nil) (goto-char (point-min)))) (if (not (string= (match-string 2) "selected")) (match-string 0) (setq end (1- (match-beginning 0))) (when (re-search-backward "\\bSQL> " nil t 1) (setq start (match-end 0))) (oq-parse-result-as-list (buffer-substring start end))))))) (provide 'oracle-query) ;;; oracle-query.el ends here