Navigation: Table of Contents, Index, next: Emacs Colors, prev: Elisp Installation HOWTO, up: Emacs
This page is home of SQL mode. SQL mode is part of Emacs and XEmacs. You can get the latest versions here. This page is also home of some other code I wrote that can be used together with SQL mode.
Overview:
The interactive SQL mode is based on comint-mode. The following interpreters are supported:
If your favorite SQL interpreter is not listed, get in touch with me and I'll consider adding it.
Please send bug reports and bug fixes to
the mailing list sql.el@gnu.org so that I can merge
them into the master source. If you want to subscribe to the mailing
list, send mail to the list sql.el-request@gnu.org with
subscribe sql.el FIRSTNAME LASTNAME in the message body.
sql.el is part of Emacs and XEmacs. Type M-x
sql-help RET to read the online help. If you are looking for
the latest version or if your Emacs does not come with sql.el, then
you get it here:
If you are using GNU Emacs 19.34, you will need two additional things: the file regexp-opt.el and the custom package. See the Elisp Installation HOWTO for help on this. Once you completed installation, M-x load-library RET sql RET to load sql.el. Then, type M-x sql-help RET to read the online help.
Older sql-modes such as the one by Jim Lange can be found in the elisp archive.
This is a very quick hack that allows me to indent SQL statements. This code still needs improvements in order to reach the level of sophistication Emacs users are used to in other indentation engines. I'm very interested in comments!
As the indentation of SQL statements depends not only on the previous line but also on the current line, empty lines cannot always be indented correctly. Hitting TAB on a line containing only whitespace will remove all whitespace and make the line empty; hitting TAB on an empty line will indent the line to the same column as the previous line; hitting TAB again will indent or outdent the line one step. Hitting TAB after certain keywords will insert a tab character as well as indent the line.
Usage note: Loading this file will make all SQL mode buffers created from then on use `sql-indent' for indentation. A possible way to install sql-indent.el would be to add the following to your .emacs:
(eval-after-load "sql" (load-library "sql-indent"))
I wrote a minor mode that can be used to remote-control a SQLi buffer from a SQL buffer, ie. scrolling the SQLi buffer without leaving the SQL buffer.
This is the way to install Master mode in conjunction with SQL mode:
(autoload 'master-mode "master" "Master mode minor mode." t)
(add-hook 'sql-mode-hook
(function (lambda ()
(master-mode t)
(master-set-slave sql-buffer))))
(add-hook 'sql-set-sqli-hook
(function (lambda ()
(master-set-slave sql-buffer))))
If you work with sql-mode on a regular basis, you will want to save your command history between sessions. Set sql-input-ring-file-name in order to save your command history.
If you enter a multi-line SQL statement in an SQLi buffer, you can retrieve it as a multi-line command from the input history. Not so when you exit Emacs and restart it at a later date: As you exit SQLi mode the command history is written to a file; when you enter SQLi mode again the history is loaded one command per line. If you entered multi-line commands, each line of those commands will end up as one command in the input history of your new session.
This patch will be in Emacs 21. Until then, you can download a patched comint.el here. The patched comint.el is not needed to use sql.el. The patched comint.el uses a special string to separate commands in the history files. sql-input-ring-separator's default value is "\n--\n".
Example: The following is a fragment of a history file used by sql-mode with an unpatched comint.el from Emacs 20. The history file contains two statements.
select v.username, v.osname, v.machine, v.program
from sys.v_$session v,
scout.db_user_secure u
where v.username = u.login_name;
select person_nr, vorname, name, abteilung
from person
where login_name = 'SCHROEDA';
At the start of the next session, the input history is initialized with a multitude of useless statement fragments:
select v.username, v.osname, v.machine, v.program
from sys.v_$session v,
scout.db_user_secure u
where v.username = u.login_name;
select person_nr, vorname, name, abteilung
from person
where login_name = 'SCHROEDA';
With the comint.el patch, the above history file is saved with the special separator "\n--\n":
select v.username, v.osname, v.machine, v.program
from sys.v_$session v,
scout.db_user_secure u
where v.username = u.login_name;
--
select person_nr, vorname, name, abteilung
from person
where login_name = 'SCHROEDA';
--
At the start of the next session with a patched comint.el, the input history is initialized with the following two statements:
select v.username, v.osname, v.machine, v.program
from sys.v_$session v,
scout.db_user_secure u
where v.username = u.login_name;
select person_nr, vorname, name, abteilung from person where login_name = 'SCHROEDA';
I provide a complete copy of a patched comint.el here because I'm tired of trying to track the changes to the original in all of the Emacs 20 versions. The complete copy is easy to install, just copy it into your load-path (see the Elisp Installation HOWTO for futher installation instructions).
I often write SQL statements for application dialogs. Usually, I need a select statement, an insert statement, an update statement, and a delete statement for every dialog box. The following elisp code allows me to write a simple select statement and generate inserts and updates from it.
Note that this code still needs improvements; I'm interested in any comments. Points that need improvement: handling of missing information, better identification of start and end of SQL statements.
The rest of this page collects interesting code fragments from the sql.el mailing list.
If you would like to see text sent from SQL buffers added to the input history of SQLi buffers, use the following piece of advice in your .emacs file:
(defadvice sql-send-region (after sql-store-in-history)
"The region sent to the SQLi process is also stored in the history."
(let ((history (buffer-substring-no-properties start end)))
(save-excursion
(set-buffer sql-buffer)
(message history)
(if (and (funcall comint-input-filter history)
(or (null comint-input-ignoredups)
(not (ring-p comint-input-ring))
(ring-empty-p comint-input-ring)
(not (string-equal (ring-ref comint-input-ring 0)
history))))
(ring-insert comint-input-ring history))
(setq comint-save-input-ring-index comint-input-ring-index)
(setq comint-input-ring-index nil))))
(ad-activate 'sql-send-region)
Since sql-interactive-mode is based on comint-mode, several aspects of SQLi buffers can be controlled by setting comint variables: automatically truncate buffers to a certain size, size of the input ring (history). You can either set these variables globally in your .emacs (in which case they will affect other modes such as shell-mode), or you can set these variables on a sql-interactive-mode-hook:
(add-hook 'sql-interactive-mode-hook
(function (lambda ()
(setq comint-output-filter-functions 'comint-truncate-buffer
comint-buffer-maximum-size 5000
comint-scroll-show-maximum-output t
comint-input-ring-size 500))))
SQL*Plus has an interesting feature: Whenever you type a line of input, SQL*Plus adds a line number to the beginning of the next line. This line number is not part of the SQL command; it just allows you to refer to and edit specific lines in your SQL command. SQL*Plus acts like the standard text editor.
There are two possibilities now:
Here is an example of the line number junk:
...
2 3 4 from v$parameter p, all_tables u
*
ERROR at line 2:
ORA-00942: table or view does not exist
The following elisp function must be added to comint-preoutput-filter-functions in order to strip the line numbers junk from the output.
(defun eat-sqlplus-junk (str)
"Eat the line numbers SQL*Plus returns.
Put this on `comint-preoutput-filter-functions' if you are
running SQL*Plus.
If the line numbers are not eaten, you get stuff like this:
...
2 3 4 from v$parameter p, all_tables u
*
ERROR at line 2:
ORA-00942: table or view does not exist
The mismatch is very annoying."
(interactive "s")
(while (string-match " [ 1-9][0-9] " str)
(setq str (replace-match "" nil nil str)))
str)
Test it by evaluating the following expression:
(string= " from" (eat-sqlplus-junk " 2 3 4 from"))
Install it by adding the following expression to your .emacs; it will check wether the iSQL mode you have just started is indeed running SQL*Plus, and if it is, it will add eat-sqlplus-junk to comint-preoutput-filter-functions.
(defun install-eat-sqlplus-junk ()
"Install `comint-preoutput-filter-functions' if appropriate.
Add this function to `sql-interactive-mode-hook' in your .emacs:
\(add-hook 'sql-mode-hook 'install-eat-sqlplus-junk)"
(if (string= (car (process-command (get-buffer-process sql-buffer)))
sql-oracle-program)
(add-to-list 'comint-preoutput-filter-functions
'eat-sqlplus-junk)))
(add-hook 'sql-interactive-mode-hook 'install-eat-sqlplus-junk)
Navigation: Top, Table of Contents, Index, next: Emacs Colors, prev: Elisp Installation HOWTO, up: Emacs