DATABASELINK TUTORIAL

Getting Started

Using This Tutorial

This tutorial contains simple examples of DatabaseLink that give an overview of its functionality and some ideas of how to get started. It uses a lightweight database, HSQLDB, that is installed as part of DatabaseLink. This allows you to try examples in the documentation without having to install your own database. The other DatabaseLink tutorials give detailed reference information.

DatabaseLink provides two styles of interface for working with a database: a command-line interface, which is more flexible and is useful for using database commands inside programs, and a graphical interface, which is simpler to use. Both interfaces are discussed here.

When you have finished trying these examples, you may wish to restore the example database, by using the package, as described in "Using the Example Databases".

The Command-Line Interface

Introduction

The command-line interface is a powerful and flexible interface that is particularly appropriate if you want to write programs that use database functionality. This section discusses a number of different operations that use a demonstration database.

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

Loading the Package

DatabaseLink is a Mathematica add-on application. Before any functions from the package can be used, it must be loaded as follows.

In[1]:=
Click for copyable input

Connecting to the Database

The details of connecting to a database are described in "Database Connections". The command-line method uses the function OpenSQLConnection, which returns a handle that can be used to work with a database. The following opens a connection to an included sample database.

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

In[2]:=
Click for copyable input
Out[2]=

There is also a GUI method to connect to the database that is invoked by executing OpenSQLConnection with no arguments. When this is done, the Connection Tool appears; at this point a connection must be opened or the tool canceled before operations can continue.

In[3]:=
Click for copyable input

You can use the Connection Tool to connect to the example database. Further information on how to open a connection to a database is provided in "Database Connections".

Fetching Data

A relational database consists of a set of tables; each table contains data in various categories (typically called columns). Each row of a table contains data values for the different categories. The database application provides functions for managing this data by supporting features such as querying, inserting, updating, or dropping data.

Tables are fundamental to relational databases, and it is important to have a convenient way to list them. You can do this with the SQLTables command as follows.

In[4]:=
Click for copyable input
Out[4]=

You can see information on the specific columns in a table with the SQLColumns command. An example that provides information on the columns in the SAMPLETABLE1 table follows.

In[5]:=
Click for copyable input
Out[5]=

You can retrieve the data in the SAMPLETABLE1 table by executing an SQLSelect command as follows.

In[6]:=
Click for copyable input
Out[6]=

The result of the database query is a Mathematica list, which can be used in any Mathematica command. In the following example, the last element of each row is plotted.

In[7]:=
Click for copyable input
Out[7]=

The following example retrieves data from the SALES table, but adds column headings and outputs the result in a tabular form.

In[8]:=
Click for copyable input
Out[8]//TableForm=

DatabaseLink also allows you to enter raw SQL commands; this might be useful if you are already familiar with SQL and do not want to learn a new language. Here is an example that shows how to obtain all the data in the SALES table.

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

More information on fetching data is available in "Selecting Data".

Inserting Data

You can use the SQLInsert command to insert data in the table. For example, this adds a new row to the SAMPLETABLE1 table.

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

You can see the extra row that has been added.

In[11]:=
Click for copyable input
Out[11]//TableForm=

It is also possible to use a raw SQL command to insert more data. Note how the string being inserted, , uses single-quote characters ('). It is also possible to use double-quote characters ("), though these need to be preceded with a Mathematica string escape backslash character (\).

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

Another version of raw SQL commands involves using placeholders to represent where the arguments will go and then giving SQLExecute a list argument that contains the actual arguments. This is particularly useful, since it avoids the need to concatenate strings to form the raw command.

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

This shows the data that is currently in the table.

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

More information on inserting data is available in "Inserting Data".

Updating Data

You can use the SQLUpdate command to update data in the table. Often this is combined with a condition, so that only some of the data is modified. For example, this sets all entries of the VALUE column that are greater than 8 to 7.

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

You can see the changes that have been made.

In[16]:=
Click for copyable input
Out[16]//TableForm=

It is also possible to use a raw SQL command to update data. This sets all rows for which the VALUE entry is greater than or equal to 6 to 7.

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

More information on updating data is available in "Updating Data".

Deleting Data

You can use the SQLDelete command to delete data in the table. Often this is combined with a condition, so that only some of the data is modified. For example, this deletes all rows for which the VALUE entry is 7 or greater.

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

You can see the changes that have been made.

In[20]:=
Click for copyable input
Out[20]//TableForm=

It is also possible to use a raw SQL command to delete data. This deletes all entries for which the VALUE entry is greater than 5.7.

In[21]:=
Click for copyable input
Out[21]=

There is only one row left in the database now.

In[22]:=
Click for copyable input
Out[22]=

More information on deleting data is available in "Deleting Data".

Batch Commands

If you want to repeat the same command many times, you can do this by providing repeated arguments in a list. Carrying out the same command like this is much faster than doing each command separately.

The following command inserts two rows.

In[23]:=
Click for copyable input
Out[23]=

This uses a raw SQL command to insert two more rows.

In[24]:=
Click for copyable input
Out[24]=

The result of the insert commands can be seen as follows.

In[25]:=
Click for copyable input
Out[25]=

Closing the Connection

When you have finished with the connection, you can close it.

In[26]:=
Click for copyable input

More information on working with connections is provided in "Database Connections". If you have modified the database and want to restore it, you can use the package, as described in "Using the Example Databases".

The Database Explorer

The Database Explorer is a graphical interface to database functionality. It can be launched by loading DatabaseLink and executing the command DatabaseExplorer[].

In[27]:=
Click for copyable input
Out[28]=

When the Database Explorer opens, you can connect to the different databases that are configured for your system. You can also create new connections. After you connect to a database, you can view the tables and columns, as seen in the following example.

6.gif

You can then see the data in the database by clicking the Result tab. Here is an example view.

7.gif

The Database Explorer supports many more features, such as forming more complicated queries, saving queries, and creating reports with the result of a query (saved as a Mathematica notebook). These are described in "The Database Explorer".

New to Mathematica? Find your learning path »
Have a question? Ask support »