Database Connections

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

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 Mathematica expression that refers to the connection. It can be used to make queries on the database.

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

SQLConnections returns a list of all the open connections.

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

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

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

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

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

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

option name
default value
"Description"""textual description of the connection
"Name"""name of the connection
"Username"""username to use for connecting
"Password"""password to use for connecting
"Catalog"Automaticlocation of the database catalog
"ReadOnly"Automaticset the connection to be read only
"TransactionIsolationLevel"Automaticset transaction isolation for the connection

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[7]:=
Click for copyable input
Out[7]=
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[9]:=
Click for copyable input
Out[9]=

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[10]:=
Click for copyable input
Out[10]=
In[11]:=
Click for copyable input

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

Connection Information

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

In[12]:=
Click for copyable input

This opens a connection to one of the sample databases.

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

Here, information on the connection is created.

In[14]:=
Click for copyable input

This prints a tidier form of information on the connection.

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

JDBC Connections

If you do not have a named database connection, you can still connect to the database by using a JDBC setting.

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[16]:=
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[17]:=
Click for copyable input
Out[17]=
In[18]:=
Click for copyable input

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

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

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[16]:=
Click for copyable input
Out[16]=

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 Mathematica 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 Mathematica application. One possible location is inside DatabaseLink itself. A disadvantage is that if you update Mathematica, 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.

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 you are using. In the following example, which is not actually configured, 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 to use.

Click for copyable input

This is the most verbose form of OpenSQLConnection. Typically, you would want to use information that had been stored previously. This 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.

Setting Up the Connection

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

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

Typically, it is not a good idea to modify files that are inside of DatabaseLink, so you might want to copy it 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 on your computer.

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

The publisher.mdb 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.

5.gif

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

6.gif

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

7.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.mdb file.

8.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[17]:=
Click for copyable input

You can use the connection to query the database.

In[19]:=
Click for copyable input
Out[19]=
In[20]:=
Click for copyable input
Out[20]//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[23]:=
Click for copyable input

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

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

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

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

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

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

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 com.mysql.jdbc.Driver.

The first example requires that a JDBC connection mysql has been configured, as described in "Database Resources: JDBC Configuration". The second does not require any DatabaseResources configuration. It does require that the JDBC driver, com.mysql.jdbc.Driver, is made available. 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".

New to Mathematica? Find your learning path »
Have a question? Ask support »