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?

Answer 1

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_\\)*=[#>] ")))

Answer 2

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.

Answer 3

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.