Level: Introductory Roy Mathew, Consultant, Anoobe, Inc.
20 Jun 2002 If you like Emacs, you'll want to read how you can use your Emacs editor to interact with DB2 easily and efficiently. This article includes information about setting up syntax highlighting, starting an SQL session, executing commands, and much more.
© 2002 International Business Machines Corporation.
All rights reserved. Introduction 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> directory):
(setq auto-mode-alist
(append
(list
;; insert entries for other modes here if needed.
(cons "\\.sq$" 'sql-mode))
auto-mode-alist))
|
Entering 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 comint
[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 M-x sql-db2.
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
comp.databases.ibm-db2 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 .emacs file:
;;; 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 M-x sql-set-sqli-buffer.
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 rescue.
Use M-x toggle-truncate-lines to turn line truncation on, and
again to toggle it off.
You can scroll right or left using the 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 .emacs file.
;; 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 > gnuclientw.
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;
|
The 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 0x09; just 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;
|
Conclusion 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 roymath@yahoo.com.
About the author  | 
| | Roy Mathew is a consultant based in Miami, Florida. He enjoys working
on projects that allow the intersection of Linux, Emacs, JavaTM and Python.
Two of his past jobs have been at large database companies, but he has scrupulously
avoided learning anything more than the most rudimentary SQL during his stints there.
Now that he has discovered the Emacs way, he is much more willing to
write SQL, and will even confess to enjoying it somewhat. You can reach Roy at roymath@yahoo.com.
|
Rate this page
| 
