Database Toolbox User's Guide

738 Pages·2016·7.85 MB·English

™
Database Toolbox
User's Guide
R2017b
How to Contact MathWorks
Latest news: www.mathworks.com
Sales and services: www.mathworks.com/sales_and_services
User community: www.mathworks.com/matlabcentral
Technical support: www.mathworks.com/support/contact_us
Phone: [phone]
The MathWorks, Inc.
3 Apple Hill Drive
Natick, MA 01760-2098
™
Database Toolbox User's Guide
© COPYRIGHT 1998–2017 by The MathWorks, Inc.
The software described in this document is furnished under a license agreement. The software may be
used or copied only under the terms of the license agreement. No part of this manual may be photocopied
or reproduced in any form without prior written consent from The MathWorks, Inc.
FEDERAL ACQUISITION: This provision applies to all acquisitions of the Program and Documentation
by, for, or through the federal government of the United States. By accepting delivery of the Program or
Documentation, the government hereby agrees that this software or documentation qualifies as
commercial computer software or commercial computer software documentation as such terms are used or
defined in FAR 12.212, DFARS Part 227.72, and DFARS 252.227-7014. Accordingly, the terms and
conditions of this Agreement and only those rights specified in this Agreement, shall pertain to and govern
the use, modification, reproduction, release, performance, display, and disclosure of the Program and
Documentation by the federal government (or other entity acquiring for or through the federal
government) and shall supersede any conflicting contractual terms or conditions. If this License fails to
meet the government's needs or is inconsistent in any respect with federal procurement law, the
government agrees to return the Program and Documentation, unused, to The MathWorks, Inc.
Trademarks
MATLAB and Simulink are registered trademarks of The MathWorks, Inc. See
www.mathworks.com/trademarks for a list of additional trademarks. Other product or brand
names may be trademarks or registered trademarks of their respective holders.
Patents
MathWorks products are protected by one or more U.S. patents. Please see
www.mathworks.com/patents for more information.
Revision History
®
May 1998 Online Only New for Version 1 for MATLAB 5.2
July 1998 First Printing For Version 1
Online only June 1999 Revised for Version 2 (Release 11)
December 1999 Second printing For Version 2 (Release 11)
Online only September 2000 Revised for Version 2.1 (Release 12)
June 2001 Third printing Revised for Version 2.2 (Release 12.1)
July 2002 Online only Revised for Version 2.2.1 (Release 13)
November 2002 Fourth printing Version 2.2.1
June 2004 Fifth printing Revised for Version 3.0 (Release 14)
October 2004 Online only Revised for Version 3.1 (Release 14SP1)
March 2005 Online only Revised for Version 3.0.2 (Release 14SP2)
September 2005 Online only Revised for Version 3.1 (Release 14SP3)
March 2006 Online only Revised for Version 3.1.1 (Release 2006a)
September 2006 Online only Revised for Version 3.2 (Release 2006b)
October 2006 Sixth printing Revised for Version 3.2 (Release 2006b)
March 2007 Online only Revised for Version 3.3 (Release 2007a)
September 2007 Seventh printing Revised for Version 3.4 (Release 2007b)
March 2008 Online only Revised for Version 3.4.1 (Release 2008a)
October 2008 Online only Revised for Version 3.5 (Release 2008b)
March 2009 Online only Revised for Version 3.5.1 (Release 2009a)
September 2009 Online only Revised for Version 3.6 (Release 2009b)
March 2010 Online only Revised for Version 3.7 (Release 2010a)
September 2010 Online only Revised for Version 3.8 (Release 2010b)
reApril 2011 Online only Revised for Version 3.9 (Release 2011a)
September 2011 Online only Revised for Version 3.10 (Release 2011b)
March 2012 Online only Revised for Version 3.11 (Release 2012a)
September 2012 Online only Revised for Version 4.0 (Release 2012b)
March 2013 Online only Revised for Version 4.1 (Release 2013a)
September 2013 Online only Revised for Version 5.0 (Release 2013b)
March 2014 Online only Revised for Version 5.1 (Release 2014a)
October 2014 Online only Revised for Version 5.2 (Release 2014b)
March 2015 Online only Revised for Version 5.2.1 (Release 2015a)
September 2015 Online only Revised for Version 6.0 (Release 2015b)
March 2016 Online only Revised for Version 6.1 (Release 2016a)
September 2016 Online only Revised for Version 7.0 (Release 2016b)
March 2017 Online only Revised for Version 7.1 (Release 2017a)
September 2017 Online only Revised for Version 8.0 (Release 2017b)
Contents
Before You Begin
1
Database Toolbox Product Description . . . . . . . . . . . . . . . . . . 1-2
Key Features . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-2
Data Type Support . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-3
Data Retrieval Restrictions . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-5
Spaces in Table Names or Column Names . . . . . . . . . . . . . . . 1-5
Quotation Marks in Table Names or Column Names . . . . . . . 1-5
Reserved Words in Column Names . . . . . . . . . . . . . . . . . . . . . 1-5
Getting Started with Database Toolbox
2
Access Relational Database Data in MATLAB . . . . . . . . . . . . . 2-3
Working with MATLAB Interface to SQLite . . . . . . . . . . . . . . 2-6
MATLAB Interface to SQLite Advantages . . . . . . . . . . . . . . . 2-6
SQLite JDBC Connection Differences . . . . . . . . . . . . . . . . . . . 2-6
MATLAB Interface to SQLite Workflow . . . . . . . . . . . . . . . . . 2-7
MATLAB Interface to SQLite Limitations . . . . . . . . . . . . . . . 2-7
Connection Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-9
Creating or Connecting to Data Source . . . . . . . . . . . . . . . . . . 2-9
Defining Operating System Authentication . . . . . . . . . . . . . . 2-9
Connection Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-9
Setup Requirements for Database Connection . . . . . . . . . . . 2-12
v
Choosing Between ODBC and JDBC Drivers . . . . . . . . . . . . 2-13
Defining Database Drivers . . . . . . . . . . . . . . . . . . . . . . . . . . 2-13
Deciding Between ODBC and JDBC Drivers . . . . . . . . . . . . . 2-13
Configuring Driver and Data Source . . . . . . . . . . . . . . . . . . . 2-15
Microsoft Access ODBC for Windows . . . . . . . . . . . . . . . . . . . 2-18
Step 1. Setup the sample Access database. . . . . . . . . . . . . . . 2-18
Step 2. Verify the driver installation. . . . . . . . . . . . . . . . . . . 2-18
Step 3. Set up the data source using the Database Explorer
app. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-19
Step 4. Connect using the Database Explorer app or the
command line. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-21
Microsoft SQL Server ODBC for Windows . . . . . . . . . . . . . . . 2-24
Step 1. Verify the driver installation. . . . . . . . . . . . . . . . . . . 2-24
Step 2. Set up the data source using the Database Explorer
app. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-24
Step 3. Connect using the Database Explorer app or the
command line. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-29
Microsoft SQL Server JDBC for Windows . . . . . . . . . . . . . . . 2-31
Step 1. Verify the driver installation. . . . . . . . . . . . . . . . . . . 2-31
Step 2. Verify the port number. . . . . . . . . . . . . . . . . . . . . . . . 2-31
Step 3. Set up the operating system authentication. . . . . . . . 2-34
Step 4. Add the JDBC driver to the MATLAB static Java class
path. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-35
Step 5. Set up the data source using the Database Explorer
app. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-36
Step 6. Connect using the Database Explorer app or the
command line. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-38
Oracle ODBC for Windows . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-41
Step 1. Verify the driver installation. . . . . . . . . . . . . . . . . . . 2-41
Step 2. Set up the data source using the Database Explorer
app. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-41
Step 3. Connect using the Database Explorer app or the
command line. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-45
Oracle JDBC for Windows . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-47
Step 1. Verify the driver installation. . . . . . . . . . . . . . . . . . . 2-47
Step 2. Set up the operating system authentication. . . . . . . . 2-47
vi Contents
Step 3. Add the JDBC driver to the MATLAB static Java class
path. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-48
Step 4. Set up the data source using the Database Explorer
app. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-48
Step 5. Connect using the Database Explorer app or the
command line. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-51
MySQL ODBC for Windows . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-54
Step 1. Verify the driver installation. . . . . . . . . . . . . . . . . . . 2-54
Step 2. Set up the data source using the Database Explorer
app. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-54
Step 3. Connect using the Database Explorer app or the
command line. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-58
MySQL JDBC for Windows . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-60
Step 1. Verify the driver installation. . . . . . . . . . . . . . . . . . . 2-60
Step 2. Add the JDBC driver to the MATLAB static Java class
path. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-60
Step 3. Set up the data source using the Database Explorer
app. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-61
Step 4. Connect using the Database Explorer app or the
command line. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-63
PostgreSQL ODBC for Windows . . . . . . . . . . . . . . . . . . . . . . . 2-65
Step 1. Verify the driver installation. . . . . . . . . . . . . . . . . . . 2-65
Step 2. Set up the data source using the Database Explorer
app. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-65
Step 3. Connect using the Database Explorer app or the
command line. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-69
PostgreSQL JDBC for Windows . . . . . . . . . . . . . . . . . . . . . . . 2-71
Step 1. Verify the driver installation. . . . . . . . . . . . . . . . . . . 2-71
Step 2. Add the JDBC driver to the MATLAB static Java class
path. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-71
Step 3. Set up the data source using the Database Explorer
app. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-72
Step 4. Connect using the Database Explorer app or the
command line. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-74
SQLite JDBC for Windows . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-76
Step 1. Verify the driver installation. . . . . . . . . . . . . . . . . . . 2-76
Step 2. Add the JDBC driver to the MATLAB static Java class
path. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-76
vii
Step 3. Set up the data source using the Database Explorer
app. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-77
Step 4. Connect using the Database Explorer app or the
command line. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-79
Microsoft SQL Server JDBC for macOS . . . . . . . . . . . . . . . . . 2-82
Step 1. Verify the driver installation. . . . . . . . . . . . . . . . . . . 2-82
Step 2. Add the JDBC driver to the MATLAB static Java class
path. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-82
Step 3. Set up the data source using the Database Explorer
app. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-83
Step 4. Connect using the Database Explorer app or the
command line. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-85
Microsoft SQL Server JDBC for Linux . . . . . . . . . . . . . . . . . . 2-87
Step 1. Verify the driver installation. . . . . . . . . . . . . . . . . . . 2-87
Step 2. Add the JDBC driver to the MATLAB static Java class
path. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-87
Step 3. Set up the data source using the Database Explorer
app. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-88
Step 4. Connect using the Database Explorer app or the
command line. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-90
Oracle JDBC for macOS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-93
Step 1. Verify the driver installation. . . . . . . . . . . . . . . . . . . 2-93
Step 2. Add the JDBC driver to the MATLAB static Java class
path. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-93
Step 3. Set up the data source using the Database Explorer
app. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-94
Step 4. Connect using the Database Explorer app or the
command line. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-96
Oracle JDBC for Linux . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-99
Step 1. Verify the driver installation. . . . . . . . . . . . . . . . . . . 2-99
Step 2. Add the JDBC driver to the MATLAB static Java class
path. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-99
Step 3. Set up the data source using the Database Explorer
app. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-100
Step 4. Connect using the Database Explorer app or the
command line. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-102
MySQL JDBC for macOS . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-105
Step 1. Verify the driver installation. . . . . . . . . . . . . . . . . . 2-105
viii Contents
Step 2. Add the JDBC driver to the MATLAB static Java class
path. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-105
Step 3. Set up the data source using the Database Explorer
app. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-106
Step 4. Connect using the Database Explorer app or the
command line. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-108
MySQL JDBC for Linux . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-110
Step 1. Verify the driver installation. . . . . . . . . . . . . . . . . . 2-110
Step 2. Add the JDBC driver to the MATLAB static Java class
path. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-110
Step 3. Set up the data source using the Database Explorer
app. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-111
Step 4. Connect using the Database Explorer app or the
command line. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-113
PostgreSQL JDBC for macOS . . . . . . . . . . . . . . . . . . . . . . . . 2-115
Step 1. Verify the driver installation. . . . . . . . . . . . . . . . . . 2-115
Step 2. Add the JDBC driver to the MATLAB static Java class
path. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-115
Step 3. Set up the data source using the Database Explorer
app. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-116
Step 4. Connect using the Database Explorer app or the
command line. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-118
PostgreSQL JDBC for Linux . . . . . . . . . . . . . . . . . . . . . . . . . 2-120
Step 1. Verify the driver installation. . . . . . . . . . . . . . . . . . 2-120
Step 2. Add the JDBC driver to the MATLAB static Java class
path. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-120
Step 3. Set up the data source using the Database Explorer
app. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-121
Step 4. Connect using the Database Explorer app or the
command line. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-123
SQLite JDBC for macOS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-125
Step 1. Verify the driver installation. . . . . . . . . . . . . . . . . . 2-125
Step 2. Add the JDBC driver to the MATLAB static Java class
path. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-125
Step 3. Set up the data source using the Database Explorer
app. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-126
Step 4. Connect using the Database Explorer app or the
command line. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-128
ix
SQLite JDBC for Linux . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-131
Step 1. Verify the driver installation. . . . . . . . . . . . . . . . . . 2-131
Step 2. Add the JDBC driver to the MATLAB static Java class
path. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-131
Step 3. Set up the data source using the Database Explorer
app. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-132
Step 4. Connect using the Database Explorer app or the
command line. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-134
Other ODBC- or JDBC-Compliant Databases . . . . . . . . . . . 2-137
ODBC-Compliant Databases . . . . . . . . . . . . . . . . . . . . . . . . 2-137
JDBC-Compliant Databases . . . . . . . . . . . . . . . . . . . . . . . . 2-137
Connecting to Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-140
Microsoft Access . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-140
Microsoft SQL Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-140
Oracle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-140
MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-141
PostgreSQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-141
SQLite . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-141
Other ODBC- or JDBC-Compliant Databases . . . . . . . . . . . 2-142
Data Import Using Database Explorer App or Command
Line . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-143
Data Import Using Database Explorer App . . . . . . . . . . . . . 2-143
Data Import Using Command Line . . . . . . . . . . . . . . . . . . . 2-144
Custom Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-145
SQL Queries Saved in Scripts or Files . . . . . . . . . . . . . . . . . 2-145
Inserting Data Using Command Line . . . . . . . . . . . . . . . . . . 2-146
Working with Large Data Sets . . . . . . . . . . . . . . . . . . . . . . . . 2-148
Connect to a Database with Maximum Performance . . . . . . 2-148
Import Large Data Sets into MATLAB . . . . . . . . . . . . . . . . 2-148
Export Large Data Sets from MATLAB . . . . . . . . . . . . . . . . 2-149
Access Large Data Using a DatabaseDatastore . . . . . . . . . . 2-149
Deploying Database Application with MATLAB Compiler 2-150
Create and Deploy Database Application . . . . . . . . . . . . . . 2-150
About Driver Configurations . . . . . . . . . . . . . . . . . . . . . . . . 2-150
Working with Database Toolbox Preferences . . . . . . . . . . . 2-152
x Contents