This is documentation for Mathematica 6, which was
based on an earlier version of the Wolfram Language.

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