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

Loading the Package

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

In[3]:=3

✖

https://wolfram.com/xid/0qdcwnf6ro2j-sk

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

In[4]:=4

✖

https://wolfram.com/xid/0qdcwnf6ro2j-w3e

Out[4]=4

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[2]:=2

✖

https://wolfram.com/xid/0qdcwnf6ro2j-bj9

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[3]:=3

✖

https://wolfram.com/xid/0qdcwnf6ro2j-mxs

Out[3]=3

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[4]:=4

✖

https://wolfram.com/xid/0qdcwnf6ro2j-u2s

Out[4]=4

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

In[5]:=5

✖

https://wolfram.com/xid/0qdcwnf6ro2j-gx8

Out[5]=5

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

In[6]:=6

✖

https://wolfram.com/xid/0qdcwnf6ro2j-s69

Out[6]=6

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

In[7]:=7

✖

https://wolfram.com/xid/0qdcwnf6ro2j-mb1

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[8]:=8

✖

https://wolfram.com/xid/0qdcwnf6ro2j-jzg

Out[8]=8

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[9]:=9

✖

https://wolfram.com/xid/0qdcwnf6ro2j-k7n

Out[9]=9

You can see the extra row that has been added.

In[10]:=10

✖

https://wolfram.com/xid/0qdcwnf6ro2j-nrj

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

In[11]:=11

✖

https://wolfram.com/xid/0qdcwnf6ro2j-v5s

Out[11]=11

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[12]:=12

✖

https://wolfram.com/xid/0qdcwnf6ro2j-ipc

Out[12]=12

This shows the data that is currently in the table.

In[13]:=13

✖

https://wolfram.com/xid/0qdcwnf6ro2j-ofn

Out[13]=13

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[14]:=14

✖

https://wolfram.com/xid/0qdcwnf6ro2j-v0m

You can see the changes that have been made.

In[15]:=15

✖

https://wolfram.com/xid/0qdcwnf6ro2j-o1t

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[16]:=16

✖

https://wolfram.com/xid/0qdcwnf6ro2j-hz6

Out[16]=16

In[17]:=17

✖

https://wolfram.com/xid/0qdcwnf6ro2j-ca5

Out[17]=17

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[18]:=18

✖

https://wolfram.com/xid/0qdcwnf6ro2j-o5v

Out[18]=18

You can see the changes that have been made.

In[19]:=19

✖

https://wolfram.com/xid/0qdcwnf6ro2j-ogn

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[20]:=20

✖

https://wolfram.com/xid/0qdcwnf6ro2j-hwm

Out[20]=20

There is only one row left in the database now.

In[21]:=21

✖

https://wolfram.com/xid/0qdcwnf6ro2j-f4m

Out[21]=21

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[22]:=22

✖

https://wolfram.com/xid/0qdcwnf6ro2j-zf5

Out[22]=22

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

In[23]:=23

✖

https://wolfram.com/xid/0qdcwnf6ro2j-gr5

Out[23]=23

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

In[24]:=24

✖

https://wolfram.com/xid/0qdcwnf6ro2j-f4z

Out[24]=24

Closing the Connection

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

In[25]:=25

✖

https://wolfram.com/xid/0qdcwnf6ro2j-car

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 "DatabaseLink`DatabaseExamples`" 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[26]:=26

✖

https://wolfram.com/xid/0qdcwnf6ro2j-mos

Out[27]=27

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.

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

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 Wolfram Language notebook). These are described in "The Database Explorer".