© 2002 International Business Machines Corporation. All rights reserved.
This article is aimed at the application developer or database programmer who writes SQL at least occasionally, and who uses Emacs. It describes a few practical tips designed to enhance your interaction with IBM DB2® without leaving the comfort and safety of the Emacs environment. Of course, this being Emacs, much of what is said here is broadly applicable to other commercial and open source database systems, too.
Figure 1 is a screenshot of a sample session in which I have set up a scratch buffer in SQL mode, and have specifed a particular database connection in the other window; I can now enter any SQL commands I want to and force their execution by using the keyboard shortcuts for sql-send-region described below.
Figure 1. A sample Emacs session with DB2 CLP
A word about Emacs
Most DBMSs have long had a command line interface, and DB2 with its CLP is no exception. An interactive GUI such as the Control Center is well suited to the task of browsing and managing the database; however, I do find that there are many situations where it is a lot easier to manage or manipulate your data by writing SQL code, and executing that code through the CLP. Emacs is particularly useful when doing this sort of thing.
What I appreciate the most about Emacs is that once you have taken the effort to make something work in it, any output produced as a result of running the application is completely selectable and can be pasted into another Emacs buffer or elsewhere in the windowing environment. How many times have you stared at text in some particular application window, and unable to select it, have had to resort to remembering it or writing it down on a piece of paper to enter it into another application?
After you have taken care of the setup and initial configuration issues, Emacs tends to be a rock-solid platform upon which to do your work. You do not need to customize it extensively, but as you read the online help and other documentation (Emacs does pride itself on being a completely self-documented editor), you may find yourself writing macros to make things easier. Then you may find yourself writing little snippets of lisp for further customization, and then eventually, you may end up writing full-blown lisp functions.
This article focuses on a Windows® 32-bit environment, since the reality of most workplaces today is that the dominant desktop platform is typically a Win32 machine. Setting up SQL mode on UNIX® machines tends to be slightly easier,
Setting up SQL mode for Emacs
If you use the GNU Emacs or XEmacs, you will find that SQL mode comes
standard with the distribution. By default, Emacs is configured such that
if you load a file named with a .sql> extension, such as
myfuncs.sql, it will automatically put its buffer in SQL mode. Typing
C-h m will give you (as in all Emacs modes) a
very specific listing of all applicable commands and keystroke bindings.
This is a very handy feature; I find that taking the time to study mode
specific commands for modes that you often use will save you a lot of
typing over time.
If you load a file that is not named with a
.sql extension, you can manually put it into
SQL mode by typing
M-x sql-mode in the
minibuffer. To force files with non-standard extensions (for example:
a.sq, b.sq) to always be loaded in SQL mode, add the following command to
your Emacs initialization file (typically .emacs in your HOME>
(setq auto-mode-alist (append (list ;; insert entries for other modes here if needed. (cons "\\.sq$" 'sql-mode)) auto-mode-alist))
C-h v auto-mode-alist is very
instructive; it shows you the current list of mode-to-filename extension
associations that are already defined. A quick glance at this showed me
more languages than I knew existed. Here is an excerpt from running this
command that shows a couple of entries. Note that this says that any file
with a .sql extension is to be loaded and its buffer set to sql-mode;
likewise, files with .tar extensions are loaded in tar-mode, etc.
... ("\\.sql\\'" . sql-mode) ("\\.tar\\'" . tar-mode) ("\\.tcl\\'" . tcl-mode) ...
Setting up syntax highlighting
Color and highlighting truly adds value to the presentation of complex information. You will need to enable font-lock mode separately to see your SQL code in color. The means to do this has changed between versions of Emacs; in Emacs-19 and earlier, you had to use the hilit-19 package. This won't be covered in this article. Emacs-20 and above use the font-lock package, which we will address here.
Here is some lisp code to get started with color under font-lock.
(add-hook 'sql-mode-hook 'font-lock-mode)
You may however wish to customize your SQL mode somewhat, and I have shown a snippet of code below that allows you to do that. This conforms to the general mechanism for enabling color and font-lock for all modes in Emacs.
(require 'font-lock) (defun my-turn-on-font-lock () ;; a few enhancements to the presentation. ;; show bold fonts with a blue background (set-face-background 'bold "lightblue") ;; quoted string presentation (set-face-foreground 'font-lock-string-face "MediumSlateBlue") ;; function name color (set-face-foreground 'font-lock-function-name-face "SeaGreen") ;; function names in bold (set-face-bold-p 'font-lock-function-name-face t) ;; I like having the currently selected region show up in grey. ;; region color in transient mark mode (set-face-background 'region '"LightGrey") (transient-mark-mode 1) ;; this is critical (font-lock-mode 1)) (defun my-window-setup-hook () ;; the cursor will really stand out! (set-cursor-color "red") (my-turn-on-font-lock)) ;;; you may repeat the add-hook for other modes as well (add-hook 'sql-mode-hook 'my-window-setup-hook)
As a footnote here, I must mention that you can run
M-x font-lock-fontify-buffer to force a buffer
to be "font-ified". However, you shouldn't need to do this after you've
set things up as shown above.
Figure 2 is a screenshot of two buffers: the bottom buffer is in SQL mode with color syntax highlighting turned on; the other shows the exact same information without color. Adding color can help readability. Of course the example presented here is not really valid SQL for the DB2 platform, but that's besides the point.
Figure 2. Font lock mode in action
Starting a SQL session from within Emacs
The way SQL mode in Emacs works is as follows: You launch a process that
communicates with the database via the command line (CLP in the case of
DB2). This process has its own buffer within the running Emacs instance.
Any SQL commands that you issue are really sent as input to this buffer
(via the versatile
[command-interpreter-in-a-buffer package] mode or some derivative of it).
This mode acts as a base package and provides you with history and job
control, just as you could in a shell mode buffer, for instance.
On most UNIX platforms, SQL mode works out of the box; all you have to do
is simply issue the appropriate SQL command to launch the aforementioned
SQL process buffer. For DB2, simply say
With Emacs on Windows platforms, there is an issue with running
M-x sql-db2. The following message is returned:
DB21061E Command line environment not initialized. Process SQL exited abnormally with code 8
I posted a somewhat manual fix for this on the
newsgroup, but received a response from Ian D. Bjorhovde that was even
better, and could be fully incorporated in the initialization setup for
Emacs. Add the following to your
;;; Change the path to db2cmd to reflect the correct ;;; location for your machine. (setq sql-db2-program "c:/sqllib/bin/db2cmd.exe") ;;; The interesting options here is the "-t" option ;;; passed to "db2". This is extremely handy - it ;;; means that ';' (semicolon) ; is treated as the command ;;; line terminator. The default is to treat the end-of-line ;;; as a SQL statement terminator. ;;; You may look up the command reference online for an ;;; explanation of the rest. (setq sql-db2-options '("-c" "-i" "-w" "db2" "-tv"))
Click here to see the full thread of that discussion.
After you have set up your environment, you should be able to interact seamlessly with the DB2 CLP on Win32 platforms.
Maintaining multiple SQL sessions
It is typical of most environments today, that you will need to interact with more than one database simultaneously. You may have a developmet database, a testing database and a production database, for instance.
Since a SQL mode buffer is associated with a single database, and all command executed in that buffer are directed to that database, you may wonder how you would target different databases. The answer to this lies in understanding that any buffer forced into SQL mode, will by default, associate itself with the SQL buffer named in the variable sql-buffer. This is typically a buffer named "*SQL*".
To enable multiple connections and multiple buffers, use the following sequence:
M-x sql-db2 ;; connect to a database (say "DB2x"); ;; This forces a buffer called "*SQL*" to be created. ;; visit "*SQL*" and rename it M-x rename-buffer ;; rename to "*SQL DB2x" ;; Now visit a buffer, say "test1.sql" M-x sql-mode ;; test1.sql is now associated with buffer "*SQL DB2x" M-x sql-db2 ;; connect to a database (say "DB2y") ;; visit newly created buffer "*SQL*" and rename it M-x rename-buffer ;; rename to "*SQL DB2y" ;; Now visit a buffer, say "test2.sql" M-x sql-mode ;; test2.sql is now associated with buffer "*SQL DB2y"
You can also manually set or alter the associated buffer for any buffer in
SQL mode by visiting that buffer, and using
Viewing long rows of data
You may have noticed that the default setting in Emacs when displaying
long lines of text is to wrap the text and make all of it visible at once.
When viewing really wide rows of result data from an SQL query, you most
likely want to avoid wraparound, so that your data is presented in neat
tabular fashion, and you can see the vertical alignment between the
columns. This is where the
toggle-truncate-lines command comes to the
M-x toggle-truncate-lines to turn line
truncation on, and again to toggle it off. You can scroll right or left
C-x < and
C-x > (scroll-right and scroll-left)
commands. Emacs-21 handles
C-x e and
C-x a better than Emacs-20. Emacs-21 takes you
to the actual end and beginning of the line, whereas Emacs-20 seems to be
stuck on the range you are viewing.
This command is useful not only when viewing SQL output, but for viewing any buffers with long lines of text. I use it often enough that I find it useful to have an alias defined thus:
(defalias 'tt 'toggle-truncate-lines)
Figure 3 shows two buffers, each with the same information from an SQL query, with and without the line truncation in effect.
Figure 3. Line truncation makes it easier to see results of an SQL query
Executing SQL code quickly and easily
As I mentioned earlier, the key to working in SQL mode is to have a buffer
in SQL mode after you have established a connection to the database
through the use of the
sql-db2 command. To the
Emacs enthusiast, none of the IDEs out there can quite match the ease with
which you can visit files, do edits and save text in Emacs... adding SQL
execution capabilities is gravy. After you have a buffer in SQL mode, I
find that the following commands are most useful:
C-c C-c: sql-send-paragraph: This command allows you to send just the current paragraph to the db2 clp interpreter; however you need to consider that your notion of a paragraph may be different than SQL mode's.
C-c C-r: sql-send-region: After you have selected a region with your mouse or with keystrokes, this command allows you to send the currently selected region to the DB2 CLP interpreter. This is useful for prototyping or for doing quick trials, or when you want to execute an existing piece of text you may have squirreled away from a while ago.
C-c C-b: sql-send-buffer: This is what you do when you want to execute the contents of the entire buffer.
Making .sql files show up in Emacs when clicked
It is handy to be able to click on a file and have it show up in an
already running Emacs window. The way to do this is through a program for
Windows called gnuserv. Figure 4 is a
screenshot showing the icons and their changed appearance after Windows
has been told they are "associated" with Emacs. All of the files in the
directory shown, (except
x.del) will, upon
being double-clicked, open up in an already running instance of Emacs.
Figure 4. Associating .sql with Emacs
Here is a sample setup that you can use in your
;; setting the exec-path variable is so you can avoid cluttering your ;; PATH env variable since really, only Emacs needs to know of it. (setq exec-path (append exec-path '("c:\\Roy\\emacs-21.1\\gnuserv\\Release"))) ;; explicitly load the feature; else you can put the gnuserv file in your load-path ;; and do a (require 'gnuserv) instead. (load-file "c:/emacs-21.1/gnuserv/gnuserv.el") ;; (require 'gnuserv) ;; Note: you can kill an already running gnuserv subprocesses by running ;;gnuserv-start ;; with an argument, as in C-u M-x gnuserv-start (gnuserv-start) ;; use this command so that the main Emacs frame is the one the opened file ;;will appear in. (setq gnuserv-frame(selected-frame))
You must then tell Windows that you'd like to open all
.sql files with Emacs; you do this as follows:
- Open Windows Explorer.
- Right click on the file icon for a .sql file.
- Choose Open With > Choose Program >
Read the gnuserv documentation for details on the differences between gnuclientw and gnuclient.
As an alternative, you can use the assoc and ftype programs in a .bat file. I believe that the settings are stored in the registry, so you only have to do this once!
@REM you may repeat this for other filename extensions as well... assoc .sql=sql_file ftype sql_file=c:/emacs-21.1/gnuserv/gnuclient.exe "%1"
Exporting data from tables to files
In the course of working with relational data, sooner or later, you will encounter the need to import data into a table from a file, or save data from a table into a file on the file system. This may not seem directly related to the use of Emacs in a database development environment; the rationale for doing so is that it is sometimes easier to read the data into Emacs if present in a file, use Emacs expressions to make modifications, and then load it back into the table.
To export a table to a file:
export to "c:/temp/table1.txt" of del modified by coldel0x09 select DISTINCT * from TABLE1;
del keyword stand for "delimited ascii", in
which the delimiter is the tab character specified in the
modified by clause. You can, of course, use any
other value in place of hexadecimal
be absolutely sure that your choice of delimiter is distinct from all
characters in the data your are exporting.
To go in the other direction:
-- Use delete if you are sure you want to clean out the table. -- delete from TABLE1; import from "c:/table1.txt" of del modified by coldel0x09 insert into TABLE1;
Mastering the online help and reading some of the source lisp code for SQL mode are great ways to enhance your facility with this environment. In times when you need to make a quick database query or modfication, you will be sure to appreciate that you can, with almost surgical precision, get to the database, do exactly what you need, and get out.
Once you have a working knowledge of the tips presented here, you might wonder if there is more, and there most certainly is. Perhaps the best place to look for tips on Emacs is the GNU Emacs website at http://www.gnu.org/software/emacs/emacs.html. The Emacs Wiki at http://www.emacswiki.org is a wonderful resource as well. A wiki, if you didn't already know this, is more like a true bulletin board that allows visitors to add or modify web pages as they vist and read.
If you have any questions or comments about this article, please feel free to drop me a line at email@example.com.