™ 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: 508-647-7000 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