Database Toolbox™User's GuideR2015a

x ContentsSQLite JDBC for Linux . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-167Step 1. Verify the driver installation. . . . . . . . .

2 Getting Started with Database Toolbox2-743Close the connection using Database Explorer by hovering the cursor over the Closebutton ( ) next to the P

PostgreSQL ODBC for Windows2-75Connect to PostgreSQL using the native ODBC connection command line.1Connect to the database with the ODBC data source

2 Getting Started with Database Toolbox2-76PostgreSQL JDBC for WindowsThis tutorial shows how to set up a data source and connect to your PostgreSQLda

PostgreSQL JDBC for Windows2-77Step 3. Set up the data source using Database Explorer.This step is required only for connecting to Database Explorer.

2 Getting Started with Database Toolbox2-783Select POSTGRESQL from the Vendor list. After selecting the vendor, if you did notadd the JDBC driver file

PostgreSQL JDBC for Windows2-796Enter a data source name in the Data Source Name field in the Create a NewJDBC data source dialog box. Use a new data

2 Getting Started with Database Toolbox2-80Database Explorer connects to your database and displays its contents in a tabnamed with the data source na

PostgreSQL JDBC for Windows2-81Connect to PostgreSQL using the JDBC connection command line.1Use the Vendor name-value pair argument of database to s

2 Getting Started with Database Toolbox2-82SQLite JDBC for WindowsThis tutorial shows how to set up a data source and connect to your SQLite database.

SQLite JDBC for Windows2-83Step 3. Set up the data source using Database Explorer.This step is required only for connecting to Database Explorer. If

xiRunning SQL Queries Saved in Scripts or Files . . . . . . . . . 2-196Inserting Data Using the Command Line . . . . . . . . . . . . . . 2-197Working

2 Getting Started with Database Toolbox2-843Select OTHER from the Vendor list.4Enter the SQLite driver Java class object in the Driver field. Here, us

SQLite JDBC for Windows2-85dbpath is the full path to your SQLite database on your computer. Enter your stringinto the URL field.6Enter your user nam

2 Getting Started with Database Toolbox2-86Database Explorer connects to your database and displays its contents in a tabnamed with the data source na

SQLite JDBC for Windows2-87If Database Explorer is docked, click the Close button ( ) to close all databaseconnections and Database Explorer.Connect

2 Getting Started with Database Toolbox2-88See Alsoclose | database | javaaddpathMore About• “Working with Database Explorer” on page 4-2• “Bringing J

Sybase ODBC for Windows2-89Sybase ODBC for WindowsThis tutorial shows how to set up a data source and connect to your Sybase database.This tutorial u

2 Getting Started with Database Toolbox2-90If you are running 32-bit or 64-bit MATLAB, the corresponding 32-bit or 64-bit MicrosoftODBC Administrator

Sybase ODBC for Windows2-91section in the apps gallery. Alternatively, enter dexplore at the command line. Ifno data sources are set up, a message bo

2 Getting Started with Database Toolbox2-923Click the System DSN tab and then click Add. When setting up an ODBC datasource, you can use a User DSN or

Sybase ODBC for Windows2-936Click Test Connection to test the connection to your database. Another screenappears with login information. Enter your u

xii ContentsCompatibility and Limitations . . . . . . . . . . . . . . . . . . . . . . 3-21Using Database Explorer4Working with Database Explorer . . .

2 Getting Started with Database Toolbox2-94After you complete the data source setup, connect to the Sybase database usingDatabase Explorer or the comm

Sybase ODBC for Windows2-953Close the connection using Database Explorer by hovering the cursor over the Closebutton ( ) next to the Sybase data sour

2 Getting Started with Database Toolbox2-96Connect to Sybase using the native ODBC connection command line.1Connect to your database with the ODBC dat

Sybase JDBC for Windows2-97Sybase JDBC for WindowsThis tutorial shows how to set up a data source and connect to your Sybase database.This tutorial u

2 Getting Started with Database Toolbox2-98Step 3. Set up the data source using Database Explorer.This step is required only for connecting to Databas

Sybase JDBC for Windows2-993Select OTHER from the Vendor list.4Enter the Sybase driver Java class object in the Driver field. Here, usecom.sybase.jdb

2 Getting Started with Database Toolbox2-100your server name, PortNumber is your port number, and dbname is your databasename. Enter your full string

Sybase JDBC for Windows2-101Database Explorer connects to your database and displays its contents in a tabnamed with the data source name.2Close the

2 Getting Started with Database Toolbox2-102you want to close Database Explorer and all database connections, click the Closebutton ( ) in the top-rig

Sybase JDBC for Windows2-103 'com.sybase.jdbc4.jdbc.SybDriver','URL');3Close the database connection conn.close(co

xiiiPreference Settings for Large Data Import . . . . . . . . . . . . . 5-19Will All Data (Size n) Fit in a MATLAB Variable? . . . . . . . . 5-20Will

2 Getting Started with Database Toolbox2-104Microsoft SQL Server JDBC for Mac OS XThis tutorial shows how to set up a data source and connect to your

Microsoft SQL Server JDBC for Mac OS X2-105Step 3. Set up the data source using Database Explorer.This step is required only for connecting to Databa

2 Getting Started with Database Toolbox2-1063Select MICROSOFT SQL SERVER from the Vendor list. After selecting the vendor,if you did not add the JDBC

Microsoft SQL Server JDBC for Mac OS X2-107field, and database name in the Database field. Set the Authentication Type toServer.5Click Test to test t

2 Getting Started with Database Toolbox2-108Database Explorer connects to your database and displays its contents in a tabnamed with the data source n

Microsoft SQL Server JDBC for Mac OS X2-109you want to close Database Explorer and all database connections, click the Closebutton ( ) in the top-lef

2 Getting Started with Database Toolbox2-110See Alsoclose | database | javaaddpathMore About• “Working with Database Explorer” on page 4-2• “Bringing

Microsoft SQL Server JDBC for Linux2-111Microsoft SQL Server JDBC for LinuxThis tutorial shows how to set up a data source and connect to your Micros

2 Getting Started with Database Toolbox2-112Step 3. Set up the data source using Database Explorer.This step is required only for connecting to Databa

Microsoft SQL Server JDBC for Linux2-1133Select MICROSOFT SQL SERVER from the Vendor list. After selecting the vendor,if you did not add the JDBC dri

xiv ContentsImport Data from Databases into MATLAB . . . . . . . . . . . . . . 6-4Create a Query Using a Date . . . . . . . . . . . . . . . . . . . .

2 Getting Started with Database Toolbox2-1145Click Test to test the connection. If your connection succeeded, Database Explorerdisplays Connection Suc

Microsoft SQL Server JDBC for Linux2-115Database Explorer connects to your database and displays its contents in a tabnamed with the data source name

2 Getting Started with Database Toolbox2-116Connect to Microsoft SQL Server using the JDBC connection command line.When using the command line, you do

Microsoft SQL Server JDBC for Linux2-117• “Bringing Java Classes into MATLAB Workspace”

2 Getting Started with Database Toolbox2-118Oracle JDBC for Mac OS XThis tutorial shows how to set up a data source and connect to your Oracle databas

Oracle JDBC for Mac OS X2-119Step 3. Set up the data source using Database Explorer.This step is required only for connecting to Database Explorer. I

2 Getting Started with Database Toolbox2-1203Select ORACLE from the Vendor list. After selecting the vendor, if you did notadd the JDBC driver file pa

Oracle JDBC for Mac OS X2-121and database name in the Database field. Select Driver Type of thin or oci. Usethin as the default driver. Use oci if yo

2 Getting Started with Database Toolbox2-122Database Explorer connects to your database and displays its contents in a tabnamed with the data source n

Oracle JDBC for Mac OS X2-123want to close Database Explorer and all database connections, click the Close button( ) in the top-left corner.If Databa

xvImporting Data Using the fetch Function . . . . . . . . . . . . . . 6-48About the fetch Function . . . . . . . . . . . . . . . . . . . . . . . . . .

2 Getting Started with Database Toolbox2-124 'Server','sname','PortNumber',123456);Or, if you have troubl

Oracle JDBC for Linux2-125Oracle JDBC for LinuxThis tutorial shows how to set up a data source and connect to your Oracle database.This tutorial uses

2 Getting Started with Database Toolbox2-126Step 3. Set up the data source using Database Explorer.This step is required only for connecting to Databa

Oracle JDBC for Linux2-1273Select ORACLE from the Vendor list. After selecting the vendor, if you did notadd the JDBC driver file path to the Java cl

2 Getting Started with Database Toolbox2-1285Click Test to test the connection. If your connection succeeded, Database Explorerdisplays Connection Suc

Oracle JDBC for Linux2-129Database Explorer connects to your database and displays its contents in a tabnamed with the data source name.2Close the co

2 Getting Started with Database Toolbox2-130Connect to Oracle using the JDBC connection command line.When using the command line, you do not have to s

Oracle JDBC for Linux2-131code assumes the value of the URL name-value pair argument is set to the followingtnsnames.ora file entry for an Oracle dat

2 Getting Started with Database Toolbox2-132MySQL JDBC for Mac OS XThis tutorial shows how to set up a data source and connect to your MySQL database.

MySQL JDBC for Mac OS X2-133Step 3. Set up the data source using Database Explorer.This step is required only for connecting to Database Explorer. If

xvi ContentsAnalyze Large Data Sets in a Database with MapReduce . . 6-85Functions — Alphabetical List7

2 Getting Started with Database Toolbox2-1343Select MYSQL from the Vendor list. After selecting the vendor, if you did not add theJDBC driver file pat

MySQL JDBC for Mac OS X2-1356Enter a data source name in the Data Source Name field in the Create a NewJDBC data source dialog box. Use a new data so

2 Getting Started with Database Toolbox2-136Database Explorer connects to your database and displays its contents in a tabnamed with the data source n

MySQL JDBC for Mac OS X2-137Connect to MySQL using the JDBC connection command line.When using the command line, you do not have to set up a data sou

2 Getting Started with Database Toolbox2-138• “Bringing Java Classes into MATLAB Workspace”

MySQL JDBC for Linux2-139MySQL JDBC for LinuxThis tutorial shows how to set up a data source and connect to your MySQL database.This tutorial uses th

2 Getting Started with Database Toolbox2-140Step 3. Set up the data source using Database Explorer.This step is required only for connecting to Databa

MySQL JDBC for Linux2-1413Select MYSQL from the Vendor list. After selecting the vendor, if you did not add theJDBC driver file path to the Java clas

2 Getting Started with Database Toolbox2-142in the existing list of data source names. Click Save. The new JDBC data sourceappears in the list of data

MySQL JDBC for Linux2-1432Close the connection using Database Explorer by hovering the cursor over the Closebutton ( ) next to the MySQL data source

1Before You Begin• “Database Toolbox Product Description” on page 1-2• “Working with Databases” on page 1-3• “Data Type Support” on page 1-6• “Data Re

2 Getting Started with Database Toolbox2-144Connect to MySQL using the JDBC connection command line.When using the command line, you do not have to se

MySQL JDBC for Linux2-145• “Bringing Java Classes into MATLAB Workspace”

2 Getting Started with Database Toolbox2-146PostgreSQL JDBC for Mac OS XThis tutorial shows how to set up a data source and connect to your PostgreSQL

PostgreSQL JDBC for Mac OS X2-147Step 3. Set up the data source using Database Explorer.This step is required only for connecting to Database Explore

2 Getting Started with Database Toolbox2-1483Select POSTGRESQL from the Vendor list. After selecting the vendor, if you did notadd the JDBC driver fil

PostgreSQL JDBC for Mac OS X2-1496Enter a data source name in the Data Source Name field in the Create a NewJDBC data source dialog box. Use a new da

2 Getting Started with Database Toolbox2-150Database Explorer connects to your database and displays its contents in a tabnamed with the data source n

PostgreSQL JDBC for Mac OS X2-151Connect to PostgreSQL using the JDBC connection command line.When using the command line, you do not have to set up

2 Getting Started with Database Toolbox2-152• “Bringing Java Classes into MATLAB Workspace”

PostgreSQL JDBC for Linux2-153PostgreSQL JDBC for LinuxThis tutorial shows how to set up a data source and connect to your PostgreSQLdatabase. This t

1 Before You Begin1-2Database Toolbox Product DescriptionExchange data with relational databasesDatabase Toolbox™ provides an app and functions for ex

2 Getting Started with Database Toolbox2-154Step 3. Set up the data source using Database Explorer.This step is required only for connecting to Databa

PostgreSQL JDBC for Linux2-1553Select POSTGRESQL from the Vendor list. After selecting the vendor, if you did notadd the JDBC driver file path to the

2 Getting Started with Database Toolbox2-1566Enter a data source name in the Data Source Name field in the Create a NewJDBC data source dialog box. Us

PostgreSQL JDBC for Linux2-1572Close the connection using Database Explorer by hovering the cursor over the Closebutton ( ) next to the PostgreSQL da

2 Getting Started with Database Toolbox2-158Connect to PostgreSQL using the JDBC connection command line.When using the command line, you do not have

PostgreSQL JDBC for Linux2-159• “Bringing Java Classes into MATLAB Workspace”

2 Getting Started with Database Toolbox2-160SQLite JDBC for Mac OS XThis tutorial shows how to set up a data source and connect to your SQLite databas

SQLite JDBC for Mac OS X2-161Step 3. Set up the data source using Database Explorer.This step is required only for connecting to Database Explorer. I

2 Getting Started with Database Toolbox2-1623Select OTHER from the Vendor list.4Enter the SQLite driver Java class object in the Driver field. Here, u

SQLite JDBC for Mac OS X2-163dbpath is the full path to your SQLite database on your computer. Enter your stringinto the URL field.6Enter your user n

Working with Databases1-3Working with DatabasesIn this section...“Connecting to Databases” on page 1-3“Platform Support” on page 1-3“Database Support

2 Getting Started with Database Toolbox2-164Database Explorer connects to your database and displays its contents in a tabnamed with the data source n

SQLite JDBC for Mac OS X2-165If Database Explorer is docked, click the Close button ( ) to close all databaseconnections and Database Explorer.Connec

2 Getting Started with Database Toolbox2-166close(conn)See Alsoclose | database | javaaddpathMore About• “Working with Database Explorer” on page 4-2•

SQLite JDBC for Linux2-167SQLite JDBC for LinuxThis tutorial shows how to set up a data source and connect to your SQLite database.This tutorial uses

2 Getting Started with Database Toolbox2-168Step 3. Set up the data source using Database Explorer.This step is required only for connecting to Databa

SQLite JDBC for Linux2-1693Select OTHER from the Vendor list.4Enter the SQLite driver Java class object in the Driver field. Here, useorg.sqlite.JDBC

2 Getting Started with Database Toolbox2-1706Enter your user name in the Username field and your password in the Passwordfield, or leave them blank if

SQLite JDBC for Linux2-171Database Explorer connects to your database and displays its contents in a tabnamed with the data source name.2Close the co

2 Getting Started with Database Toolbox2-172If Database Explorer is docked, click the Close button ( ) to close all databaseconnections and Database E

SQLite JDBC for Linux2-173See Alsoclose | database | javaaddpathMore About• “Working with Database Explorer” on page 4-2• “Bringing Java Classes into

How to Contact MathWorksLatest news:www.mathworks.comSales and services:www.mathworks.com/sales_and_servicesUser community:www.mathworks.com/matlabcen

1 Before You Begin1-4• Microsoft Access™•Microsoft Excel®• Microsoft SQL Server• MySQL• Oracle• PostgreSQL (Postgres)•Sybase SQL Anywhere®•Sybase SQL

2 Getting Started with Database Toolbox2-174Sybase JDBC for Mac OS XThis tutorial shows how to set up a data source and connect to your Sybase databas

Sybase JDBC for Mac OS X2-175Step 3. Set up the data source using Database Explorer.This step is required only for connecting to Database Explorer. I

2 Getting Started with Database Toolbox2-1763Select OTHER from the Vendor list.4Enter the Sybase driver Java class object in the Driver field. Here, u

Sybase JDBC for Mac OS X2-177is jdbc:sybase:Tds:ServerName:PortNumber/dbname, where ServerName isyour server name, PortNumber is your port number, an

2 Getting Started with Database Toolbox2-178Database Explorer connects to your database and displays its contents in a tabnamed with the data source n

Sybase JDBC for Mac OS X2-179want to close Database Explorer and all database connections, click the Close button( ) in the top-left corner.If Databa

2 Getting Started with Database Toolbox2-180object. This code assumes the class object is com.sybase.jdbc4.jdbc.SybDriver.The last argument is the URL

Sybase JDBC for Linux2-181Sybase JDBC for LinuxThis tutorial shows how to set up a data source and connect to your Sybase database.This tutorial uses

2 Getting Started with Database Toolbox2-182Step 3. Set up the data source using Database Explorer.This step is required only for connecting to Databa

Sybase JDBC for Linux2-1833Select OTHER from the Vendor list.4Enter the Sybase driver Java class object in the Driver field. Here, usecom.sybase.jdbc

Working with Databases1-5Structured Query Language (SQL)This toolbox supports American National Standards Institute (ANSI®) standard SQLcommands.

2 Getting Started with Database Toolbox2-184your server name, PortNumber is your port number, and dbname is your databasename. Enter your full string

Sybase JDBC for Linux2-185Database Explorer connects to your database and displays its contents in a tabnamed with the data source name.2Close the co

2 Getting Started with Database Toolbox2-186If Database Explorer is docked, click the Close button ( ) to close all databaseconnections and Database E

Sybase JDBC for Linux2-187 'com.sybase.jdbc4.jdbc.SybDriver','URL');3Close the database connection conn.close(conn

2 Getting Started with Database Toolbox2-188Other ODBC- or JDBC-Compliant DatabasesThis tutorial provides high-level workflows for using other ODBC- o

Other ODBC- or JDBC-Compliant Databases2-189Derby or Microsoft Windows Azure, here are some basic steps to follow. The details ofthe steps below can

2 Getting Started with Database Toolbox2-190Related Examples• “MySQL ODBC for Windows” on page 2-57• “Sybase JDBC for Windows” on page 2-97More About•

Connecting to a Database2-191Connecting to a DatabaseTo connect to your database, your ODBC or JDBC driver must be installed and your datasource must

2 Getting Started with Database Toolbox2-192• “Connect to Microsoft SQL Server using the native ODBC connection commandline.” on page 2-31• JDBC• “Con

Connecting to a Database2-193• “Connect to PostgreSQL using Database Explorer.” on page 2-73• “Connect to PostgreSQL using the native ODBC connection

1 Before You Begin1-6Data Type SupportYou can import the following data types into the MATLAB Workspace and export themback to your database:• BOOLEAN

2 Getting Started with Database Toolbox2-194• “Configuring a Driver and Data Source” on page 2-13

Selecting Data2-195Selecting DataIn this section...“Use Database Explorer to Select Data” on page 2-195“Use the Command Line to Select Data” on page

2 Getting Started with Database Toolbox2-196Running SQL Queries Saved in Scripts or FilesIf you have SQL queries stored in .sql or text files that you

Inserting Data Using the Command Line2-197Inserting Data Using the Command LineYou can use datainsert, fastinsert, or insert to insert data using the

2 Getting Started with Database Toolbox2-198Working with Large Data SetsIn this section...“Connect to a Database with Maximum Performance” on page 2-1

Working with Large Data Sets2-199Export Large Data Sets from MATLABWhen inserting large volumes of data into a database, you might experience slowpro

2 Getting Started with Database Toolbox2-200Deploying a Database Application with MATLAB CompilerIn this section...“Create and Deploy a Database Appli

Deploying a Database Application with MATLAB Compiler2-201Native ODBC and ODBC DriversAfter compiling your database application, you must define the

3Working with Data Sources• “Setting Up ODBC Data Sources” on page 3-2• “Setting Up JDBC Data Sources” on page 3-3• “Accessing Existing JDBC Data Sour

Data Type Support1-7Note: Data types LONGCHAR and NTEXT are not supported for the native ODBC interface.

3 Working with Data Sources3-2Setting Up ODBC Data SourcesFor instructions on setting up ODBC data sources, see “Configuring a Driver and DataSource”

Setting Up JDBC Data Sources3-3Setting Up JDBC Data SourcesFor instructions on setting up JDBC data sources, see “Configuring a Driver and DataSource

3 Working with Data Sources3-4Accessing Existing JDBC Data SourcesTo access an existing data source from Visual Query Builder in future MATLAB session

Modifying Existing JDBC Data Sources3-5Modifying Existing JDBC Data Sources1Access the existing data source as described in “Accessing Existing JDBC

3 Working with Data Sources3-6Removing JDBC Data Sources1Access the existing data source as described in “Accessing Existing JDBC DataSources” on page

Fetching Data Common Errors3-7Fetching Data Common ErrorsThis table describes how to address common errors you might encounter while workingwith Data

3 Working with Data Sources3-8Vendor Error Message Probable Causes Resolutionerror happensafter executingfetch. This errorhappens only withthe command

Database Connection Error Messages3-9Database Connection Error MessagesThis table describes how to address common errors you might encounter while co

3 Working with Data Sources3-10Vendor Error Message Probable Causes ResolutionMicrosoftAccess[Microsoft][ODBCMicrosoft Access Driver]‘(unknown)’ is no

Database Connection Error Messages3-11Vendor Error Message Probable Causes ResolutionMicrosoftSQLServer orSybaseInvalid string or bufferlength.64-bit

1 Before You Begin1-8Data Retrieval RestrictionsIn this section...“Spaces in Table Names or Column Names” on page 1-8“Quotation Marks in Table Names o

3 Working with Data Sources3-12Vendor Error Message Probable Causes Resolutionnot have enough rightsto access the remotemachine. This erroralso occurs

Database Connection Error Messages3-13Vendor Error Message Probable Causes Resolutionwith Windowsauthentication.Oracle The Network Adaptercould not e

3 Working with Data Sources3-14Database Explorer Error MessagesThis table describes how to address common errors you might encounter while workingwith

Database Explorer Error Messages3-15Vendor ErrorLocationError Message Probable Causes ResolutionDatabaseBrowserpane.Oracle ErroroccursinsidetheDataba

3 Working with Data Sources3-16More About• “Working with Database Explorer”

Connecting to a Database Using the Native ODBC Interface3-17Connecting to a Database Using the Native ODBC InterfaceIn this section...“About the Nati

3 Working with Data Sources3-18Note: The native ODBC interface has a default batch size of 100,000 that enablesacceptable performance. To override thi

Connecting to a Database Using the Native ODBC Interface3-19 'unitCost' 'productDescription'Define the data for the row to

3 Working with Data Sources3-20Item Native ODBC JDBC/ODBC Bridge JDBC• Query data(exec)• Import data(fetch)• Run storedprocedure (exec)• Export data(i

Connecting to a Database Using the Native ODBC Interface3-21Item Native ODBC JDBC/ODBC Bridge JDBCData type support Long data types arenot supported

Creating and Running SQL Queries1-9Creating and Running SQL QueriesYou can select data from your database and import it into MATLAB by doing any of t

4Using Database Explorer• “Working with Database Explorer” on page 4-2• “Configure Data Sources and Connect to Databases” on page 4-6• “Modify and Del

4 Using Database Explorer4-2Working with Database ExplorerIn this section...“Getting Started with Database Explorer” on page 4-2“Migrate from VQB to D

Working with Database Explorer4-3• If you use VQB to export data from MATLAB to your database, use the command-linefunctions datainsert or fastinsert

4 Using Database Explorer4-42Specify the Preferences settings that apply to Database Explorer as described inthe following table.Preference Allowable

Working with Database Explorer4-5Select Database Toolbox to manage additional preferences for Database Toolbox.For details, see “Working with Prefere

4 Using Database Explorer4-6Configure Data Sources and Connect to DatabasesIn this section...“Configure Your Environment” on page 4-6“Work with Multip

Configure Data Sources and Connect to Databases4-7Tip Some Windows systems support both ODBC and JDBC drivers. On such systems,JDBC drivers generally

4 Using Database Explorer4-8Configure ODBC Data SourcesWhen setting up a data source for an ODBC driver, the target database can be on aPC running the

Configure Data Sources and Connect to Databases4-9A list of installed ODBC drivers appears in the Create New Data Source dialog box.5Select Microsoft

4 Using Database Explorer4-10The ODBC Microsoft Access Setup dialog box for your driver opens. The dialog boxfor your driver can differ from the follo

Configure Data Sources and Connect to Databases4-116Enter dbtoolboxdemo as the data source name and tutorial database as thedescription.7Select the d

4 Using Database Explorer4-1210Click OK to close the dialog box.Configure JDBC Data Sources1Find the name of the JDBC driver file. This file is provid

Configure Data Sources and Connect to Databases4-13loading performance than the dynamic path. To add folders to the static path, createthe file javac

4 Using Database Explorer4-146Use the information in the following table to set up JDBC drivers for DatabaseExplorer.aUse the Create a New JDBC data s

Configure Data Sources and Connect to Databases4-15Field DescriptionData SourceNameThe name that you assign to the data source. For somedatabases, Na

4 Using Database Explorer4-16dialog box to create a MAT-file that saves your specified data source informationfor future Database Explorer sessions.Na

Configure Data Sources and Connect to Databases4-17For details about potential errors, see “Database Connection Error Messages”.Work with Multiple Da

4 Using Database Explorer4-18See AlsodatabaseMore About• “Choosing Between ODBC and JDBC Drivers”• “Configuring a Driver and Data Source”• “Working wi

Modify and Delete Database Connections4-19Modify and Delete Database ConnectionsIn this section...“ODBC Drivers” on page 4-19“JDBC Drivers” on page 4

2Getting Started with DatabaseToolbox• “Working with a Database and MATLAB” on page 2-3• “Connection Options” on page 2-6• “Initial Setup Requirements

4 Using Database Explorer4-20settings. If you do not want to overwrite the existing data source, enter a new datasource name. Click Save.For data sour

Refine Results Using Query Criteria and Rules4-21Refine Results Using Query Criteria and RulesIn this section...“Define Query Criteria to Refine Resu

4 Using Database Explorer4-22Each row in the SQL Criteria panel has four columns to define your SQL query.Column 1 Column 2 Column 3 Column 4Column 1

Refine Results Using Query Criteria and Rules4-23specifying your query criteria. You can add more rows for these options in the SQLCriteria panel by

4 Using Database Explorer4-24Note: If you click + to add a query condition between two previously entered conditions,the available query options do no

Generate SQL and MATLAB Code4-25Generate SQL and MATLAB CodeIn this section...“Save Queries as SQL Code” on page 4-25“Generate MATLAB Code” on page 4

4 Using Database Explorer4-26Generate MATLAB CodeYou can generate MATLAB code to automate accessing data that you display in the DataPreview pane.1Con

Generate SQL and MATLAB Code4-273Save the MATLAB code to a file. You can run this code file at the command line toconnect to a data source and run a

4 Using Database Explorer4-28More About• “Working with Database Explorer” on page 4-2

5Using Visual Query Builder• “Getting Started with Visual Query Builder” on page 5-2• “Working with Preferences” on page 5-15• “Preference Settings fo

2 Getting Started with Database Toolbox2-2• “PostgreSQL JDBC for Mac OS X” on page 2-146• “PostgreSQL JDBC for Linux” on page 2-153• “SQLite JDBC for

5 Using Visual Query Builder5-2Getting Started with Visual Query BuilderIn this section...“What Is Visual Query Builder?” on page 5-2“Using Queries to

Getting Started with Visual Query Builder5-3*Required step10* Run query.1* Specify Select. 2* Select data source.4* Select tables.5* Select

5 Using Visual Query Builder5-4• Visual Query Builder (querybuilder)• Database ExplorerTo create and run a query using Visual Query Builder to import

Getting Started with Visual Query Builder5-53Accept the default values <default> for the Catalog and Schema fields. Settingthese fields to the

5 Using Visual Query Builder5-6• Alternatively, you can select a schema without specifying a catalog; thatis, when the Catalog field set to <defaul

Getting Started with Visual Query Builder5-7You supplyinput tothese fields.MATLABdisplays outputin this field.8Double-click A in the Data area. The c

5 Using Visual Query Builder5-8Alternatively, you can view the contents of A by entering A in the Command Window.

Getting Started with Visual Query Builder5-9Using Queries to Export DataThe following steps summarize how to use VQB to export data.

5 Using Visual Query Builder5-10*Required step8* Run query.1* Specify Insert. 2* Select data source.4* Select tables.5* Select fields to

Getting Started with Visual Query Builder5-111Select Data Operation > Insert to select data to export.2Select dbtoolboxdemo as the data source to

Working with a Database and MATLAB2-3Working with a Database and MATLABThis tutorial shows how to use Database Toolbox. You must make many decisionst

5 Using Visual Query Builder5-12VQB adds each field you select to the query in the MATLAB command field.6In the MATLAB workspace, assign the data you

Getting Started with Visual Query Builder5-139View the inventoryTable table in the Microsoft Access database to verify thequery results.

5 Using Visual Query Builder5-1410To save this query, select Query > Save and name it export.qry.Clearing Variables from the VQB Data AreaVariables

Working with Preferences5-15Working with PreferencesDatabase Toolbox preferences enable you to specify:• How NULL data in a database is represented a

5 Using Visual Query Builder5-162Specify the Preferences settings as described in this table.Preference Acceptable Values DescriptionReadNULLstrings a

Working with Preferences5-17Preference Acceptable Values Descriptionnumbersas:as NaN. Setting this field to 0 causes NULL data imported into theMATLA

5 Using Visual Query Builder5-18Preference Acceptable Values DescriptionCursorFetchFetch InBatches andBatch SizeSpecifies if fetch retrieves data in b

Preference Settings for Large Data Import5-19Preference Settings for Large Data ImportIn this section...“Will All Data (Size n) Fit in a MATLAB Varia

5 Using Visual Query Builder5-20results = runsqlscript(conn,'filename.sql','rowInc','m')• If you are using the native OD

Preference Settings for Large Data Import5-21The value of your JVM heap can be determined by selecting MATLAB Preferences andGeneral > Java Heap M

Revision HistoryMay 1998 Online OnlyNew for Version 1 for MATLAB® 5.2July 1998 First Printing For Version 1Online only June 1999 Revised for Version 2

2 Getting Started with Database Toolbox2-4

5 Using Visual Query Builder5-22Method 2 — Data Does Fit In MATLAB Variable But Not in JVM HeapIf your data (n) does fit in a MATLAB variable but not

Displaying Query Results5-23Displaying Query ResultsIn this section...“How to Display Query Results” on page 5-23“Displaying Data Relationally” on pa

5 Using Visual Query Builder5-24This display shows only unique values for each field, so you should not read eachrow as a single record. In this examp

Displaying Query Results5-254As another example, click 3000 under January. It shows three different items withsales of 3000 units in January: 400314,

5 Using Visual Query Builder5-26

Displaying Query Results5-27Charting Query ResultsTo chart the results of basic.qry:1Select Display > Chart.The Visual Query Builder Charting dial

5 Using Visual Query Builder5-28The pie chart preview now shows percentages for March data.4To display a legend, which maps colors to the stock number

Displaying Query Results5-29Displaying Query Results in an HTML ReportTo display results for basic.qry in an HTML report, select Display > Report.

5 Using Visual Query Builder5-301Select Display > Report Generator.2The Report Explorer opens, listing sample report templates that you can use toc

Displaying Query Results5-31aIn the Outline pane on the left, under Report Generator > databasetlbx.rpt,select Table.bIn the Properties pane on th

Working with a Database and MATLAB2-5More About• “Working with Database Explorer” on page 4-2

5 Using Visual Query Builder5-32aModify the workspace variable A as follows:A = [{'Stock Number', 'January', 'February',

Displaying Query Results5-33Tip Because some browsers are not configured to launch automatically, you may need toopen your Web browser before display

5 Using Visual Query Builder5-34Fine-Tuning Queries Using Advanced Query OptionsIn this section...“Retrieving All Occurrences vs. Unique Occurrences o

Fine-Tuning Queries Using Advanced Query Options5-358To retrieve only unique occurrences of data:aIn Advanced query options, select Distinct.bAssign

5 Using Visual Query Builder5-362Set the Data return format preference to cellarray.3Set Read NULL numbers as to NaN.4In Advanced query options, click

Fine-Tuning Queries Using Advanced Query Options5-37dClick Apply.The clause that you defined, StockNumber > 400000, appears in the Currentclauses

5 Using Visual Query Builder5-38The Current clauses field now displays:StockNumber > 400000 ANDeIn Fields, select StockNumber.fIn Condition, select

Fine-Tuning Queries Using Advanced Query Options5-39To modify basic_where.qry:1Click Where in VQB. The WHERE Clauses dialog box appears.2Modify the q

5 Using Visual Query Builder5-403Group the criteria that require sales in each month to exceed 1500 units.aIn Current clauses, select the statement Ja

Fine-Tuning Queries Using Advanced Query Options5-41aSelect March > 1500 ) in Current clauses and click Edit.bSelect AND for Operator and click Ap

2 Getting Started with Database Toolbox2-6Connection OptionsIn this section...“Creating or Connecting to a Data Source” on page 2-6“Defining Operating

5 Using Visual Query Builder5-42Removing Grouping of StatementsTo use the WHERE Clauses dialog box to remove grouping criteria from the previousexampl

Fine-Tuning Queries Using Advanced Query Options5-434In Advanced query options, select Order by.The ORDER BY Clauses dialog box appears.5Enter values

5 Using Visual Query Builder5-44January ASCFebruary ASC7To specify March as the third sort field and display results in descending order:aIn Fields, s

Fine-Tuning Queries Using Advanced Query Options5-45For B, results are first sorted by January sales in ascending order. The lowestvalue for January

5 Using Visual Query Builder5-461From the Fields list box, select the entry whose value to restrict.2Define the Condition for the selected field, as d

Fine-Tuning Queries Using Advanced Query Options5-47• To delete a clause, Select the clause from Current clauses and click Delete. UseCtrl+click or S

5 Using Visual Query Builder5-484Click OK.The HAVING Clauses dialog box closes. The SQL statement field in the VQB dialogbox reflects the specified Ha

Fine-Tuning Queries Using Advanced Query Options5-491Load basic.qry.2Set the Data return format Preference to cellarray and Read NULL numbersas to Na

5 Using Visual Query Builder5-50The statement SELECT stockNumber FROM productTable is created in the SQLsubquery statement.7Limit the query to product

Fine-Tuning Queries Using Advanced Query Options5-51This updates the Current clauses area using the subquery criteria specified insteps 3 through 8.1

Connection Options2-7Connection Option Why Use This Option?Database Explorer Use Database Explorer to:• Visually inspect the structure, or schema, of

5 Using Visual Query Builder5-52eClick Execute.fType P at the prompt in the Command Window to view the results.The results show that item 400345 has t

Fine-Tuning Queries Using Advanced Query Options5-531Set the Data return format preference to cellarray and the Read NULLnumbers as preference to NaN

5 Using Visual Query Builder5-54fClick OK to close the WHERE Clauses dialog box. The Where field and SQLstatement in VQB display the Where clause.8Ass

Retrieving BINARY and OTHER Data Types5-55Retrieving BINARY and OTHER Data TypesThis example shows how to retrieve data of types BINARY and OTHER, wh

5 Using Visual Query Builder5-56For details about parsebinary, enter help parsebinary, or view theparsebinary file in the MATLAB Editor/Debugger by en

Importing and Exporting Boolean Data5-57Importing and Exporting Boolean DataIn this section...“Import Boolean Data from Databases” on page 5-57“Expor

5 Using Visual Query Builder5-58 [61178] [0] [62145] [1]9Compare these results to the data in Microsoft Access.10In the VQB Data area, dou

Importing and Exporting Boolean Data5-59

5 Using Visual Query Builder5-60Exporting Boolean Data to DatabasesLogical data is exported from the MATLAB workspace to a database as type BOOLEAN.Th

Saving Queries in Files5-61Saving Queries in FilesIn this section...“About Generated Files” on page 5-61“VQB Query Elements in Generated Files” on pa

2 Getting Started with Database Toolbox2-8Working with Multiple DatabasesYou can connect to multiple databases using Database Explorer or the command

5 Using Visual Query Builder5-62VQB Query Elements in Generated FilesThe following VQB query elements do not appear in generated files:• Generated cod

Saving Queries in Files5-63• To run a Select query, use the MATLAB workspace variable field to assign avariable to the data and click Execute.• For a

6Using Database Toolbox Functions• “Getting Started with Database Toolbox Functions” on page 6-3• “Import Data from Databases into MATLAB” on page 6-4

6 Using Database Toolbox Functions6-2• “Retrieving Object Properties Using the get Function” on page 6-70• “Setting Database Preferences Using the set

Getting Started with Database Toolbox Functions6-3Getting Started with Database Toolbox FunctionsThe following sections provide examples of how to us

6 Using Database Toolbox Functions6-4Import Data from Databases into MATLABThis example shows how to import data from a Microsoft Access database call

Import Data from Databases into MATLAB6-5 [ 3] 'Slinky' [10] 'Teddy Bear' Close the cursor object and da

6 Using Database Toolbox Functions6-6Run the SQL script file named salesvolume.sql using the runsqlscript function.results = runsqlscript(conn,'s

Import Data from Databases into MATLAB6-7close(conn)See Alsoclose | database | exec | fetch | runsqlscript | setdbprefsMore About• “Connecting to a D

Initial Setup Requirements2-9Initial Setup RequirementsRefer to the setup requirements below to establish the first connection to your database.• For

6 Using Database Toolbox Functions6-8Create a Query Using a DateThis example shows how to format a date in an SQL query.When you want to write an SQL

Create a Query Using a Date6-9The query returns the records where the date in the column test_dt is after June 9,2013.Close the Cursor and Database C

6 Using Database Toolbox Functions6-10Create a Query Using a StringThis example shows how to include a string in your SQL query using a Microsoft Acce

Create a Query Using a String6-11close(conn)See Alsoclose | database | exec | fetchMore About• “Connecting to a Database Using the Native ODBC Interf

6 Using Database Toolbox Functions6-12Create a Query Using a MATLAB VariableThis example shows how to include a MATLAB variable in your SQL query. Thi

Create a Query Using a MATLAB Variable6-13 [2101.00] '2010-08-01 00:00...' [1.00] [0] [1948410x1 int8]The fetch function ret

6 Using Database Toolbox Functions6-14Create a Query Using Special CharactersThis example shows how to write an SQL query for table names or columns n

Create a Query Using Special Characters6-15 'some text'Close the Cursor and Database Connectionclose(curs)close(conn)See Alsoclose | dat

6 Using Database Toolbox Functions6-16Delete Data from DatabasesThis example shows how to delete data from your database using MATLAB.Create the SQL s

Delete Data from Databases6-17 ... [10] [ 723] [ 24] '2012-03-14 13:13...' [11] [ 567] [ 0] '2012-

2 Getting Started with Database Toolbox2-10Choosing Between ODBC and JDBC DriversIn this section...“Defining Database Drivers” on page 2-10“Deciding B

6 Using Database Toolbox Functions6-18Exporting Data to New Record in DatabaseThis example does the following:1Retrieves sales data from a salesVolume

Exporting Data to New Record in Database6-192800800150010008215Calculate the sum of the March sales and assign the result to the variable sumA:sumA =

6 Using Database Toolbox Functions6-20• conn, the connection object for the database• yearlySales, the name of the table to which you are exporting da

Replacing Existing Database Data with Exported Data6-21Replacing Existing Database Data with Exported DataThis example updates the Month field that y

6 Using Database Toolbox Functions6-22Exporting Multiple Records from the MATLAB WorkspaceThis example does the following:1Imports monthly sales figur

Exporting Multiple Records from the MATLAB Workspace6-23curs = fetch(curs);5Use columnnames to view the column names in the fetched data set:columnna

6 Using Database Toolbox Functions6-24 25100 15621 14606 11944 9965 8643 6525 5899 8632 13170 48345 1720009Create a string array containing the column

Exporting Multiple Records from the MATLAB Workspace6-2512Close the cursor and the database connection.close(curs)close(conn)

6 Using Database Toolbox Functions6-26Exporting Data Using Bulk InsertIn this section...“About Bulk Insert Functionality” on page 6-26“Bulk Insert int

Exporting Data Using Bulk Insert6-273Enter data records. A sample record appears as follows.A = {100000.00,'KGreen','06/22/2011',

Choosing Between ODBC and JDBC Drivers2-11Depending on your environment and what you want to accomplish, you need to decidewhether using an ODBC driv

6 Using Database Toolbox Functions6-28results = Attributes: [] Data: {10000x4 cell} DatabaseObject: [1x1 database] Ro

Exporting Data Using Bulk Insert6-29Tip When connecting to a database on a remote machine, you must write this file tothe remote machine. Microsoft S

6 Using Database Toolbox Functions6-30close(conn)Bulk Insert into MySQL1Connect to the MySQL server. For JDBC driver use, add the JAR file to theMATLA

Exporting Data Using Bulk Insert6-317Confirm the number of rows and columns in BULKTEST.e = exec(conn, 'select * from BULKTEST');results =

6 Using Database Toolbox Functions6-32Retrieve Image Data TypesThis example retrieves images from the dbtoolboxdemo data source using a sample filetha

Retrieve Image Data Types6-33receipt = curs.Data{1,2};6Run parsebinary. This program writes the retrieved data to a file, stripsODBC header informati

6 Using Database Toolbox Functions6-34Display Database MetadataThis example shows how to display database information for database connection objectsu

Display Database Metadata6-35props = AlterTableWithAddColumn: 1 AlterTableWithDropColumn: 1 ANSI92EntryLevelSQL: 1...A 1 for

6 Using Database Toolbox Functions6-36More About• “Working with Database Explorer”

Using Driver Functions6-37Using Driver FunctionsThis example uses the following Database Toolbox functions to create driver anddrivermanager objects,

2 Getting Started with Database Toolbox2-12For a list of native ODBC supported functionality and a full comparison of the JDBC/ODBC bridge to native O

6 Using Database Toolbox Functions6-38This result shows that d is a valid JDBC driver object. If it is a not valid JDBCdriver object, the returned res

About Database Toolbox Objects and Methods6-39About Database Toolbox Objects and MethodsThis toolbox is an object-oriented application. You do not ne

6 Using Database Toolbox Functions6-40Selecting Data Using the exec FunctionIn this section...“About the exec Function” on page 6-40“Using Cursor Obje

Selecting Data Using the exec Function6-41• A cursor stays open until you close it using the close function.Working with Microsoft ExcelFor Microsoft

6 Using Database Toolbox Functions6-42Call a Stored Procedure That Returns DataThis example shows how to call a stored procedure that returns data usi

Call a Stored Procedure That Returns Data6-43 Driver: [] URL: [] Constructor: [1x1 com.mathworks.toolbox.database.databaseConne

6 Using Database Toolbox Functions6-44curs = fetch(curs)curs = Attributes: [] Data: [3x5 table] DatabaseObject: [1x1 database]

Call a Stored Procedure That Returns Data6-45close(conn)See Alsodatabase | exec | fetch | runstoredprocedure

6 Using Database Toolbox Functions6-46Run a Custom Database FunctionThis example shows how to run a custom database function on Microsoft SQL Server.C

Run a Custom Database Function6-47Close the Database ConnectionClose the cursor and database connection.close(curs)close(conn)See Alsoclose | databas

Configuring a Driver and Data Source2-13Configuring a Driver and Data SourceConnect to a database and interact with the data by first installing the

6 Using Database Toolbox Functions6-48Importing Data Using the fetch FunctionIn this section...“About the fetch Function” on page 6-48“fetch Workflow”

Importing Data Using the fetch Function6-49Note: You can pass conn as an input argument to fetch when using an JDBC/ODBCbridge or a JDBC interface. F

6 Using Database Toolbox Functions6-50fetch(curs)curs2 = fetch(curs)• The next time fetch is run, records are imported starting with the row following

Importing Data Using the fetch Function6-51Difference Cursor Object Database Connection ObjectFetching in batches You can fetch datain batches by usi

6 Using Database Toolbox Functions6-52Fetch Data Incrementally Using the Cursor ObjectThis example shows how to work with large data sets by retrievin

Fetch Data Incrementally Using the Cursor Object6-53• Size per row being retrieved• Java heap memory value• Driver’s default fetch size• System archi

6 Using Database Toolbox Functions6-54• “Connecting to a Database Using the Native ODBC Interface”• “Preference Settings for Large Data Import”

Display Information About Imported Data6-55Display Information About Imported DataThis example shows how to import data and display information about

6 Using Database Toolbox Functions6-56 1Retrieve the Column Name in the Imported DataRetrieve the column name colname using curs.colname = columnn

Display Information About Imported Data6-57close(curs)See Alsoattr | cols | columnnames | database | fetch | rows | setdbprefs | width

2 Getting Started with Database Toolbox2-14Database Platform Windows Mac OS X 64-bit Linux 64-bitMySQL “MySQL ODBC forWindows” on page2-57“MySQL JDBC

6 Using Database Toolbox Functions6-58Importing Data Using a Scrollable CursorIn this section...“About Scrollable Cursors” on page 6-58“Differences Be

Importing Data Using a Scrollable Cursor6-59position of 5000 after fetching data in Example 5, Example 6 fetches 3000 records usinga relative cursor

6 Using Database Toolbox Functions6-60• The fetch Action column describes the rows of data to retrieve based on the specifiedinput arguments.For examp

Importing Data Using a Scrollable Cursor6-61InitialScrollableCursorPositionRow Limit ScrollableCursor PositionTypeOffset EndingScrollableCursor Posit

6 Using Database Toolbox Functions6-62JDBCInitialScrollableCursorPositionRow Limit ScrollableCursor PositionTypeOffset EndingScrollableCursor Position

Importing Data Using a Scrollable Cursor6-63InitialScrollableCursorPositionRow Limit ScrollableCursor PositionTypeOffset EndingScrollableCursor Posit

6 Using Database Toolbox Functions6-64See Alsoexec | fetch | get

Import Data Using a Scrollable Cursor with a Relative Position Offset6-65Import Data Using a Scrollable Cursor with a Relative PositionOffsetThis exa

6 Using Database Toolbox Functions6-66The columns in curs.Data are:• Product number• Stock number• Supplier number• Unit cost• Product descriptionDisp

Import Data Using a Scrollable Cursor with a Relative Position Offset6-67Close the Cursor ObjectAfter finishing with the cursor object, close it.clos

Microsoft Access ODBC for Windows2-15Microsoft Access ODBC for WindowsThis tutorial shows how to set up a data source and connect to your Microsoft A

6 Using Database Toolbox Functions6-68Inserting Data Using the fastinsert FunctionIn this section...“About the fastinsert Function” on page 6-68“Datab

Inserting Data Using the fastinsert Function6-69• Use update to replace existing data in a database.Database Considerations• The order of records in

6 Using Database Toolbox Functions6-70Retrieving Object Properties Using the get FunctionIn this section...“Database Connection Objects” on page 6-70“

Retrieving Object Properties Using the get Function6-71Property Value'Type' Object type, specifically Database Object.'URL' For J

6 Using Database Toolbox Functions6-72Property Value'Fetch' 0 for the cursor created using exec; fetchTheData forthe cursor created using fe

Retrieving Object Properties Using the get Function6-73Property Example of Value'URL' 'jdbc:odbc:dbtoolboxdemo''NullsAreSort

6 Using Database Toolbox Functions6-74Property Example of Value'ColumnTypeName' {'TEXT' 'LONG'}'isNullable' {[

Setting Database Preferences Using the setdbprefs Function6-75Setting Database Preferences Using the setdbprefs FunctionIn this section...“About the

6 Using Database Toolbox Functions6-76Property Allowable Values Description'numeric' Import data into MATLAB matrix of doubles.Nonnumeric da

Setting Database Preferences Using the setdbprefs Function6-77Property Allowable Values Description'NullStringRead' Input value, forexample

2 Getting Started with Database Toolbox2-16Database Explorer. The drivers listed in the Create New Data Source dialog box in theMicrosoft ODBC Adminis

6 Using Database Toolbox Functions6-78Property Allowable Values Description'DefaultRowPreFetch' Input numericvalue, defaultvalue is '10

Working with a DatabaseDatastore6-79Working with a DatabaseDatastoreIn this section...“About DatabaseDatastore Objects” on page 6-79“Advantages of Da

6 Using Database Toolbox Functions6-80MapReduce” on page 6-85. For more MapReduce examples, see Building EffectiveAlgorithms with MapReduce.See Alsocl

Import Data Using a DatabaseDatastore6-81Import Data Using a DatabaseDatastoreThis example shows how to import data into MATLAB using a DatabaseDatas

6 Using Database Toolbox Functions6-82 _____________ ___________ ______________ ________ __________________ 1.00 400345.

Import Data Using a DatabaseDatastore6-83 'No Data'read returns the cell array containing the string 'No Data' when you connec

6 Using Database Toolbox Functions6-84• Building Effective Algorithms with MapReduceMore About• “Working with a DatabaseDatastore” on page 6-79

Analyze Large Data Sets in a Database with MapReduce6-85Analyze Large Data Sets in a Database with MapReduceThis example shows how to analyze large d

6 Using Database Toolbox Functions6-86Define the Mapper and Reducer FunctionsYou can write your own mapper function to process large data sets in chun

Analyze Large Data Sets in a Database with MapReduce6-87% intermKey is 'PartialCountSumDelay'count = 0;sum = 0;while hasnext(intermValIter)

Microsoft Access ODBC for Windows2-17In the ODBC Data Source Administrator dialog box, you can define the ODBC datasource.4Click the User DSN tab and

6 Using Database Toolbox Functions6-88close(dbds)See Alsoclose | database | datastore | mapreduce | readall | setdbprefsRelated Examples• “Import Data

7Functions — Alphabetical List

7 Functions — Alphabetical List7-2attrRetrieve attributes of columns in fetched data setSyntaxattributes = attr(curs)attributes = attr(curs,colnum)Des

attr7-3attributes = 1x4 struct array with fields: fieldName typeName typeValue columnWidth precision scale currency readOnly

7 Functions — Alphabetical List7-4inventoryTable with these columns: productNumber, Quantity, Price, andinventoryDate.conn = database(dbname,username,

attr7-5colnum — Column numberscalarColumn number, specified as a scalar to denote the column in the fetched data set cursfor retrieving attribute inf

7 Functions — Alphabetical List7-6bestrowidUnique identifier for row in database tableSyntaxb = bestrowid(dbmeta,'cata','sch')b =

bestrowid7-7See Alsocolumns | dmd | get | tables

7 Functions — Alphabetical List7-8clearwarningsClear warnings for database connection or resultsetSyntaxclearwarnings(conn)clearwarnings(rset)Descript

close7-9closeClose database connection, DatabaseDatastore, cursor, or resultset objectSyntaxclose(object)Descriptionclose(object) closes the database

2 Getting Started with Database Toolbox2-18data sources a user defines under System DSN on a machine can be seen by any userwho logs into that machine

7 Functions — Alphabetical List7-10dbds = datastore(conn,sqlquery);Close the DatabaseDatastore object dbds.close(dbds)Close the Cursor ObjectUsing the

close7-11close(rset)Close the cursor object curs before closing the database connection.close(curs)Close the database connection conn.close(conn)Inpu

7 Functions — Alphabetical List7-12See Alsodatabase | datastore | exec | fetch | resultset

cols7-13colsRetrieve number of columns in fetched data setSyntaxnumcols = cols(curs)Descriptionnumcols = cols(curs) returns the number of columns in

7 Functions — Alphabetical List7-14 [ 6] [400876] [1004] [ 8] 'Sail Boat' [ 3] [400999] [1009] [17] &apo

cols7-15See Alsoattr | close | columnnames | columnprivileges | columns | database | fetch| get | rows | width

7 Functions — Alphabetical List7-16columnnamesRetrieve names of columns in fetched data setSyntaxcolumnlist = columnnames(curs)columnlist = columnname

columnnames7-17'SupplierNumber','SupplierName','City','Country','FaxNumber'columnlist contains one

7 Functions — Alphabetical List7-18close(conn)Input Argumentscurs — Database cursordatabase cursor objectDatabase cursor, specified as an open SQL dat

columnprivileges7-19columnprivilegesList database column privilegesSyntaxlp = columnprivileges(dbmeta,'cata','sch','tab&apos

Microsoft Access ODBC for Windows2-19After you complete the data source setup, connect to the Microsoft Access databaseusing Database Explorer or the

7 Functions — Alphabetical List7-20columnsReturn database table column namesSyntaxcolumnlist = columns(conn,catalog)columnlist = columns(conn,catalog,

columns7-21ExamplesRetrieve the Column List for a Catalog Using the Database ConnectionCreate a database connection conn. This code uses database nam

7 Functions — Alphabetical List7-22conn = database('dbname','username','pwd',... 'Vendor',&apos

columns7-23Retrieve the column names in a database table. Here, this code assumes that thedatabase contains the catalog name toy_store, the schema na

7 Functions — Alphabetical List7-24Display the column names for the suppliers table.columnlist{2,2}ans = 'SupplierNumber' 'Suppl

columns7-25 'productNumber' 'Quantity' 'Price' 'inventoryDate'Close the database connection.close

7 Functions — Alphabetical List7-26Database connection, specified as a database connection object created using database.dbmeta — Database metadataobj

commit7-27commitMake database changes permanentSyntaxcommit(conn)Descriptioncommit(conn) makes permanent changes made to the database connection conn

7 Functions — Alphabetical List7-28See Alsodatabase | exec | fastinsert | get | rollback | update

confds7-29confdsConfigure JDBC data source for Visual Query BuilderAlternativesSelect Define JDBC data sources from the Visual Query Builder Query me

2 Getting Started with Database Toolbox2-204Close the connection using Database Explorer by hovering the cursor over the Closebutton ( ) next to the d

7 Functions — Alphabetical List7-30Tip Use the database function to define JDBC data sources programmatically.See Alsodatabase | querybuilder

crossreference7-31crossreferenceRetrieve information about primary and foreign keysSyntaxf = crossreference(dbmeta, 'pcata', 'psch&apo

7 Functions — Alphabetical List7-32Column Description Value1 Catalog that contains primary key, referenced byforeign imported keyorcl2 Schema that con

crossreference7-33See Alsodmd | exportedkeys | get | importedkeys | primarykeys

7 Functions — Alphabetical List7-34cursor.fetchImport data into MATLAB Workspace from cursor object created by execAlternativesRetrieve data using Dat

cursor.fetch7-35Fetching large amounts of data can result in memory or speed issues. In this case, userowLimit to limit how much data you retrieve at

7 Functions — Alphabetical List7-36 Statement: [1x1 database.internal.ODBCStatementHandle]With the native ODBC interface, curs returns an ODBCCurso

cursor.fetch7-37View the contents of the Data element in the cursor object.curs.Dataans = 'New York' 'London' 'Ade

7 Functions — Alphabetical List7-38ans = 'Victorian Doll' 'Train Set' 'Engine Kit'Rerun the fetch function to

cursor.fetch7-39ans = 'Victorian Doll' 'Train Set'ans = 'Engine Kit' 'Painting Set'ans = &a

Microsoft Access ODBC for Windows2-21Connect to Microsoft Access using the native ODBC connection command line.1Connect to the database with the ODBC

7 Functions — Alphabetical List7-40 ans = 13 5 16Close the cursor object.close(curs)Import Boolean DataImport data that includes a BOOLEAN

cursor.fetch7-41setdbprefs('FetchInBatches’,'yes')setdbprefs('FetchBatchSize’,'2')conn = database('dbtoolboxdemo&a

7 Functions — Alphabetical List7-42 [9] [125970] [1003] [13] 'Victorian Doll' [8] [212569] [1001] [ 5] 'T

database7-43databaseConnect to databaseSyntaxconn = database(instance,username,password)conn = database.ODBCConnection(instance,username,password)con

7 Functions — Alphabetical List7-44conn = database.ODBCConnection('dbtoolboxdemo','username','pwd')conn = ODBCConnecti

database7-45close(conn)Connect to Microsoft SQL Server Using Windows AuthenticationConnect to a Microsoft SQL Server database with integrated Windows

7 Functions — Alphabetical List7-46 Instance: 'dbname' UserName: 'username' Driver: 'com.sybase.jdbc4.jdb

database7-47Close the database connection conn.close(conn)Connect to MySQL Using a JDBC DriverConnect to a MySQL database using name-value pair argum

7 Functions — Alphabetical List7-48Fetch data from the database.curs = exec(conn,'SELECT ALL January FROM salesVolume');curs = fetch(curs);d

database7-49Data source setup or database name, specified as a string. Specify a data source forODBC connection, and the database name for JDBC conne

2 Getting Started with Database Toolbox2-22See Alsoclose | databaseMore About• “Working with Database Explorer” on page 4-2

7 Functions — Alphabetical List7-50quotes (' '). You can specify several name and value pair arguments in any order asName1,Value1,...,NameN

database7-51• 'Server'• 'Windows'Specify 'Windows' for Windows Authentication.Example: 'AuthType','Windo

7 Functions — Alphabetical List7-52Property DescriptionConstructor Internal Java or C++ representation of database connectionobjectMessage Database co

database7-53Database JDBC Driver Name and Database URL Example SyntaxDatabase URL: jdbc:sqlserver://localhost:port;database=databasenameMySQL JDBC dr

7 Functions — Alphabetical List7-54Database JDBC Driver Name and Database URL Example SyntaxPostgreSQL withSSL ConnectionJDBC driver: org.postgresql.D

database.catalogs7-55database.catalogsGet database catalog namesSyntaxP = catalogs(conn)DescriptionP = catalogs(conn) returns the catalogs for the da

7 Functions — Alphabetical List7-56database.columnsGet database table column namesSyntaxP = columns(conn)P = columns(conn,C)P = columns(conn,C,S)P = c

database.fetch7-57database.fetchExecute SQL statement to import data into MATLAB workspaceSyntaxresults = fetch(conn,sqlquery)results = fetch(conn,sq

7 Functions — Alphabetical List7-58Output ArgumentsresultsA cell array, table, dataset array, structure, or numeric matrix depending onspecifications

database.fetch7-59 10 1Import Two Columns of Data and View Information About the DataImport the InvoiceNumber and Paid columns from the Invoic

Microsoft SQL Server ODBC for Windows2-23Microsoft SQL Server ODBC for WindowsThis tutorial shows how to set up a data source and connect to your Mic

7 Functions — Alphabetical List7-60• The order of records in your database does not remain constant. Use the SQL ORDERBY command in your sqlquery stat

database.schemas7-61database.schemasGet database schema namesSyntaxP = schemas(conn)DescriptionP = schemas(conn) returns the schema names for the dat

7 Functions — Alphabetical List7-62database.tablesGet database table namesSyntaxT = tables(conn)T = tables(conn,C)T = tables(conn,C,S)DescriptionT = t

datastore7-63datastoreCreate datastore to access collection of data in a databaseThis datastore function creates a DatabaseDatastore object. You can

7 Functions — Alphabetical List7-64sqlquery = 'select * from productTable';dbds = datastore(conn,sqlquery)dbds = DatabaseDatastore with pr

datastore7-65 Data: 0 RowLimit: 0 SQLQuery: 'select * from productTable' Message: [] Type: 'ODBCCursor Ob

7 Functions — Alphabetical List7-66• “Working with a DatabaseDatastore”• “Getting Started with Datastore”See Alsoclose | database | datastore | exec |

hasdata7-67hasdataDetermine if cursor in DatabaseDatastore contains more dataSyntaxtf = hasdata(dbds)Descriptiontf = hasdata(dbds) returns logical 1

7 Functions — Alphabetical List7-68sqlquery = ['select * from [toy_store].[dbo].[productTable] '... 'order by productNumber&a

hasdata7-69 11.00 408143.00 1004.00 11.00 'Convertible' 12.00 210456.00 1010.00

vContentsBefore You Begin1Database Toolbox Product Description . . . . . . . . . . . . . . . . . 1-2Key Features . . . . . . . . . . . . . . . . . . .

2 Getting Started with Database Toolbox2-24If you are running 32-bit or 64-bit MATLAB, the corresponding 32-bit or 64-bit MicrosoftODBC Data Source Ad

7 Functions — Alphabetical List7-70previewDisplay first eight records of data in DatabaseDatastoreSyntaxdata = preview(dbds)Descriptiondata = preview(

preview7-71Preview the first eight records in the data set returned by executing the SQL querysqlquery.preview(dbds)ans = productNumber stockN

7 Functions — Alphabetical List7-72More About• Using DatabaseDatastore Objects• “Working with a DatabaseDatastore”See Alsoclose | database | datastore

read7-73readRead data in DatabaseDatastoreSyntaxdata = read(dbds)data = read(dbds,rowcount)[data,info] = read( ___ )Descriptiondata = read(dbds) retr

7 Functions — Alphabetical List7-74Create a DatabaseDatastore object dbds using the database connection conn andSQL query sqlquery. This SQL query ret

read7-75database with the data source named MS SQL Server Auth. MS SQL Server Authcontains the table named productTable with 15 product records.conn

7 Functions — Alphabetical List7-76database with the data source named MS SQL Server Auth. MS SQL Server Authcontains the table named productTable wit

read7-77close(dbds)Retrieve Data and Database Information Using a Row CountThe default output data type of any datastore is a table. Set the database

7 Functions — Alphabetical List7-78Close the DatabaseDatastore, cursor, and database connection.close(dbds)• “Import Data Using a DatabaseDatastore”•

read7-79Field Descriptiondatasource Data source name for ODBC drivers or adatabase name for JDBC driversoffset Current cursor position in the returne

Microsoft SQL Server ODBC for Windows2-25section in the apps gallery. Alternatively, enter dexplore at the command line. Ifno data sources are set up

7 Functions — Alphabetical List7-80readallRead every record in DatabaseDatastoreSyntaxdata = readall(dbds)Descriptiondata = readall(dbds) retrieves da

readall7-81Read every record in the DatabaseDatastore object dbds.data = readall(dbds)data = productNumber stockNumber supplierNumber un

7 Functions — Alphabetical List7-82Query results, returned as a cell array, matrix, table, structure, or dataset arrayof the records in the data set.

reset7-83resetReset cursor position in DatabaseDatastoreSyntaxreset(dbds)Descriptionreset(dbds) repositions the cursor object in the DatabaseDatastor

7 Functions — Alphabetical List7-84dbds = datastore(conn,sqlquery);Read the data in the data set.readall(dbds)ans = productNumber stockNumber

reset7-85 15.00 899752.00 1011.00 20.00 'Snacks' Close the DatabaseDatastore, cursor, and datab

7 Functions — Alphabetical List7-86Using DatabaseDatastore ObjectsAccess collection of data stored in databaseMATLAB has various datastores that let y

Using DatabaseDatastore Objects7-87dbds = datastore(conn,sqlquery)dbds = DatabaseDatastore with properties: Connection: [1x1 database.ODBCConnec

7 Functions — Alphabetical List7-88Object Functionshasdata previewreadreadall resetcloseCreate ObjectCreate a DatabaseDatastore object using datastore

datainsert7-89datainsertExport MATLAB data into database tableTo export MATLAB data into a database, use these functions: datainsert,fastinsert, and

2 Getting Started with Database Toolbox2-263Click the System DSN tab and then click Add. When setting up an ODBC datasource, you can use a User DSN or

7 Functions — Alphabetical List7-90conn = database('MySQL','username','pwd');Display the last rows in inventoryTable bef

datainsert7-91close(conn)Export MATLAB Numeric Matrix DataEstablish connection conn to a MySQL database with user name username andpassword pwd. This

7 Functions — Alphabetical List7-92tablename = 'salesVolume';datainsert(conn,tablename,colnames,data) Display inserted data in salesVolume.c

datainsert7-93Data Types: charcolnames — Database table column namescell array of stringsDatabase table column names, specified as a cell array of on

7 Functions — Alphabetical List7-94Database ExplorerConfigure, explore, and import database dataDescriptionUsing the Database Explorer app, you can qu

Database Explorer7-95ExamplesDisplay Data from a Single Database TableSet up the data source for the tutorial.mdb database and connect to this databa

7 Functions — Alphabetical List7-96Select (All) to choose all database columns or select check boxes for specific tablecolumns.

Database Explorer7-97To change your display, select or clear check boxes in the Database Browser pane. Thedata updates in the Data Preview pane.The D

7 Functions — Alphabetical List7-98When you select additional tables in the Database Browser pane, the SQL Criteriapanel updates.

Database Explorer7-99Display the contents for the selected tables using the SQL Criteria panel to define a joinof the selected tables. Click the drop

Microsoft SQL Server ODBC for Windows2-27Or, if you want to connect to Microsoft SQL Server without Windows authentication,click the With SQL Server

7 Functions — Alphabetical List7-100

Database Explorer7-101Close the database connection. For details, see “Microsoft Access ODBC for Windows”Query Data Using a Left Outer JoinSet up the

7 Functions — Alphabetical List7-102condition. There are two empty conditions in the SQL Criteria panel on the DatabaseExplorer Toolstrip.From the SQL

Database Explorer7-103rows in suppliers that do not have a match with any row in producttable are paddedwith null values in the final result.In the D

7 Functions — Alphabetical List7-104suppliers.Country, and the third to NOT LIKE. In the last text box, enter UnitedStates and then enter the new cond

Database Explorer7-105Close the database connection. For details, see “Microsoft Access ODBC for Windows”Import Data to the MATLAB WorkspaceSet up th

7 Functions — Alphabetical List7-106Define the data type for a MATLAB variable in the Imported Data panel to store thedata displayed in the Data Previ

Database Explorer7-107Note: When importing large amounts of data, Database Explorer imports data inbatches. The batch size is set to 1,000 rows by de

7 Functions — Alphabetical List7-108Optionally, manipulate the data using MATLAB functions.Close the database connection. For details, see “Microsoft

Database Explorer7-109See AlsoFunctionsclose | database | exec | fetch

2 Getting Started with Database Toolbox2-2810If the connection establishes successfully, this message appears in the SQL ServerODBC Data Source Test d

7 Functions — Alphabetical List7-110dmdConstruct database metadata objectSyntaxdbmeta = dmd(conn)Descriptiondbmeta = dmd(conn) constructs a database m

driver7-111driverConstruct database driver objectSyntaxd = driver('s')Descriptiond = driver('s') constructs a database driver obj

7 Functions — Alphabetical List7-112drivermanagerConstruct database drivermanager objectSyntaxdm = drivermanagerDescriptiondm = drivermanager construc

exec7-113execExecute SQL statement and open cursorSyntaxcurs = exec(conn,sqlquery)curs = exec(conn,sqlquery,qTimeOut)curs = exec(conn,sqlquery,Name,V

7 Functions — Alphabetical List7-114 Data: 0 RowLimit: 0 SQLQuery: 'select * from productTable' Message: [] Typ

exec7-115Connect to the MySQL database. This code assumes you are connecting to a data sourcenamed MySQL with user name username and password pwd.con

7 Functions — Alphabetical List7-116sqlquery = 'select * from productTable';curs = exec(conn,sqlquery,10)curs = Attributes: []

exec7-117curs.Dataans = [8] [212569] [1001] [5] 'Train Set'The select statement is created by using square brackets to conc

7 Functions — Alphabetical List7-118Commit the data.sqlquery = 'commit';exec(conn,sqlquery);After finishing with the cursor object, close it

exec7-119Define a stored procedure named create_table that creates a table namedtest_table by executing this code. This procedure has no input or out

Microsoft SQL Server ODBC for Windows2-2911The ODBC Data Source Administrator dialog box shows the new data source underSystem Data Sources in the Sy

7 Functions — Alphabetical List7-120close(curs)• “Call a Stored Procedure That Returns Data”• “Run a Custom Database Function”Input Argumentsconn — Da

exec7-121Output Argumentscurs — Database cursordatabase cursor objectDatabase cursor, returned as a database cursor object. The properties of this ob

7 Functions — Alphabetical List7-122For a native ODBC connection, the cursor object has only these properties from theprevious list: Data, RowLimit, S

exportedkeys7-123exportedkeysRetrieve information about exported foreign keysSyntaxe = exportedkeys(dbmeta, 'cata', 'sch')e = exp

7 Functions — Alphabetical List7-124Column Description Value2 Schema containing primary key that is exported SCOTT3 Table containing primary key that

fastinsert7-125fastinsertAdd MATLAB data to database tableTo export MATLAB data into a database, use these functions: fastinsert,datainsert, and inse

7 Functions — Alphabetical List7-126• supplierNumber• unitCost• productDescriptionconn = database.ODBCConnection('dbtoolboxdemo','admin

fastinsert7-127• unitCost equal to $20• productDescription equal to 'Cooking Set'Then, convert the cell array to the table data_table.data

7 Functions — Alphabetical List7-128• Price• inventoryDateconn = database('dbtoolboxdemo','admin','admin');Alternatively

fastinsert7-129curs = fetch(curs);curs.Dataans = ... [ 12] [ 1278] [ 0] '2010-10-29 18:17...' [ 13] [ 1700]

2 Getting Started with Database Toolbox2-303Connect without operating system authentication by selecting the data source thatyou set up without Window

7 Functions — Alphabetical List7-130 [13] [ 1700] [14.5000] '2009-05-24 10:58...'Assign multiple rows of data to the cell array

fastinsert7-131 [7779] [ 1160] [14.7000] '2014-10-23 10:21...' [7780] [ 150] [54.5000] '2014-10-23 10:21...&a

7 Functions — Alphabetical List7-132821Calculate the sum of the March sales and assign the result to the variable sumA.sumA = sum(AA(:))sumA = 14

fastinsert7-133Use fastinsert to export the data into the yearlySales table.fastinsert(conn,tablename,colnames,data)fastinsert appends the data as a

7 Functions — Alphabetical List7-134 ... [400876] [3000] [2400] [1500] [1500] [1300] [1100] [ 900] [400999] [3000]

fastinsert7-135The last row contains the inserted data.Close the cursor and database connection.close(curs)close(conn)Insert and Commit DataConnect t

7 Functions — Alphabetical List7-136close(curs)close(conn)Insert Boolean DataConnect to the data source dbtoolboxdemo. This data source identifies a M

fastinsert7-137colnames = {'InvoiceNumber';'InvoiceDate';'productNumber';'Paid'};tablename = 'invoice&ap

7 Functions — Alphabetical List7-138Database table column names, specified as a cell array of one or more strings to denotethe columns in the existing

fetch7-139fetchImport data into MATLAB workspace from cursor object or from execution of SQLstatementSyntaxcurs = fetch(curs)curs = fetch(curs,rowlim

Microsoft SQL Server ODBC for Windows2-31tab. The Close button turns into a red circle ( ). Click it to close the databaseconnection. If you want to

7 Functions — Alphabetical List7-140ExamplesImport All Data Using the Native ODBC Interface and Cursor ObjectCreate a connection conn using the native

fetch7-141ans = 'Victorian Doll' 'Train Set' 'Engine Kit' 'Painting Set' 'Space Cruiser&

7 Functions — Alphabetical List7-142 'London' 'Adelaide' 'Dublin' 'Boston' 'New York'

fetch7-143Rerun the fetch function to return the second three rows of data.curs = fetch(curs, 3);View the data.curs.Dataans = 'Painting Set&

7 Functions — Alphabetical List7-144 'Engine Kit' 'Painting Set'ans = 'Space Cruiser' 'Building Blocks

fetch7-145curs = exec(conn,'select * from productTable order by productNumber',... 'cursorType','scrollable'

7 Functions — Alphabetical List7-146conn = database.ODBCConnection('MySQL','username','pwd');Select all products from th

fetch7-147close(curs)Import Data with Different Formats Using the Cursor ObjectImport data that includes a BOOLEAN field, using the setdbprefs functi

7 Functions — Alphabetical List7-148sqlquery = 'select productdescription from productTable';results = fetch(conn, sqlquery)results = &a

fetch7-149fetch returns all the data by importing it in batches of five rows at a time.Close the database connection.close(conn)• “Fetch Data Increme

2 Getting Started with Database Toolbox2-32close(conn)See Alsoclose | databaseMore About• “Working with Database Explorer” on page 4-2

7 Functions — Alphabetical List7-150Fetch batch size, specified as a scalar denoting the number of rows of data to batch ata time. Use fetchbatchsize

fetch7-151Output Argumentscurs — Database cursordatabase cursor objectDatabase cursor, returned as a database cursor object populated with fetched da

7 Functions — Alphabetical List7-152fetchmultiImport data from multiple resultsetsSyntaxcurs = fetchmulti(curs)Descriptioncurs = fetchmulti(curs) impo

fetchmulti7-153 ResultSet: [1x1 sun.jdbc.odbc.JdbcOdbcResultSet] [1x1 sun.jdbc.odbc.JdbcOdbcResultSet] Cursor: ... [1

7 Functions — Alphabetical List7-154getRetrieve object propertiesSyntaxs = get(object)v = get(object,property)Descriptions = get(object) returns a str

get7-155v = get(dbmeta)v = AllProceduresAreCallable: 1 AllTablesAreSelectable: 1 DataDefinitionCausesTransactio

7 Functions — Alphabetical List7-156 NullsAreSortedAtEnd: 0 NullsAreSortedAtStart: 0 Nul

get7-157• Database connection object, which is created using database• Cursor object, which is created using exec or fetch• Driver object, which is c

7 Functions — Alphabetical List7-158getdatasourcesReturn names of ODBC and JDBC data sources on systemSyntaxd = getdatasourcesDescriptiond = getdataso

getdatasources7-159See Alsodatabase | get | setdbprefs

Microsoft SQL Server JDBC for Windows2-33Microsoft SQL Server JDBC for WindowsThis tutorial shows how to set up a data source and connect to your Mic

7 Functions — Alphabetical List7-160importedkeysReturn information about imported foreign keysSyntaxi = importedkeys(dbmeta, 'cata', 's

importedkeys7-161Column Description Value1 Catalog containing primary key, referenced by foreignimported keyorcl2 Schema containing primary key, refe

7 Functions — Alphabetical List7-162See Alsocrossreference | dmd | exportedkeys | get | primarykeys

indexinfo7-163indexinfoReturn indices and statistics for database tablesSyntaxx = indexinfo(dbmeta, 'cata', 'sch', 'tab&apos

7 Functions — Alphabetical List7-164Column Description Value4 Not unique: 0 if index values can be not unique,1 otherwise05 Index catalog null6 Index

insert7-165insertAdd MATLAB data to database tablesTo export MATLAB data into a database, use these functions: insert, datainsert,and fastinsert. For

7 Functions — Alphabetical List7-166• stockNumber• supplierNumber• unitCost• productDescriptionconn = database.ODBCConnection('dbtoolboxdemo&apos

insert7-167data_table = productNumber stockNumber supplierNumber unitCost productDescription ------------- ----------- -----

7 Functions — Alphabetical List7-168Select and display the data from the yearlySales table.curs = exec(conn,'select * from yearlySales');cur

insert7-169Close the database connection.close(conn)Input Argumentsconn — Database connectiondatabase connection objectDatabase connection, specified

vi ContentsInitial Setup Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-9Choosing Between ODBC and JDBC Drivers . . . . . . . .

2 Getting Started with Database Toolbox2-342In the Sql Server Configuration Manager window, click SQL Server NetworkConfiguration on the left side. Do

7 Functions — Alphabetical List7-170More AboutTips• When working with a JDBC driver connection or a JDBC/ODBC bridge connectionestablished using datab

isconnection7-171isconnectionDetermine if database connections are validSyntaxa = isconnection(conn)Descriptiona = isconnection(conn) returns 1 if t

7 Functions — Alphabetical List7-172isdriverDetect whether driver is valid JDBC driver objectSyntaxa = isdriver(d)Descriptiona = isdriver(d) returns 1

isjdbc7-173isjdbcDetect whether driver is JDBC compliantSyntaxa = isjdbc(d)Descriptiona = isjdbc(d) returns 1 if the driver object d is JDBC complian

7 Functions — Alphabetical List7-174isnullcolumnDetermine if last record read in resultset is NULLSyntaxa = isnullcolumn(rset)Descriptiona = isnullcol

isnullcolumn7-1751Run:curs = fetch(curs,1);rset = resultset(curs);isnullcolumn(rset)ans = 12Verify this result.curs.Dataans = [NaN]See Alsofe

7 Functions — Alphabetical List7-176isreadonlyDetermine if database connection is read onlySyntaxa = isreadonly(conn)Descriptiona = isreadonly(conn) r

isurl7-177isurlDetect whether database URL is validSyntaxa = isurl(d, 's')Descriptiona = isurl(d, 's') returns 1 if the database

7 Functions — Alphabetical List7-178logintimeoutSet or get time allowed to establish database connectionSyntaxtimeout = logintimeout('driver&apos

logintimeout7-179Note: Apple Mac OS platforms do not support logintimeout.ExamplesExample 1 — Get Timeout Value for ODBC ConnectionView the current c

Microsoft SQL Server JDBC for Windows2-356The server restarts enabling TCP/IP. Click Protocols for SQLEXPRESS and right-click TCP/IP. Select Properti

7 Functions — Alphabetical List7-180logintimeout('oracle.jdbc.driver.OracleDriver')ans = 5See Alsodatabase | get | set

namecolumn7-181namecolumnMap resultset column name to resultset column indexSyntaxx = namecolumn(rset, n)Descriptionx = namecolumn(rset, n) maps a re

7 Functions — Alphabetical List7-182pingRetrieve status information about database connectionSyntaxping(conn)Descriptionping(conn) retrieves the statu

ping7-183ping returns the database name, database version, JDBC driver name, JDBC driverversion, maximum number of database connection allowed, user

7 Functions — Alphabetical List7-184close(conn)Input Argumentsconn — Database connectiondatabase connection objectDatabase connection, specified as a

primarykeys7-185primarykeysGet primary key information for database table or schemaSyntaxk = primarykeys(dbmeta, 'cata', 'sch')k

7 Functions — Alphabetical List7-186The results show the primary key information as described in the following table.Column Description Value1 Catalog

procedurecolumns7-187procedurecolumnsGet stored procedure parameters and result columns of catalogsSyntaxpc = procedurecolumns(dbmeta, 'cata&apo

7 Functions — Alphabetical List7-188The results show stored procedure parameter and result information. Because two rowsof data are returned, there ar

procedurecolumns7-189Following is a full description of the procedurecolumns results for the first row(Month).Column Description Value for First Row1

2 Getting Started with Database Toolbox2-36Step 3. Set up the operating system authentication.Windows authentication lets you to connect to your datab

7 Functions — Alphabetical List7-190proceduresGet stored procedures for catalogsSyntaxp = procedures(dbmeta, 'cata')p = procedures(dbmeta, &

procedures7-191curs = exec(conn,'sp_customer_list');curs = fetch(curs)curs = Attributes: [] Data: {10x2 cell} DatabaseObject:

7 Functions — Alphabetical List7-192querybuilderStart Visual Query Builder GUI to import and export dataCompatibilityThe querybuilder function will be

querytimeout7-193querytimeoutGet time specified for SQL queries to succeedSyntaxtimeout = querytimeout(curs)Descriptiontimeout = querytimeout(curs) r

7 Functions — Alphabetical List7-194registerLoad database driverSyntaxregister(d)Descriptionregister(d) loads the database driver object d. Use unregi

resultset7-195resultsetConstruct resultset objectSyntaxrset = resultset(curs)Descriptionrset = resultset(curs) creates a resultset object rset for th

7 Functions — Alphabetical List7-196rollbackUndo database changesSyntaxrollback(conn)Descriptionrollback(conn) reverses changes made to a database usi

rollback7-197See Alsocommit | database | fastinsert | get | insert | update

7 Functions — Alphabetical List7-198rowsReturn number of rows in fetched data setSyntaxnumrows = rows(curs)Descriptionnumrows = rows(curs) returns the

rows7-199Return the number of rows in the Data property of curs.numrows = rows(curs)numrows = 5Display the rows of data in the Data property of c

Microsoft SQL Server JDBC for Windows2-373Close MATLAB if it is running.4Navigate to the folder and create a file called javalibrarypath.txt in the f

7 Functions — Alphabetical List7-200rsmdConstruct resultset metadata objectSyntaxrsmeta = rsmd(rset)Descriptionrsmeta = rsmd(rset) creates a resultset

runsqlscript7-201runsqlscriptRun SQL script on databaseSyntaxresults = runsqlscript(connect,sqlfilename)results = runsqlscript(connect,sqlfilename,Na

7 Functions — Alphabetical List7-202 1x2 array of cursor objectsThe SQL script has two queries, and returns two results when executed.Display the resu

runsqlscript7-203To get the file of SQL commands, navigate to \toolbox\database\dbdemos\compare_sales.sql in your MATLAB root folder, or copy and pas

7 Functions — Alphabetical List7-204close(res2)close(conn)Run SQL Script to Fetch Data in BatchesRun SQL commands from a file on a connected data sour

runsqlscript7-205results = runsqlscript(conn, 'compare_sales.sql')results = 1x2 array of cursor objectsBatching occurs internally within fe

7 Functions — Alphabetical List7-206importing large amounts of data. Retrieving data in increments helps reduce overallretrieval time.Example: 'r

runsqlscript7-207• An SQL script containing more than 25,000 characters causes runsqlscript toreturn an error.More AboutBatchOne or more SQL statemen

7 Functions — Alphabetical List7-208runstoredprocedureCall stored procedure with and without input and output argumentsThis function calls a stored pr

runstoredprocedure7-209ExamplesCall a Stored Procedure Without Input and Output ArgumentsDefine a stored procedure named create_table that creates a

2 Getting Started with Database Toolbox2-38Step 5. Set up the data source using Database Explorer.This step is required only for connecting to Databas

7 Functions — Alphabetical List7-210close(conn)Call a Stored Procedure with Input ArgumentsDefine a stored procedure named insert_data that inserts a

runstoredprocedure7-211Close the database connection conn.close(conn)Call a Stored Procedure with Output ArgumentsDefine a stored procedure named max

7 Functions — Alphabetical List7-212results{1}ans = 35000 The maximum sales volume in December is 35,000.Close the database connection conn.close(conn

runstoredprocedure7-213 [1x1 java.math.BigDecimal]results is a cell array that contains the supplier count as a Java decimal data type.Display the

7 Functions — Alphabetical List7-214GOCreate a Microsoft SQL Server database connection conn using the JDBC driver. Fordetails, see “Connecting to a D

runstoredprocedure7-215Here, the narrow unit cost range returns only one product. If the unit cost range iswider, then more than one product might sa

7 Functions — Alphabetical List7-216Example: {java.sql.Types.NUMERIC}Data Types: cellOutput Argumentsresults — Stored procedure resultslogical | cell

set7-217setSet properties for database, cursor, or drivermanager objectSyntaxset(object, 'property', value)set(object)Descriptionset(object

7 Functions — Alphabetical List7-218Database Connection ObjectsThe allowable values for property and value for a database connection object appear int

set7-219Property Value Descriptionset, fetch behaves differently dependingon what type of database you are using.Drivermanager ObjectsThe allowable p

Microsoft SQL Server JDBC for Windows2-393Select MICROSOFT SQL SERVER from the Vendor list. After selecting the vendor,if you did not add the JDBC dr

7 Functions — Alphabetical List7-220 SQLQuery: 'select * from EMP' Message: [] Type: 'Database Cursor Object&apo

set7-221Example 2 — Set the AutoCommit Flag to OnThis example shows what happens when you run a database update function on adatabase whose AutoCommi

7 Functions — Alphabetical List7-222Example 4 — Set the AutoCommit Flag to Off and Roll Back DataThis example runs update to insert data into a databa

setdbprefs7-223setdbprefsSet preferences for retrieval format, errors, NULLs, and moreSyntaxsetdbprefsv = setdbprefssetdbprefs(property)setdbprefs(pr

7 Functions — Alphabetical List7-224 NullNumberRead: '0' NullNumberWrite: 'NaN' NullStringRe

setdbprefs7-225conn = database('MySQL','username','pwd');Alternatively, you can use the native ODBC interface for an OD

7 Functions — Alphabetical List7-226setdbprefs('DataReturnFormat','structure')Import data into the MATLAB workspace.curs = exec(co

setdbprefs7-227conn = database('MySQL','username','pwd');Specify NaN for the NullNumberWrite format.setdbprefs('Nu

7 Functions — Alphabetical List7-228 [24] [ NaN] [ 30] '2014-10-22 11:19...'Close the connection.close(conn)Specify Error Ha

setdbprefs7-229Specify the report format for the ErrorHandling preference.setdbprefs('ErrorHandling','report')With the ErrorHandl

2 Getting Started with Database Toolbox2-405Create a data source without Windows authentication by setting theAuthentication Type to Server.Or, create

7 Functions — Alphabetical List7-230 Statement: 0 Fetch: 0The error appears in the cursor object Message field. Furthermore, the D

setdbprefs7-231setdbprefs DataReturnFormat: 'numeric' ErrorHandling: 'store' NullNumberRead

7 Functions — Alphabetical List7-232ans = yesSave PreferencesYou can save your preferences to a MAT-file to use them in future MATLAB sessions.Supp

setdbprefs7-233Database preference value, specified as a string to denote a value for a particulardatabase preference property. To set multiple datab

7 Functions — Alphabetical List7-234sql2nativeConvert JDBC SQL grammar to SQL grammar native to systemSyntaxn = sql2native(conn, 'sqlquery')

supports7-235supportsDetect whether property is supported by database metadata objectSyntaxa = supports(dbmeta)a = supports(dbmeta, 'property&ap

7 Functions — Alphabetical List7-236See Alsodatabase | dmd | get | ping

tableprivileges7-237tableprivilegesReturn database table privilegesSyntaxtp = tableprivileges(dbmeta, 'cata')tp = tableprivileges(dbmeta, &

7 Functions — Alphabetical List7-238tablesReturn database table namesSyntaxt = tables(dbmeta, 'cata')t = tables(dbmeta, 'cata', &a

tables7-239See Alsoattr | bestrowid | dmd | indexinfo | tableprivileges

Microsoft SQL Server JDBC for Windows2-41Database Explorer connects to your database and displays its contents in a tabnamed with the data source nam

7 Functions — Alphabetical List7-240unregisterUnload database driverSyntaxunregister(d)Descriptionunregister(d) unloads the database driver object d,

update7-241updateReplace data in database table with MATLAB dataSyntaxupdate(conn,tablename,colnames,data,whereclause)Descriptionupdate(conn,tablenam

7 Functions — Alphabetical List7-242curs.Dataans = [ 1] [1700] [14.5000] '2014-09-23 09:38...' [ 2] [1200] [ 9]

update7-243Close the database connection.close(conn)Update Multiple Records with Multiple ConditionsCreate a database connection conn using the dbtoo

7 Functions — Alphabetical List7-244Update the column Quantity in the inventoryTable for the products with productnumbers equal to 5 and 8. Create a c

update7-245• inventoryDateconn = database('dbtoolboxdemo','','');Import all data from inventoryTable using conn. Store

7 Functions — Alphabetical List7-246curs.Dataans = ... [ 5] [10000] [ 5.5000] '2012-09-14 15:00...' [ 6] [ 4540] [

update7-247ans = [ 1] [ 1700] [14.5000] '2014-10-20 00:00...' [ 2] [ 1200] [ 9.3000] '2014-10-20 00:00...&apo

7 Functions — Alphabetical List7-248curs = fetch(curs);curs.Dataans = [ 1] [ 1700] [14.5000] '2014-10-20 00:00...' [ 2] [

update7-249data — Update datacell array | numeric matrix | structureUpdate data, specified as a MATLAB variable with cell array, numeric matrix, orst

2 Getting Started with Database Toolbox2-42connection. If you want to close Database Explorer and all database connections,click the Close button ( )

7 Functions — Alphabetical List7-250• Running an update operation that matches the one that you just ran can cause thiserror message to appear.??? Err

versioncolumns7-251versioncolumnsAutomatically update table columnsSyntaxvl = versioncolumns(dbmeta, 'cata')vl = versioncolumns(dbmeta, &ap

7 Functions — Alphabetical List7-252See Alsocolumns | dmd | get

width7-253widthReturn field size of column in fetched data setSyntaxcolsize = width(cursor, colnum)Descriptioncolsize = width(cursor, colnum) return

Microsoft SQL Server JDBC for Windows2-43Server. For example, the following code assumes you are connecting to a databasenamed dbname with user name

viiStep 3. Add the JDBC driver to the MATLAB static Java classpath. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-

2 Getting Started with Database Toolbox2-44Oracle ODBC for WindowsThis tutorial shows how to set up a data source and connect to your Oracle database.

Oracle ODBC for Windows2-45Step 2. Verify the driver installation.The ODBC driver is typically preinstalled on your computer. For details about the d

2 Getting Started with Database Toolbox2-46on a machine. Any data sources a user defines under User DSN are seen only by thatspecific user. Conversely

Oracle ODBC for Windows2-476Click Test Connection to test the connection to your database. The Oracle ODBCDriver Connect dialog box opens. If you are

2 Getting Started with Database Toolbox2-48Oracle JDBC for WindowsThis tutorial shows how to set up a data source and connect to your Oracle database.

Oracle JDBC for Windows2-496Add the Oracle OCI library full path to the Windows Path environment variable.7Open MATLAB.For details about Java librari

2 Getting Started with Database Toolbox2-50The Create a New JDBC data source dialog box opens.

Oracle JDBC for Windows2-513Select ORACLE from the Vendor list. After selecting the vendor, if you did notadd the JDBC driver file path to the Java c

2 Getting Started with Database Toolbox2-526To establish the data source without Windows authentication, set Driver Type tothin.7Click Test to test th

Oracle JDBC for Windows2-53Or, to connect with Windows authentication, select the data source that you set up.Leave the user name and password blank.

viii ContentsSybase ODBC for Windows . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-89Step 1. Check the 32-bit and 64-bit compatibility. . .

2 Getting Started with Database Toolbox2-542Close the connection using Database Explorer by hovering the cursor over the Closebutton ( ) next to the O

Oracle JDBC for Windows2-55dbname can be the service name or the Oracle system identifier (SID) depending onyour specific Oracle database setup. For

2 Getting Started with Database Toolbox2-56• “Bringing Java Classes into MATLAB Workspace”

MySQL ODBC for Windows2-57MySQL ODBC for WindowsThis tutorial shows how to set up a data source and connect to your MySQL database.This tutorial uses

2 Getting Started with Database Toolbox2-58If you are running 32-bit or 64-bit MATLAB, the corresponding 32-bit or 64-bit MicrosoftODBC Administrator

MySQL ODBC for Windows2-593Click the System DSN tab and then click Add. When setting up an ODBC datasource, you can use a User DSN or System DSN. A U

2 Getting Started with Database Toolbox2-60Enter your database name in the Database field. Leave all tabs under the Detailsbutton with default setting

MySQL ODBC for Windows2-612In the Connect to a Data Source dialog box, connect to your database by selecting thedata source name for the MySQL databa

2 Getting Started with Database Toolbox2-62Close button turns into a red circle ( ). Click it to close the database connection. Ifyou want to close Da

MySQL JDBC for Windows2-63MySQL JDBC for WindowsThis tutorial shows how to set up a data source and connect to your MySQL database.This tutorial uses

ixStep 3. Set up the data source using Database Explorer. . . 2-126Step 4. Connect using Database Explorer or the commandline. . . . . . . . . . . . .

2 Getting Started with Database Toolbox2-64Step 3. Set up the data source using Database Explorer.This step is required only for connecting to Databas

MySQL JDBC for Windows2-653Select MYSQL from the Vendor list. After selecting the vendor, if you did not add theJDBC driver file path to the Java cla

2 Getting Started with Database Toolbox2-666Enter a data source name in the Data Source Name field in the Create a NewJDBC data source dialog box. Use

MySQL JDBC for Windows2-67Database Explorer connects to your database and displays its contents in a tabnamed with the data source name.2Close the co

2 Getting Started with Database Toolbox2-68Connect to MySQL using the JDBC connection command line.1Use the Vendor name-value pair argument of databas

PostgreSQL ODBC for Windows2-69PostgreSQL ODBC for WindowsThis tutorial shows how to set up a data source and connect to your PostgreSQLdatabase. Thi

2 Getting Started with Database Toolbox2-70If you are running 32-bit or 64-bit MATLAB, the corresponding 32-bit or 64-bit MicrosoftODBC Data Source Ad

PostgreSQL ODBC for Windows2-71section in the apps gallery. Alternatively, enter dexplore at the command line. Ifno data sources are set up, a messag

2 Getting Started with Database Toolbox2-723Click the System DSN tab and then click Add. When setting up an ODBC datasource, you can use a User DSN or

PostgreSQL ODBC for Windows2-737Click Save in the PostgreSQL ANSI ODBC Driver (psqlODBC) Setup dialog box.The ODBC Data Source Administrator dialog b