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 DatabaseExamples` package.

This loads the package.

In[1]:=

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

In[2]:=

Out[2]=

In[3]:=

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[4]:=

Out[4]=

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

In[5]:=

Out[5]=

If you want to get information on just one driver, you can do this by giving its name to JDBCDrivers. It can be useful to see what the protocol setting is, this can help to make a setting for OpenSQLConnection.

In[6]:=

Out[6]=

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

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 these must be added to Mathematica using the standard way that J/Link provides for adding Java classes. Typically, this is done by adding the classfile or a jar file to a Java subdirectory in a Mathematica application. One possible location is inside DatabaseLink itself. This has the disadvantage that if you update your software you may have to copy the new material. Another location would be inside an application inside of $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 of $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 the DatabaseResources Directories section.

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

Possible location for database driver classfiles.

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 suggested above. 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 the Database Resources section.