DatabaseLink Workshop

Chris Williamson

Wolfram Research

What is DatabaseLink?

An interface for manipulating and querying databases.

What is DatabaseLink?

An interface for manipulating and querying databases.

Implemented using JDBC.

What is DatabaseLink?

An interface for manipulating and querying databases.

Implemented using JDBC.

Java independent

What is DatabaseLink?

An interface for manipulating and querying databases.

Implemented using JDBC.

Java independent

Works cross-platform

What is DatabaseLink?

An interface for manipulating and querying databases.

Implemented using JDBC.

Java independent

Works cross-platform

Supports many different database through JDBC drivers

What is DatabaseLink?

An interface for manipulating and querying databases.

Implemented using JDBC.

Java independent

Works cross-platform

Supports many different database through JDBC drivers

Manipulate and query the database using SQL or Mathematica commands

What is DatabaseLink?

An interface for manipulating and querying databases.

Implemented using JDBC.

Java independent

Works cross-platform

Supports many different database through JDBC drivers

Manipulate and query the database using SQL or Mathematica commands

Includes a lightweight database

How does DatabaseLink work?

Establish a connection to the server.

How does DatabaseLink work?

Establish a connection to the server.

Manipulate and/or query data in the database using the connection.

How does DatabaseLink work?

Establish a connection to the server.

Manipulate and/or query data in the database using the connection.

Close connection.

Load the Package

The package needs to be loaded before we can use DatabaseLink.

<< DatabaseLink`

Establish a connection

A connection can be opened to an existing data source bying passing the data source's name into OpenSQLConnection.

conn = OpenSQLConnection["demo"]

Data Sources

Existing data source names can be found using DataSourceNames.

DataSourceNames[]

Establish a connection (con't)

If no parameters are passed to OpenSQLConnection, a GUI will present itself and allow the user to choose an existing data source or configure a new data source that is saved to disk.

conn1  =  OpenSQLConnection[]

Establish a connection (con't)

JDBC may be passed to OpenSQLConnection as a parameter to specify a specific JDBC connection.  JDBC may contain the JDBC driver class name and URL used to connect to the database.  Options such as Username and Password may be passed to OpenSQLConnection as well.

conn2  =  
      OpenSQLConnection[
        JDBC["org.hsqldb.jdbcDriver",  "jdbc:hsqldb:/temp/test2"],  
        Username -> "sa"]

Establish a connection (con't)

To make things a bit easier to use, JDBC information may be stored and reused.  In this example, we can specify hsqldb rather than the JDBC driver class name and we can leave off the protocol from the URL.

\(conn3\  = \ 
      OpenSQLConnection[
        JDBC["hsqldb", \ 
          ToFileName[{$UserBaseDirectory, \ "DatabaseResources", \ "Examples"}, \ "test"]], \ Username -> "sa"]\)

JDBC Drivers

Existing JDBC driver names can be found using JDBCDriverNames.

JDBCDriverNames[]

Establish a connection (con't)

Passwords are stored in plain text for existing data sources.  The password may also be set to "$Prompt", so the user will be prompted for the password when trying to connect to data source.

\(conn4\  = \ 
      OpenSQLConnection[
        JDBC["hsqldb", \ 
          ToFileName[{$UserBaseDirectory, \ "DatabaseResources", \ "Examples"}, \ "test"]], \ Username -> "sa", \ 
        Password -> "$Prompt"]\)

Listing open SQLConnections

SQLConnections is used to list open connections to databases.

SQLConnections[]

Closing a connection

CloseSQLConnection is used to close an open connection to the database.  This prevents resources from being wasted on a connection not being used.

CloseSQLConnection[conn];
CloseSQLConnection  /@  SQLConnections[];

Executing SQL

SQL commands may be executed using SQLExecute.

conn = OpenSQLConnection["demo"];
SQLExecute[conn,  "SELECT * FROM SAMPLETABLE1"]

Querying table information

SQLTableNames may be used to retrieve the names of tables in the database.

SQLTableNames[conn]

Querying table information (con't)

SQLTables may be used to retrieve the tables in the database.  A list of SQLTable expressions are returned.

SQLTables[conn]

Querying table information (con't)

SQLTableInformation may be used to retrieve more detailed information about tables in the database.

SQLTableInformation[conn,  ShowColumnHeadings -> True] // 
      TableForm

Querying column information

SQLColumnNames may be used to retrieve the names of columns in the database.  The result is a paired list of table and column names.

SQLColumnNames[conn,  "SAMPLETABLE1"]

Querying column information (con't)

SQLColumns may be used to retrieve the columns in the database.  A list of SQLColumn expressions are returned.

SQLColumns[conn,  "SAMPLETABLE1"]

Querying column information (con't)

SQLColumnInformation may be used to retrieve more detailed information about columns in the database.

SQLColumnInformation[conn,  "SAMPLETABLE1",  
        ShowColumnHeadings -> True] // TableForm

Querying data

SQLSelect is used retrieve data from a database.  The second parameter is used specify which table to retrieve data from.

SQLSelect[conn,  "SAMPLETABLE1"]

Querying data (con't)

The option ShowColumnHeadings may be used to retrieve the column names along with the data from a table.

SQLSelect[conn,  "SAMPLETABLE1",  
        ShowColumnHeadings -> True] // TableForm

Querying data (con't)

The third parameter of SQLSelect may be used to specify which columns of data to retrieve from a table.

SQLSelect[conn,  "SAMPLETABLE1",  {"NAME",  "VALUE"},  
        ShowColumnHeadings -> True] // TableForm

Querying data (con't)

The fourth parameter of SQLSelect may be used to specify a condition that limits the amount of data retrieved from a table.

SQLSelect[conn,  "SAMPLETABLE1",  {"NAME",  "VALUE"},  
        SQLColumn["VALUE"]  <  6,  ShowColumnHeadings -> True] // 
      TableForm

Exercise 1 - Plot data from the database

Using the database named "publisher", plot the quantity of books shipped using ListPlot.

Querying data type information

SQLDataTypeNames may be used to retrieve the names of data types supported by a database.

conn = OpenSQLConnection["demo"];
SQLDataTypeNames[conn]

Creating a table

SQLCreateTable is used to create a table. The second parameter is used to specify the table name. The third parameter specifies a list SQLColumn expressions defining columns of the table.

\(\(SQLCreateTable[
        conn, \ "TEST", \ {
SQLColumn["X", \ 
            DataTypeName -> "REAL"], \ 
SQLColumn["Y", \ 
            DataTypeName -> "REAL"], \ 
SQLColumn["Z", \ 
            DataTypeName -> "REAL"]
}];\)\)
SQLTableNames[conn]

Inserting data

SQLInsert is used to store data in a table.  The second parameter specifies is the name of the table to insert data into.  The third parameter specifies the column names to insert data into.  The fourth parameter specifies the values of the data to be inserted into the table.

SQLInsert[
        conn,  "TEST",  {"X", "Y", "Z"},  {0, 1, 
          0}];
SQLSelect[conn,  "TEST"]

Inserting data (con't)

A table of data can be passed as the fourth parameter of SQLInsert to store in a table in the database.

SQLInsert[
      conn,  "TEST",  {"X", "Y", "Z"},  {Random[],  
            Random[], Random[]}  &  /@  Range[3]]; 
    ArrayPlot[
      SQLSelect[conn,  "TEST",  {"X", "Y",  "Z"}, 
        MaxRows -> 3]]

Exercise 2 - Create a table and store

Using the database named "demo", create a table called "Addresses".  This table should include the column for name, address, city, state, zipcode, and country.  Next insert a record into the database.

Updating data

SQLUpdate is used to update the contents of a table.  The second parameter specifies the table that is being updated.  The third parameter specifies the column names of the data being updated.  The fourth parameter specifies the values of the data being updated.

ArrayPlot[
        SQLSelect[conn,  "TEST",  {"X", "Y",  "Z"}, 
          MaxRows -> 3]];
\(SQLUpdate[
      conn, \ "TEST", \ {"X", "Y", "Z"}, \ {SQLColumn["X"]\  + \  .2, \ SQLColumn["Y"]\ \  + \  .2, \ 
        SQLColumn["Z"]\  + \  .2}]\)

ArrayPlot[
      SQLSelect[conn,  "TEST",  {"X", "Y",  "Z"}, 
        MaxRows -> 3]]

Deleting data

SQLDelete is used to delete the contents of a table.  The second parameter specifies the table of the data being deleted.  A condition may be specified as the third parameter that limits the data deleted.  If a condition is not specified, all data in a table will be deleted.

SQLDelete[conn,  "TEST"]

Dropping a table

SQLDropTable is used to remove a table from the database.  The second parameter specifies the table to drop from the database.

SQLDropTable[conn,  "TEST"];
CloseSQLConnection[conn]

Exercise 3 - Delete the data and drop the table

Using the database named "demo", remove the data from "Addresses" that was inserted in the previous example.  Next drop the table "Addresses" from the database.

Conclusion

Questions.

My email is chrisw@wolfram.com.