SQL Source Code Blocks in Org Mode

Table of Contents

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, Org mode 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;
msosql
the Microsoft osql Utility that uses ODBC to connect to an RDBMS server;
mysql
MySQL advertises itself as the world's most popular open source database–the executable expected by Org mode is mysql;
postgresql
PostgreSQL advertises itself as the world's most advanced open source database–the executable expected by Org mode is psql.

Emacs has shipped with SQL mode since version 21.4.

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

;; active Babel languages
(org-babel-do-load-languages
 'org-babel-load-languages
 '((sql . t)))
;; add additional languages with '((language . t)))

Org Mode 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", "msosql", "mysql", "postgresql";
:cmdline
extra command line arguments for the RDBMS executable;
:dbhost
the host name;
:dbuser
the user name;
:dbpassword
the user's password;
:database
the database name;

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 <- 
"SELECT * FROM mytable
WHERE id > 500"
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

Documentation from the http://orgmode.org/worg/ website (either in its HTML format or in its Org format) is licensed under the GNU Free Documentation License version 1.3 or later. The code examples and css stylesheets are licensed under the GNU General Public License v3 or later.