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

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

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?

Solution

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