An introduction to emacs SQLi mode

Introduction

Early in my career, when I was first introduced to Unix, my mentor at the time recommended that I use a text editor called emacs, but also learn how to use vi as well. I personally like both editors, but I prefer emacs for coding. Over the decades, I’ve continued to use emacs; I use it for nearly everything work-related that doesn’t fit in a web browser these days. I use it for email (GNUS), writing presentations and papers (org-mode), and coding every possible language and system imaginable(perl-mode, SQLi mode, gnuplot-mode, various lisp-modes, and more). I’ve found that I’m usually much more productive using emacs than using a full-featured integrated development environment like Eclipse, SQL*Developer, or Toad except in some very few special cases. Over the years, there have been many changes and improvements to the original SQL mode and related pl-sql mode; these have all been superseeded with a new mode called SQLi mode. SQLi mode has support for most databases today, including Oracle and mySQL among many others.

In this quick tutorial, I’ll show how to set up emacs version 24.2.1 to work with an Oracle database, and what you can do with this setup.

What does SQLi mode do?

SQLi mode makes it easier to interact with sql*plus.

Developing and administering Oracle ends up going something like this:

  1. Write a script or SQL statement
  2. Start a sqlplus session in another window
  3. Test your script in the new window by invoking @scriptname
  4. Look at the output to see what went wrong
  5. Fix script
  6. Switch back to your editing buffer, and repeat from step 3 above.

It’s not very fun, because of the constant switching between the terminal in which you’re running sqlplus, and the terminal you’re editing in. It’s also not fun having to retype the SQL file name over & over again. SQLi mode automates this so that you can send either the latest statement, or a highlighted region, or an entire buffer to sqlplus with just a few quick keystrokes. It gives you a very useful ability to re-execute previous bits of SQL you’ve used. Finally, it highlights and color-codes your SQL and pl/sql code which makes it very nice to code in – for example, it becomes very easy to see comments, as they show up in red.

SQLi mode comes automatically with Emacs v24, so there’s nothing extra that you need to install.

Finding more in-depth help about SQLi mode

Just enter M-x sql-help[return] to open a buffer with documentation about SQLi mode.

Configuring sql-mode to access an Oracle database

The easiest way to set it up is to execute configure-group for the SQL group; [escape] x customize-group [return] SQL [return], or M-x customize-group [return] SQL [return]. A buffer will be created that has some options you’ll probably want to change as follows:

Option Change/description
Sql Database change to your target database name
SQL Oracle Program Full path to ‘sqlplus’
SQL Password oracle account password
SQL Product set to ‘Oracle’
SQL Server database host (or localhost if using port forwarding)
SQL User oracle username

Note that I’ve been able to get this working even on Windows 7, under emacs running under cygwin/X, with sqlplus installed under windows. Under that environment, the cygwin path to sqlplus will need to be configured for the SQL Oracle Program; for me it is /cygdrive/c/product/11.1.0/client_2/SQLPLUS.EXE.

If you do not wish to be prompted for login credentials, you can optionally set the (defalias 'sql-get-login 'ignore) in your .emacs initialization file.

If you do not have global-font-lock-mode set, you may wish to do that so that whenever you visit some SQL, it will automatically show up displayed in color.

Starting it up

First, start the *SQL* buffer, which actually runs sqlplus. Just type M-x sql-oracle[return] – unless you have set sql-get-login, it will prompt for the username,password, and database; if you have set the variables above they will be the defaults.

Normally, this *SQL* buffer is not directly manipulated; instead, start up a new buffer/file. M-x find-file[return] /home/jstanley/test.sql You’ll notice that it will automatically be put into SQL[Oracle] mode.

Then, start typing some SQL that you’d like to work with;

select object_name from user_objects;

You can then use C-c C-b to send this to your sqlplus session!

You can use the following key bindings to interact with the your sql process:

Key binding What it does
C-c C-b sql-send-buffer
C-c C-c sql-send-paragraph
C-c C-r sql-send-region
C-c C-s sql-send-string
C-M-q prog-indent-sexp
C-c C-l a sql-list-all
C-c C-l t sql-list-table
C-c TAB sql-product-interactive

If you get an error that says: No sql process started, then in your test.sql buffer, enter: M-x sql-set-sqli-buffer RET *SQL* RET

The sql-list-all command basically lists all of the objects in the USER_OBJECTS view.

The sql-list-table command lists only the tables owned by the current user.

Conclusion

You can use emacs SQLi mode to really make development much easier than doing it with vi and sqlplus.

For a more in-depth tutorial (for IBM DB2, but very similar), check out http://www.ibm.com/developerworks/data/library/techarticle/0206mathew/0206mathew.html

Author: Jay Stanley <jstanley@dbspecialists.com>

Date: 2012-10-26 Fri

HTML generated by org-mode 6.34c in emacs 24

Rack Blogger is our catchall blog byline, subbed in when a Racker author moves on, or used when we publish a guest post. You can email Rack Blogger at blog@rackspace.com.

LEAVE A REPLY

Please enter your comment!
Please enter your name here