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.
| |
| String | used mostly with SQL types such as CHAR, VARCHAR, and LONGVARCHAR |
| Integer | used mostly with SQL types such as INTEGER, TINYINT, SMALLINT, and BIGINT |
| Real | used mostly with SQL types such as DOUBLE, FLOAT, and REAL |
| True | used mostly with the SQL type BIT |
| False | used mostly with the SQL type BIT |
| Null | used mostly with the SQL type NULL |
| SQLBinary | used mostly with SQL types such as BINARY, VARBINARY, and LONGVARBINARY |
| SQLDateTime | used mostly with SQL types such as DATE, TIME, and TIMESTAMP |
| SQLExpr | a 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
DatabaseExamples` package, as described in
"Using the Example Databases".
This loads
DatabaseLink and connects to the
demo database.
This generates a string that contains a GIF image.
| Out[3]= |  |
ToCharacterCode is used to create a list of bytes that represent the image. This list will also be wrapped in
SQLBinary.
This creates a table for demonstration purposes.
| Out[5]= |  |
This inserts the data into the table.
| Out[6]= |  |
The data is now retrieved using
SQLSelect. Since it is binary data, it is returned as an
SQLBinary expression.
Then, the data is converted back into a string using
FromCharacterCode.
Finally, you can import the data and display it.
| Out[9]= |  |
This drops the table and closes the connection.
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
DatabaseExamples` package, as described in
"Using the Example Databases".
This loads
DatabaseLink and connects to the
demo database.
You can create a table for demonstration purposes. This table contains
DATE,
TIME,
DATETIME, and
TIMESTAMP columns.
| 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.
| Out[4]= |  |
SQLSelect can be used to retrieve the data from the database. The data will be returned as
SQLDateTime expressions.
| Out[5]= |  |
This drops the table and closes the connection.
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
DatabaseExamples` package, as described in
"Using the Example Databases".
This loads
DatabaseLink and connects to the
demo database.
In order to store a
Mathematica expression, you need to create a column that can be used to store a string such as VARCHAR.
| Out[3]= |  |
This inserts a
Mathematica expression into the database.
| Out[4]= |  |
SQLSelect retrieves the data from the database. The data is returned as an
SQLExpr expression.
| Out[5]= |  |
This drops the table and closes the connection.
