ob-doc-sql.org 6.9 KB

Org Mode support for SQL

Template Checklist [10/12] noexport

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.


    (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))

    (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))

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

SELECT * FROM mytable WHERE id > 500

SELECT * FROM mytable WHERE id > 500

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

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

expands to this:

library(RMySQL) con <- dbConnect(MySQL(), user="user", password="pwd", dbname="dbname", host="host") q <- <> 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:

SELECT * FROM mytable WHERE id > 500