Open Database Connectivity (ODBC) is a low-level protocol for establishing connections with the majority of databases and datasources ( for more details, refer to http://en.wikipedia.org/wiki/Open_Database_Connectivity).

Julia has an ODBC package that enables Julia scripts to talk to ODBC data sources. Install the package through Pkg.add("ODBC"), and at the start of the code, run it using ODBC.

The package can work with a system Data Source Name (DSN) that contains all the concrete connection information, such as server name, database, credentials, and so on. Every operating system has its own utility to make DSNs. In Windows, the ODBC administrator can be reached by navigating to Control Panel | Administrative Tools | ODBC Data Sources; on other systems, you have IODBC or Unix ODBC.

For example, suppose we have a database called pubs running in a SQL Server or a MySQL Server, and the connection is described with a DSN pubsODBC. (Included in the code download is a script, instpubs.sql, to install the pubs database with only the titles table in a SQL Server; the script can be easily adapted for MySQL.)

Now, I can connect to this database as follows:

// code in Chapter 8odbc.jl 
using ODBC 
ODBC.DSN("pubsODBC",<user>,<password>)

This returns an output as follows:

Connection Data Source: pubsODBC
pubsODBC Connection Number: 1
    Contains resultset? No

You can also store this DSN object in a dsn variable, as follows:

dsn = ODBC.DSN("pubsODBC",<user>,<password>) 

This way, you are able to close the connection when necessary through ODBC.disconnect!(dsn) to save database resources, or handle multiple connections.

To launch a query on the titles table, you only need to use the query function, as follows:

results = ODBC.query(dsn, "select * from titles")

The result is of the DataFrame type and the dimensions are 18 x 10, because the table contains 18 rows and 10 columns, for example; here are some of the columns:

If you haven't stored the query results in a variable, you can always retrieve them from conn.resultset, where conn is an existing connection. Now we have all the functionalities of DataFrames at our disposal to work with this data. Launching data-manipulation queries works in the same way:

updsql = "update titles set type = 'psychology' where 
   title_id='BU1032'"
stmt = ODBC.prepare(dsn, updsql)
ODBC.execute!(stmt)

In order to see which ODBC drivers are installed on the system, ask for ODBC.listdrivers(). The already available DSNs are listed with ODBC.listdsns().

Julia already has database drivers for Memcache, FoundationDB, MongoDB, Redis, MySQL, SQLite, and PostgreSQL (for more information, refer to https://github.com/JuliaDatabases).