Quantcast

Documentation Center

  • Trial Software
  • Product Updates

Using Database Explorer

About Database Explorer

dexplore starts Database Explorer, which is a Database Toolbox™ app for connecting to a database and importing data to the MATLAB® workspace.

Database Explorer is an interactive app that lets you:

  • Create and configure JDBC and ODBC data sources.

  • Establish multiple connections to databases.

  • Select tables and columns of interest.

  • Fine-tune your selection using SQL query criteria.

  • Preview selected data.

  • Import selected data into the MATLAB workspace.

  • Save generated SQL queries.

  • Generate MATLAB code.

Migrate from Visual Query Builder (VQB) to Database Explorer

Database Explorer replaces VQB as an app for exploring the data in your database. If you are using VQB, refer to the following points to help migrate from VQB to Database Explorer:

  • If you previously used Visual Query Builder (querybuilder) to access a JDBC data source, before starting Database Explorer for the first time, execute this command because you cannot use this JDBC data source with Database Explorer.

    setdbprefs('JDBCDataSourceFile','')

    Then, define your JDBC data source using Database Explorer.

  • If you use VQB to export data from MATLAB to your database, use the command-line functions datainsert or fastinsert.

  • If you use VQB to generate reports, use MATLAB reporting and plotting functionality to generate reports. You can also use MATLAB Report Generator™ to generate reports.

  • If you use VQB to display charts, use the MATLAB plotting tools to generate charts and graphics.

  • If you generate MATLAB files using VQB, open Database Explorer and recreate your SQL query. Then, using Database Explorer you can generate a script (.m file) that includes your SQL query, preference settings, and connection.

  • If you save your SQL queries using VQB, open Database Explorer and recreate your SQL query. Then, using Database Explorer you can generate a script with just your SQL query. Save the SQL script file with a .sql extension in MATLAB.

Configure Your Environment

Before using Database Explorer 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, folder, 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 Oracle® Java® and database management systems.

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™.

This figure illustrates how drivers interact with 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 JDBC/ODBC bridge is not used to access databases.

Before You Begin

Before you can use Database Explorer with the examples in this documentation, do the following:

  1. Set up the data sources that are provided with Database Toolbox.

      Caution   If you previously used Visual Query Builder (querybuilder) to access a JDBC data source, before starting Database Explorer for the first time, execute this command because you cannot use this JDBC data source with Database Explorer.

      setdbprefs('JDBCDataSourceFile', '')
  2. Configure the data sources for use with your database driver.

Set Up the dbtoolboxdemo Data Source

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

  1. Copy tutorial.mdb into a folder to which you have write access.

  2. Confirm you have write access to tutorial.mdb.

  3. Open tutorial.mdb from the MATLAB Current Folder by right-clicking the file and selecting Open Outside MATLAB. The file opens in Microsoft Access™.

      Note:   You might need to convert the database to the version of Access you are currently running. For example, beginning in Microsoft Access 2007, you see the option to save as *.accdb. For details, consult your database administrator.

Configure ODBC Data Sources

When setting up a data source for use with an ODBC driver, the target database can be located on a PC running the Windows operating system or on another system to which the PC is networked. These instructions use the Microsoft ODBC Data Source Administrator Version 6.1 for the U.S. English version of Microsoft Access 2010 for Windows systems. If you have a different configuration, you might need to modify these instructions. For details, consult your database administrator.

  1. Close open databases, including tutorial.mdb in the database program.

  2. Open Database Explorer by clicking the Apps tab on the MATLAB Toolstrip and then selecting Database Explorer from the Database Connectivity and Reporting section in the apps gallery. Alternatively, at the command line, enter:

    dexplore

    If no data sources are set up, a message box opens. Click OK to close it. Otherwise, the Connect to a Data Source dialog box opens. Click Cancel to close this dialog box.

  3. Click the Database Explorer tab and then select New > ODBC to open the ODBC Data Source Administrator dialog box to define the ODBC data source.

      Requirement   When using a 32-bit version of Microsoft Office, you must also use a 32-bit version of MATLAB to complete the remaining steps.

  4. Click the User DSN tab and click Add.

    A list of installed ODBC drivers appears in the Create New Data Source dialog box.

  5. Select Microsoft Access Driver (*.mdb, *.accdb) and click Finish.

    The ODBC Microsoft Access Setup dialog box for your driver opens. The dialog box for your driver might differ from the following.

  6. Enter dbtoolboxdemo as the data source name and tutorial database as the description.

  7. Select the database for this data source to use. For some drivers, you can skip this step. If you are unsure about skipping this step, consult your database administrator.

    1. In the ODBC Microsoft Access Setup dialog box, click Select.

    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 opens.

      2. Specify the folder containing the database you want to use and click Finish.

    4. Click OK to close the Select Database dialog box.

  8. In the ODBC Microsoft Access Setup dialog box, click OK.

  9. Repeat steps 6 through 8 with the following changes to define the data source for any additional databases that you want to use.

    The ODBC Data Source Administrator dialog box displays the dbtoolboxdemo and any additional data sources that you have added in the User DSN tab.

  10. Click OK to close the dialog box.

Configure JDBC Data Sources

  1. Find the name of the JDBC driver file. This file is provided by your database vendor. The name and location of this file differ for each system. If you do not know the name or location of this file, consult your database administrator.

      Caution   If you previously used Visual Query Builder (querybuilder) to access a JDBC data source, before starting Database Explorer for the first time, execute this command because you cannot use this JDBC data source with Database Explorer.

      setdbprefs('JDBCDataSourceFile', '')

      Then follow these instructions to set up the JDBC data source using Database Explorer.

  2. Specify the location of the JDBC drivers file in the MATLAB Java class path by adding this file's path to the javaclasspath.txt file. MATLAB loads the static class path at the start of each session. The static path offers better class loading performance than the dynamic path. To add folders to the static path, create the file javaclasspath.txt, and then restart MATLAB.

    Create an ASCII file in your preferences folder named javaclasspath.txt. To view the location of the preferences folder, type:

    prefdir

    Each line in the file is the path name of a folder or JAR file. For example:

    d:\work\javaclasses

    To simplify the specification of folders in cross-platform environments, use any of these macros: $matlabroot, $arch, and $jre_home. You can also create a javaclasspath.txt file in your MATLAB startup folder. Classes specified in this file override classes specified in the javaclasspath.txt file in the preferences folder.

      Note:   MATLAB reads the static class path only at startup. If you edit javaclasspath.txt or change your .class files while MATLAB is running, you must restart MATLAB to put those changes into effect.

    If the drivers file is not located where javaclasspath.txt indicates, errors do not appear, and Database Explorer does not establish a database connection.

    For details, see Bringing Java Classes into MATLAB Workspace.

  3. Close the open database, tutorial.mdb, in the database program.

  4. Open Database Explorer by clicking the Apps tab on the MATLAB Toolstrip and then selecting Database Explorer from the Database Connectivity and Reporting section in the apps gallery. Alternatively, at the command line, enter:

    dexplore
  5. Click the Database Explorer tab and then select New > JDBC to open the Create a New JDBC data source dialog box.

  6. Use the following table to set up JDBC drivers for use with Database Explorer.

    1. Using the Create a New JDBC data source dialog box, this table describes the fields that you use to define your JDBC data source. For examples of syntax used in these fields, see JDBC Driver Name and Database Connection URL on the database function reference page.

      FieldDescription
      Data Source Name

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

      Vendor

      The vendor's name for the data source. When using Other:

      • 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. subname can contain other information used by Driver, such as the location of the database and/or a port number. It can take the form //hostname:port/databasename.

        Note:   When using Other as the Vendor, your driver manufacturer's documentation specifies the Driver and URL formats. You might need to consult your database system administrator for this information.

      Server Name

      Server name.

      Port Number

      Server port number.

      Authentication Type

      (Microsoft SQL Server® only) Server or Windows authentication.

      Driver Type

      (Oracle only) Driver type is thin or oci.

      Username

      User name to access the database.

      Password

      Password.

      Database

      Database name.

    2. In the Create a New JDBC data source dialog box, click Save.

    3. If this is the first time you are creating a data source using Database Explorer, the New file to store JDBC connection parameters dialog box opens. Use this dialog box to create a MAT-file that saves your specified data source information for future Database Explorer 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.

    4. Test the connection by clicking Test.

      If your database requires a user name and password, a dialog box prompting you to supply them opens. Enter values into these fields and click OK.

      A confirmation dialog box states that the database connection succeeded.

    5. To add more data sources, repeat steps 5 and 6 for each new data source.

        Note:   You can use tabs in Database Explorer to access different data sources. All of the data sources created using Database Explorer are stored in a single MAT-file for easy access. This MAT-file name is stored in setdbprefs('JDBCDataSourceFile') and is valid for all MATLAB sessions.

Connect to a Data Source

After configuring your OBDC or JDBC data sources, use Database Explorer to connect to the database.

  1. Open Database Explorer by clicking the Apps tab on the MATLAB Toolstrip and then selecting Database Explorer from the Database Connectivity and Reporting section in the apps gallery. Alternatively, at the command line, enter:

    dexplore
  2. Select your data source from the Connect to a Data Source dialog box or click Cancel and then click the Database Explorer tab and then click Connect to select your data source.

  3. Select your data source from the Data Sources list and enter your user name and password.

Database Connection Error Messages

VendorError MessageProbable Causes

All

Unable to find JDBC driver.

  • Path to the JDBC driver JAR file is not on the static or dynamic class path.

  • Incorrect driver name provided while using the 'driver' and 'url' syntax.

All

[Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between Driver and Application

You tried to open a 32-bit application when running MATLAB in 64-bit mode. Restart MATLAB to run in 32-bit mode using the command matlab –win32.

Microsoft SQL Server

The TCP/IP connection to the host hostname, port portnumber has failed. Error: "null. Verify the connection properties, check that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port, and that no firewall is blocking TCP connections to the port."

Incorrect server name or port number. Microsoft SQL Server uses a dynamic port for JDBC and the value should be verified using Microsoft SQL Server Configuration Manager.

Microsoft SQL Server

This driver is not configured for integrated authentication.

The Microsoft SQL Server Windows authentication library is not added to librarypath.txt. For details, see the database example for Microsoft SQL Server Authenticated Database Connection.

Microsoft SQL Server

Invalid string or buffer length.

64-bit ODBC driver error. Use a JDBC driver or the native ODBC interface instead.

MySQL®

Access denied for user 'user'@'machinename' (using password: YES)

Incorrect user name and password combination.

MySQL

Communications link failure.
The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.

Incorrect server name or port number.

MySQL

Unknown database 'databasename'.

Provided database name is incorrect.

Oracle

Error when connecting to Oracle oci8 database using JDBC driver:

Error using com.mathworks.toolbox.database.databaseConnect/makeDaabaseConnection 
Java exception occurred:
java.lang.UnsatisfiedLinkError: no ocijdbc11 in 
java.library.pathat 
java.lang.ClassLoader.loadLibrary(Unknown Source)at 
java.lang.Runtime.loadLibrary0.......

MATLAB cannot find the Oracle DLL that the oci8 drivers need. To correct the problem, add the path for the location of the Oracle DLLs to $MATLAB/toolbox/local/librarypath.txt.

Oracle

Invalid Oracle URL specified:

OracleDataSource.makeURL

DriverType parameter is not specified.

Oracle

The Network Adapter could not establish the connection.

Either Server or Portnumber is not specified or has an incorrect value.

Set Database Preferences

  1. Select Preferences from the Database Explorer Toolstrip to open the Database Explorer Preferences dialog box. These preference settings apply only to Database Explorer.

  2. Specify the Preferences settings that apply to Database Explorer as described in the following table.

    PreferenceAllowable ValuesDescription

    Data Preview size

    5 to 10,000 rows

    The number of rows you see in the Data Preview pane of Database Explorer.

    Import batch size

    1,000 to 1,000,000 rows

    The number of rows fetched at one time from a database. When importing large amounts of data using Database Explorer, tune this value for optimum performance. For details, see Preference Settings for Large Data Import.

    From this Preferences dialog box, select Database Toolbox to manage additional preferences for Database Toolbox. For details, see Working with Preferences. Alternatively, you can use setdbprefs to specify preferences for the retrieved data.

  3. Click OK.

Display Data from a Single Database Table

After connecting to your database, you can display data in database tables in the Data Preview pane.

  1. Display data in the Data Preview pane by opening the database table of interest in the Database Browser pane. When a database table is selected in the Database Browser pane, it is highlighted and there is a corresponding entry in the SQL Criteria panel on the Database Explorer Toolstrip. The SQL Criteria panel is where you enter query conditions for the selected table.

    For any given table, you can select the table information any of three ways:

    • Click to highlight the database table name. This does not display data in the Data Preview pane but does update the SQL Criteria panel.

    • Select (All) to choose all table columns and display them in the Data Preview pane.

    • Select specific check boxes to choose individual table columns and display them in the Data Preview pane.

        Note:   The order of the columns in the Data Preview pane matches the order in which you select them in the Database Browser pane.

  2. Select (All) to choose all database columns or select check boxes for specific table columns.

  3. To change your display, select or clear check boxes in the Database Browser pane. The data updates in the Data Preview pane.

    The Data Preview pane displays a limited number of rows. The total number of rows actually selected in the database appears at the right of the display. You can change the display size by clicking Preferences and adjusting the Data Preview size.

Join Data from Multiple Database Tables

After connecting to your database, you can display data from database tables in the Data Preview pane.

  1. Display data in the Data Preview pane by opening the desired database table in the Database Browser pane. The SQL Criteria panel on the Database Explorer Toolstrip is updated.

  2. When you select additional tables in the Database Browser pane, the SQL Criteria panel is updated.

  3. Display the contents for the selected table using the SQL Criteria panel to define a join of the selected tables. Click the drop-down lists to specify which table column to join the selected tables. The join results appear in the Data Preview pane.

Define Query Criteria to Refine Results

Database Browser selections and SQL criteria work together.

Using the Database Browser pane and the SQL Criteria panel, you can define query conditions and display the results in the Data Preview pane. Each row in the SQL Criteria panel has drop-down controls to define SQL query conditions. You can create SQL query conditions that span multiple rows in the SQL Criteria panel.

    Requirement:   When the right side of a query condition is a custom value that you enter in the text box, you must press the Enter or Tab key for the query condition to take effect. Alternatively, you can press the Import button to apply the condition as well as import data into a MATLAB variable.

    Tip:   If you do not use the Enter or Tab key to apply the query condition, selecting Import > Import applies the condition to the Data Preview pane and imports the data into a MATLAB variable. If there is no data to satisfy the condition, then the Nothing to import error message appears.

Each row in the SQL Criteria panel has four columns to define your SQL query.

Column 1Column 2Column 3Column 4

Column 1 defines the SQL condition type where the supported values are:

  • INNER JOIN

  • LEFT JOIN

  • RIGHT JOIN

  • FULL JOIN

  • WHERE

  • ORDER BY

  • AND

  • OR

Column 2 defines the column names for every table selected in the Database Browser pane.

Column 3 defines the mathematical operator for each row of SQL statements where the supported values are:

  • =

  • !=

  • >

  • <

  • <=

  • >=

  • LIKE

  • NOT LIKE

  • IS

  • IN

  • NOT IN

  • ASC

  • DES

Depending on the preceding condition of the query statement, Column 4 displays column names for every table selected in the Database Browser pane.

Use multiple rows in the SQL Criteria panel to define multiple SQL query statements.

Query Rules Using the SQL Criteria Panel

The control options for the SQL Criteria panel depend on your selections in the Database Browser pane. The SQL Criteria panel supports multiple rows for specifying your query criteria. You can add more rows for these options in the SQL Criteria panel by clicking + or you can remove a row by clicking -.

  • If one table is selected in the Database Browser pane, the available options for the first query condition are WHERE and ORDER BY.

  • If two tables are selected in the Database Browser pane, the available options for the first query condition are:

    • INNER JOIN

    • LEFT JOIN

    • RIGHT JOIN

    • FULL JOIN

    • WHERE

    • ORDER BY

    • AND

    • OR

  • After you apply a condition for a row in the SQL Criteria panel using the Enter or Tab keys, for every subsequent condition that you add, the first (leftmost) column contains only those query options that produce semantically correct SQL statements. For example, if the leftmost column of an applied condition contains an ORDER BY option, if you click + to add a new query option in a new row, the ORDER BY option from the previous row can only be followed by another ORDER BY option.

    In addition, a Join option can only be followed by another JOIN or WHERE and a JOIN option cannot follow a WHERE or ORDER BY option.

  • When defining a new query line in the SQL Criteria panel for any conditions other than a JOIN, the new SQL line does not take effect until you apply the new line. When you apply a condition, all preceding and succeeding conditions that are not applied are removed from the SQL Criteria panel. Similarly, if you click - to remove a query line, if that query line has been applied, all succeeding conditions are removed. If the query line has not yet been applied, then only that line is removed from the SQL Criteria panel.

  • When using a WHERE SQL statement with a mathematical operator, to match a string, you must include the string value in ' ' to successfully apply the condition. If you use the LIKE or NOT LIKE SQL operator to match a string, the ' ' are automatically added to the string value.

    Note:   If you click + to add a new query condition between two previously entered conditions, the available query options do not always produce semantically correct SQL statements. In this case, you must ensure that your query options are semantically correct. For best results using the SQL Criteria panel, add and apply your conditions in sequence.

Query Example Using a Left Outer Join

This example shows how to use a query to obtain supplier and product information using a LEFT JOIN. To use this example, you must set up a data source for the tutorial.mdb database. For information on setting up this data source, see Set Up the dbtoolboxdemo Data Source.

  1. Open tutorial.mdb in Database Explorer and expand the table suppliers and select the fields SupplierName, City, and Country.

  2. Expand the table producttable and select the fields productDescription and unitCost. The Data Preview pane displays a message prompting you to enter a join condition. Also, there are two empty conditions in the SQL Criteria panel on the Database Explorer Toolstrip.

  3. From the SQL Criteria panel, in the first (topmost) condition, change the first combo box for condition type to LEFT JOIN. Change the second combo box to suppliers.SupplierNumber. Change the last combo box to producttable.SupplierNumber. A left join, with the suppliers table on the left, implies that all the rows in the suppliers table are included in the final result, and the rows in suppliers that do not have a match with any row in producttable, are padded with null values in the final result.

    In the Data Preview, there are 11 rows that match the query conditions. For the supplier named The Great Teddy Bear Company, notice that there is a null in productDescription and a NaN for unitCost. This is because there is no product that is supplied by The Great Teddy Bear Company. If the condition type were INNER JOIN instead of LEFT JOIN, this row would not appear in the final result.

  4. From the SQL Criteria pane, click + at the end of the LEFT JOIN condition to add a new query condition. Change the first combo box to WHERE, the second to suppliers.Country, and the third to NOT LIKE. In the last text box, type United States and then enter the new condition using the Enter or Tab key. The query results appear in the Data Preview pane.

  5. Enter the variable name as data in the text box untitled located above the table preview, and select Import > Import to import the data displayed in the Data Preview pane into MATLAB as a variable named data. For details about using the MATLAB Variables editor, see View, Edit, and Copy Variables.

Work with Multiple Databases

  1. If you have not defined the OBDC or JDBC connection for your new data source, click Open and select ODBC or JDBC and complete the associated dialog box. For details, see Configure ODBC Data Sources or Configure JDBC Data Sources.

  2. Select Connect > Connect to select your new data source.

  3. The new data source appears in a new tab in the Database Browser pane. You can change databases by clicking the associated tab.

    You can only use Database Explorer to create SQL queries for a single database at a time.

In addition, you can work with a different catalog and schema on the same database server as the one connected to your current data source. To change to a different catalog and schema:

  • Select the catalog/schema from the drop-down list in the address bar of the Database Browser. For a database system like Microsoft SQL Server that has a hierarchy of catalogs and schemas, make sure you choose the correct value for both to access data in your tables.

Import Data to the MATLAB Workspace

  1. Use the Database Browser pane to select data from a single table or use the SQL Criteria panel to create a query and display the results in the Data Preview pane.

  2. Name the MATLAB variable by entering it in the untitled text box in the Data Preview pane.

  3. Use the Imported Data panel to define the data type for a MATLAB variable to store the data displayed in the Data Preview pane. Supported data types are:

    • Cell Array

    • Numeric

    • Structure

    • Table

    • Dataset (requires Statistics Toolbox™)

  4. Select Import > Import to import the data displayed in the Data Preview pane.

      Tip   When importing large amounts of data, Database Explorer imports data in batches. The batch size is set to 1,000 rows by default. To change the batch size, click Preferences and adjust Import batch size.

  5. (Optional) Display the imported data in the MATLAB workspace using the Variables editor. For details about using the Variables editor, see View, Edit, and Copy Variables.

  6. (Optional) Use MATLAB functions to manipulate the data.

Save Queries as SQL Code

You can save a Database Explorer query as SQL code.

  1. Use the Database Browser pane to select data from a single table or multiple tables. Then use the SQL Criteria panel to create queries and display the results in the Data Preview pane.

  2. After you have created a query using the SQL Criteria panel, select Import > Generate SQL to display the SQL code in the MATLAB Editor.

  3. Save the SQL code to a .txt or .sql file. You can then use the SQL statements to manually rebuild a query using the SQL Criteria panel. Alternatively, you can use the .sql file to import data programmatically into MATLAB by using runsqlscript.

Generate MATLAB Code

You can generate MATLAB code to automate the steps for accessing data that you display in the Data Preview pane.

  1. Connect to a data source and then use the Database Browser pane to select data from a single table or use the SQL Criteria panel to create a query and display the results in the Data Preview pane.

  2. Select Import > Generate Script to display MATLAB code in the MATLAB Editor.

  3. Save the MATLAB code to a file. You can run this code file from the command line to connect to a data source and run a query.

Was this topic helpful?