(cache)EmacsWiki: Sql Mode

 sql.el --- specialized comint.el for SQL interpreters

This page is home of SQL mode for Emacs and XEmacs.

Overview

SQL mode is part of both Emacs and XEmacs. You can get the latest versions here.

The interactive SQL mode is based on ComintMode. The following interpreters are supported:

psql by PostgreSQL
mysql by MySQL
sqlite or sqlite3 for SQLite
solsql by Solid
SQL*Plus by Oracle
dbaccess by Informix
isql by SyBase
sql by Ingres
osql by MS SQL Server
isql by Interbase
db2 by DB2 (IBM)
inl by RELEX

Type M-x sql-help RET to read the online help. The latest version is available from Emacs’s source control:

GIT:progmodes/sql.el

The current maintainer of sql.el is MichaelMauger.

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 are maintained in CategorySql

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’

  (defun my-sql-save-history-hook ()
    (let ((lval 'sql-input-ring-file-name)
          (rval 'sql-product))
      (if (symbol-value rval)
          (let ((filename 
                 (concat "~/.emacs.d/sql/"
                         (symbol-name (symbol-value rval))
                         "-history.sql")))
            (set (make-local-variable lval) filename))
        (error
         (format "SQL history will not be saved because %s is nil"
                 (symbol-name rval))))))
 
  (add-hook 'sql-interactive-mode-hook 'my-sql-save-history-hook)

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.

  (defvar sql-last-prompt-pos 1
    "position of last prompt when added recording started")
  (make-variable-buffer-local 'sql-last-prompt-pos)
  (put 'sql-last-prompt-pos 'permanent-local t)
 
  (defun sql-add-newline-first (output)
    "Add newline to beginning of OUTPUT for `comint-preoutput-filter-functions'
    This fixes up the display of queries sent to the inferior buffer
    programatically."
    (let ((begin-of-prompt
           (or (and comint-last-prompt-overlay
                    ;; sometimes this overlay is not on prompt
                    (save-excursion
                      (goto-char (overlay-start comint-last-prompt-overlay))
                      (looking-at-p comint-prompt-regexp)
                      (point)))
               1)))
      (if (> begin-of-prompt sql-last-prompt-pos)
          (progn
            (setq sql-last-prompt-pos begin-of-prompt)
            (concat "\n" output))
        output)))
 
  (defun sqli-add-hooks ()
    "Add hooks to `sql-interactive-mode-hook'."
    (add-hook 'comint-preoutput-filter-functions
              'sql-add-newline-first))
 
  (add-hook 'sql-interactive-mode-hook 'sqli-add-hooks)

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.

  (add-hook 'sql-interactive-mode-hook
            (lambda ()
              (setq sql-prompt-regexp "^[_[:alpha:]]*[=][#>] ")
              (setq sql-prompt-cont-regexp "^[_[:alpha:]]*[-][#>] ")))

– LukeBurton

The regular expression might be better expressed as “^[_[:alnum:]]*[=][#>] “ as tables names may also contain digits.

– hdasch

In Emacs 26.1, the above workarounds for Postgres database names didn’t work for me, but there seems to be a better way to fix it:

  (sql-set-product-feature 'postgres :prompt-regexp "^[-[:alnum:]_]*=[#>] ")
  (sql-set-product-feature 'postgres :prompt-cont-regexp
                           "^[-[:alnum:]_]*[-(][#>] ")

– JonathanRRogers

Automatically upcase SQL keywords

A function to uppercase all sql keywords in the buffer using FontLockMode to determine the keywords.

  (defun upcase-sql-keywords ()
    (interactive)
    (save-excursion
      (dolist (keywords sql-mode-postgres-font-lock-keywords)
        (goto-char (point-min))
        (while (re-search-forward (car keywords) nil t)
          (goto-char (+ 1 (match-beginning 0)))
          (when (eql font-lock-keyword-face (face-at-point))
            (backward-char)
            (upcase-word 1)
            (forward-char))))))

– Douglas La Rocca

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).

– Phil S.

I wrote a minor mode, available on MELPA and MELPA Stable ( https://melpa.org/#/sqlup-mode and https://stable.melpa.org/#/sqlup-mode ), called ‘sqlup-mode’, which also upcases SQL keywords as one types.

– Aldric G.

Misaligned query results / Filtering the prompts from query output

For PostgreSQL at least, this can be an annoyance. The following answer at EmacsStackExchange provides a solution:

http://emacs.stackexchange.com/a/18403/454

– Phil S.

Alternatively https://www.reddit.com/r/emacs/comments/579lvs/work_around_newline_issue_in_sqlpostgres/ points out that you can circumvent this issue by telling psql to echo each query before displaying its results, as this additionally causes it to output the necessary newline ahead of the results. To do this, issue the following psql command:

 \set ECHO queries

(n.b. Contrary to the reddit post, it appears important (at least for some postgres versions) for ECHO to be upper-case.)

Reddit user freesteph writes “This will cause PSQL to output all queries before executing them, which means you get a better idea of what your results are, and the new line problems goes away.”

This could be set persistently in your .psqlrc file, or else using sql-login-hook:

 (add-hook 'sql-login-hook 'my-sql-login-hook)
 (defun my-sql-login-hook ()
   "Custom SQL log-in behaviours. See `sql-login-hook'."
   ;; n.b. If you are looking for a response and need to parse the
   ;; response, use `sql-redirect-value' instead of `comint-send-string'.
   (when (eq sql-product 'postgres)
     (let ((proc (get-buffer-process (current-buffer))))
       ;; Output each query before executing it. (n.b. this also avoids
       ;; the psql prompt breaking the alignment of query results.)
       (comint-send-string proc "\\set ECHO queries\n"))))

Emacs 19

If you are using GNU Emacs 19.34, you will need two additional things:

Once you completed installation, type M-x load-library RET sql RET to load sql.el. Then, type M-x sql-help RET to read the online help.

Other SQL modes

RobRiepel’s mode based on a mode by LynnSlater with support for SQL*Plus by Oracle and isql by Sybase: http://www.stanford.edu/people/riepel/SQL-mode.html
KarelSprenger’s mode based on a mode by JimLange with support for SQL*Plus by Oracle and exhaustive highlighting: http://paddington.ic.uva.nl/public/sql-modes.zip
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.
Oracle’s PL/SQL pls-mode. It used to be on the Oracle web site. Contact the author: DmitryNizhegorodov <mailto:Dmitry.Nizhegorodov@oracle.com> or get a download from his site: http://www.dmitrynizh.com Older sql-modes such as the one by JimLange can be found in the ElispArchive.
Oracle 10g Documentation: http://www.oracle-10g.de/oracle_10g_documentation
oracle-wiki: http://www.orawiki.com/ and http://www.oracle-doku.de/oracle-wiki

There is a useful tutorial style article on using sql-mode (targeted at DB2 but more broadly applicable) by RoyMathew at http://www.ibm.com/developerworks/data/library/techarticle/0206mathew/0206mathew.html Also check out the Oracle FAQ at http://www.orafaq.com/

PostgreSQL Query Analyzer (http://www.nongnu.org/pgqa/). The project is still in its early stage. Contributions and ideas are welcome.

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.

– SebastianMonia

The different command line terminals that come with MS SQL server can give you some troubles:

isql goes back to SQL Server 2000 and is removed with Server 2008
osql is the terminal for which ms-sql is configured by default, but it is marked as deprecated by Microsoft http://msdn.microsoft.com/en-us/library/ms162806.aspx
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

– RolfUnger

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.

(add-to-list 'process-coding-system-alist '("sqlcmd" . cp850-dos))

– AlexSchroeder

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.

– RolfUnger

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.

Just in case, my current config for sqlcmd is:

(defcustom sql-ms-program "sqlcmd"
  "Command to start ;osql; (replaced for sqlcmd) by Microsoft.

Starts `sql-interactive-mode' after doing some setup."
  :type 'file
  :group 'SQL)

(defcustom sql-ms-options '("-w" "30000" "-y" "79" "-s" "|" "-k")
  ;; -W is the linesize
  "List of additional options for `sql-ms-program'."
  :type '(repeat string)
  :version "22.1"
  :group 'SQL)

Again, I saw in the sql.el comments that there are ways to configure the tool, etc. without actually touching the file, but since I was already fiddling with it, that’s how I got it working.

– SebastianMonia

Connect better

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

(defun sql-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)))

– JoeBloggs

CategoryModes CategorySql

SqlMode