Database Connections
This feature is not supported on the Wolfram Cloud.

The first step in using a database is making a connection. This part of the tutorial discusses how to do this.

If you are just starting to use DatabaseLink, you might want to look at some of the basic examples in this tutorial. Then, to learn if DatabaseLink comes with a driver for your database, you might want to study "Database Connections: JDBC Connections", which contains further information about adding new drivers. Finally, if you want to give your connection a name, you might want to study "Database Connections: Named Connections".

Setting Up a Database

Many users of DatabaseLink will have an existing database they wish to connect to and use. If you have one, you should be able to read this documentation and modify it to connect to your own database. If you do not already have a database, you can use HSQLDB, SQLite, H2, or Derby (all included in DatabaseLink). If you want to set up a different type of database, you will need to refer to the specific information for that database. Once you have set up your database, you can continue to use this tutorial to learn how to connect to it.

Establishing a Connection

If you find that the examples in this section do not work as shown, you may need to install or restore the example database with the package, as described in "Using the Example Databases".

OpenSQLConnection[name]connect to a named SQL data source
OpenSQLConnection[JDBC[driver,url]]connect to the data source URL using JDBC
OpenSQLConnection[args,opts]set options for the connection
OpenSQLConnection[]use the Connection Tool to open a connection
CloseSQLConnection[conn]close a connection
SQLConnections[]list SQL connections
SQLConnectionInformation[conn]verbose information about an SQL connection
SQLConnectionOpenQ[conn]test whether a connection is open, without contacting server
SQLConnectionUsableQ[conn]test whether a connection is usable, contacting server

Functions for working with database connections.

This loads DatabaseLink:

In[1]:=
Click for copyable input

Now you can connect to a named database, called demo, that is provided by DatabaseLink for documentation. "Database Resources: Connection Configuration" shows how to set up new named connections. You can learn about existing named connections in "Database Connections: Named Connections".

OpenSQLConnection returns a Wolfram Language expression that refers to the connection. It can be used to make queries on the database:

In[1]:=
Click for copyable input
Out[1]=

SQLConnections returns a list of all the open connections:

In[2]:=
Click for copyable input
Out[2]=

In the following example, the tables that are found in the database are returned:

In[3]:=
Click for copyable input
Out[3]=

When you have finished with a connection, you can close it with CloseSQLConnection:

In[2]:=
Click for copyable input
In[5]:=
Click for copyable input
Out[5]=

There are a number of options that can be given to OpenSQLConnection.

option name
default value
"Name"""name of the connection
"Description"""textual description of the connection
"Username"""username to use for connecting
"Password"""password to use for connecting
"Catalog"Automaticlocation of the database catalog
"Properties"{}key-value pairs, as rules, passed to JDBC driver
"ReadOnly"Automaticset the connection to be read only
"RelativePath"Falseindicates whether or not database location is specified relative to configuration (for file-based databases)
"Timeout"$SQLTimeouttimeout setting for operations, in seconds
"TransactionIsolationLevel"Automaticset transaction isolation for the connection
"UseConnectionPool"Automaticopen the connection from a managed pool

OpenSQLConnection options.

These options can be used when opening a connection. For instance, the following allows you to use a different username and password for the connection:

In[3]:=
Click for copyable input
Out[3]=
In[8]:=
Click for copyable input

If you enter as a password, a dialog box opens that will prompt you for the password. This helps keep the password more secure.

In[4]:=
Click for copyable input
Out[4]=

Once a connection has been created, certain options can be changed using SetOptions.

"Catalog"location of the database catalog
"ReadOnly"whether to open read only
"TransactionIsolationLevel"whether to add transaction isolation

Connection options that can be changed after the connection is created.

This changes the connection to only allow read access to the database.

In[5]:=
Click for copyable input
Out[5]=
In[6]:=
Click for copyable input

More information on the option is found in "Transactions: Transaction Isolation".

Connection Information

Connection status can be checked using the functions SQLConnectionOpenQ and SQLConnectionUsableQ.

In[7]:=
Click for copyable input

This opens a connection to one of the sample databases:

In[8]:=
Click for copyable input
Out[8]=

SQLConnectionOpenQ determines whether or not the connection is still valid on the client side, but does not communicate with the database server:

In[11]:=
Click for copyable input
Out[11]=

SQLConnectionUsableQ communicates with the database server to determine whether or not queries may be issued on the connection:

In[12]:=
Click for copyable input
Out[12]=
In[13]:=
Click for copyable input
Out[14]=

Detailed information about a connection can be obtained from SQLConnectionInformation. This can be demonstrated in the following sequence:

In[8]:=
Click for copyable input
Out[8]=

Here, information on the connection is fetched:

In[9]:=
Click for copyable input

This prints a tidier form of the connection information:

In[10]:=
Click for copyable input
Out[10]//TableForm=

JDBC Connections

If you do not have a named database connection, you may connect to the database by using explicit JDBC settings.

If you find that the examples in this section do not work as shown, you may need to install or restore the example database with the package, as described in "Using the Example Databases".

JDBC[name,url]a JDBC setting
JDBC[classname,url]a JDBC setting that gives the explicit class name for the driver
JDBCDriverNames[]a list of the names of possible JDBC drivers
JDBCDrivers[]the details of all JDBC drivers
JDBCDrivers[name]the details of the JDBC driver labeled name

This loads the package.

In[3]:=
Click for copyable input

The following opens a connection to HSQLDB using the file $UserBaseDirectory/DatabaseResources/Examples/demo. This works because the package knows what JDBC driver to use for connecting to HSQLDB.

In[15]:=
Click for copyable input
Out[15]=
In[16]:=
Click for copyable input

The JDBCDriverNames command returns the list of builtin drivers. appears in this list and therefore you can use the setting as an argument to JDBC.

In[12]:=
Click for copyable input
Out[12]=

You can get more complete information on all of the built-in drivers by using JDBCDrivers without a parameter.

If you want to get information on just one driver, you can do this by giving its name to JDBCDrivers. Finding the protocol set for a driver can help to use OpenSQLConnection.

In[15]:=
Click for copyable input
Out[15]=

The details of how the built-in drivers are configured is described in "Database Resources".

If DatabaseLink does not already contain a driver for your database, you can add your own. The driver is a collection of Java classes, and they must be added to the Wolfram Language using the standard that J/Link provides for adding Java classes. Typically, this is done by adding the class file or a jar file to a Java subdirectory in a Wolfram Language application. One possible location is inside DatabaseLink itself. A disadvantage is that if you update the Wolfram System, you may have to copy the new material. Another location would be in an application inside $UserBaseDirectory or $BaseDirectory; this would not need to be changed if you updated your software.

As an example, you could create an application for connecting to the Oracle database. This could be done by creating an application called Oracle inside $UserBaseDirectory/Applications or $BaseDirectory/Applications. You might have to create some of the directories manually, but you would not need to change anything if you update your software. Another advantage is that you can use the same location to hold a DatabaseResources directory; this could hold other configuration information as discussed in "Database Resources".

The following table shows some possible locations that you could use to install drivers for connecting to Oracle.

$UserBaseDirectory/Applications/Oracle/Javalocation for database driver class files
$BaseDirectory/Applications/Oracle/Javalocation for database driver class files

Possible locations for database driver class files.

A Java subdirectory of an application is automatically on the classpath. However, if the location selected for the driver class files is not on the path returned by JavaClassPath, you must add the driver directory to the classpath using AddToClassPath.

In[18]:=
Click for copyable input

Note that this setting does not persist between Wolfram Language sessions.

When you have installed the driver classes, you can make a connection. It should be noted that the URL argument you use depends on the server, port, and database type you are using. In the following example, a connection is made to an Oracle database using a driver installed in one of the locations previously suggested. The documentation for the JDBC driver will tell you what class and URL format to use.

Click for copyable input

This is the most verbose form of OpenSQLConnection. Typically, you would reuse previously stored configuration information. This technique is discussed in "Database Resources".

ODBC Connections

Open Database Connectivity (ODBC) is a general way to connect to SQL databases that is supported in a number of operating systems, particularly Microsoft Windows. DatabaseLink comes configured with a driver for ODBC connections. This example, which works only on Windows, shows how to connect to a sample database using ODBC.

If you find that the examples in this section do not work as shown, you may need to install the Microsoft Access Database Engine 2010 Redistributable, which facilitates ODBC and JDBC-ODBC connections to Access and Excel.

Setting Up the Connection

This example uses a sample database file, publisher.accdb, which is located inside the DatabaseLink package structure. You can find the location by evaluating the following line.

In[9]:=
Click for copyable input
Out[10]=

Rather than modifying files inside of DatabaseLink, it is preferable to copy them into some other location. One possible location would be inside the DatabaseResources/Examples directory inside $UserBaseDirectory (it may be necessary to create these directories). You can find the location by evaluating the following.

In[11]:=
Click for copyable input
Out[11]=

The publisher.accdb file is found inside the Examples subdirectory.

Now, you need to use the ODBC control panel to register the data source. This is typically found in the Administrative Tools folder of the Windows Control Panel. When it is opened it looks something like the following.

7.gif

Click the Add button; this will bring up the Create New Data Source window.

8.gif

Select Microsoft Access Driver and then click Finish. This will bring up an ODBC Microsoft Access Setup window.

9.gif

You should fill in the Data Source Name text field, using the name "publisher" (this is the name that ODBC will use). Then click the Select button, which allows you to find and select the publisher.accdb file.

10.gif

Click OK in each successive window until the control panel has been closed. Note that publisher and its driver now appear in the list of available files in the ODBC Data Source Administrator window.

Using the Connection

You should now be able to connect to the ODBC data source that was configured. The following loads DatabaseLink and connects to the ODBC data source publisher. This will use the ODBC driver that is configured by the package.

In[20]:=
Click for copyable input

Alternatively, you may use the Access-specific ODBC driver and a path to the Access file. This method does not require a configured ODBC source.

In[24]:=
Click for copyable input

You can use the connection to query the database.

In[22]:=
Click for copyable input
Out[22]=
In[23]:=
Click for copyable input
Out[23]//TableForm=

This closes the connection.

In[21]:=
Click for copyable input

Named Connections

If your work requires that you frequently connect to the same database, it might be beneficial to give this connection a name and use the name in OpenSQLConnection. The details of how to set up a named connection are given in "Database Resources". This section describes how to learn what named connections are available.

DataSourceNames[]list of the names of all connections
DataSources[]details of all named connections
DataSources[name]details of the connection called name

Functions for working with named connections.

This loads the package.

In[7]:=
Click for copyable input

The following lists all the named connections. If you have installed more connections, you may see a larger list.

In[12]:=
Click for copyable input
Out[12]=

You can get more complete information on all the connections by using DataSources.

In[2]:=
Click for copyable input

You can get information on just one named connection by giving a name argument to DataSources.

In[11]:=
Click for copyable input
Out[11]=

Database Timeouts

Database operations typically involve connecting to a server, and the possibility of problems accessing the server must be taken into account. Consequently, there is a timeout for database operations such as connecting or executing queries. This timeout is controlled by the global variable $SQLTimeout.

option name
default value
$SQLTimeoutAutomatictimeout for making a connection and executing queries

Specification of the timeout for working with the database.

The default value, Automatic, means that the default value given by the driver will be used.

Example Connections

This section shows some sample connection commands and explains how they work.

In this example, you connect to a MySQL database called conn_test running on the computer named databases on port 1234 using the built-in driver with the username test.

In this example, you connect to the same MySQL database as in the previous example, but this time using the driver class name com.mysql.jdbc.Driver. This driver is built-in, so there is no need to add it to the session classpath.

The first example makes use of the built-in JDBC configuration MySQL(Connector/J). Alternatively, a custom driver configuration could be supplied, as described in "Database Resources: JDBC Configuration". The second example does not require any DatabaseResources configuration. Furthermore, the driver class com.mysql.jdbc.Driver is available in the default DatabaseLink layout, so there is no need to modify the session classpath.

Some further examples using explicit JDBC specifications follow.

Open a connection to the in-memory HSQL database scratchpad.

Open a read-only connection to the on-disk SQLite database master, specifying an absolute path.

Open a connection to the Firebird database overflow, available on port 3050 of localhost. Note the specification requires a path to the database file.

Open a connection to the Oracle database xe, available on port 1521 of the machine . Use the guest account and prompt for password entry. The Oracle driver (in this case, ojdbc6.jar) is located in /My/Driver/Directory, which is added to the session classpath.

Open a connection to the SQL Server database staging, available on port 1433 of the machine mssql.domain.com.

Open a connection to the PostgreSQL database archive, available on port 5432 of the machine warehouse.domain.org. Here the driver class name is given explicitly; alternatively, supply the built-in "PostgreSQL" configuration or a named custom configuration.

Open a connection to the H2 database users, available on port 9092 of the machine 10.11.12.13. In this case, H2 is running in server mode.

Open a connection to the Derby database transactions, instructing the driver via the "Properties" option to create the database if it does not already exist. In this case, Derby is running in embedded mode.

Open a connection to a Microsoft Access database using the preconfigured ODBC source "Access sandbox".

Open a connection to a Microsoft Access database using the built-in Access driver, supplying a path to the database file. This method does not require a preexisting ODBC data source.

Connections to Microsoft Excel work similarly. Open a connection to a Microsoft Excel spreadsheet using the preconfigured ODBC source "Excel sandbox".

Open a connection to a Microsoft Excel spreadsheet using the built-in Excel driver, supplying a path to the spreadsheet. This method does not require a preexisting ODBC data source.

More information on drivers is found in "Database Connections: JDBC Connections".

The Connection Tool

The Connection Tool is a graphical interface tool that simplifies opening a connection to a database. It is launched by executing the command OpenSQLConnection[]. It is described in "The Database Explorer: The Connection Tool".