Documentation Center

  • Trial Software
  • Product Updates

Working with Visual Query Builder

Using Queries to Import Database Data

You can graphically construct and run SQL queries to import database data using:

  • Visual Query Builder (querybuilder)

  • Database Explorer

    dexplore starts Database Explorer, which is the Database Toolbox™ app for connecting to a database and importing data to the MATLAB® Workspace. Alternatively, you can start Database Explorer by selecting Database Explorer from the Database Connectivity and Reporting section of the Apps tab in the MATLAB Toolstrip. For more information on Database Explorer, after starting Database Explorer, click Help on the Database Explorer Toolstrip.

To create and run a query using Visual Query Builder to import data from a database into the MATLAB workspace:

  1. Select data from a database by clicking the Select button under Data operation. The data sources that you defined in Configuring Your Environment appear.

  2. Select dbtoolboxdemo as the data source from which to import data.

    After you select a data source, the catalog, schema, and tables for your specified data source appear in the Catalog, Schema, and Tables fields.

  3. Accept the default values <default> for the Catalog and Schema fields. Setting these fields to the default values indicates that you have not specified a catalog or schema.

      Tip   To populate the VQB Schema and Catalog fields, you must associate your user name with schemas or catalogs before starting VQB.

      • To specify a Catalog, select one from the list, and then select a schema from within that catalog. The Schema field updates to reflect your selections.

      • Alternatively, you can select a schema without specifying a catalog; that is, when the Catalog field set to <default>. The Tables field updates to reflect the schema you selected.

  4. In the Tables list, select salesVolume as the table that contains the data you want to import.

    The set of Fields (column names) in the table appears.

  5. In the Fields list, select StockNumber, January, February, and March as the fields that contain the data you want to import.

      Tip   To select more than one field, hold down the Ctrl or Shift key while selecting multiple fields. To clear an entry, use Ctrl+click.

    VQB adds each field you select to the query in the SQL statement field.

  6. Enter the name A in the MATLAB workspace variable field. A is a cell array that stores the data that the query returns.

  7. Click Execute to run the query and import the data. The Data field displays information about the query result.

  8. Double-click A in the Data area. The contents of A appear in the Variables editor, where you can view and edit the data. In this example, sales for item 400876 are 3000 in January, 2400 in February, and 1500 in March.

    For more information about using the Variables editor, see View, Edit, and Copy Variables.

    Alternatively, you can view the contents of A by entering A in the MATLAB Command Window.

Saving Queries

  1. Select Query > Save. The Save SQL Statement dialog box appears.

  2. Enter a name (without spaces) for the query into the File name field and click Save. Save the query as basic.qry.

    Note:   When you save a Select query (a query that imports data), MATLAB does not save your specified preferences or the workspace variable that contains the query results. This prevents you from inadvertently overwriting an existing variable in the MATLAB workspace when you run a saved query.

    When you save an Insert query (a query that exports data), MATLAB saves the workspace variable whose data you exported, but does not save your preferences.

Running Saved Queries

  1. Select Query > Load. The Load SQL Statement dialog box appears.

  2. Select the name of the query you want to load and click Open. The VQB fields reflect the values for the saved query.

  3. Run a Select query to import data into the MATLAB workspace, or an Insert query to export data from the MATLAB workspace.

    • To run a Select query, use the MATLAB workspace variable field to assign a variable to the data and click Execute.

    • For an Insert query, the saved query may include a workspace variable, which appears as part of the MATLAB command field. Type that variable name or enter a new name in the MATLAB workspace variable field. Press Return or Enter to see the updated MATLAB command.

  4. Click Execute to run the query.

      Tip   You can generate a file that runs the query from the MATLAB Command Window in the future. For more information, see Saving Queries in Files.

Editing Queries

Edit a query using one of the following options:

  • Changing your selections

  • Editing the SQL statement field

  • Editing the MATLAB command field

Clearing Variables from the VQB Data Area

Variables in the Data area include those you assigned in the Command Window and those that contain query results. The variables do not appear in the Data area until you execute a query. They then remain in the Data area until you clear them. To clear the variables, run the clear function in the Command Window.

Using Queries to Export Data to Databases

To build, run, and save a query that exports data from the MATLAB workspace into new rows in a database:

  1. Select Data Operation > Insert to select data to export.

  2. Select dbtoolboxdemo as the data source to which to export data from the Data source list box. The Catalog, Schema, and Tables fields for dbtoolboxdemo appear.

  3. Do not specify values for Catalog and Schema.

  4. In the Tables list box, select inventoryTable as the table to which you want to export data from the MATLAB software.

    The set of Fields (column names) in your selected table appears.

  5. In the Fields list box, select productNumber, Quantity, and Price as the fields to which you want to export data from the MATLAB software.

    VQB adds each field you select to the query in the MATLAB command field.

  6. In the MATLAB workspace, assign the data you want to export to a cell array, export_data.

    export_data = {14,1500,18.50};
    

  7. In the MATLAB workspace variable field, enter the name of the variable containing data to export, export_data. Press Enter or Return to view the MATLAB command that exports the data.

  8. Click Execute to run the query to export the data.

    Information about the exported data appears in the Data area.

  9. View the inventoryTable table in the Microsoft® Access™ database to verify the query results.

  10. To save this query, select Query > Save and name it export.qry.

Exiting Visual Query Builder

To exit Visual Query Builder, select Query > Exit.

Was this topic helpful?