DatabaseLink is a Mathematica toolkit that provides a set of tools allowing convenient integration of Mathematica with database management systems. DatabaseLink exposes data to the vast world of analysis and visualization tools available in Mathematica, letting you use DatabaseLink to quickly develop or enhance any data-centric application by applying the world's foremost high-level analysis system to your data.
Implemented using JDBC, DatabaseLink can connect to most popular database systems, such as MySQL, Sybase, Oracle, Microsoft SQL Server, PostgreSQL, Informix, and many more. Users familiar with SQL programming can take advantage of the full power of SQL by passing SQL commands directly to Mathematica functions. Prepared statements allow users to easily integrate SQL commands with Mathematica data.
Mathematica users will find jumping into the database world quite easy with DatabaseLink. A built-in database allows users to get acquainted with databases without needing to install a database server. Familiarity with SQL is not required, as Mathematica functions are available for querying the database. These queries allow users to manage tables and retrieve, update, and store data. The Database Explorer also provides a simple, easy-to-use interface for visually building queries.
DatabaseLink runs on Windows, Macintosh, Linux, and UNIX.Please direct comments, questions, etc. to databaselink@wolfram.com.
| 3.0.0 | 1/14 | Numerous bugfixes and gui revisions, added support for H2, Derby, SQLite, and Firebird. |
| 2.0.0 | 3/06 | Added connection pool feature, added additional configuration options, added additional information functions, added support for generated keys, upgraded drivers, and fixed some bugs. |
| 1.1.0 | 7/05 | Enhanced the SQLConnection Wizard, upgraded drivers, and fixed some bugs. |
| 1.0.2 | 6/05 | Minor bug fixes. |
| 1.0.1 | 1/05 | Minor bug fixes and SQLResultSet functionality. |
| 1.0.0 | 11/04 | First release. |
Upgraded all drivers.
Added drivers/support for H2, Derby, Firebird, and SQLite.
Added "Mode" -> "MySQLStreaming" for streaming result sets in MySQL.
SQLArgument now supports lists.
Configurations set to Password -> $Prompt no longer leak password value.
SQLConnectionOpenQ and SQLConnectionUsableQ added for ascertaining connection status.
Warning added for attempted non-paramterized multirow insert with Access and Excel.
Fixed setLong bug affecting large integer inserts in Access and Excel.
HSQL databases now automatically shut down when the last connection is closed, releasing lock files.
Special (URL) characters now allowed in server names.
Option lists now supported for all DatabaseLink functions.
Added Index option to SQLCreateTable.
Added Default and PrimaryKey options to SQLColumn.
Added support for column aliases.
Fixed cases where SQLDateTime was adding the current time to its argument.
Fixed units bug in Timeout.
Added BatchSize, JavaBatching options to permit finer control over memory use and performance on large inserts.
Revisions to Data Source Wizard and DatabaseExplorer guis.
Data Source Wizard no longer corrupts IP hostnames.
Fixed bug in assignments to $SQLTimeout.
Fixed bug in FetchSize option of SQLResultSetOpen.
Upgraded to MySQL 3.1.12 driver.
Upgraded to HSQL 1.8.0.7.
Upgraded to jTDS 1.2 driver.
Fixed a bug where large integers passed as parameters to SQLExecute, SQLInsert, etc. were not converted into the proper Java objects.
Transactions are now cleaned up after a connection is closed, so a new transaction can be created.
Fixed a bug that prevented $SQLTimeout from working properly.
Added UseConnectionPool option to OpenSQLConnection that determines whether connection pool technology should be used to manage connections. This allows users to reuse connections to enhance performance.
Added $SQLUseConnectionPool to determine whether a connection pool is used by default.
Added Properties option to OpenSQLConnection that allows the user to specify connection properties outside the URL. Added an interface to this option in the SQLConnection Wizard.
Added ReadOnly, TransactionIsolationLevel, and Catalog as options of OpenSQLConnection. Also added SetSQLConnectionOptions and SetSQLConnectionPoolOptions that take these same options to set these dynamically. Added an interface to these options in the SQLConnection Wizard.
Added MaxFieldSize, FetchDirection, FetchSize, and EscapeProcessing as options to SQLSelect and SQLExecute. Also added SetSQLResultSetOptions that take FetchSize and FetchDirection as options to set dynamically.
Fixed a bug that left Java statements open. These need to be closed to free resources for executing additional statements. This bug only occurred for insert, update, and remove statements.
Updated the password prompt to include the username so it looks more familiar and may be changed via a user interface.
Updated the SQLConnection GUI to use SQLConnection wizard for editing existing connections.
Updated the JDBCDriver GUI to use JDBCDriver wizard for editing existing driver configurations.
Added ReturnGeneratedKeys option to SQLInsert and SQLExecute for returning keys that are generated for inserts.
Added ColumnSymbols option to SQLSelect and SQLExecute for assigning vectors containing column values to symbols.
Added SQLConnectionInformation function that returns information about a connection.
Added SQLColumnPrivileges and SQLTablePrivileges functions that returns information the access rights of columns and tables respectively.
Added SQLTableExportedKeys and SQLTableImportedKeys functions that returns information about foreign keys.
Added SQLTablePrimaryKeys function that returns information about primary keys.
Added SQLTableIndexInformation function that returns information about indices.
Updated AddDatabaseResources and DatabaseResourcesPath to use ResourceLocator Package. This affects a couple of functions such as DataSources, JDBCDrivers, and SQLQueries. This in turn fixed a bug with AddDatabaseResources which did not add a new path properly.
Added functionality that makes sure the SQLConnections[] and SQLQueries[] are not reset when the package is reloaded.
Fixed a bug with generating SQL conditions that occurred if logical operators such as Or, And, Equal, etc were too long. For instance, if an Or expression had 40 parameters, it would fail.
Fixed a bug in SQLConnection wizard that prevented saving connection configurations, if the DatabaseResources directory did not already exist.
Added NewDataSource function for storing a new SQLConnection configuration. This has defaults that make it very easy to create a new HSQL database configuration.
Added support for launching a SQLServer. Added SQLServerLaunch for launching a server, SQLServerShutdown for shutting it down, and SQLServerInformation for getting information about the server. Each server launched is an HSQL server. And each server may be connected to over the network. It is a nice way for Mathematica users to share their databases.
Added more error checking to all functions. More messages and failures may result, but better output should be provided.
Removed SQLTableNames[conn, tablename]. There is no need for a function which returns its parameter.
Added SQLSchemaInformation for retrieving information about a schema.
Fixed SQLSchemaNames to only return the names of schema. It was returning more than just the names.
Added support for multiple tables in SQLUpdate.
Upgraded to MySQL 3.1.10 driver.
Upgraded to HSQL 1.8.0.0.
Upgraded to jTDS 1.1 driver.
Fixed a bug with SQLExpr that resulted in an expr returning as a string rather than an expression.
Enhanced the SQLConnection wizard to be easier to use. There are many more preconfigured connection types, each preconfigured connection type has its own configuration page, added a test button to test the connection before it is saved, added a connection properties page, and simplified choices for storing the SQL connection.
Changed the default ResultSet type to be ForwardOnly for non-ResultSet queries. This improves the speed of queries quite a bit.
Changed the default Timeout and MaxRows values. The old value caused problems with Oracle 10g connections.
Fixed a bug with SQLSelect that caused the wrong result when using x < SQLColumn < y and x > SQLColumn > y.
Added functionality for support for SQLSelect when using x <= SQLColumn <= y and x => SQLColumn => y.
Added code to limit the amount of time metadata is retrieved for a result set. This increases the speed of queries slightly.
Added code that enables the use of unsigned integers with MySQL.
Fixed a small bug that caused an error when receiving no results for a query using the ODBC driver.
Fixed a small bug that caused an error when receiving no results when using SQLResultSetRead.
Improved error messages for Java errors that do not have messages but rather define the error based on its class.
Fixed a bug that made SQLExpr data types expand very large within Java and often cause OutOfMemory errors. The fix involved converting SQLExpr data types into String within Mathematica. (SQLExpr data types are stored as strings within the database anyway.)
Added SQLResultSet functionality for customizing how result sets are processed.
Fixed a bug within jtds_sqlserver and jtds_sybase configuration files that caused problems opening connections using these JDBC driver configurations.
Fixed a bug that left Java statements open. These need to be closed to free resources for executing additional statements.
Fixed a bug in DatabaseExplorer that caused queries connected using the ODBC driver to fail because of an unsupported feature. The unsupported feature was the Timeout option. The Timeout option is no longer enabled when using the ODBC driver. So ODBC queries by default will now work, but without a timeout.
Fixed a bug in DatabaseExplorer that caused queries to fail if the Timeout option was not checked.
Fixed DatabaseExplorer to no longer use a blank status message. The blank message caused DatabaseExplorer to redo the layout and looked jerky.
Fixed a problem that prevented DatabaseResources such JDBCDrivers and SQLConnections from showing up in Linux in the functions JDBCDriverNames[], JDBCDrivers[], DataSourceNames[], and DataSources if the resource had extra carriage returns at the beginning of the file.