Org Mode support for SQL
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:
- the DBI shell command-line tool, dbish, used by Perl programmers;
- a column storage technology designed to work with very
large databases uses an executable named
- the Microsoft osql Utility that uses ODBC to connect to
an RDBMS server with an executable named
- MySQL advertises itself as the world's most popular open
source database–the executable expected by Org Babel is
- PostgreSQL advertises itself as the world's most
advanced open source database–the executable expected by Org Babel
- an open-source interactive SQL client for the Adaptive Server Enterprise RDBMS;
- Vertica Systems analytic database management software–the executable expected by Org Babel is
- Oracle RDBMS–the executable expected by Org Babel is
- SAP HANA in-memory database–the executable expected by Org Babel is
Emacs has shipped with SQL mode since version 21.4.
You'll need to activate SQL source code blocks in
(org-babel-do-load-languages 'org-babel-load-languages '((sql . t)))
Babel Features for SQL Source Code Blocks
:colnames header argument defaults to "yes".
There are several SQL-specific header arguments:
- one of "dbi", "monetdb", "mssql", "mysql", "postgresql", "postgres", "sqsh", "vertica", "oracle", "saphana";
- extra command line arguments for the RDBMS executable;
- the host name;
- the port number;
- the user name;
- the user's password;
- to reference connections in
- specific to SAP HANA;
- the database name;
- default "sql-out-";
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
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
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