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 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 |
| 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 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 "DatabaseLink`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.
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.
This inserts the data into the table.
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.
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 "DatabaseLink`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.
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.
SQLSelect can be used to retrieve the data from the database. The data will be returned as SQLDateTime expressions.
This drops the table and closes the connection.
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 "DatabaseLink`DatabaseExamples`" package, as described in "Using the Example Databases".
This loads DatabaseLink and connects to the demo database.
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.
This inserts a Wolfram Language expression into the database.
SQLSelect retrieves the data from the database. The data is returned as an SQLExpr expression.