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 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 Named Connections.

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.

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 DatabaseExamples` package, as described in "Using the Example Databases".

This loads DatabaseLink.

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 Named Connections.

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

SQLConnections returns a list of all the open connections.

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

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

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

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.

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

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

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

More information on the TransactionIsolationLevel option is found in Transaction Isolation.

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

This opens a connection to one of the sample databases.

Here, information on the connection is created.

This prints a tidier form of information on the connection.

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 DatabaseExamples` package, as described in "Using the Example Databases".

This loads the package.

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.

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.

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.

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.

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.

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".

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.

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.

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.

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.

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

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

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.

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.

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.

You can use the connection to query the database.

This closes the connection.

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.

This loads the package.

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

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

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

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.

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

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.

OpenSQLConnection[ JDBC[ "mysql",
"databases:1234/conn_test"], "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.

OpenSQLConnection[ JDBC[ "com.mysql.jdbc.Driver",
"databases:1234/conn_test"], "Username" -> "test"]

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 JDBC Connections.

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.