Data Type Mapping

One of the most important issues for using a database is the conversion of data as it is stored and retrieved from a database. This tutorial will discuss how Mathematica expressions interact with data stored in a database.

The following table shows the mappings between data types and Mathematica expressions. For example, a Mathematica Integer expression can be stored in SQL integral types such as INTEGER and TINYINT. In addition, if data from a column that is of type VARCHAR is selected, this will result in a Mathematica String expression.

Mathematica expression
data type
Stringused mostly with SQL types such as CHAR, VARCHAR, and LONGVARCHAR
Integerused mostly with SQL types such as INTEGER, TINYINT, SMALLINT, and BIGINT
Realused mostly with SQL types such as DOUBLE, FLOAT, and REAL
Trueused mostly with the SQL type BIT
Falseused mostly with the SQL type BIT
Nullused mostly with the SQL type NULL
SQLBinaryused mostly with SQL types such as BINARY, VARBINARY, and LONGVARBINARY
SQLDateTimeused mostly with SQL types such as DATE, TIME, and TIMESTAMP
SQLExpra special type of binary data that is used to store Mathematica expressions

The mapping between Mathematica expressions and data types stored in a database.

Atomic Mathematica expressions such as String, Integer, Real, True, False, and Null, and compound expressions formed from SQLBinary, SQLDateTime, and SQLExpr are converted to and from Java objects. These Java objects are then processed with JDBC operations, taking advantage of any encoding or escaping functionality that is provided by the JDBC driver. It is typical that they contain code specific to a database for encoding a value passed into or received from a query. Since these drivers are often implemented by the makers of the database, it is very advantageous to use their functionality as much as possible.

Certain data types require Mathematica expressions that use a special wrapper. For example, the data type BINARY requires a Mathematica expression that uses the wrapper SQLBinary. These wrappers are necessary to prevent ambiguities in the command structure.

SQLBinary

SQLBinary can be used to work with binary data in a database. This allows you to store data such as images or compiled code.

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

This loads DatabaseLink and connects to the demo database.

In[3]:=
Click for copyable input

This generates a string that contains a GIF image.

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

ToCharacterCode is used to create a list of bytes that represent the image. This list will also be wrapped in SQLBinary.

In[4]:=
Click for copyable input

This creates a table for demonstration purposes.

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

This inserts the data into the table.

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

The data is now retrieved using SQLSelect. Since it is binary data, it is returned as an SQLBinary expression.

In[7]:=
Click for copyable input

Then, the data is converted back into a string using FromCharacterCode.

In[8]:=
Click for copyable input

Finally, you can import the data and display it.

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

This drops the table and closes the connection.

In[10]:=
Click for copyable input

SQLDateTime

SQLDateTime allows you to store and retrieve date and time information. It also allows you to execute queries that depend on specific dates or times.

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

This loads DatabaseLink and connects to the demo database.

In[12]:=
Click for copyable input

You can create a table for demonstration purposes. This table contains DATE, TIME, DATETIME, and TIMESTAMP columns.

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

Now, you can insert data into the table. You can use the output of the Mathematica DateList[] function for all data types except for the data type TIME; for this you must specify a list of three integers that specify hours, minutes, and seconds. Note that DATE will only use the date information from DateList[] and not the time information. DATETIME and TIMESTAMP will use both and also nanoseconds.

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

SQLSelect can be used to retrieve the data from the database. The data will be returned as SQLDateTime expressions.

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

This drops the table and closes the connection.

In[6]:=
Click for copyable input

SQLExpr

SQLExpr can be used to store Mathematica expressions in a database. When they are retrieved, they are converted back into Mathematica expressions.

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

This loads DatabaseLink and connects to the demo database.

In[8]:=
Click for copyable input

In order to store a Mathematica expression, you need to create a column that can be used to store a string such as VARCHAR.

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

This inserts a Mathematica expression into the database.

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

SQLSelect retrieves the data from the database. The data is returned as an SQLExpr expression.

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

This drops the table and closes the connection.

In[6]:=
Click for copyable input
New to Mathematica? Find your learning path »
Have a question? Ask support »