Configuring Your Environment :: Introduction to the Database Toolbox™ Software (Database Toolbox™)

Database Toolbox™

Configuring Your Environment

On this page…
About Data Sources and Database Drivers Before You Begin Preparing Examples Setting Up Data Sources for Use with ODBC Drivers Setting Up Data Sources for Use with JDBC Drivers

On this page…

About Data Sources and Database Drivers

Before You Begin

Preparing Examples

Setting Up Data Sources for Use with ODBC Drivers

Setting Up Data Sources for Use with JDBC Drivers

About Data Sources and Database Drivers

Before you can use the Database Toolbox™ software to connect to a database, you must set up a data source. A data source consists of:

Data that the toolbox accesses
Information required to find the data, such as driver, directory, server, or network names

Data sources interact with ODBC drivers or JDBC drivers. An ODBC driver is a standard Microsoft^® Windows^® interface that enables communication between database management systems and SQL-based applications. A JDBC driver is a standard interface that enables communication between applications based on Sun™ Java™ and database management systems.

The Database Toolbox software is based on Java. It uses a JDBC/ODBC bridge to connect to the ODBC driver of a database, which is automatically installed as part of the MATLAB^® JVM™.

The following figure illustrates how drivers interact with the Database Toolbox software.

Tip Some Windows systems support both ODBC and JDBC drivers. On such systems, JDBC drivers generally provide better performance than ODBC drivers because the ODBC/JDBC bridge is not used to access databases.

Before You Begin

Before you can use the Database Toolbox software with the examples provided in this documentation, do the following:

Set up the data sources that are provided with the Database Toolbox product. For instructions, see Preparing Examples.
Configure the data sources for use with your database driver.
- If you are using an ODBC driver, see Setting Up Data Sources for Use with ODBC Drivers.
- If you are using a JDBC driver, see Setting Up Data Sources for Use with JDBC Drivers.

Preparing Examples

Setting Up the dbtoolboxdemo Data Source

The dbtoolboxdemo data source uses the tutorial database located in matlabroot/toolbox/database/dbdemos/tutorial.mdb.

To set up this data source:

Copy tutorial.mdb into a directory to which you have write access.
Rename tutorial.mdb to tutorial_copy.mdb.
Make sure you have write access to tutorial_copy.mdb.
Open tutorial_copy.mdb in Microsoft^® Access™ from within the MATLAB Current Directory browser by right-clicking the file and selecting Open Outside MATLAB.
Save the database as tutorial.mdb.

Setting Up the SampleDB Data Source

The SampleDB data source uses a Microsoft Access sample database called Nwind. If you do not have the Nwind database on your system, download it from the Microsoft downloads page at http://www.microsoft.com/downloads/. The version referred to in this documentation is the Northwind Traders sample database, Nwind.exe.

To set up this data source:

Run Nwind.exe to create the Nwind.mdb database.
Make sure you have write access to Nwind.mdb.
Rename Nwind.mdb to Nwind_orig.mdb.
Open Nwind_orig.mdb in Access from within the MATLAB Current Directory Browser by right-clicking the file and selecting Open Outside MATLAB.
You may need to convert the database to the version of the Access software you are currently running. For more information, consult your database administrator.
Save the database as Nwind.mdb.

To create a table in the Nwind database into which to export MATLAB data:

Open the Nwind database in the Access application.
Create a new table with two fields, Calc_Date and Avg_Cost.
Use the default type, which is Text, for the Calc_Date Data Type field.
Set the Data Type of the Avg_Cost field to Number.

Save the table as Avg_Freight_Cost, and then close it.

Note Access warns you that there is no primary key, but you do not need one. If you choose to designate a primary key, you can run the example as written only once.Access prevents you from inserting the same record into the database twice.

Setting Up Data Sources for Use with ODBC Drivers

This section describes how to set up a data source whose database is located on a PC running the Windows operating system, or on another system to which the PC is networked, for use with an ODBC driver. These instructions use the Microsoft ODBC driver Version 4.00 for the U.S. English version of Access 2000 for Windows systems. If you have a different configuration, you may need to modify these instructions. For more information, consult your database administrator.

Close open databases, including tutorial.mdb and Nwind.mdb, in the database program.
Open the Windows Data Source Administrator dialog box to define the ODBC data source:
1. Start Visual Query Builder by entering the command querybuilder at the MATLAB command prompt.
2. In Visual Query Builder, select Query > Define ODBC data source.
The ODBC Data Source Administrator dialog box appears, listing existing data sources.
Click the User DSN tab.
Click Add. A list of installed ODBC drivers appears in the ODBC Data Source Administrator dialog box.
Select MS Access Database.
Click OK.
The ODBC Microsoft Access Setup dialog box for your driver appears. The dialog box for your driver may look different from the following.
Enter dbtoolboxdemo as the Data Source Name.
Enter tutorial database as the Description.
Select the database for this data source to use. For some drivers, you can skip this step. If you are not sure whether you can skip this step, consult your database administrator.
1. In the ODBC Microsoft Access Setup dialog box, click Select.
  The Select Database dialog box appears.
2. Specify the database you want to use. For the dbtoolboxdemo data source, select tutorial.mdb.
3. If your database is on a system to which your PC is connected:
  1. Click Network. The Map Network Drive dialog box appears.
  2. Specify the directory containing the database you want to use.
  3. Click Finish.
4. Click OK to close the Select Database dialog box.
In the ODBC Microsoft Access Setup dialog box, click OK.
Repeat steps 7 through 9, with the following changes, to define the data source for the other sample database, Nwind:
1. In step 7, enter SampleDB as the Data Source Name.
2. In step 8, enter Northwind database as the Description.
3. In step 9, select Nwind.mdb.
The ODBC Data Source Administrator dialog box now displays the dbtoolboxdemo and SampleDB data sources in the User DSN tab.
Click OK to close the dialog box.

Setting Up Data Sources for Use with JDBC Drivers

To set up data sources for use with JDBC drivers:

Find the name of the file that contains the JDBC drivers. This file is provided by your database vendor. The name and location of this file differs for each system. If you do not know the name or location of this file, consult your database administrator.
Specify the location of the drivers file in theMATLAB Sun Java classpath by adding this file's path to the matlabroot/toolbox/local/classpath.txt file.
The following example references a MySQL^® JDBC drivers file that is located in D:/mysql/mysql-connector-java-3.0-bin.jar. Add a reference to this file to classpath.txt as follows:
```
D:/mysql/mysql-connector-java-3.0-bin.jar
```
Restart the MATLAB software session before you access the database.
If the drivers file is not located where classpath.txt indicates, errors do not appear, but MATLAB does not establish a database connection. Make sure that you update classpath.txt with the correct location and file name for your drivers file if the path and/or name of this file changes. If a MATLAB software session is running while you modify classpath.txt, be sure to restart it before you connect to the database.

Do the following to set up JDBC drivers for use with Visual Query Builder:

Run the querybuilder command in the MATLAB Command Window to start Visual Query Builder.

Click Query > Define JDBC Data Source.

The Define JDBC data sources dialog box appears.

The following table describes the fields that you use to define your JDBC data source.

Field name in Define JDBC data sources dialog box Description

Field name in Define JDBC data sources dialog box	Description
Name	The name you assign to the data source. For some databases, the Name must exactly match the name of the database as recognized by the machine it runs on.
Driver	The JDBC driver name (sometimes referred to as the class that implements the Java SQL driver for your database).
URL	The JDBC URL object, of the form `jdbc:``subprotocol``:``subname`. `subprotocol` is a database type; for example, the Oracle^® Corporation Oracle database. `subname` may contain other information used by Driver, such as the location of the database and/or a port number. It may take the form `//``hostname``:``port``/``databasename`.

Name

The name you assign to the data source. For some databases, the Name must exactly match the name of the database as recognized by the machine it runs on.

Driver

The JDBC driver name (sometimes referred to as the class that implements the Java SQL driver for your database).

URL

The JDBC URL object, of the form jdbc:subprotocol:subname.

subprotocol is a database type; for example, the Oracle^® Corporation Oracle database.

subname may contain other information used by Driver, such as the location of the database and/or a port number. It may take the form //hostname:port/databasename.

Note Your driver manufacturer's documentation specifies the Driver and URL formats. You may need to consult your database system administrator for this information.

In the Define JDBC data sources dialog box, click Create New File.
The Specify new JDBC data source MAT-file dialog box appears. Use this dialog box to create a MATLAB MAT-file that saves your specified data source information for future Visual Query Builder sessions.
Navigate to the folder where you want to put the MAT-file, specify a name for it that includes a .mat extension, and click Save.
In the Define JDBC data sources dialog box, enter information for your JDBC data source into the Name, Driver, and URL fields as described in the table in step 3b.
Test the connection by clicking Test.
If your database requires a user name and password, a dialog box prompting you to supply them appears. Enter values into these fields and click OK.
A confirmation dialog box states that the database connection was successful.
In the Define JDBC data sources dialog box, click Add/Update. The data source now appears in the Data source list.

To add more data sources, repeat steps c through g for each new data source. You can add more data sources in the future by editing the MAT-file that contains the data source information.

Note You can create another MAT-file to add new data sources, but Visual Query Builder can only access data sources from one MAT-file at a time. Include multiple data sources in a single MAT-file for easy access.

Click OK to close the Define JDBC data sources dialog box.
The newly added data source now appears in the VQB Data source list.
The defined JDBC data source is only valid for the current MATLAB software session. To access the data source you defined in a future MATLAB software session, follow the instructions in Accessing Existing JDBC Data Sources in the Database Toolbox documentation.

Product Prerequisites

Starting the Database Toolbox™ and Visual Query Builder Software