Mathworks DATABASE TOOLBOX 3 user guide

Database Toolbox™

User’s Guide

How to Contact The MathWorks

www.mathworks. comp.soft-sys.matlab Newsgroup www.mathworks.com/contact_TS.html Technical Support

suggest@mathworks.com Product enhancement suggestions bugs@mathwo doc@mathworks.com Documentation error reports service@mathworks.com Order status, license renewals, passcodes info@mathwo

com

rks.com

Web

Bug reports

Sales, prici

ng, and general information

508-647-7000 (Phone)

508-647-7001 (Fax)

The MathWorks, Inc. 3 Apple Hill Drive Natick, MA 01760-2098

For contact information about worldwide offices, see the MathWorks Web site.

Database Toolbox™ User’s Guide

The software described in this document is furnished under a license agreement. The software may be used or copied only under the terms of the license agreement. No part of this manual may be photocopied or reproduced in any form without prior written consent from The MathW orks, Inc.

FEDERAL ACQUISITION: This provision applies to all acquisitions of the Program and Documentation by, for, or through the federal government of the United States. By accepting delivery of the Program or Documentation, the government hereby agrees that this software or documentation qualifies as commercial computer software or commercial computer software documentation as such terms are used or defined in FAR 12.212, DFARS Part 227.72, and DFARS 252.227-7014. Accordingly, the terms and conditions of this Agreement and only those rights specified in this Agreement, shall pertain to and govern theuse,modification,reproduction,release,performance,display,anddisclosureoftheProgramand Documentation by the federal government (or other entity acquiring for or through the federal government) and shall supersede any conflicting contractual terms or conditions. If this License fails to meet the government’s needs or is inconsistent in any respect with federal procurement law, the government agrees to return the Program and Docu mentation, unused, to The MathWorks, Inc.

Trademarks

MATLAB and Simulink are registered trademarks of The MathWorks, Inc. See

www.mathworks.com/trademarks for a list of additional trademarks. Other product or brand

names may be trademarks or registered trademarks of their respective holders.

Patents

The MathWorks products are protected by one or more U.S. patents. Please see

www.mathworks.com/patents for more information.

Revision History

May 1998 Online Only New for Version 1 for MATLAB®5.2 July 1998 First Printing For Version 1 Online only June 1999 Revised for Version 2 (Release 11) December 1999 Second printing For Version 2 (Release 11) Online only September 2000 Revised for Version 2.1 (Release 12) June 2001 Third printing Revised for Version 2.2 (Release 12.1) July 2002 Online only Revised for Version 2.2.1 (Release 13) November 2002 Fourth printing Version 2.2.1 June 2004 Fifth printing Revised for Version 3.0 (Release 14) October 2004 Online only Revised for Version 3.1 (Release 14SP1) March 2005 Online only Revised for Version 3.0.2 (Release 14SP2) September 2005 Online only Revised for Version 3.1 (Release 14SP3) March 2006 Online only Revised for Version 3.1.1 (Release 2006a) September 2006 Online only Revised for Version 3.2 (Release 2006b) October 2006 Sixth printing Revised for Version 3.2 (Release 2006b) March 2007 Online only Revised for Version 3.3 (Release 2007a) September 2007 Seventh printing Revised for Version 3.4 (Release 2007b) March 2008 Online only Revised for Version 3.4.1 (Release 2008a) October 2008 Online only Revised for Version 3.5 (Release 2008b) March 2009 Online only Revised for Version 3.5.1 (Release 2009a) September 2009 Online only Revised for Version 3.6 (Release 2009b) March 2010 Online only Revised for Version 3.7 (Release 2010a)

Before You Begin

Working with Databases ........................... 1-2

Connecting to Databases Supported Platforms Supported Databases Supported Drivers Structured Query Language (SQL)

................................. 1-3

........................... 1-2

............................... 1-2

.............................. 1-2

................... 1-3

Contents

Supported Data Types

Data Retrieval Restrictions

Spaces in Table Names or Column Names Quotation Marks in Table Names or Column Names Reserved Words in Column Names

............................. 1-4

......................... 1-6

............. 1-6

................... 1-6

Working with Data Sources

Setting up ODBC Data Sources ..................... 2-2

Setting up JDBC Data Sources

Accessing Existing JDBC Data Sources

Modifying Existing JDBC Data Sources

...................... 2-3

.............. 2-4

.............. 2-5

..... 1-6

Removing JDBC Data Sources

Troubleshooting JDBC Driver Problems

...................... 2-6

............. 2-7

Database Toolbox Functions vs. Visual Query

When to Use Visual Query Build er .................. 3-2

Tasks You Can Perform Using Visual Query Builder Limitations of Visual Query Builder

.................. 3-2

Builder

..... 3-2

When to Use Database Toolbox Functions

............ 3-3

Using Visual Query Builder

Getting Started with Visual Query Builder ........... 4-2

What Is Visual Query Builder? Using Queries to Import Data Using Queries to Export Data

Working with Preferences

Specifying Preferences Saving Preferences

Displaying Query Results

How to Display Q uery Results Displaying Data Relationally Charting Q u ery Results Displaying Query Results in an HTML Report Using the MATLAB

Customize Display of Query Results

............................. 4-6

................................ 4-9

............................ 4-14

Report Generator Software to

...................... 4-2

....................... 4-2

....................... 4-4

.......................... 4-6

.......................... 4-10

....................... 4-10

........................ 4-10

.......... 4-16

................ 4-17

vi Contents

Fine-Tuning Queries Using Advanced Query

Options

Retrieving All Occurrences vs. Unique Occurrences of

Data Retrieving Data That Meets Specified Criteria Grouping Statements Displaying Results in a Specified Order UsingHavingClausesToRefineGroupByResults

......................................... 4-22

.......................................... 4-22

.......... 4-24

.............................. 4-27

............... 4-31

...... 4-34

Creating Subqueries for Values from Multiple Tables .... 4-37

Creating Queries That Include Results from Multiple

Tables

Additional Advanced Query Options

Retrieving BINARY and OTHER Sun Java Data

Types

........................................ 4-42

.................. 4-45

........................................... 4-46

Importing and Exporting BOOLEAN Data

Importing BOOLEAN Data from Databases to the MATLAB

Workspace

Exporting BOOLEAN Data from the MATLAB Workspace

to Databases

Saving Queries in Files

About Generated Files VQB Q uery Elements in Generated Files

..................................... 4-48

................................... 4-51

............................. 4-52

........... 4-48

.............. 4-53

Using Database Toolbox Functions

Getting Started with Database Toolbox Functions .... 5-2

Importing Data from Databases into the MATLAB

Workspace

Viewing Information About Imported Data

...................................... 5-3

.......... 5-5

ExportingDatafromtheMATLABWorkspacetoaNew

Record in a Database

Replacing Existing Data in Databases with Data

Exported from the MATLAB Workspace

Exporting Multiple Records from the MATLAB

Workspace

...................................... 5-13

............................ 5-7

........... 5-11

vii

Retrieving BINARY or OTHER Sun Java SQL Data

Types

........................................... 5-17

Working with Database Metadata

Accessing Metadata Resultset Metadata Objects

Using Driver Functions

About Objects and Methods in the Database Toolbox

Software

........................................ 5-27

............................... 5-19

......................... 5-24

............................ 5-25

................... 5-19

Function Reference

Utilities ........................................... 6-2

Database Connection

SQL Cursor

Data Import

....................................... 6-3

.............................. 6-2

viii Contents

Database Metadata Object

Data Export

Driver Object

Drivermanager Object

Resultset Object

Resultset Metadata Object

....................................... 6-5

...................................... 6-5

............................. 6-6

................................... 6-6

.......................... 6-4

.......................... 6-7

Visual Query Builder .............................. 6-7

Functions — Alphabetical List

Examples

Visual Query Builder GUI: Importing Data ........... A-2

Visual Q uer y Builder GUI: Displaying Results

Visual Query Builder GUI: Advanced Query Options

Visual Query Builder GUI: Exporting Data

Using Database Toolbox Functions

.................. A-2

........ A-2

........... A-2

.. A-2

Index

x Contents

Before You Begin

• “Working with Databases” on page 1-2

• “Supported Data Types” on page 1-4

• “Data Retrieval Restrictions” on page 1-6

1 Before You B egin

Working with Databases

In this section...

“Connecting to Databases” on page 1-2

“Supported Platforms” on page 1-2

“Supported Databases” on page 1-2

“Supported Drivers” on page 1-3

“Structured Query Language (SQL)” on page 1-3

Connecting to Databases

Before you can use this toolbox to connect to a database, you must set up data sources. For more information, see “Configuring Your Environment” in the Database Toolbox™ Getting Started Guide.

Supported Platforms

This toolbox runs on all platforms that the M ATLAB®software supports.

1-2

For more information, s ee Database Toolbox system requirements at

http://www.mathworks.com/products/database/requirements.html.

Note This toolbox does not support running MATLAB software sessions with the

-nojvm startup option enabled on UNIX

trademark of the Open Group in the United States and other countries.)

platforms. (UNIX is a registered

Supported Databases

This to olbox supports importing and exporting data from any ODBC- and/or JDBC-compliant database management system, including:

Working with Databases

• IBM DB2

• IBM®Informix

• Ingres

• Microsoft®Access™

• Microsoft

Excel

• Microsoft®SQL Server™

• MySQL

• Oracle

• Postgre SQL (Postgres)

• Sybase

SQL Anywhere

• Sybase SQL Server

Ifyouareupgradinganearlierversion of a database, you need not do anything special for this toolbox. Simply configure the data sources for the new version of the database application as you did for the original version.

Supported Drivers

This toolbox requires a database driver. Typically, you install a driver when you install a database. For instructions about how to install a database driver, consult your database administrator.

On Microsoft

Windows®platforms, the toolbox supports Open Database Connectivity (ODBC) drivers and Sun™ Java™ Database Connectivity (JDBC) drivers.

On UNIX platforms, the toolbox supports Java Database Connectivity (JDBC) drivers. If your database does not ship with JDBC drivers, download drivers from the Sun JDBC Web Site at

http://industry.java.sun.com/products/jdbc/drivers.

Structured Query Language (SQL)

This toolbox supports American N ational Standards Institute (ANSI) standard SQL commands.

1-3

1 Before You B egin

Supported Data Types

You can import the follow ing data types into the MATLAB workspace and export them back to your database:

•

BOOLEAN

• CHAR

• DATE

• DECIMAL

• DOUBLE

• FLOAT

• INTEGER

• LONGCHAR

• NUMERIC

• REAL

1-4

• SMALLINT

• TIME

• TIMESTAMP

• TINYINT

Note The D atabase Toolbox software interprets this data type as BOOLEAN

and imports it into the MATLAB workspace as logical true (1) or false

. For more information about how the Database Toolbox software

(0)

handles on page 4-48.

• VARCHAR

• NTEXT

BOOLEAN data, see “Importing and Exporting BOOLEAN Data”

Supported Data Types

You can import data of types n ot included in this list into the MATLAB workspace. However, you may need to manipulate such data before you can process it in MATLAB.

1-5

1 Before You B egin

Data Retrieval Restrictions

In this section...

“SpacesinTableNamesorColumnNames”onpage1-6

“Quotation Marks in Table Names or Column Names” on page 1-6

“Reserved Words in Column Names” on page 1-6

Spaces in Table Names or Column Names

Microsoft Access supports the use of spaces in table and column names, but most other databases do not. Queries that retrieve data from tables and fields whose n ames contain spaces require delimiters around table names and field names. In Access™, enclose the table names or field names in quotation marks, for example, such as brackets, [ ]. In Visual Query Builder, table names and field names that include spaces appear in quotation marks.

"order id". Other databases use different delimiters,

1-6

Quotation Marks in Table Names or Column Names

Do not include quotation marks in table names or column names. The Database Toolbox softw are does not support data retrieval from table and column names that contain quotation marks.

ReservedWordsinColumnNames

You cannot use the Database Toolbox software to import or export data in columns whose names contain database reserved words, such as

TABLE.

DATE or

Working with Data Sources

• “Setting up ODBC Data Sources” on page 2-2

• “Setting up JDBC Data Sources” on page 2-3

• “Accessing Existing JDBC Data Sources” on page 2-4

• “Modifying Existing JDBC Data Sources” on page 2-5

• “Removing JDBC Data So urce s” on page 2-6

• “Troubleshooting JDBC Driver Problems” on page 2-7

2 Working with Data Sources

Setting up ODBC Data Sources

For instructions on setting up OD BC data sources, see “Setting Up Data Sources for Use with ODBC D rivers” in the Database ToolboxGetting Started Guide.

2-2

Setting up JDBC Data Sources

For instructions on setting up JDBC data sources, see “Setting Up Data Sources for Use w ith JDBC Drivers” in the Database ToolboxGetting Started Guide.

Setting up JDBC Data Sources

2-3

2 Working with Data Sources

Accessing Existing JDBC Data Sources

To access an existing data source from Visual Query Builder in future MATLAB software sessions:

1 In Visual Query Builder, select Query > Define JDBC data source.

2 In the Define JDBC data sources dialog box, click Use Existing File.

3 In the Specif

MAT-file tha

The data sources in the selected MAT-file appear in the Define JDBC data sources dialog box.

4 Click OK to close the Define JDBC data sources dialog box. The data

sources now appear in the Visual Query Builder Data source list.

y Existing JDBC data source MA T-file dialog box, select the t contains the data sources you want to use and click Open.

2-4

Modifying Existing JDBC Data Sources

1 Access the existing data source as described in “Accessing Existing JDBC

Data Sources” on page 2-4.

2 Select the data source in the Define JDBC Data Sources dialog box .

3 Modify the data in the Driver and URL fields.

4 Click Add/Update.

5 Click OK to save your changes and close the Define JDBC data sources

dialog box.

ModifyingExistingJDBCDataSources

2-5

2 Working with Data Sources

Removing JDBC Data Sources

1 Access the existing data source as described in “Accessing Existing JDBC

Data Sources” on page 2-4.

2 Click Remove.

3 Click OK to sav

dialog box.

eyourchangesandclosetheDefineJDBCdatasources

2-6

Troubleshooting JDBC Driver Problems

This section describes how to address comm on data source access problems, in which selecting a data source in the Visual Query Builder list produces an error, or the data source is not in the list as expected. There are several potential causes for these issues:

• The database is unavailable, or there are con n ectiv ity problems. Try

reselecting the data source in VQB. If you are still unable to access the data source, contact your database administrator.

Troubleshooting JDBC Driver Problems

• You ran the

you defined a JDBC data source. In this case, redefine the data source by following the instructions in “Setting Up Data Sources for Use with JDBC Drivers” in the Database Toolbox Getting Started Guide.

clear all command in the MATLAB Command Window after

2-7

2 Working with Data Sources

2-8

Database Toolbox Functions vs. Visual Query Builder

• “When to Use Visual Query B uilde r” on page 3-2

• “When to U se Database Toolbox Functions” on page 3-3

3 Database Toolbox™ Functions vs. Visual Query Builder

When to Use Visual Query Builder

In this section...

“Tasks You Can Perform Using Visual Query Builder” on page 3-2

“Limitations of Visual Query Builder” on p age 3-2

Tasks You Can Perform Using Visual Query Builder

You can use Visual Query Builder to:

• Import data from relational databases into the MATLAB workspace by

selecting information from lists to build queries.

• Display retrieved information in relation al tables, reports, and charts.

• Export data from the MATLAB workspace into new records in a database.

• Easily build SQL queries and exchange data between databases and the

MATLAB workspace.

3-2

• View and edit SQL statements for queries generated with VQB.

• Automatically generate a MATLAB file that consists of Database Toolbox

functions that perform queries y ou built using VQB.

Limitations of Visual Query Builder

• You cannot use Visual Query Builder to replace existing data in a database

with data from the MATLAB workspace. Use the

• You cannot use Visual Query Builder to export binary data. Instead, use

the

fastinsert function.

update function instead.

When to Use Database Toolbox™ Functions

When to Use Database Toolbox Functions

Database Too lbox functions can do everything that Visual Query Builder can, and more. You can use these functions to:

• Replace existing records in databases with data from the MATLAB

workspace.

• Retrieve large data sets or partial data sets in a single

in discrete amounts using multiple fetches.

• Dynamically import data into the MATLAB workspace.

• Modify SQL queries in MATLAB statements.

• Write MATLAB files and applications that access databases.

• Perform other functions that are not available with Visual Query Builder,

including:

fetch command, or

- Exporting binary data or other data types that you can import into the

MATLAB workspace, but cannot export from the MATLAB workspace using VQB.

- Accessing database metadata.

3-3

3 Database Toolbox™ Functions vs. Visual Query Builder

3-4

Using Visual Query Builder

• “Getting Started w ith Visual Query Builder” on page 4-2

• “Working with Preferences” on page 4-6

• “Displaying Query Results” on page 4-10

• “Fine-Tuning Queries Using Advanced Query Options” on page 4-22

• “Retrieving BINARY and OTHER Sun Java Data Types” on page 4-46

• “Importing and Exporting BOOLEAN Data” on page 4-48

• “Saving Queries in Files” on page 4-52

4 Using Visual Query Builder

Getting Started with Visual Query Builder

In this section...

“What Is Visual Query Builder?” on page 4-2

“Using Queries to Import Data” on page 4-2

“Using Queries to Export Data” on page 4-4

What Is Visual Query Builder?

Visual Q uery Builder (VQB) is an easy-to-use graphical u ser interface (GUI) for exchanging data w ith your database. You can use VQB to:

• Build queries to retrieve data by selecting information from lists instead of

using MATLAB functions.

• Store data retrieved from a d atabase in a MATLAB cell array, structure, or

numeric matrix.

4-2

• Process the retrieved data using the MATLAB suite of functions.

• Display retrieved information in relation al tables, reports, and charts.

• Export data from the MATLAB workspace into new rows in a database.

Using Queries to Import Data

ThefollowingstepssummarizehowtouseVQBtoimportdata.

To start the Visual Query Builder, type querybuilder at the MATLAB prompt.

*Required step

Getting Started with Visual Query Builder

12 View query results in table,

chart, and report formats.

8 Set preferences

for data retrieval.

13 Save, load,

and run queries, and generate M-files.

6 Refine query.

7 View SQL

statement.

9* Assign variable

for results.

1* Specify Select. 2* Select data

source.

3 Select catalog and schema.

4* Select tables.

5* Select fields to retrieve.

11 Double-click to view query results

in MATLAB Array Editor.

10* Run query.

For a step-by-step example of how to use queries to import data into the MATLAB workspace from a database, see “Using Queries to Import Database

4-3

4 Using Visual Query Builder

Data into the MATLAB Workspace” in the Database Toolbox Getting Started Guide.

Using Queries to Export Data

The following steps summarize how to use VQB to export data.

4-4

To start the Visual Query Builder, type querybuilder at the MATLAB prompt.

*Required step

Getting Started with Visual Query Builder

9 Save, load, and

run queries, set preferences for exporting NULLs, and generate M-files.

7 View MATLAB

statement.

6* Specify variable

containing data to export.

1* Specify Insert. 2* Select data

source.

3 Select catalog and schema.

4* Select tables.

5* Select fields to which to export data.

8* Run query.

For a step-by-step example of how to use queries to export data from the MATLAB workspace to a database, see “Using Queries to Export MATLAB WorkspaceDatatoaDatabase”intheDatabase Toolbox Getting Started Guide.

4-5

4 Using Visual Query Builder

Working with Preferences

In this section...

“Specifying Preferences” on page 4-6

“Saving Preferences” on page 4-9

Specifying Preferences

This section describes how to set VQB preferences to specify:

• How

• The format of data retrieved from databases

• The method of error notification

1 Click Query > Preferences. The Preferences dialog box appears.

NULL data in a database is represented after you import it into the

MATLAB workspace

4-6

Working with Preferences

4-7

4 Using Visual Query Builder

2 Specify the Preferences settings as described in the following table.

Preference Value Description

Read NULL numbers as

Data return format

Error handling

numeric

report

If you accept the default value for this field, NULL data imported from databases into the M ATLAB workspace appears as Setting this field to 0 causes MATLABworkspacetoappearas

Select a data format based on the type of data you are importing, memory considerations, and your preferred method of working with retrieved data.

Cell arrays and structures support mixed data types, but require more memory and process more slowly than numeric matrices. Select

• The data you are retrieving is numeric, or

• You need to convert nonnumeric data to the format specified

• Set this field to store or empty to direct errors to a dialog box

NaN.

NULL data imported into the

0s.

numeric if:

in the Read NULL numbers as field.

rather than to the MATLAB Command Window.

4-8

• Set this field to

Command Window.

report to display query errors in the MATLAB

3 Click OK.

4 Assign the query results to a workspace variable, A.

5 Click Execute to rerun the query.

Information about the retrieved data appears in the Data area.

6 To see the results, enter A in the Command Window.

125970 1400 1100 981 212569 2400 1721 1414 389123 1800 1200 890 400314 3000 2400 1800 400339 4300 0 2600 400345 5000 3500 2800 400455 1200 900 800 400876 3000 2400 1500 400999 3000 1500 1000 888652 0 900 821

Working with Preferences

values appear as 0sinsteadofNaNs.

NULL

For more information about Preferences, see the

setdbprefs function

reference page.

Saving Preferences

Preferences apply only to the current MATLAB software session. They are not saved with queries. Default Preferences apply when you start a new session, orafteryouclearallvariables(using,forexample,the It is a good practice to check Preferences settings before you run queries.

clear all command).

4-9

4 Using Visual Query Builder

Displaying Query Results

In this section...

“How to Display Query Results” on page 4-10

“Displaying Data Relationally” on page 4-10

“Charting Query Results” on page 4-14

“Displaying Query Results in an HTML Report” on page 4-16

“Using the MATLAB®Report Generator SoftwaretoCustomizeDisplayof Query Results” on page 4-17

How to Display Query Results

To display query results, perform one of the following actions:

• Enter the variable name to which to assign the query results in the

MATLAB Command W indow.

4-10

• Double-click the variable in the VQB Data area to view the data in the

Variable Editor.

The examples in this section use the saved query configure this query:

1 Click Quer y > Preferences,andsetRead NULL numbers as to 0.

2 Click Query > Load.

3 In th

4 In VQB, enter a value for the M ATLAB workspace variable,for

e Load SQL Statement dialog box, select

name

example,

field and click Open.

A,andclickExecute.

basic.qry.Toloadand

basic.qry from th e File

Displaying Data Relationally

To display the results of basic.qry:

1 Execute basic.qry.

2 Click Display > Data.

The query results appearinafigurewindow.

Displaying Query Results

This display shows only unique values for each field, so you should not read each row as a single record. In this examp le , there are 10 entries for

StockNumber, 8 entries for January and February, and 10 entries for March. The number of entries in each field corresponds to the number of

unique values in the field.

ck a value in the figure window, for example, StockNumber

3 Cli

ee its ass ociated values.

to s

400876,

4-11

4 Using Visual Query Builder

The data associated with the selected value appears in bold font and is connected with a dotted line. The data shows that sales for item 400876 are 3000 in January, 2400 in February, and 1500 in March.

4-12

4 As another example, click 3000 under January. It shows three different

items with sales of 3000 units in January: 400314, 400876, and 400999.

Displaying Query Results

4-13

4 Using Visual Query Builder

Charting Query R

To chart the resu

1 Click Display >

The Visual Query Builder Charting dialog box appears.

lts of

esults

basic.qry:

Chart.

4-14

2 Select a type of chart from the Charts list. In this example, choose a pie

chart by specifying

A preview of the pie chart, with each stock item displayed in a different color, appears at the bottom of the dialog box.

pie.

Displaying Query Results

3 SelectthedatatodisplayinthechartfromtheXdata, Ydata,andZ

data list boxes. In this example, select

March from the Xdatalist box to

display a pie chart of M arch data.

The pie chart preview now shows percentages for March data.

4 To display a legend, which maps colors to the stock numbers, select the

Show legend check box.

The Legend labels field becomes active.

5 Select StockNumber from the Legend labels list box.

A legend appears in the chart prev iew. D rag and move the legend in the preview as needed.

4-15

4 Using Visual Query Builder

Displaying Query Results in an HTML Report

To display results for basic.qry in an HTML report, click Display > Report.

The query results appear as a table in a Web browser. Each row represents a record from the database. In this example, sales for item 400876 are 3000 in January, 2400 in February, and 1500 in March.

6 Click Close to close the Charting dialog box.

4-16

Displaying Query Results

Note Because some browsers do not start automatically, you may need to open your Web browser before displaying the query results.

Using the MATLAB Report Generator Software to Customize Display of Query Results

To use the MATLAB®Report G enerator™ software to customize the display of the results of

1 Click Display > Report Generator.

2 The Report Explorer opens, listing sample report templates

that you can use to create custom reports. Select the template

matlabroot/toolbox/database/vqb/databasetlbx.rpt from the Options

paneinthemiddleoftheReportExplorerwindow.

basic.qry:

4-17

4 Using Visual Query Builder

4-18

3 Open the report template for editing by clicking Open a Report file or

stylesheet.

a In the Outline pane on the left, under Report Generator >

databasetlbx.rpt,select

b In the Properties pane on the right, do the following:

Table.

Displaying Query Results

i In Table Content > Workspace Variable Name,enterthename

ofthevariabletowhichyouassignedthequeryresultsinVQB,for example,

'A'.

ii Under Header/Footer Options,setNumber of header rows to

c Click Apply.

4 Click File > Report to run the report.

The report appears in a Web browser.

ld names do not automatically display as column headers in the report.

5 Fie

display the field names:

4-19

4 Using Visual Query Builder

a Modify the workspace variable A as follows:

A = [{'Stock Number', 'January', 'February', 'March'};A]

b In the MATLAB Report Generator properties pane, change Number of

header rows to

field names as headings.

Each row represents a record from the database. For example, sales for item 400876 are 3000 in January, 2400 in February, and 1500 in March.

1 and regenerate the report. The report now displays

4-20

Displaying Query Results

For more information about the MATLAB Report Generator product, click the Help button in the R eport Explorer or see the MATLAB Report Generator documentation.

Note Because some browsers are not configured to launch automatically, you may need to open your Web browser before displaying the report.

4-21

4 Using Visual Query Builder

Fine-Tuning Queries Using Advanced Quer y Options

In this section...

“Retrieving All Occurrences vs. Unique Occurrences of Data” on page 4-22

“Retrieving Data That Meets Specified Criteria” on page 4-24

“Grouping Statements” on page 4-27

“Displaying Results in a Specified Order” on page 4-31

“Using Having Clauses To Refine Group By Results” on page 4-34

“Creating Subqueries for Values f r om Multiple Tables” on page 4-37

“Creating Queries That Include Results from Multiple Tables” on page 4-42

“Additional Advanced Query Options” on page 4-45

Note For more information about advanced query options, select Help in any of the dialog boxes for the options.

4-22

Retrieving All Occurrences vs. Unique Occurrences of Data

To use the dbtoolboxdemo data source to demonstrate how to retrieve all versus distinct occurrences of data:

1 Set the Data return format preference to cellarray.

2 Set Read NULL numbers as to NaN.

3 In D

4 In Data source,selectdbtoolboxdemo.

5 I

6 In Fields,selectJanuary.

ata operation, choos e Select.

Do not specify Catalog or Schema.

n Tables,select

SalesVolume.

Fine-Tuning Queries Using Advanced Query Options

7 To retrieve all occurrences of January:

a In Advanced query options, select All.

b Assign the query results to the MATLAB workspace variable Al l.

c Click Execute to run the query.

8 To retrieve only unique occurrences of data:

a In Advanced query options,selectDistinct.

b Assign the query re sults to a MATLAB workspace variable Dist inct.

c Click Execute to run the query.

9 In the MATLAB Command Window, enter A ll, Distinct to display the

query results:

4-23

4 Using Visual Query Builder

Retrieving Data That Meets Specified Criteria

Use basic.qry and the Where field in Advanced query options to retrieve stock numbers greater than 400000 and less than 500000:

The value 3000 appearsthreetimesinAll, but appears only once in

Distinct.

1 Load basic.qry.

2 Set the Data return format preference to cellarray.

3 Set Read NULL numbers as to NaN.

4 In Advanced query options,clickWhere.

The WHERE Clauses dialog box appears.

4-24

5 In Fields, select the field whose values you want to restrict, StockNumber.

6 In Condition,specifythatStockNumber must be greater than 400000.

a Select Relation.

b In the drop-down list to the right of Relation,select>.

c In the field to the right of the drop-down list, enter 4 00000.

The WHERE Clauses dialog box now looks as follows.

d Click Apply.

Fine-Tuning Queries Using Advanced Query Options

Theclausethatyoudefined, Current clauses area.

StockNumber > 400000,appearsinthe

4-25

4 Using Visual Query Builder

7 Add the condition that StockNumber must also be less than 500000.

a In Current clauses, select StockNumber > 400000.

b In Current clauses,clickEdit or double-click the StockNumber entry.

c For Operator, select AND.

d Click Apply.

The Current clauses field n ow displays:

StockNumber > 400000 AN D

e In Fields, select StockNumber.

f In Condition, select Relation.

g In the drop-down list to the right of Relation,select<.

h In the field to the right of the drop-down list, enter 500000.

i Click Apply.

4-26

The Current clauses field n ow displays:

StockNumber > 400000 AN D StockNumber < 500000

8 Click OK.

The WHERE Clauses dialog box closes. The Where field and SQL statement display the Where Clause you specified.

9 Assign the query results to the MATLAB workspace variable A.

10 Click Execute.

Fine-Tuning Queries Using Advanced Query Options

11 To view the results, enter A in the Command Window:

12 Save this query as basic_where.qry.

Grouping Statements

Use the WHERE Clauses dialog box to group query statements. In this example, modify February, or March exceed units.

basic_where.qry to retrieve data where sales in January,

1500 units, if sales in each month exceed 1000

To modify

1 Click Where in VQB. The WHERE Clauses dialog box appears.

2 Modify the query to retrieve data if sales in January, February, or March

basic_where.qry:

exceed 1500 units.

4-27

4 Using Visual Query Builder

a In Current clauses, select StockNumber < 500000 and click Edit.

b For Operator,selectOR and click Apply.

c In Fields, select January.ForRelation, select > and enter 1500 in its

field. For Operator, select

d Repeat step c twice, specifying February and March in Fields.

OR.ClickApply.

The WHERE Clauses dialog box now looks as follows.

4-28

3 Group the criteria that require sales in each month to exceed 1500 units.

a In Current clauses, select the statement January > 1500 OR.Click

Shift+click to select

b Click Group.

An opening parenthesis is added before parenthesis is added after

February > 1500 OR and March > 1500 also.

January and a closing

March > 1500, indicating that these

statements are evaluated as a group.

Fine-Tuning Queries Using Advanced Query Options

4 Modify the query to retrieve data if sales in each month exceed 1000 units.

a Select March > 1500 ) in Current clauses and click Edit.

b Select AND for Operator and click Apply.

c Select January in Fields. Select > for Relation and enter 1 000 in its

field. Select

AND for Operator.ClickApply.

d Repeat step c twice, specifying February and March in Fields.

The WHERE Clauses dialog box now looks as follows.

4-29

4 Using Visual Query Builder

e Click OK.

4-30

The WHERE Clauses dialog box closes. The SQL statement dialog box displays the modified w here clause.

5 Assign t

6 Click Execute to run the query.

he query results to the MATLAB workspace variable

AA.

Fine-Tuning Queries Using Advanced Query Options

7 To view the results, enter AA in the MATLAB Command Window.

Removing Gr

ouping of Statements

To use the WHERE Clauses dialog box to remove grouping criteria from the previous example:

1 In Current clauses, select (January > 1000 AND.

2 Click Shi

3 Click Ungroup.

ft+click to select

February > 1000 AND an d March > 1000) also.

The parentheses are removed from the statements, indicating that their grouping is removed.

Displaying Results in a Specified Order

Use Order by in Advanced query options to specify the order in which query results display.

This example uses the Data That Meets Specified Criteria” on page 4-24. The results of

basic_where.qry are sorted so that January is the primary sort field,

February the secondary, and March the last. Results for January and February appear in ascending order, and results for March appear in descending order.

basic_where.qry query you created in “Retrieving

To specify the order in which results appear in

basic_where.qry:

4-31

4 Using Visual Query Builder

1 Load basic_where.qry.

2 Set the Data return format preference to cellarray.

3 Set Read NULL numbers to NaN.

4 In Advanced query options, select Order by.

The Order By Clauses dialog box appears.

4-32

5 Enter values for the Sort key number and Sort order fields for the

appropriate Fields.

To specify

January as the primary sort field and display results in

ascending order:

a In Fields, select January.

b For Sort key number,enter1.

c For Sort order,selectAscending.

d Click Apply.

The Current clauses area now displays:

January ASC

Fine-Tuning Queries Using Advanced Query Options

6 To specify Februa ry as the second sort field and display results in

ascending order:

a In Fields,selectFebruary.

b For Sort key number,enter2.

c For Sort order,selectAscending.

d Click Apply.

The Current clauses area now displays:

January ASC February ASC

7 To specify March as the third sort field and display res ults in descending

order:

a In Fields,selectMarch.

b For Sort key number,enter3.

c For Sort order,selectDescending.

d Click Apply.

The Current clauses area now displays:

January ASC February ASC March DESC

8 Click OK.

The Order By Clauses dialog box closes. The Order by field and the SQL statement in VQB display the specified Order By clause.

9 Assign the query results to the MATLAB workspace variable B.

10 Click Execute to run the query.

4-33

4 Using Visual Query Builder

11 To view the results, enter B in the MATLAB C ommand Window. Enter A to

display the unordered query results and compare them to look as follows:

B. Your results

4-34

For B, results are first sorted by January sales, in ascending order. The lowest value for first. The highest value, 5000 (for item number for 400345), appears last.

For items 400999, 400314, and 400876, Therefore, the second sort key, appear in ascending order: 1500, 2400, and 2400 respectively.

For items 400314 and 400876, sort key, 1800 and 1500, respectively.

March sales, applies. March sales appear in descending order:

January sales, 1200 (for item number 400455), appears

January sales were 3000.

February sales, applies. February sales

February sales were 2400, so the third

Using Having Clauses To Refine Group By Results

UsingtheHAVINGClausesDialogBox

Use the Having function to refine the results of a Group By clause.

Fine-Tuning Queries Using Advanced Query Options

After specifying a group-by clause in Advanced query options,click Having. The HAVING Clauses dialog box appears.

1 From the Fields list box, select the entry whose value to restrict.

2 Define the Condition for the selected field, as described in “Retrieving

Data That Meets Specified Criteria” on page 4-24.

3 Select Operator to add another condition.

4 Click Apply to create the clause.

The subquery appears in the Current clauses area.

5 Repeat steps 1 through 4 to add more conditions as needed.

6 Change the clauses as needed:

• To edit a clause:

a Select the clause from Current clauses and click Edit.

b Modify the Fields, Condition,andOperator fields as needed.

c Click Apply.

• To group clauses:

4-35

4 Using Visual Query Builder

Example: Using Having Clauses

This example restricts the results from basic_where.qry to sales greater than 2000 for January and February:

d Select the clauses to group from Current clauses.UseCtrl+click or

Shift+click to select multiple clauses.

e Click Group. Parentheses are added around the set of clauses.

To ungroup clauses, select the clauses and then click Ungroup.

• To delete a clause, Select the clause from Current clauses and click

Delete.UseCtrl+click or Shift+click to select multiple clauses.

7 Specify a subquery in the HAVING Clauses dialog box, as needed. For

more information, see “Creating Subqueries for Values from Multiple Tables” on page 4-37.

8 Click OK.

The Having Clauses dialog box closes. The SQL statement in the Visual Query Builder dialog box updates to reflect the specified having clause.

4-36

1 In Advanced query options,clickHaving. The HAVING Clauses dialog

box appears.

2 For January:

a Select > as the Relation Condition.

b Enter 2000 as the Relation value.

c Select the AND Operator.

d Click Apply.

3 For February:

a Select > as the Relation Condition.

b Enter2000 as the Relation value.

c Click Apply. The HAVING Clauses dialog box appears as follows.

Fine-Tuning Queries Using Advanced Query Options

4 Click OK.

The Having Clauses dialog box closes. The SQL statement field in the VQB dialog box reflects the specified Having clause.

5 Assign a MATLAB workspace variable C,andclickExecute to run

the query.

Compare these results to those in “Displaying Results in a Specified Order” on page 4-31.

Creating Subqueries for Values from Multiple Tables

Use the Where feature in Advanced query options to create subqueries. Creating subqueries in this way is referred to as nested SQL.

This example uses Database Toolbox Getting Started Guide.

The

salesVolume table has sales volumes and stock number fields, but no

product description field. The

basic.qry, which you created in “Saving Queries ” in the

productTable has product description and

4-37

4 Using Visual Query Builder

stock number fields, but no sales volumes. This example retrieves the stock number for the product whose description is

productTable table. It then gets the sales volume values for tha t stock

number from the

Building Blocks from the

salesVolume table.

1 Load basic.qry.

2 Set the Data return format Preference to cellarray and Read NULL

numbers as to

3 Click Where in Advanced query options.

NaN.

The WHERE Clauses dialog box appears.

4 Click Subquery.

The Subquery dialog box appears.

4-38

Fine-Tuning Queries Using Advanced Query Options

5 In Tables, select productTable, which includes the association between

the stock number and the p roduct description. The fields in that table appear.

6 In Fields, select stockNumber, the field that is common to this table and

the table from which you are retrieving results.

The statement

SELECT stockNumber FROM p roductTable is created in the

SQL subquery statement.

7 Limit the query to product descriptions that are Building Blocks.

a In Fields in Subquery WHERE clauses, select productDescription.

b For Condition, select Relation.

c In the drop-down list to the right of Relation,select=.

d In the field to the right of the drop-down list, enter 'Building Blocks'.

e Click Apply.

TheclauseappearsintheCurrent subquery WHERE clauses field and is add ed to the SQL subquery statement.

4-39

4 Using Visual Query Builder

4-40

8 Click OK to close the Subquery dialog box.

9 In the WHERE Clauses dialog box, click A pply.

This up specif

dates the Current clauses area using the subquery criteria

ied in steps 3 through 8.

Fine-Tuning Queries Using Advanced Query Options

10 In the WHERE Clauses dialog box, click OK.

The WHERE Clauses dialog box closes. The SQL statement in the VQB dialog box updates.

11 Assign the query results to the MATLAB workspace variable C.

12 Click Execute.

13 Type C at the prompt in the MATLAB Command Window to see the results.

14 The results are for item 400345, which has the product description

Building Blocks, although that is not evident from the results. Create

and run a query to verify that the product description is

a For Data source,selectdbtoolboxdemo.

b In Tables, select productTable.

c In Fields, select stockNumber and productDescription.

d Assign the query results to the MATLAB workspace variable P.

e Click Execute.

Building Blocks:

4-41

4 Using Visual Query Builder

f Type P at the prompt in the MATLAB Command Window to view the

results.

The results show that item 400345 has the product description Building

Blocks. description

In the next section, you create a query that includes product

in the results.

4-42

Note YoucanincludeonlyonesubqueryinaqueryusingVQB;youcan include multiple subqueries using Database Toolbox functions.

Creating Queries That Include Results from Multiple Tables

A query whose results include values from multiple tables is said to perform a join operation in SQL.

This example retrieves sales volumes by product description. It is like the one in “Creating Subqueries for Values from Multiple Tables” on page 4-37, but this example creates a query that returns than

stock number.

The

salesVolume table has sales volume and stock number fields, but

product description field. The productTable table has pro duct

description

a query that retrieves data from both tables and equates the stock number from

productTable with the stock number from salesVolume :

and stock number fields, but no sales volume field. To create

product description rather

Fine-Tuning Queries Using Advanced Query Options

1 Set the Data return format preference to cellarray and the Read

NULL numbers as preference to

2 For Data operation,chooseSelect.

3 For Data source, select dbtoolboxdemo.

NaN.

The Catalog, Schema,andTables for

dbtoolboxdemo appear.

Do not specify Catalog or Schema.

4 In Tables, select the tables from which you want to retrieve data. For this

example, click Ctrl+click and sele ct both

productTable and salesVolume.

The fields (columns) in those tables appear in Fields. Field names appear in the format

productTable.stockNumber indicates the stock number in the product

table and

salesVolume.StockNumber indicates the stock number in the

fieldName.tableName. Therefore,

sales volume table.

5 In Fields,clickCtrl+click to select the following fields:

productTable.productDescription

•

• salesVolume.January

• salesVolume.February

• salesVolume.March

6 In this example, the Where clause equates the productTable.stockNumber

with the salesVolume.StockNumber, so that product description is associated with sales volumes in the query results.

In Advanced query options,clickWhere to associate fields from different tables. The WHERE Clauses dialog box appears.

7 In the WHERE clauses dialog box:

a In Fields, select productTable.stockNumber.

b For Condition, select Relation.

c In the drop-down list to the right of Relation,select=.

4-43

4 Using Visual Query Builder

d In the field to the right of the drop-down list, enter

salesVolume.StockNumber.

e Click Apply.

The clause appears in the Current clauses field.

4-44

f Click OK to close the W H ERE Clauses dialog box. The Where field and

SQL statement in VQB display the Where clause.

8 Assign the query results to the MATLAB workspace variable P1.

9 Click Execute to run the query.

10 Type P1 in the MATLAB Co mmand Window.

P1 =

'Victorian Doll' [14 00] [1100] [ 981] 'Train Set' [2400] [1721] [1414] 'Engine Kit' [1800] [1200] [ 89 0] 'Painting Set' [3000] [2400] [1800] 'Space Cruiser' [4300] [ NaN] [2600] 'Building Blocks' [5000] [3500] [2800] 'Tin Soldier' [1200] [ 900] [ 800] 'Sail Boat' [3000] [2400] [1500] 'Slinky' [3000] [1500] [1000] 'Teddy Bear' [ NaN] [ 900] [ 821]

Fine-Tuning Queries Using Advanced Query Options

Additional Adva

For more informa

Help in its dialo options,andth

tion on advanced query options, choose an option and click

g box. For example, click Group by in Advanced query

en click Help in the Group by Clauses dialog box.

nced Query Options

4-45

4 Using Visual Query Builder

Retrieving BINARY and OTHER Sun Java Data Types

This example shows how to retrieve data of types BINARY and OTHER,which may require manipulation before it can undergo MATLAB processing. To retrieve images using the image data,

1 For Data Operation, select Select.

2 In Data source,selectSampleDB.

3 In Tables,s

4 In Fields, select EmployeeID and Photo (which contains bitmap images).

5 Select Query > Preferences.

matlabroot/toolbox/database/vqb/parsebinary.m:

elect

SampleDB data source and a sample file that parses

Employees.

6 In the Data

7 As the MATLAB workspace variable,specifyA.

8 Click Execute to run the query.

return format field, specify

cellarray.

4-46

Retrieving BINARY and OTHER Sun™ Jav a™ Data Types

9 Type A in the MATLAB Command Window to view the query results.

[1] [21626x1 int8] [2] [21626x1 int8] [3] [21722x1 int8] [4] [21626x1 int8] [5] [21626x1 int8] [6] [21626x1 int8] [7] [21626x1 int8] [8] [21626x1 int8] [9] [21626x1 int8]

10 Assign the first element in A to the variable photo.

photo = A{1,2};

11 Make sure your current folder is writable.

12 Run the sample program parsebinary, which writes the retrieved data to

a file, strips ODBC header information, and displays

photo as a bitmap

image.

cd I:\MATLABFiles\myfiles parsebinary(photo, 'BMP');

For more information on parsebinary,enterhelp parsebinary,orview the

parsebinary file in the MATLAB Editor/Debugger by entering open

parsebinary

in the Command Window.

4-47

4 Using Visual Query Builder

Importing and Exporting BOOLEAN Data

In this section...

“Importing BOOLEAN Data from Databases to the MATLAB Workspace” on page 4-48

“Exporting BO O LEAN Data from the MATLAB W orkspace to Databases” on page 4-51

Importing BOOLEAN Data from Databases to the MATLAB Workspace

BOOLEAN data is imported from databases into the MATLAB workspace as

data type in a cell array or structure.

logical.Thisdatahasavalueof0 (false) or 1 (true), and is stored

This example imports data from the

Products table in the Nwind database

into the MATLA B workspace.

1 Set Data return format to cell array.

2 For Data operation,chooseSelect.

3 In Data

4 In Tables, select Products.

5 In Fields, select ProductName and Discontinued.

6 Assig

7 Click Execute to run the query.

source,select

n the query results to the MATLAB workspace variable

SampleDB.

VQB retrieves a 77-by-2 array.

8 Ent

D in the MATLAB Command Window. 77 records are returned; only

first five records appear here due to space constraints.

the

'Chai' [0] 'Chang' [0]

4-48

Importing and Exporting BOOLEAN Data

'Aniseed Syrup' [0]

[1x28 char] [0] [1x22 char] [1]

9 Compare these results to the data in Microsoft Access.

Discontinued field is BOOLEAN, where a check means true or Yes.

Design view in Access for the Discontinued field shows it is a Yes/No(BOOLEAN) data type.

4-49

4 Using Visual Query Builder

10 In the VQB Data area, double-click D to view its contents in the Variable

11 In the Variable Editor, double-click false.Itslogicalvalue,0,appearsin

Editor. The logical value for the first product, instead of 0.

a separate window.

Chai,appearsasfalse

4-50

For m ore information about MATLAB logical data types, see Logical Types in the MATLAB Programming Fundamentals documentation.

Importing and Exporting BOOLEAN Data

Exporting BOOLEAN Data from the MATLAB Workspace to Databases

Logical data is e xported from the MATLAB workspace to a database as type

BOOLEAN. This example adds two rows of data to the Products table in the Nwind database.

1 In the MATLAB workspace, create P, the structure you want to export.

P.ProductName{1,1}='Chocolate Truffles'; P.Discontinued{1,1}=logical(0); P.ProductName{2,1}='Guatemalan Coffee'; P.Discontinued{2,1}=logical(1);

2 For Data operation,chooseInsert.

3 In Data source,selectSampleDB.

4 In Tables, select Products.

5 In Fields, select ProductName and Discontinued.

6 Assign results to the MATLAB workspace variable P.

7 Click Execute to run the query.

VQB inserts two new rows into the

Products table.

View the table in Microsoft Access to verify that the data was correctly inserted.

4-51

4 Using Visual Query Builder

Saving Queries in Files

In this section...

“About Generated Files” on page 4-52

“VQB Query Elements in G enerated Files” on page 4-53

About Generated Files

Select Query > Generate M-File to cre ate a file that contains the equivalent Database Toolbox functions required to run an existing query that was created in VQB. Edit the file to include MATLAB or related toolbox fu nctions, as needed. To run the que ry, execute the file.

The following is an example ofafilegeneratedbyVQB.

% Set preferences with setdbprefs.

s.DataReturnFormat = 'cellarray';

s.ErrorHandling = 'store';

s.NullNumberRead = 'NaN';

s.NullNumberWrite = 'NaN';

s.NullStringRead = 'null';

s.NullStringWrite = 'null';

s.JDBCDataSourceFile = '';

s.UseRegistryForSources = 'yes';

s.TempDirForRegistryOutput = '';

setdbprefs(s)

4-52

% Make connection to database. Note that the password has been omitted.

% Using ODBC driver.

conn = database('dbtoolboxdemo','','password');

% Read data from database.

e = exec(conn,'SELECT A LL StockNumber,January,February FROM salesVolume');

e = fetch(e);

close(e)

% Close database connec tion.

close(conn)

Saving Queries in Files

VQB Query Elemen

The following VQ

• Generated code

you assigned qu results to you can add a st as follows:

myVar = e.Data

• For securit

to connect t string

'pas

that requi in the data

B query elements do not appear in generated files.

files do not include MATLAB workspace variables to which eryresultsintheVQBquery. Thefileassignsthequery

ccess these results using the variable

e;a

atement to the file that assigns a variable name to

y reasons, generated files do not include passwords required

o databases. Instead, the

sword'

as a placeholder. To run files to connect to databases

re passwords, substitute your password for the string

base

statement.

ts in Generated Files

e.Data. For example,

e.Data

database statement includes the

password

4-53

4 Using Visual Query Builder

4-54

Using Database Toolbox Functions

• “Getting Started with Database Toolbox Functions” on page 5-2

• “Importing Data from Databases into the MATLAB Workspace” on page 5-3

• “Viewing Information About Imported Data” on page 5-5

• “Exporting Data from the MATLAB Workspace to a New Record in a

Database” on page 5-7

• “Replacing Existing Data in Databases with Data Exported from the

MATLAB Workspace” on page 5-11

• “Exporting Multiple Records from the MATLAB Workspace” on page 5-13

• “Retrieving BINARY or OTHER Sun Java SQL Data Types” on page 5-17

• “Working with Database Metadata” on page 5-19

• “Using Driver Functions” on page 5-25

• “About Objects and Methods in the Database Toolbox Software” on page

5-27

5 Using Database Toolbox™ Functions

Getting Star ted with Database Toolbox Functions

The following sections provide examples of how to use Database Toolbox functions. M-files that include functions used in some of these examples are available in

Follow these simple examples consecutively wh en you first start using the product. Once you are familiar with Database Toolbox usage, refer to these examples as needed.

matlab/toolbox/database/dbdemos.

5-2

Importing Data from Databases into the MATLAB®Works p ace

Importing Data from Databases into the MATLAB Workspace

This example demonstrates a sample workflow on a sample database called SampleDB.

1 Before you connect to a database, set the maximum time that you want to

allow the MATLAB software session to try to connect to a database to 5 seconds.

logintimeout(5)

Note If you are connecting to a database using a JDBC connection, you must use different function synta x in this step. For more information, see the

logintimeout function reference page.

2 Use the database function to define a MATLAB variable, conn,to

represent the returned connection object. Pass the following arguments to this function:

• The name of the database, which is

• The

username and password

conn = database('SampleDB', 'username', 'password')

SampleDB for this example

Enter conn at the command prompt to see the data.

Note If you are connecting to a database using a JDBC connection, you need to specify different syntax for the information, see the

3 Use ping to check that the database connection status is successful.

4 Use the exec function to open a cursor and execute an SQL statement.

Pass the following arguments to

database reference page.

exec:

database function. For more

5-3

5 Using Database Toolbox™ Functions

• conn, the name of the connection object

select country from cus tomers, a SQL statement that selects the

•

country column of data from the customers table

curs = exec(conn, 'select country fr om customers')

The exec function returns the MATLAB variable curs.

5 The returned data contains strings, so you must convert it to a format that

supports strings. Use

setdbprefs('DataReturnFormat','cellarray')

6 To stop working now and resume working on the next example at a later

time, close the cursor and the connection as follows:

close(curs) close(conn)

setdbprefs to specify the format cellarray:

5-4

Viewing Information About Imported Data

This example shows how to view information about imported data and close theconnectiontothedatabaseusingthefollowing Database Toolbox functions:

•

attr

• close

• cols

• columnnames

• rows

• width

For more information on these functions, see

matlab\toolbox\database\dbdemos\dbinfodemo.m.

1 Open the cursor and connection if needed:

conn = database('SampleDB', '', ''); curs = exec(conn, 'select country fro m customers'); setdbprefs('DataReturnFormat','cellarray'); curs = fetch(curs, 10);

2 Use rows to return the number of rows in the data set:

numrows = rows(curs) numrows =

3 Use cols to return the number of columns in the data set:

numcols = cols(curs) numcols =

4 Use columnnames to return the names of the columns in the data set:

colnames = columnnames(curs) colnames =

5-5

5 Using Database Toolbox™ Functions

'country'

5 Use width to return the column width, or size of the field, for the specified

column number:

colsize = width(curs, 1) colsize =

6 Use attr to view multiple attributes for a column:

attributes = attr(curs) attributes =

fieldName: 'country'

typeName: 'VARCHAR'

typeValue: 12

columnWidth: 15

precision: []

scale: [] currency: 'false' readOnly: 'false' nullable: 'true'

Message: []

5-6

Tip To import multiple columns, include a colnum argument in attr to specify the number of columns whose information you want.

7 Close the cursor.

close(curs)

8 Continue with the next example. To stop working now and resume working

on the next example at a later time, close the connection.

close(conn)

Exporting Data from the MATLAB®Workspace to a New Record in a Database

Exporting Data from the MATLAB Workspace to a New Record in a Database

Thisexampledoesthefollowing:

1 Retrieves freight costs from an orders table.

2 Calculates the average freight cost and records the date on which the

calculation was made.

3 Stores this data in a cell array.

4 Exportsthisdatatoanemptytable.

You learn to use the following Database Toolbox functions:

•

get

• fastinsert

• setdbprefs

For more information on these functions, see

matlab\toolbox\database\dbdemos\dbinsertdemo.m.

1 Connect to the data source, SampleDB, if needed:

conn = database('SampleDB', '', '');

2 Use setdbprefs to set the format for retrieved data to numeric:

setdbprefs('DataReturnFormat','numeric')

3 Import three rows of data the freight column of data from the orders

table.

curs = exec(conn, 'select freight fr om orders'); curs = fetch(curs, 3);

4 AssignthedatatotheMATLA B workspace variable AA:

AA = curs.Data

5-7

5 Using Database Toolbox™ Functions

AA =

5 Calculate average freight cost and assign the number of rows in the array

numrows:

numrows = rows(curs);

6 Calculate the average of the data and assign the result to the variable

meanA:

meanA = sum(AA(:))/numrows meanA =

7 AssignthedateonwhichthecalculationwasmadetothevariableD:

D = '20-Jan-2002';

32.3800

11.6100

65.8300

36.6067

5-8

8 Assignthedateandmeantoacellarraytoexporttoadatabase. Putthe

date in the first cell of

exdata(1,1) = {D} exdata =

'20-Jan-2002'

exdata:

Put the mean in the second cell of exdata:

exdata(1,2) = {meanA} exdata =

'20-Jan-2002' [36.6067]

9 Define the names of the columns to which to export data. In this

example, the column names are

Avg_Freight_Cost table in the SampleDB database. Ass ign the cell array

containing the column names to the variable

colnames = {'Calc_Date','Avg_Cost'};

Calc_Date and Avg_Cost,fromthe

colnames:

Exporting Data from the MATLAB®Workspace to a New Record in a Database

10 Use the get function to determine the current status of the AutoCommit

database flag. This status determines whether the exported data is automatically committed to the database. If the flag is an update; if it is

get(conn, 'AutoCommit') ans =

on, data is automatically committed to the database.

off, you can undo

The AutoCommit flag is set to on, so the exported data is automatically committed to the database.

11 Use the fastinsert function to export the data into the Avg_Freight_Cost

table. Pass the following arguments to this function:

conn, the connection object for the database

•

Avg_Freight_Cost, the name of the table to which you are exporting

•

data

• The cell arrays

fastinsert(conn, 'Avg_Freight_Cost', colnames, exdata)

fastinsert Avg_Freight_Cost table.

12 In Microsoft Access, view the Avg_Freight_Cost tabletoverifytheresults.

colnames and exdata

appends the data as a new record at the end of the

The Avg_Cost value was rounded to a whole number to match the properties of that field in Access.

13 Close the cursor.

5-9

5 Using Database Toolbox™ Functions

close(curs)

14 Continue with the next example. To stop now and resume working with the

next example at a later time, close the connection.

close(conn)

5-10

Replacing Existing Data in Databases with Data Exported from the MATLAB®Works p ace

Replacing Existing Data in Databases with Data Exported from the MATLAB W

orkspace

This example up

Avg_Freight_C

•

dates the date field that you previously imported into the

ost

table using the following Database Toolbox functions:

• update

For more info

matlab\too

1 If you have c

enter the fo

conn = database('SampleDB', '', ''); colnames = {'Calc_Date', 'Avg_Cost'}; D = '20-Jan-2002'; meanA = 36.6067; exdata = {D, meanA} exdata =

'20-Jan-2002' [36.6067]

2 Change

19-Jan

D = '19-Jan-2002'

rmation on these functions, see

lbox\database\dbdemos\dbupdatedemo.m

ompleted the previous example, skip this step. Otherwise,

llowing commands:

the date in the

-2002

Avg_Freight_Cost table from 20-Jan -200 2 to

3 Assig

4 Spe

n the new date value to the

newdata(1,1) = {D} newdata =

'19-Jan-2002'

cifytherecordtoupdateinthedatabasebydefiningaSQL

tement and assigning it to the variable

sta

ate is the record whose

upd

ing is within a string, it is embedded within two single quotation marks

str

ther than one.

newdata cell array.

whereclause. The record to

Calc_Date is 20-Jan-2002. Because the date

where

5-11

5 Using Database Toolbox™ Functions

whereclause = 'where Calc _Date = ''20-Ja n-2002''' whereclause =

where Calc_Date = '20-Jan -2002'

5 Export the data, replacing the record whose Calc_Date is 20-Jan-20 02.

update(conn,'Avg_Freight_Cost',colnames,newdata,whereclause)

6 In Microsoft Access, view the Avg_Freight_Cost tabletoverifytheresults.

7 Close the cursor and disconnect from the database.

5-12

close(conn)

Exporting Multiple Records from the MATLAB®Works p ace

Exporting Multiple Records from the MATLAB Workspace

Thisexampledoesthefollowing:

1 Imports monthly sales figures for all products from the tutorial database

into the MATLAB workspace.

2 Computes total sales for each month.

3 Exports the totals to a new table.

You use the following Database Toolbox functions:

•

fastinsert

• setdbprefs

For more information on these functions, see

matlab\toolbox\database\dbdemos\dbinsert2demo.m.

1 Ensure that the tutorial database is writable, that is, not read-only.

2 Use the database function to connect to the data source, assigning the

returned connection object as

conn. Pass the following arguments to this

function:

•

dbtoolboxdemo, the name of the data source

username and password, which are passed as empty strings because no

•

user name or password is required to access the database

conn = database('dbtoolboxdemo', '', '');

3 Use the setdbprefs function to specify preferences for the retrieved data.

Set the data return format to from the database are converted to

setdbprefs... ({'NullNumberRead';'DataReturnFormat'},{'0';'numeric'})

numeric and specify that NULL values read

0 in the M ATLA B workspace.

When you specify DataReturnFormat as numeric,thevaluefor

NullNumberRead must also be numeric.

5-13

5 Using Database Toolbox™ Functions

4 Import data from the salesVolume table.

curs = exec(conn, 'select * from salesVolume'); curs = fetch(curs);

5 Use columnnames to view the column names in the fetched data set:

columnnames(curs) ans =

'StockNumber', 'January', 'February', 'March', 'April', 'May', 'June', 'July', 'A ugust', 'September', 'October', 'November', 'December'

6 View the data for January (column 2).

curs.Data(:,2) ans =

1400 2400 1800 3000 4300 5000 1200 3000 3000

5-14

Exporting Multiple Records from the MATLAB®Works p ace

7 Assign the dimensions of the matrix containing the fetched data set to

m and n.

[m,n] = size(curs.Data) m=

8 Use m and n to compute monthly totals. The variable tmp is the sales

volume for all products in a given month total sales volume of all products for that month. For example, if row

1 of monthly is the total of all rows in column 2 of curs.Data,where

column

2 is the sales volume for January.

forc=2:n

tmp = curs.Data(:,c); monthly(c-1,1) = sum(tmp(:));

end

c. The variable monthly is the

c is 2,

View the result.

monthly

25100 15621 14606 11944 9965 8643 6525 5899 8632 13170 48345 172000

5-15

5 Using Database Toolbox™ Functions

9 Create a string array containing the column names into which you want to

insert the data, and assign the array to the variable

colnames{1,1} = 'salesTotal';

10 Use fastinsert to insert the data into the year lySales table:

fastinsert(conn, 'yearlySales', colnames, monthly)

11 To verify that the data was imported correctly, view the ye arlySales table

in the

colnames.

tutorial database.

5-16

12 Close the cursor and the database connection.

close(curs) close(conn)

Retrieving BINARY or OTHER Sun™ Java™ SQL Data Types

Retrieving BINARY or OTHERSunJavaSQLDataTypes

This example retrieves images from the SampleDB data source using a sample file that parses image data,

matlabroot/toolbox/database/vqb/parsebinary.m.

1 Connect to the SampleDB data source.

conn = database('SampleDB', '', '');

2 Specify cellarray as the data return format preference.

setdbprefs('DataReturnFormat','cellarray');

3 Import the EmployeeID and Photo columns of data from the Employees

table.

curs = exec(conn, 'select EmployeeID,Photo from Employees') curs = fetch(curs);

4 View the data you imported.

curs.Data ans =

[1] [21626x1 int8] [2] [21626x1 int8] [3] [21722x1 int8] [4] [21626x1 int8] [5] [21626x1 int8] [6] [21626x1 int8] [7] [21626x1 int8] [8] [21626x1 int8] [9] [21626x1 int8]

Note Some OTHER data type fields may be empty, indicating that the data could not pass through the JDBC/ODBC bridge.

5 Assign the image element you want to the variable photo.

5-17

5 Using Database Toolbox™ Functions

photo = curs.Data{1,2};

6 Run parsebinary. This program writes the retrieved data to a file, strips

ODBC header information from it, and displays in a figure window. Ensure that your current folder is writable so that the output of

cd 'I:\MATLABFiles\myfiles parsebinary(photo, 'BMP');

For more information on parsebinary,enterhelp parsebinary or view the M-file in the MATLAB Editor/Debugger by entering

photo as a bitmap im age

parsebinary can be written to it.

open parsebinary.

5-18

Mathworks DATABASE TOOLBOX 3 user guide

Specifications and Main Features

Frequently Asked Questions

User Manual

Before You Begin

Working with Databases

Connecting to Databases

Supported Platforms

Supported Databases

Supported Drivers

Structured Query Language (SQL)

Supported Data Types

Data Retrieval Restrictions

Spaces in Table Names or Column Names

Quotation Marks in Table Names or Column Names

ReservedWordsinColumnNames

Working with Data Sources

Setting up ODBC Data Sources

Setting up JDBC Data Sources

Accessing Existing JDBC Data Sources

Modifying Existing JDBC Data Sources

Removing JDBC Data Sources

Troubleshooting JDBC Driver Problems

Database Toolbox Functions vs. Visual Query Builder

When to Use Visual Query Builder

Tasks You Can Perform Using Visual Query Builder

Limitations of Visual Query Builder

When to Use Database Toolbox Functions

Using Visual Query Builder

Getting Started with Visual Query Builder

What Is Visual Query Builder?

Using Queries to Import Data

Using Queries to Export Data

Working with Preferences

Specifying Preferences

Saving Preferences

Displaying Query Results

How to Display Query Results

Displaying Data Relationally

Displaying Query Results in an HTML Report

Fine-Tuning Queries Using Advanced Quer y Options

Retrieving All Occurrences vs. Unique Occurrences of Data

Retrieving Data That Meets Specified Criteria

Grouping Statements

Displaying Results in a Specified Order

Using Having Clauses To Refine Group By Results

UsingtheHAVINGClausesDialogBox

Example: Using Having Clauses

Creating Subqueries for Values from Multiple Tables

Creating Queries That Include Results from Multiple Tables

Retrieving BINARY and OTHER Sun Java Data Types

Importing and Exporting BOOLEAN Data

Importing BOOLEAN Data from Databases to the MATLAB Workspace

Exporting BOOLEAN Data from the MATLAB Workspace to Databases

Saving Queries in Files

About Generated Files

Using Database Toolbox Functions

Getting Star ted with Database Toolbox Functions

Importing Data from Databases into the MATLAB Workspace

Viewing Information About Imported Data

Exporting Multiple Records from the MATLAB Workspace

Retrieving BINARY or OTHERSunJavaSQLDataTypes