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.

Share:

Roy Mathew, Consultant, Anoobe, Inc.

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.



20 June 2002

© 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 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
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
screens without and with truncate lines

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
icons in explorer show emacs associations

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:

  1. Open Windows Explorer.
  2. Right click on the file icon for a .sql file.
  3. 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.

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=13897
ArticleTitle=Tips for Using Emacs with DB2
publish-date=06202002