sql.el --- specialized comint.el for SQL interpreters
This page is home of SQL mode for Emacs and XEmacs.
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:
Type M-x sql-help RET to read the online help. The latest version is available from Emacs’s source control:
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.
After the experience of writing a sqlcmd replacement for sql-ms (see below), I wrote a second Python utility that aims to be a single ODBC client that you can use for any database, and that can be easily configured for SQLi mode. You can find it at https://github.com/sebasmonia/datum, it includes code and instructions to set it up in Emacs. Goal is for this tool to work on any platform.
Related pages are maintained in CategorySql
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:]]*[-][#>] ")))
The regular expression might be better expressed as “^[_[:alnum:]]*[=][#>] “ as tables names may also contain digits.
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:]_]*[-(][#>] ")
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.
– 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
(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"))))
If you can live with not typing on the same line as the prompt, you can just add a newline to the end of your psql prompt. Put something like this in your
\set PROMPT1 '// %n@%M %/\n'
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.
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/
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.
The different command line terminals that come with MS SQL server can give you some troubles:
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
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
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
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
"^[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))
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
(add-to-list sql-ms-options "-q") (add-to-list sql-ms-options "select '1>'")
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.
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)))
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.