Newsletters - MATLAB News & Notes

Talk Directly to Databases with
New Toolbox

An efficient tool for working with ODBC/JDBC
compliant databases

by Linda Chuss

If you're fluent in Structured Query Language (SQL), you probably can save yourself a lot of trouble by using the Database Toolbox, introduced in the fall of 1998. The Database Toolbox allows you to extract data from popular databases directly into MATLAB, using MATLAB commands that contain SQL statements. After you have imported the data, all of MATLAB's powerful computational tools are available to you for analyzing and processing the data. You can then export your results directly from MATLAB back to the source database or to another database.

Streamline and automate a painstaking process

Before the introduction of the Database Toolbox, a series of primarily manual steps was required to get data from a database into MATLAB. For example, you might have done the following: 1. Use SQL statements to import data to Microsoft Excel.
2. Massage or reformat the data in Excel to get it into a form you can use.
3. From Excel, write the data to a flat file.
4. Finally, read the flat file into MATLAB using dlmread or fread.

The Database Toolbox reduces four steps to one.

This process took hours and needed to be repeated every time the data was updated or you wanted a different set of data. After processing the data in MATLAB, you had to go through a similar series of steps, in reverse order, to export the results to a database. Users knowledgeable in C, C++, and database API calls might have written their own CMex interfaces to connect MATLAB to databases to save some time. If these steps sound painfully familiar, you're not alone. Quantitative analysts report that the process of getting data from its source to their analysis package occupies about half of their time! With a better process, you can virtually eliminate the time required to get data and therefore spend much more time performing analyses.

The Database Toolbox is designed to streamline the data import process. You no longer need to perform the interim steps of putting data into a correctly formatted flat file, but you can read data directly from database tables into MATLAB. You use MATLAB commands to retrieve data as strings from the database, parse the data into correct data types, and store the data in a MATLAB cell array, which supports mixed data types. The same benefits apply to exporting data using the toolbox.

Example of commands used to import data
This is a series of commands to import a column of data, X, from table Y.

% connect to specified database, specifying
   % username and password

connectionA = database('database', 'username', ...
   'password')

% open cursor and issue SQL statement to select data

cursorA = exec(connectionA, 'select X from Y')

% retrieve R rows of data

cursorA = fetch(cursorA, R)

The SQL statement in the exec command can include a where clause to further define the data to be imported and the fetch statement can retrieve all data at once or just selected rows. This process takes only a few minutes and can be automated if you save the commands in an M-file. Once the data is in MATLAB, you can view attributes of the imported data, as well as the data itself, and you can use all of MATLAB's commands to process the data.

It's thaat esy to export your results too. Put the data to be exported into a cell array and define the database column names to which you will be writing. For example:

insert(connectionA, 'tablename', 'columnname', ...
   'cell_array')

Instead of an insert command, which adds data, you can use an update command, which replaces data. With the update command, you can use a where clause to specify exactly which data to replace.

Database Toolbox being adopted quickly
The Database Toolbox has already been adopted by MATLAB users in a wide variety of fields and organizations, including: • The financial sector including asset management, investment banking, traders, insurance, lending and credit, the financial arm of corporations, and other organizations that perform risk analyses
• Scientific research in academia, government, and the private sector
• Manufacturing companies, including those in process and discrete manufacturing and biotechnology
• Computing organizations, especially those performing data warehousing, data mining, and market research
• Utility companies

Even greater benefits for distributed applications
If you develop MATLAB based applications that access a database and you distribute the applications to multiple users, the Database Toolbox offers even more time savings. Many developers send out an application with a fixed set of data that they've extracted from the database and prepared for use with the application. Without the Database Toolbox, this process is not only time-consuming for the application developer, it also limits users to processing only the prepared data sent to them. Furthermore, the developer needs to update the data set regularly, using the manual process described earlier.

With the Database Toolbox, you can include database access commands in M-files and therefore in the applications you develop. As a result, you need to distribute only the application to users; the data remains in the database. Users can access the most current data directly from the database using your application. This means users can access precisely the data they want, without any special programming on your part. You no longer need to prepare the data set to accompany the application, nor manually update the data set to reflect changes.

Click on image to see a larger view.

Don't know SQL?
For those who aren't fluent in SQL, the Database Toolbox comes with a demonstration version of the Visual Query Builder; a more robust product version is planned. The Visual Query Builder is a graphical user interface that allows you to specify data to retrieve by pointing and clicking rather than using SQL statements.

Requirements for the Database Toolbox
The Database Toolbox works with MATLAB version 5.2 or later and runs on Windows platforms (NT, 95, and 98), with a UNIX version planned. It supports any ODBC/JDBC compliant database, including Oracle, Sybase SQL Server and SQL Anywhere, Microsoft Access, Microsoft SQL Server, IBM DB2, Informix, and Ingres. The toolbox works with all ODBC and JDBC drivers that are used with the supported databases. You can access any database on any server on your network. The Database Toolbox is available for downloading over the Web, and a trial version is available for current MATLAB customers.