Database Reference

HSQLDB

HSQLDB is a relational database engine written in Java that is bundled with DatabaseLink, which also contains a JDBC driver and necessary configuration. It offers a small (about 100k), fast database engine, which can run in a variety of ways, including server, inprocess, and inmemory modes. DatabaseLink is configured to use an inprocess standalone mode. This makes it very simple to run and use (no special configuration is required). However, it means that nothing else can connect to the database and only one connection to a particular database can be made at any one time (even by multiple copies of the Wolfram System).

To create a new database with HSQLDB, you just need to make a connection to a database that does not already exist, and HSQLDB will create it for you. You could use the Connection Tool, which will deploy a wizard and write a named connection. This is described in "The Database Explorer: The Connection Tool". You could also write a connection file and place this in a DatabaseResources directory, as described in "Database Resources: Connection Configuration". Finally, you can use OpenSQLConnection as follows. All of these issues are described in "Database Connections".

The following is a sample command that will create a new database called example.

The details of the HSQLDB driver in DatabaseLink can be seen as follows.

To connect to an HSQLDB database, you would typically give the filename, username, and password as in the following example.

For more information, see hsqldb.sourceforge.net.

MySQL

The MySQL database server is a popular open source database used in many different types of applications. DatabaseLink comes configured with the Connector/J driver for MySQL.

If you want to create a new database for MySQL, you should contact the server administrator.

The details of the MySQL drivers in DatabaseLink can be seen as follows.

To connect to a MySQL database, you would typically set the server, database, username, and password as in the following example.

When opening result sets, the mode option "MySQLStreaming" is available in some drivers (MariaDB, Connector/J) for reading unscrollable result sets with reduced memory use.

Other RDBMSs may implement streaming for forward-only result sets without requiring an explicit setting.

For more information, see www.mysql.com.

PostgreSQL

Support for PostgreSQL is provided by the PostgreSQL JDBC driver.

The details of the PostgreSQL driver in DatabaseLink can be seen as follows.

To connect to a PostgreSQL database, you would typically set the server, database, username, and password as in the following example.

For more information, see jdbc.postgresql.org.

SQL Server

Support for Microsoft SQL Server is provided by the jTDS driver. The jTDS driver is strongly recommended over ODBC for SQL Server applications.

The details of the SQL Server driver in DatabaseLink can be seen as follows.

To connect to a Microsoft SQL Server database, you would typically set the server, database, username, and password as in the following example.

For more information, see jtds.sourceforge.net, an open source driver for Microsoft SQL Server and Sybase.

Derby

Derby is an open source pure Java relational database maintained by the Apache Software Foundation, emphasizing small footprint and standards compliance. Derby operates in embedded and server modes. In embedded mode, as in HSQLDB, the engine is started by DatabaseLink and databases may be automatically created on connection.

The details of the Derby driver in DatabaseLink can be seen as follows.

To connect to a new Derby database, you would typically set the path to a directory along with a property directive for database creation.

For more information, see db.apache.org/derby.

Firebird

Firebird is an open source cross-platform RDBMS emphasizing concurrency, performance, and procedure and trigger support. Support for Firebird is provided by the Firebird JDBC driver.

The details of the Firebird driver in DatabaseLink can be seen as follows.

To connect to a Firebird database, you would typically set the connection url, including a path to the database file, along with authentication information.

For more information, see firebirdsql.org.

H2

H2 is an open source pure Java relational database emphasizing small footprint, performance, concurrency, and security. H2 runs in server, embedded, and in-memory modes. In the latter two modes, DatabaseLink starts the H2 engine and creates databases as necessary.

The details of the H2 driver in DatabaseLink can be seen as follows.

To connect to an embedded H2 database, you would typically set a path terminating in a stem that H2 will use to name its resource files.

For more information, see www.h2database.com.

SQLite

SQLite is a serverless, zero-configuration SQL database engine. It emphasizes ease of use, small footprint, and reliability. SQLite can run in embedded and in-memory modes.

The details of the SQLite driver in DatabaseLink can be seen as follows.

To connect to an SQLite database, you would typically set a path to a file used to store the database.

For more information, see www.sqlite.org.

Sybase

Support for Sybase is provided by the jTDS driver.

The details of the Sybase driver in DatabaseLink can be seen as follows.

To connect to a Microsoft SQL Server database, you would typically set the server, database, username, and password as in the following example.

For more information, see jtds.sourceforge.net, an open source driver for Microsoft SQL Server and Sybase.

Microsoft Access

Support for Microsoft Access is provided by the UCanAccess driver. This driver allows JDBC programs to read and write Microsoft Access databases.

The details of the Microsoft Access driver in DatabaseLink can be seen as follows.

Database operations, such as insertions and deletions, that use TimeObject are not supported.

To connect to a Microsoft Access database, you would typically set the server, database, username, and password as in the following example.

For more information, see ucanaccess.sourceforge.net.

Other Databases

DatabaseLink can connect to any other type of database with a JDBC driver. You can install the driver by following the instructions in "Database Connections: Installing JDBC Drivers" and "Database Resources: JDBC Configuration".

JDBC

The Java Database Connectivity API: www.oracle.com/technetwork/java/javase/jdbc/index.html.