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

Functions for working with database connections.

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

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.

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:

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.

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

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

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

Connection Information

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

This opens a connection to one of the sample databases:

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

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

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

Here, information on the connection is fetched:

This prints a tidier form of the connection information:

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 "DatabaseLink`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. "HSQL(Standalone)" appears in this list and therefore you can use the setting "HSQL(Standalone)" 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 are given in "Database Resources".

Installing JDBC Drivers

If DatabaseLink does not already contain a driver for your database, you can install your own by placing the driver class or jar file on the path returned by JavaClassPath. A driver for connecting to an Oracle database, for example, could be placed in any of the following locations.

Possible locations for JDBC driver class files.

A disadvantage of adding the driver to DatabaseLink itself is that if you update the Wolfram System, it will be necessary to repeat the installation. Installations under $UserBaseDirectory or $BaseDirectory are persistent across software updates.

A Java subdirectory of an application is automatically on the classpath, and driver class or jar files placed in it will be available to DatabaseLink. A DatabaseResources directory in the same location is convenient for holding configuration information, as discussed in "Database Resources".

If you wish to use a driver not on the classpath, you must add the driver directory to the classpath using AddToClassPath.

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

When you have installed the driver classes, you can make a connection. The URL argument you supply 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 any of the locations previously suggested. The documentation for the JDBC driver will tell you what class and URL format to use.

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

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.

Functions for working with named connections.

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

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 Drizzle driver with the username test.

OpenSQLConnection[JDBC["MySQL(Drizzle)", "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 class name com.drizzle.jdbc.DrizzleDriver.

OpenSQLConnection[JDBC["org.drizzle.jdbc.DrizzleDriver", "databases:1234/conn_test"], 
  "Username" -> "test"]

The first example makes use of the built-in JDBC configuration MySQL(Drizzle). 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 org.drizzle.jdbc.DrizzleDriver 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.

OpenSQLConnection[JDBC["HSQL(Memory)", "scratchpad"]]

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

OpenSQLConnection[JDBC["SQLite", FileNameJoin[{$UserBaseDirectory, 
  "DatabaseResources","master.db"}]], "ReadOnly" -> True]

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

OpenSQLConnection[JDBC["Firebird", 
  "localhost:3050//tmp/overflow.fdb"], "Username" -> "SYSDBA", 
  "Password" -> "pineapple"]

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, ojdbc14.jar) is located in /My/Driver/Directory, which is added to the session classpath.

Needs["JLink`"];
AddToClassPath[FileNameJoin[{"/My", "Driver", "Directory"}];
OpenSQLConnection[JDBC["oracle.jdbc.driver.OracleDriver",
  "jdbc:oracle:thin:@servern.domain.com:1521/xe"],
  "Username" -> "GUEST", "Password" -> "$Prompt"]

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

OpenSQLConnection[JDBC["Microsoft SQL Server(jTDS)", 
  "mssql.domain.com:1433/staging"], "Username" -> "QA", "Password" -> "Kangaroo"]

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.

OpenSQLConnection[JDBC["org.postgresql.Driver", 
  "jdbc:postgresql://warehouse.domain.org:5432/archive"],
  "Username" -> "postgres", "Password" -> "password"]

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.

OpenSQLConnection[JDBC["H2(Server)", "10.11.12.13:9092//path/to/users"]]

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.

OpenSQLConnection[JDBC["Derby(Embedded)", "/path/to/transactions"], 
  "Properties" -> {"create" -> "true"}]

Open a connection to a Microsoft Access database using the built-in Access driver, supplying a path to the database file.

OpenSQLConnection[JDBC["Microsoft Access(UCanAccess)", "C:\\path\\to\\sandbox.accdb"]]

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