How do I define commands to run upon starting sql-mode?

Question

I use Sql-mode to connect to an Oracle database. The default settings for linesize and pagesize, and colsep are not ideal, so I'd like to have Emacs automatically run the following commands upon connecting to my databases:

SET COLSEP "|"
SET LINESIZE 9999
SET PAGESIZE 9999

How can I accomplish this?

To run the above commands, always, they can be included in the login.sql file. http://docs.oracle.com/cd/B19306_01/server.102/b14357/ch2.htm#i1133106. However this will run the commands whether SQL*Plus is run from within or without Emacs. — Shannon Severance, Nov 05 '14 at 18:58

score 3 · Answer 1 · answered Nov 05 '14 at 18:58

3

To run the commands, always, they can be included in the login.sql file. http://docs.oracle.com/cd/B19306_01/server.102/b14357/ch2.htm#i1133106. However this will run the commands whether SQL*Plus is run from within or without Emacs.

answered Nov 05 '14 at 18:58

Shannon Severance

18,025
3
46
67

phils · Accepted Answer · 2015-03-17T23:47:08.970

Adapted from an earlier answer by Tobias, which correctly pointed out the use of sql-login-hook to send the SQL via comint functions.

With Postgres I needed to send each command separately, so here I've used comint-send-string to do that (and sql.el maintainer Michael has indicated that this is indeed the preferred method).

Note also that as the same sql-login-hook is used with all database products, it's a good idea to check sql-product before sending product-specific commands. I've included the check for Oracle in this instance.

(add-hook 'sql-login-hook 'my-sql-login-hook)

(defun my-sql-login-hook ()
  "Custom SQL log-in behaviours. See `sql-login-hook'."
  (when (eq sql-product 'oracle)
    (let ((proc (get-buffer-process (current-buffer))))
      (comint-send-string proc "SET COLSEP \"|\";\n")
      (comint-send-string proc "SET LINESIZE 9999;\n")
      (comint-send-string proc "SET PAGESIZE 9999;\n"))))

Note that you should include a newline at the end of the command, to replicate typing RET when submitting a command interactively. (If you don't do this, the command(s) will still have been 'typed', but will not take effect until you manually type RET at the prompt).

If this still isn't working, take note that sql-login-hook is only run by sql-product-interactive if it recognises the interactive SQL prompt in the buffer. This prompt is matched using the regular expression sql-prompt-regexp (which is established using the per-product defaults in sql-product-alist). If the default pattern does not match your prompts, you can modify it in sql-interactive-mode-hook.

For example, the following allows Postgres prompts to include symbol-constituent characters (such as an underscore _) in the database name, as well as word-constituent characters:

(add-hook 'sql-interactive-mode-hook 'my-sql-interactive-mode-hook)

(defun my-sql-interactive-mode-hook ()
  "Custom interactive SQL mode behaviours. See `sql-interactive-mode-hook'."
  (when (eq sql-product 'postgres)
    ;; Allow symbol chars in database names in the prompt.
    ;; Default postgres pattern was: "^\\w*=[#>] " (see `sql-product-alist').
    (setq sql-prompt-regexp "^\\(?:\\sw\\|\\s_\\)*=[#>] ")))

Looks like you had the possibility to test it. Therefore I delete my answer und give your answer an upvote so that it appears first in the list. I also change "Tobias' answer" to "Tobias' former answer". Hope you are okay with this. — Tobias, Nov 06 '14 at 12:44
No problem, Tobias. I've edited again to elaborate slightly on some aspects, but your change was also fine. — phils, Nov 06 '14 at 20:30
I'm unsure if you've noticed this, but for this solution to work for me, I must add "\n" to the end of each string. — Babu, Dec 10 '14 at 22:04
Thanks Babu. I've now incorporated that information into the answer. — phils, Mar 17 '15 at 23:48

score 1 · Answer 3 · edited Nov 06 '14 at 00:39

Your LOGIN.SQL will affect many Oracle sessions including those outside of Emacs.

The "sql-login-hook" was added specifically for configuring the SQL command tool within Emacs. As "phils" pointed out, using comint-send-string' is the preferred way of sending over commands. If you are looking for a response and need to parse the response, use "sql-redirect-value".

I set LINESIZE 32767 PAGESIZE 50000 (their max values) and use C-prior and C-next to scroll right and left.

How do I define commands to run upon starting sql-mode?

3 Answers3