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
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
DatabaseExamples` 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.
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
DatabaseExamples` package, as described in
"Using the Example Databases".
| 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.
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.
| 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.
| Out[5]= |  |
You can retrieve the data in the
SAMPLETABLE1 table by executing an
SQLSelect command as follows.
| 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.
| Out[7]= |  |
The following example retrieves data from the
SALES table, but adds column headings and outputs the result in a tabular form.
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.
| 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.
| Out[10]= |  |
You can see the extra row that has been added.
Out[11]//TableForm= |
| |  |
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
Mathematica string escape backslash character (\).
| 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.
| Out[13]= |  |
This shows the data that is currently in the table.
| 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.
| Out[15]= |  |
You can see the changes that have been made.
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.
| Out[17]= |  |
| 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
ENTRY entry is 7 or greater.
| Out[19]= |  |
You can see the changes that have been made.
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.
| Out[21]= |  |
There is only one row left in the database now.
| 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.
| Out[23]= |  |
This uses a raw SQL command to insert two more rows.
| Out[24]= |  |
The result of the insert commands can be seen as follows.
| Out[25]= |  |
Closing the Connection
When you have finished with the connection, you can close it.
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
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[].
| 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.
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
Mathematica notebook). These are described in
"The Database Explorer".
