Database Resources

DatabaseLink allows other Mathematica applications to hold resource information for database connections in DatabaseResources directories. There are a number of possible locations of DatabaseResources directories inside $InstallationDirectory, $BaseDirectory, and $UserBaseDirectory.

The command DatabaseResourcesPath shows the current locations of DatabaseResources directories.

DatabaseResources directories can hold two sorts of files: those that contain JDBC settings and those that contain connection settings.

Any file that is in a DatabaseResources directory with an extension of .m will be inspected to see if it contains possible JDBC configuration information. Here is the format of a JDBC configuration file.

JDBCDriver[
    "Name" -> "name",
    "Driver" -> "driverclass",
    "Protocol" -> "protocol",
    "Version" -> 1
]

In this format name is the name of the connection (as might be used in OpenSQLConnection), driverclass is the class file of the JDBC driver, and protocol is the JDBC protocol. The version of the configuration file is specified by the Version setting.

Here is an example file (configured for HSQLDB).

JDBCDriver[
    "Name" -> "hsqldb",
    "Driver" -> "org.hsqldb.jdbcDriver",
    "Protocol" -> "jdbc:hsqldb:",
    "Version" -> 1
]

This file specifies the driver and protocol to use when OpenSQLConnection is invoked for an hsqldb connection, such as the following command.

OpenSQLConnection[JDBC["hsqldb",ToFileName[{"DatabaseLink","Examples"},"example"]]

Here is another example file (configured for Oracle).

JDBCDriver[
    "Name" -> "oracle",
    "Driver" -> "oracle.jdbc.driver.OracleDriver",
    "Protocol" -> "jdbc:oracle:thin:@",
    "Version" -> 1
]

This specifies the driver and protocol to use when OpenSQLConnection is invoked for an oracle connection, such as the following command.

OpenSQLConnection[JDBC["oracle","server.business.com:1999"],
    "Username" -> "server1"]

Note that if you added an application to hold JDBC driver classes (as shown in Database Connections: JDBC Connections), you could create a DatabaseResources directory in the same application to hold JDBC configuration information. The following table shows the layout of an application, named Oracle, that could be used for connecting to the Oracle database.

When you have installed a new JDBC driver, you might want to confirm that your new driver is accessible to the system. This is described in Database Connections: JDBC Connections.

Any file that is in a DatabaseResources directory with an extension of .m will be inspected to see if it contains possible connection configuration information. Here is the format of a connection configuration file.

SQLConnection[
  connectdata,
  "Name" -> "name", 
  "Description" -> "text", 
  "Username" -> "user", 
  "Password" -> "pass",
  "RelativePath" -> True|False,
  "Version" -> 1
]

Here connectdata holds connection data (typically a JDBC setting), name is the name of the connection (as might be used in OpenSQLConnection), text is a textual description of the connection, and user and pass are the username and password to use when connecting to the database. A password of $Prompt causes a GUI to appear to enter the password. If the connection data involves a relative path, this is specified with the RelativePath setting. The version of the configuration file is specified by the Version setting.

Here is an example file (configured for HSQLDB).

SQLConnection[
    JDBC["hsqldb", "../Examples/example"], 
     "Name" -> "example", 
     "Description" -> "Connection to hsql db for documention.", 
     "Username" -> "sa", 
     "Password" -> "", 
    "RelativePath" -> True,
     "Version" -> 1]

This file specifies that HSQLDB should be used to connect to the file Examples/example, which is found relative to the location of the configuration file. The username sa and a blank password are also given. This connection information is given the name "example". This configuration file supports the following OpenSQLConnection command.

OpenSQLConnection["example"]

Here is another example file (configured for Oracle).

SQLConnection[
    JDBC["oracle", "server.business.com:1999"], 
     "Name" -> "businessDB", 
     "Description" -> "Connection to Oracle db.", 
     "Username" -> "server1", 
     "Version" -> 1]

This specifies connection information to use when OpenSQLConnection is invoked with businessDB, such as the following command.

OpenSQLConnection["businessDB"]

Note that if you added an application to hold JDBC driver classes (as shown in Database Connections: JDBC Connections), and JDBC configuration information (as shown previously), you could use the same location for holding the Oracle connection information. The following table shows the layout of an application that could be used for connecting to Oracle.

To help you to write the connection configuration file, you can use the command WriteDataSource.

This creates a data source named testSource, it will use the HSQL database.

The new data source shows up in the listing from DataSourceNames.

You can connect to the data source and start to work with it. One benefit of the HSQL database is that it will create the database if it does not exist.

It is typically a good practice to close the connection.

If you want to connect to a database other than HSQL you can give a second argument to WriteDataSource. For example, the following will write a data source file that uses MySQL database.

Note that this does not communicate with the MySQL server to create the database, main/test. It is assumed that the database already exists. However, this is still a convenient way to create a named connection. Note how the parameters such as Username, Password, and Location are set. The choices for Location are "User" and "System".

If you did not wish to write the connection configuration file yourself, you could use the New Connection Wizard, described in The Database Explorer: New Connection Wizard.

When you have made a new named connection, you might want to confirm that the new connection is accessible to the system. This is described in Database Connections: Named Connections.