9.7. SQL Support

For you database folks out there, you can even run interactive SQL sessions through Emacs. You can navigate through your SQL command history using normal motion commands and even create complex SQL statements in any buffer and then shuttle them off to the interactive area for debugging.

9.7.1 Prerequisites

Before we get started with SQL queries, you do need to have a few things in place. Most of the SQL interaction modes require an actual client application for their particular database. For example, we use the MySQL server. We have to install the MySQL client programs (mysql, at a minimum) on any system where we want to use SQL mode. Even though the MySQL version of SQL mode is built-in, we still need access to a real client. This is true for every type of database you expect to access.

And speaking of communicating with the database, you must also have the basics of communication taken care of. You need to have network access to the server in question. You also need to have a valid username and password for connecting to that server. A good rule before jumping into SQL mode in Emacs is to make sure you can connect and interact with your database server from your machine. If it works from a terminal window or other client application, you can make it work in Emacs.

One last thing to remember: the various SQL modes in Emacs are just helpers, so you can't do anything with them that you couldn't do with your normal database client. You won't magically have access to that restricted table with everyone's salaries. Sorry. Even so, it's just more convenient to stay in Emacs when possible, so let's forge ahead.

9.7.2 Modes of Operation

You'll find two modes of operation for dealing with SQL. The interactive mode lets you communicate directly with a database server and run commands and view their output immediately. The editing mode allows you to build up (and edit) more complex commands. If you want, you can have the editing buffer send parts of itself to the interactive session for testing and verification.

9.7.2.1 Interactive mode

Start the interactive mode by typing M-x sql-mysql (or rather, your own variant of the interactive modes shown in Table 9-9).

Table 9-9. Commands for entering database-specific SQL modes

sql-db2	sql-linter	sql-postgres
sql-informix	sql-ms (Microsoft)	sql-solid
sql-ingres	sql-mysql	sql-sqlite
sql-interbase	sql-oracle	sql-sybase

You'll be prompted for things like your username and password, the database or catalog to use, and the server to contact. Remember the prerequisites, though; many modes require that you have a normal command-line client available. The mode simply supplies an intelligent layer on top of those clients.

After you get connected, just type normal SQL commands that your server understands. Most interactive clients have some type of "end-of-line" marker to let the system know when to send a completed command. In MySQL, for example, you can end statements with a semicolon (;) or the \g sequence.

Emacs keeps these commands in a history buffer for you so that you can revisit them. M-p and M-n allow you navigate to previous and next commands respectively. (C-p and C-n simply allow you to move around in the buffer as you would expect.)

9.7.2.2 Editing mode

You can also put a buffer directly into SQL mode with M-x sql-mode. This provides some assistance for motion and composition of SQL statements, but mostly it's there to let you build complex statements and then ship them to the interactive buffer for execution. Table 9-10 shows how to send various segments of the buffer to the database.

Table 9-10. SQL mode send commands

Keystroke	Command name	Action
C-c C-c	sql-send-paragraph	Send the paragraph the cursor is on. A paragraph is defined by the particular database client. For the sql-mysql process, for example, a paragraph begins with a statement like select or update and ends with a semicolon. Any number of lines can intervene.
C-c C-r	sql-send-region	Send the marked region.
C-c C-b	sql-send-buffer	Send the entire buffer.

The output of all of these send commands shows up in your interactive buffer. Nothing changes in the editing buffer so you should feel free to experiment. That's what these modes are here for!