SQL Source Code Blocks in Org Mode

Org Mode support for SQL

Introduction

Structured Query Language, or SQL, is an ANSI and ISO standard programming language for managing data held in a relational database management system (RDBMS). SQL implementations vary in their adherence to the standard and how they attempt to extend the language, so that it is often not possible to port code across implementations without modification. Consequently, Babel supports several SQL implementations.

An alternative is SQLite.

Requirements and Setup

In order to evaluate an SQL source code block you must have a properly installed RDBMS. Org mode supports the following implementations:

dbi: the DBI shell command-line tool, dbish, used by Perl programmers;
monetdb: a column storage technology designed to work with very large databases uses an executable named mclient;
mssql: the Microsoft osql Utility that uses ODBC to connect to an RDBMS server with an executable named sqlcmd;
mysql: MySQL advertises itself as the world's most popular open source database–the executable expected by Org Babel is mysql;
postgresql: PostgreSQL advertises itself as the world's most advanced open source database–the executable expected by Org Babel is psql;
sqsh: an open-source interactive SQL client for the Adaptive Server Enterprise RDBMS;
vertica: Vertica Systems analytic database management software–the executable expected by Org Babel is vsql;
oracle: Oracle RDBMS–the executable expected by Org Babel is sqlplus; and
saphana: SAP HANA in-memory database–the executable expected by Org Babel is hdbsql.

Emacs has shipped with SQL mode since version 21.4.

You'll need to activate SQL source code blocks in .emacs.

(org-babel-do-load-languages
 'org-babel-load-languages
 '((sql . t)))

Babel Features for SQL Source Code Blocks

Header Arguments

The :colnames header argument defaults to "yes".

There are several SQL-specific header arguments:

:engine: one of "dbi", "monetdb", "mssql", "mysql", "postgresql", "postgres", "sqsh", "vertica", "oracle", "saphana";
:cmdline: extra command line arguments for the RDBMS executable;
:dbhost: the host name;
:dbport: the port number;
:dbuser: the user name;
:dbpassword: the user's password;
:dbconnection: to reference connections in sql-connection-alist;
:dbinstance: specific to SAP HANA;
:database: the database name;
:out-file: default "sql-out-";

Sessions

There is currently no support for sessions.

Examples of Use

Use SQL Source Code Blocks to Prepare Query Strings

Support for SQL is also available in several languages. Typically in these languages, an SQL query is represented by a string. One use of SQL source code blocks is for editing SQL queries for use in those languages. The advantage is that editing can be done with SQL mode, instead of as a generic string in the mode of the parent language.

The source code block, quote-blks, was posted to the Org mode mailing list by Charles Berry. The blk argument takes a string of comma separated source code block names, and the optional sep argument is a separator used in the output when there is more than one source code block.


#+name: quote-blks
#+BEGIN_SRC emacs-lisp :var blk="abc" :var sep="\"\n\""
    (save-excursion
      (replace-regexp-in-string "\"\"" ""
       (mapconcat
        (lambda (x)
          (org-babel-goto-named-src-block x)
          (format "%S" (cadr  (org-babel-get-src-block-info  t))))
        (split-string blk "," t)
        sep)
       t t))
#+END_SRC

The query is written in a named SQL source code block:


#+name: my-query
#+begin_src sql
  SELECT * FROM mytable
  WHERE id > 500
#+end_src

Then, the query can be used in a source code block for a language, such as R, with SQL support. The following code block, with its noweb reference to quote-blkes and the SQL source code block name


#+begin_src R :colnames yes :noweb yes
  library(RMySQL)
  con <- dbConnect(MySQL(), user="user", password="pwd", dbname="dbname", host="host")
  q <-
  <<quote-blks("my-query")>>
  c <- dbGetQuery(con, q)
  dbDisconnect(con)
  c
#+end_src

expands to this:

library(RMySQL)
con <- dbConnect(MySQL(), user="user", password="pwd", dbname="dbname", host="host")
q <-
nil
c <- dbGetQuery(con, q)
dbDisconnect(con)
c

Note that the noweb reference must be on its own line.

Alternatively, the query could be set up to run on its own:


#+name: my-query
#+header: :engine mysql
#+header: :dbhost host
#+header: :dbuser user
#+header: :dbpassword pwd
#+header: :database dbname
#+begin_src sql
  SELECT * FROM mytable
  WHERE id > 500
#+end_src