DATABASELINK TUTORIAL

Database Reference
This feature is not supported on the Wolfram Cloud.

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

Click for copyable input

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

In[14]:=
Click for copyable input
Out[15]=

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

Click for copyable input

For more information, see hsqldb.sourceforge.net.

MySQL

The MySQL database server is an extremely popular open source database. It is used in many different types of applications. DatabaseLink comes configured with a driver for MySQL.

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

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

In[16]:=
Click for copyable input
Out[17]=

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

Click for copyable input

When opening result sets, the mode option is available for reading unscrollable result sets with reduced memory use.

In[15]:=
Click for copyable input
Out[3]=

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

For more information, see www.mysql.com.

ODBC

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.

Under Windows, there is an ODBC Data Source Administrator that can be used to connect to a variety of different databases. Database Connections: ODBC Connections shows how to connect to a database using ODBC.

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

In[14]:=
Click for copyable input
Out[15]=

To connect to an ODBC database, you would typically use a data source name as in the following example.

Click for copyable input

PostgreSQL

Support for PostgreSQL is provided by the PostgreSQL JDBC driver.

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

In[18]:=
Click for copyable input
Out[19]=

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

Click for copyable input

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.

In[9]:=
Click for copyable input
Out[10]=

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

Click for copyable input

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.

In[8]:=
Click for copyable input
Out[9]=

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

Click for copyable input

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.

In[10]:=
Click for copyable input
Out[11]=

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

Click for copyable input

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.

In[12]:=
Click for copyable input
Out[13]=

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.

Click for copyable input

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.

In[18]:=
Click for copyable input
Out[19]=

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

Click for copyable input

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.

In[16]:=
Click for copyable input

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

Click for copyable input

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

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: JDBC Connections" and "Database Resources: JDBC Configuration".

Information on how to obtain and install drivers as well as configuring connection information for a number of databases is available at www.wolfram.com/solutions/connections/database/vendors.html.

JDBC

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