Starting with version 21.4-a, sql.el is included in the regular sources of Emacs. The CVS version linked above depends on functions that haven’t made it out of CVS, so don’t use the latest version if you’re not running a bleeding edge Emacs from CVS.

If those with pre-22.1 versions of Emacs can identify changes needed to run on older releases, send them to MichaelMauger for consideration. However, the emphasis is on improving future versions of Emacs.

Related Pages

Miscellaneous Settings

If you don’t like window splittings related to the SQL buffer, try the following, per Force Same Window.

(add-to-list 'same-window-buffer-names "*SQL*")

If you only work on one database, you might prefer not to confirm the default user, password, database, etc. settings. In that case, load sql.el, and disable sql-get-login in your ~/.emacs:

(require 'sql)
(defalias 'sql-get-login 'ignore)

To interact with the interpreter from a window already in SQL mode, execute the following:

M-x sql-set-sqli-buffer RET *SQL* RET

Want to save your history between sessions? Consider adding this hook to your .emacs. It saves the history in a separate file for each SQL “product”. You’ll need to run `M-x make-directory RET ~/.emacs.d/sql/ RET’

When using sql-send-region to execute a query in a SQLi buffer, the table formatting is off because the column names are printed on the same row as the the prompt. By adding a newline before the comint output we can make sure everything lines up nice. This will add a preceding newline to every comint output, even queries run at the prompt - though the extra line isn’t too noticeable.

PostgreSQL databases with underscores in their names trip up the prompt specified in sql.el. I work around this with the following. Warning, this sets the prompt globally, which is fine by me since I only ever use Postgres.

And inspired by the above, I’ve written ‘sql-upcase-mode’ (see SqlUpcase) which upcases SQL keywords automatically as text is inserted into the buffer.

(I was originally using AbbrevMode for as-you-type upcasing, but it entailed maintaining a separate list of keywords, and a lot of hacking around the limitations of abbrev to prevent it from being overly eager. Using the pre-existing font-lock keywords is a great improvement.)

‘sql-upcase-mode’ processes all inserted text; so it not only upcases keywords as you type, but it will upcase all keywords in any text you yank into the buffer (so to reformat existing SQL, you can simply kill it and yank it into a SqlMode buffer – although commands to upcase a SqlMode buffer or region are also provided).

Peter D. Pezaris’ huge mode with extensive XEmacs support: http://www.dwwc.com/sql-mode/ (doesn’t work, use this: http://www.pezaris.com/sql-mode/) [RR] Warning - there have not been any updates since v0.922 and this doesn’t work on the recent XEmacs (uses obsolete functions). I can only imagine that it is not under active development.

MS SQL server with ms-sql

If you’re trying to use MS SQL Server in sql-mode, and the Microsoft command-line query processors osql and isql are not available to you, try jisql, a free, Java-based “workalike” provided by the clever (and generous) folks at Xigole Systems, Inc. http://www.xigole.com/software/jisql.jsp. Apache license.

Another option for users lacking access to osql/isql is sqsh, another Java-based command-line SQL client. The primary advantage to sqsh is that it is in the Debian/Ubuntu repositories (I can’t speak for Fedora, etc.) making it easier to install. When using sqsh to connect to SQL Server, use M-x sql-sybase, not sql-ms. Otherwise, Emacs won’t pass the right command-line parameters and it won’t connect.

I wrote a Python utility that is a drop in replacement for sqlcmd/osql as far as SQLi is concerned. You can find it in https://github.com/sebasmonia/sqlcmdline. It uses ODBC so in theory should work in platforms other than Windows. customize-group SQL, change Sql Ms Program to “/path/to/sqlcmdline” (or compiled/batch if under Windows), and for Emacs up to version 25 you still need to fix the prompt-regex as mentioned below.

sqlcmd is the one that Microsoft recommends to use as it is likely to be available for the next 5 years

I use this command line tools on a German MS Windows 7 (and GNU Emacs 24.3). Compared to what I am used from sql-oracle or sql-mysql (on Linux) working with sql-ms is a pain. The first 5 or 6 times I connected to a local MS SQL Express and a SQL-Server on a remote machine my sql-mode buffer was empty. I thought it is stuck somewhere and killed the sqlcmd.exe process with the Task-Manager and started another connect attempt. Unfortunately there is also no banner as it is displayed in sql-oracle with sqlplus.

At some point I realized that the comint-buffer is waiting for my input, it just doesn’t show a prompt. Both osql and sqlcmd have 1> and it seems like this is not recognized as prompt by the underlying comint-mode. Thankfully sqlcmd supports colon commands like :help that are not send to the server but directly respond with output. This brought me on the right track. So my next step was SELECT 10+20 followed by Enter in order to get into the next line to add the GO for sending the query to the server. But stuck again, the wait cursor appears and won’t let me type the GO.

The usual brute force method to kill the sqlcmd process brought me out and after some time I also realized that a simple C-g will also help with the advantage that the connection to the server is kept alive. It seems like the lisp code is in some infinite loop waiting for a prompt or feedback that never comes. But as it is in lisp code it can be interrupted with C-g.

So here is what I had to do:

SELECT 10+20 AS thirty (Ctrl-j brings you in the next line)
GO (Enter sends this to the server)
thirty
-----------
30
(1 Zeilen betroffen)

These works for SELECTs and UPDATEs, but it won’t work for ALTER TABLE or CREATE VIEW. For these commands the wait cursor raises its ugly head again. Thankfully C-g can help us here, as well. So the sequence your fingers have to get trained for is: C-j .. GO .. Enter .. C-g. That’s really difficult if you have used semicolon and Enter with all the other database clients, so far.

And there is another catch: sqlcmd supports special characters like Umlauts, but it doesn’t use Latin1 or windows-1252. Instead it is based on the old DOS code pages. So you’d better change the input mode for the comint process to cp850 or cp437: C-x RET p followed by cp850.

The first thought that I had when working with sql-ms was: “How could this have ever been released at this state where nothing is working.” Which is a bit strange because I haven’t found any other voices on the internet complaining about the bad quality of the implementation. Maybe it is only related to my German environment which forces sql-ms to stumble? Any attempts to change the language of sqlcmd were without success. Only in an ideal world Microsoft tools would respect environment settings like set lang=C

I’m not sure that “recognizing the prompt” is the problem. If you look at ‘sql-product-alist’ you’ll see that the prompt for ms is "^[0-9]*>" which looks OK. Perhaps it’s the EOL convention since you’re saying that the process encoding is borked. Here is how to force Emacs to use code page 850 for every sqlcmd process and to force DOS line endings.

Better late then never: This is a general Problem with the cmdproxy.exe that is used in the comint-mode. The sqlcmd program Looks for some special characteristic of the usual Windows command processor cmd.exe to decide if it is running in interactive mode (shows a prompt) or if it is running in Batch mode (no prompt shown). If SQLCMD is started from cmdproxy it thinks it is running in Batch mode and does not Display any prompt. Something analogous is happening with the PostgreSQL terminal psql’.

So there is no solution for this Problem. Even a change in the source code of cmdproxy might not help. I think I saw a work around for the PostgreSQL case, where someone patched the source of the psql tool. But I doubt that there is such a someone to do something similar for a Microsoft product.

But there is a rather crude workaround: add “-q” and “select ‘1>’ to sql-ms-options and you can get it to work. This tells sqlcmd to execute a query without exit. And so sqlcmd ends up in interactive mode

There’s a way to fix the interaction so that you can use Enter instead of C-j for a new line. For MSSQL, there’s no prompt continuation regex in sql.el, and that causes errors when parsing the output of the tool. You can fix it by adding the same regex than for regular prompts, like this:

:prompt-regexp"^[0-9]*>";existing line:prompt-cont-regexp"^[0-9]*>";new line

There’s probably a way to configure this, it’s just that I got to sql.el by trying a different approach: I wrote my own command line tool for MSSQL interaction (in Python, then compiled). I was getting the prompt, which I hardcoded to “1>”, but still query results were not returned as expected if the output was big enough. Turns out that sql-interactive-remove-continuation-prompt was breaking.

Connect better

I find this little convenience function useful if you want to connect to several different types of databases.

(defunsql-connect-better (name)
"Like `sql-connect' but fixed so that `sql-product' and buffer name are set automatically.
NAME is the name of the connection in `sql-connection-alist'."
(interactive (list (sql-read-connection "Connection: " nil '(nil))))
(let* ((sql-product (or (cadadr (assoc 'sql-product (cdr (assoc name sql-connection-alist))))
sql-product)))
(sql-connect name name)))

Documentation

I notice that sql mode doesn’t seem to have any Info documentation in the Emacs 26 distribution. Is there some that I’m missing? Can some of this stuff be convered to Info? What I hoped to find was a buffer variable that tells an SQL file what database to use, so it could be set with Local Variables in the sql file. Then M-x sql-sqlite (or whatever) would choose that database by default.

This work is licensed to you under version 2 of the
GNUGeneral Public License.
Alternatively, you may choose to receive this work under any other
license that grants the right to use, copy, modify, and/or distribute
the work, as long as that license imposes the restriction that
derivative works have to grant the same rights and impose the same
restriction. For example, you may choose to receive this work under
the
GNUFree Documentation License, the
CreativeCommonsShareAlike
License, the XEmacs manual license, or
similar licenses.