Data Type Mapping
This feature is not supported on the Wolfram Cloud.

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 Wolfram Language expressions interact with data stored in a database.

The following table shows the mappings between data types and Wolfram Language expressions. For example, a Wolfram Language 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 Wolfram Language String expression.

Wolfram Language 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 Wolfram Language expressions

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

Atomic Wolfram Language 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 Wolfram Language expressions that use a special wrapper. For example, the data type BINARY requires a Wolfram Language 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[1]:=
Click for copyable input

This generates a string that contains a GIF image.

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

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

In[3]:=
Click for copyable input

This creates a table for demonstration purposes.

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

This inserts the data into the table.

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

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

In[6]:=
Click for copyable input

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

In[7]:=
Click for copyable input

Finally, you can import the data and display it.

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

This drops the table and closes the connection.

In[9]:=
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[11]:=
Click for copyable input

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

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

Now, you can insert data into the table. You can use the output of the Wolfram Language 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[14]:=
Click for copyable input
Out[14]=

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

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

This drops the table and closes the connection.

In[16]:=
Click for copyable input

SQLExpr

SQLExpr can be used to store Wolfram Language expressions in a database. When they are retrieved, they are converted back into Wolfram Language 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[25]:=
Click for copyable input

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

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

This inserts a Wolfram Language expression into the database.

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

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

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

This drops the table and closes the connection.

In[30]:=
Click for copyable input