MATLAB Database Toolbox Users Guide 2021 (The MathWorks, Inc.) (Z-Library)

Database Toolbox™ User's Guide R2021b

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. 1 Apple Hill Drive Natick, MA 01760-2098 Database Toolbox™ User's Guide © COPYRIGHT 1998–2021 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) April 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) March 2018 Online only Revised for Version 8.1 (Release 2018a) September 2018 Online only Revised for Version 9.0 (Release 2018b) March 2019 Online only Revised for Version 9.1 (Release 2019a) September 2019 Online only Revised for Version 9.2 (Release 2019b) March 2020 Online only Revised for Version 9.2.1 (Release 2020a) September 2020 Online only Revised for Version 10.0 (Release 2020b) March 2021 Online only Revised for Version 10.1 (Release 2021a) September 2021 Online only Revised for Version 10.2 (Release 2021b)

Before You Begin 1 Database Toolbox Product Description . . . . . . . . . . . . . . . . . . . . . . . . . . . . 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-2 Working with MATLAB Interface to SQLite . . . . . . . . . . . . . . . . . . . . . . . . . 2-5 MATLAB Interface to SQLite Advantages . . . . . . . . . . . . . . . . . . . . . . . . . 2-5 SQLite JDBC Connection Differences . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-5 MATLAB Interface to SQLite Workflow . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-5 MATLAB Interface to SQLite Limitations . . . . . . . . . . . . . . . . . . . . . . . . . . 2-6 Connection Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-7 Creating or Connecting to Data Source . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-7 Defining Operating System Authentication . . . . . . . . . . . . . . . . . . . . . . . . 2-7 Connection Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-7 Setup Requirements for Database Connection . . . . . . . . . . . . . . . . . . . . . 2-10 Choosing Between ODBC and JDBC Drivers . . . . . . . . . . . . . . . . . . . . . . . 2-11 Defining Database Drivers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-11 Deciding Between ODBC and JDBC Drivers . . . . . . . . . . . . . . . . . . . . . . 2-11 Configuring Driver and Data Source . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-13 Configure Driver and Create Data Source . . . . . . . . . . . . . . . . . . . . . . . . 2-13 Limitations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-14 Create JDBC Data Source and Set Options Programmatically . . . . . . . . 2-16 Microsoft Access ODBC for Windows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-18 Step 1. Set up the sample Access database. . . . . . . . . . . . . . . . . . . . . . . 2-18 Step 2. Verify the driver installation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-18 v Contents

Step 3. Set up the data source using the Database Explorer app. . . . . . . 2-18 Step 4. Connect using the Database Explorer app or the command line. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-20 Microsoft SQL Server ODBC for Windows . . . . . . . . . . . . . . . . . . . . . . . . . 2-22 Step 1. Verify the driver installation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-22 Step 2. Set up the data source using the Database Explorer app. . . . . . . 2-22 Step 3. Connect using the Database Explorer app or the command line. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-25 Microsoft SQL Server ODBC for Windows DSN-Less Connection . . . . . . 2-27 Step 1. Verify the driver installation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-27 Step 2. Connect using the DSN-less connection string and command line. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-27 Microsoft SQL Server JDBC for Windows . . . . . . . . . . . . . . . . . . . . . . . . . 2-28 Step 1. Verify the driver installation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-28 Step 2. Verify the port number. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-28 Step 3. Set up the operating system authentication. . . . . . . . . . . . . . . . . 2-30 Step 4. Set up the data source. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-30 Step 5. Connect using the Database Explorer app or the command line. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-33 Oracle ODBC for Windows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-35 Step 1. Verify the driver installation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-35 Step 2. Set up the data source using the Database Explorer app. . . . . . . 2-35 Step 3. Connect using the Database Explorer app or the command line. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-38 Oracle JDBC for Windows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-40 Step 1. Verify the driver installation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-40 Step 2. Set up the operating system authentication. . . . . . . . . . . . . . . . . 2-40 Step 3. Set up the data source. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-40 Step 4. Connect using the Database Explorer app or the command line. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-44 MySQL ODBC for Windows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-46 Step 1. Verify the driver installation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-46 Step 2. Set up the data source using the Database Explorer app. . . . . . . 2-46 Step 3. Connect using the Database Explorer app or the command line. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-48 MySQL ODBC for Windows DSN-Less Connection . . . . . . . . . . . . . . . . . . 2-50 Step 1. Verify the driver installation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-50 Step 2. Connect using the DSN-less connection string and command line. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-50 MySQL JDBC for Windows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-51 Step 1. Verify the driver installation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-51 Step 2. Set up the data source. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-51 Step 3. Connect using the Database Explorer app or the command line. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-53 PostgreSQL ODBC for Windows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-55 Step 1. Verify the driver installation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-55 vi Contents

Step 2. Set up the data source using the Database Explorer app. . . . . . . 2-55 Step 3. Connect using the Database Explorer app or the command line. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-57 PostgreSQL ODBC for Windows DSN-Less Connection . . . . . . . . . . . . . . 2-59 Step 1. Verify the driver installation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-59 Step 2. Connect using the DSN-less connection string and command line. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-59 PostgreSQL JDBC for Windows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-60 Step 1. Verify the driver installation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-60 Step 2. Set up the data source. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-60 Step 3. Connect using the Database Explorer app or the command line. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-62 SQLite JDBC for Windows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-64 Step 1. Verify the driver installation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-64 Step 2. Set up the data source. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-64 Step 3. Connect using the Database Explorer app or the command line. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-67 Microsoft SQL Server JDBC for macOS . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-69 Step 1. Verify the driver installation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-69 Step 2. Set up the data source. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-69 Step 3. Connect using the Database Explorer app or the command line. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-71 Microsoft SQL Server JDBC for Linux . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-73 Step 1. Verify the driver installation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-73 Step 2. Set up the data source. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-73 Step 3. Connect using the Database Explorer app or the command line. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-75 Microsoft SQL Server ODBC for Linux . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-77 Step 1. Verify the driver installation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-77 Step 2. Set up the data source. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-77 Step 3. Connect using the command line. . . . . . . . . . . . . . . . . . . . . . . . . 2-77 Microsoft SQL Server ODBC for Linux DSN-Less Connection . . . . . . . . . 2-78 Step 1. Verify the driver installation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-78 Step 2. Connect using the DSN-less connection string and command line. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-78 Oracle JDBC for macOS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-79 Step 1. Verify the driver installation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-79 Step 2. Set up the data source. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-79 Step 3. Connect using the Database Explorer app or the command line. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-82 Oracle JDBC for Linux . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-84 Step 1. Verify the driver installation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-84 Step 2. Set up the data source. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-84 Step 3. Connect using the Database Explorer app or the command line. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-87 vii

MySQL JDBC for macOS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-89 Step 1. Verify the driver installation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-89 Step 2. Set up the data source. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-89 Step 3. Connect using the Database Explorer app or the command line. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-91 MySQL ODBC for Linux . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-93 Step 1. Verify the driver installation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-93 Step 2. Set up the data source. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-93 Step 3. Connect using the command line. . . . . . . . . . . . . . . . . . . . . . . . . 2-93 MySQL ODBC for Linux DSN-Less Connection . . . . . . . . . . . . . . . . . . . . . 2-94 Step 1. Verify the driver installation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-94 Step 2. Connect using the DSN-less connection string and command line. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-94 MySQL JDBC for Linux . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-95 Step 1. Verify the driver installation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-95 Step 2. Set up the data source. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-95 Step 3. Connect using the Database Explorer app or the command line. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-97 PostgreSQL JDBC for macOS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-99 Step 1. Verify the driver installation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-99 Step 2. Set up the data source. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-99 Step 3. Connect using the Database Explorer app or the command line. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-101 PostgreSQL ODBC for Linux . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-103 Step 1. Verify the driver installation. . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-103 Step 2. Set up the data source. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-103 Step 3. Connect using the command line. . . . . . . . . . . . . . . . . . . . . . . . 2-103 PostgreSQL ODBC for Linux DSN-Less Connection . . . . . . . . . . . . . . . . 2-104 Step 1. Verify the driver installation. . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-104 Step 2. Connect using the DSN-less connection string and command line. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-104 PostgreSQL JDBC for Linux . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-105 Step 1. Verify the driver installation. . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-105 Step 2. Set up the data source. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-105 Step 3. Connect using the Database Explorer app or the command line. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-107 SQLite JDBC for macOS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-109 Step 1. Verify the driver installation. . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-109 Step 2. Set up the data source. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-109 Step 3. Connect using the Database Explorer app or the command line. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-111 SQLite JDBC for Linux . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-113 Step 1. Verify the driver installation. . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-113 Step 2. Set up the data source. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-113 Step 3. Connect using the Database Explorer app or the command line. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-115 viii Contents

Other ODBC-Compliant or JDBC-Compliant Databases . . . . . . . . . . . . . 2-117 ODBC-Compliant Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-117 JDBC-Compliant Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-117 Connecting to Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-120 Database Explorer App Connection Workflow . . . . . . . . . . . . . . . . . . . . 2-120 Command Line Connection Workflow . . . . . . . . . . . . . . . . . . . . . . . . . . 2-120 Database List . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-120 Data Import Using Database Explorer App or Command Line . . . . . . . 2-124 Data Import Using Database Explorer App . . . . . . . . . . . . . . . . . . . . . . 2-124 Data Import Using Command Line . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-124 Custom Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-125 SQL Queries Saved in Scripts or Files . . . . . . . . . . . . . . . . . . . . . . . . . . 2-125 Working with Large Data Sets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-126 Connect to a Database with Maximum Performance . . . . . . . . . . . . . . . 2-126 Import Large Data Sets into MATLAB . . . . . . . . . . . . . . . . . . . . . . . . . . 2-126 Export Large Data Sets from MATLAB . . . . . . . . . . . . . . . . . . . . . . . . . 2-126 Access Large Data Using a DatabaseDatastore . . . . . . . . . . . . . . . . . . . 2-126 Working with Data Sources 3 Writing Data Common Errors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-2 Importing Data Common Errors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-3 Data Import Common Errors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-3 Custom Import Options Common Errors . . . . . . . . . . . . . . . . . . . . . . . . . . 3-5 Database Connection Error Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-7 Database Explorer App Error Messages . . . . . . . . . . . . . . . . . . . . . . . . . . 3-13 SQL Prepared Statement Error Messages . . . . . . . . . . . . . . . . . . . . . . . . . 3-15 Using Database Explorer 4 Create SQL Queries Using Database Explorer App . . . . . . . . . . . . . . . . . . . 4-2 Create SQL Query Using Toolstrip Buttons . . . . . . . . . . . . . . . . . . . . . . . . 4-2 Enter SQL Query Manually . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-4 Work with Multiple SQL Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-5 SQL Query Limitations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-5 Customize Import Options Using Database Explorer App . . . . . . . . . . . . . 4-7 Join Tables Using Database Explorer App . . . . . . . . . . . . . . . . . . . . . . . . . 4-10 Different Join Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-10 ix

Join Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-10 Join Diagram . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-12 Join Type Limitations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-13 Data Preview Using Database Explorer App . . . . . . . . . . . . . . . . . . . . . . . 4-14 Automatic Preview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-14 Preview Size . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-14 Preview Data by Creating SQL Query . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-15 Preview Data by Entering SQL Query Manually . . . . . . . . . . . . . . . . . . . 4-16 Modify and Delete Data Sources . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-17 Modify Data Sources Interactively . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-17 Modify Data Sources Programmatically . . . . . . . . . . . . . . . . . . . . . . . . . 4-17 Delete Data Sources Interactively . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-18 Delete Data Sources Programmatically . . . . . . . . . . . . . . . . . . . . . . . . . . 4-18 Generate SQL Query and MATLAB Script . . . . . . . . . . . . . . . . . . . . . . . . . 4-20 Generate SQL Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-20 Generate MATLAB Script . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-20 Using Database Toolbox Functions 5 Roll Back Data in Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-2 Change Database Connection Catalog . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-4 Create Table and Add Column . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-5 Delete Data from Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-6 Roll Back Data After Updating Record . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-9 Replace Existing Data in Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-12 Export Data Using Bulk Insert . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-13 Bulk Insert Functionality . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-13 Bulk Insert into Oracle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-13 Bulk Insert into Microsoft SQL Server 2005 . . . . . . . . . . . . . . . . . . . . . . 5-14 Bulk Insert into MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-15 Call Stored Procedure That Returns Data . . . . . . . . . . . . . . . . . . . . . . . . . 5-17 Run Custom Database Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-19 Data Import Memory Management . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-20 sqlread Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-20 select Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-20 Define Import Strategy Using SQLImportOptions Object . . . . . . . . . . . . . 5-21 Import Large Data Using DatabaseDatastore Object . . . . . . . . . . . . . . . . 5-23 x Contents

Analyze Large Data in Database Using MapReduce . . . . . . . . . . . . . . . . . 5-27 Analyze Large Data in Database Using Tall Arrays . . . . . . . . . . . . . . . . . . 5-30 Import Data Using MATLAB® Interface to SQLite . . . . . . . . . . . . . . . . . . 5-32 Retrieve Image Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-36 Import Boolean Data from Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-39 Append Data to Existing Database Table Using Insert Functionality . . . 5-41 Insert Data into New Database Table Using Insert Functionality . . . . . . 5-43 Join Tables Using Command Line . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-45 Import Data from Database Table Using sqlread Function . . . . . . . . . . . 5-46 Insert Data into Database Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-49 Retrieve Database Metadata . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-52 Customize Options for Importing Data from Database into MATLAB . . . 5-54 Deploy Relational Database Application with MATLAB Compiler . . . . . . 5-57 Import Data Using SQL Prepared Statement with Multiple Parameter Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-62 MySQL Native Interface Topics 6 MySQL Native Interface for Windows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-2 Step 1. Verify the driver installation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-2 Step 2. Set the environment variable. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-2 Step 3. Set up the data source. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-2 Step 4. Connect using the Database Explorer app or the command line. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-4 MySQL Native Interface for macOS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-6 Step 1. Verify the driver installation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-6 Step 2. Copy the runtime libraries. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-6 Step 3. Set up the data source. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-6 Step 4. Connect using the Database Explorer app or the command line. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-8 Import Data from MySQL Database Table . . . . . . . . . . . . . . . . . . . . . . . . . 6-10 Customize Options for Importing Data from Database into MATLAB Using MySQL Native Interface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-12 xi

Import Large Data Using DatabaseDatastore Object and MySQL Native Interface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-15 Insert Data into Database Table Using MySQL Native Interface . . . . . . . 6-18 Roll Back Data in Database Using MySQL Native Interface . . . . . . . . . . 6-20 Create Table and Add Column Using MySQL Native Interface . . . . . . . . 6-22 Delete Data from Database Using MySQL Native Interface . . . . . . . . . . . 6-23 Deploy MySQL Native Interface Database Application with MATLAB Compiler . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-25 PostgreSQL Native Interface Topics 7 Configure PostgreSQL Native Interface Data Source . . . . . . . . . . . . . . . . . 7-2 Step 1. Set up the data source. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7-2 Step 2. Connect using the Database Explorer app or the command line. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7-4 Deploy PostgreSQL Native Interface Database Application with MATLAB Compiler . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7-6 Import Data from PostgreSQL Database Table . . . . . . . . . . . . . . . . . . . . . 7-11 Customize Options for Importing Data from PostgreSQL Database into MATLAB . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7-13 Import Large PostgreSQL Data Using DatabaseDatastore Object . . . . . . 7-16 Insert Data into Database Table Using PostgreSQL Native Interface . . . 7-19 Roll Back Data in Database Using PostgreSQL Native Interface . . . . . . 7-21 Create Table and Add Column Using PostgreSQL Native Interface . . . . 7-23 Delete Data from Database Using PostgreSQL Native Interface . . . . . . . 7-24 Database Toolbox Interface for Apache Cassandra Database and Apache Cassandra Database C++ Interface Topics 8 Convert CQL Data Types to MATLAB Data Types . . . . . . . . . . . . . . . . . . . . 8-2 xii Contents

Database Toolbox Interface for Apache Cassandra Database Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8-9 Supported Cassandra Database Versions . . . . . . . . . . . . . . . . . . . . . . . . . 8-9 Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8-9 Fill Values for Missing Data from Apache Cassandra Database . . . . . . . 8-10 Database Toolbox Interface for Apache Cassandra Database Error Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8-12 Explore and Import Data from Cassandra Database Table . . . . . . . . . . . 8-15 Import Data from Cassandra Database Table Using CQL . . . . . . . . . . . . . 8-18 Export MATLAB Data into Cassandra Database . . . . . . . . . . . . . . . . . . . . 8-20 Convert CQL Data Types to MATLAB Data Types Using Apache Cassandra Database C++ Interface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8-24 Fill Values for Missing Data from Database Using Apache Cassandra Database C++ Interface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8-31 Explore and Import Data from Cassandra Database Table . . . . . . . . . . . 8-33 Import Data from Cassandra Database Table Using CQL . . . . . . . . . . . . . 8-36 Export MATLAB Data into Cassandra Database . . . . . . . . . . . . . . . . . . . . 8-38 Neo4j Topics 9 Explore Graph Database Structure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9-2 Graph Database Workflow for Neo4j Database Interfaces . . . . . . . . . . . . . 9-6 About Neo4j Graph Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9-6 Neo4j Graph Database Workflow . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9-6 Advantage of Database Toolbox Interface for Neo4j Bolt Protocol . . . . . . . 9-7 Search Graph Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9-9 Search Functionality . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9-9 General and Targeted Search Workflows . . . . . . . . . . . . . . . . . . . . . . . . . . 9-9 Update Friend Information in Social Neighborhood . . . . . . . . . . . . . . . . 9-11 Add and Query Group of Colleagues in Social Neighborhood . . . . . . . . . 9-14 Error Messages for Neo4j Database Interfaces . . . . . . . . . . . . . . . . . . . . 9-20 Determine Dependencies of Services in Network . . . . . . . . . . . . . . . . . . . 9-22 Find Shortest Path Between People in Social Neighborhood . . . . . . . . . 9-27 xiii

Find Friends of Friends in Social Neighborhood . . . . . . . . . . . . . . . . . . . 9-32 Database Toolbox Interface for Neo4j Bolt Protocol Installation . . . . . . 9-37 Supported Neo4j Versions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9-37 Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9-37 Deploy Graph Database Application with MATLAB Compiler . . . . . . . . . 9-38 MongoDB C++ Interface Topics 10 Import and Analyze Data from MongoDB Using MongoDB C++ Interface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10-2 Import Filtered Data from MongoDB Using MongoDB C++ Interface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10-4 Import Large Data from MongoDB Using MongoDB C++ Interface . . . . 10-6 Export MATLAB Data into MongoDB Using MongoDB C++ Interface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10-8 Import and Export MATLAB Objects Using MongoDB and MongoDB C++ Interface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10-10 Database Toolbox Interface For MongoDB Topics 11 Import and Analyze Data from MongoDB . . . . . . . . . . . . . . . . . . . . . . . . . 11-2 Import Filtered Data from MongoDB . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11-4 Import Large Data from MongoDB . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11-6 Export MATLAB Data into MongoDB . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11-9 Import and Export MATLAB Objects Using MongoDB . . . . . . . . . . . . . . 11-12 Database Toolbox Interface for MongoDB Installation . . . . . . . . . . . . . 11-15 Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11-15 Supported MongoDB Versions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11-15 Database Toolbox Interface for MongoDB Error Messages . . . . . . . . . . 11-16 xiv Contents

Functions 12 xv

Before You Begin 1

Database Toolbox Product Description Exchange data with relational and nonrelational databases Database Toolbox provides functions and an app for exchanging data with relational and nonrelational databases. It enables this exchange by automatically converting between database and MATLAB® data types. Database Toolbox supports any ODBC-compliant or JDBC-compliant relational database. It also provides NoSQL support for Apache™ Cassandra®, MongoDB®, and Neo4j®. Native interfaces are provided for MySQL® and PostgreSQL. With the Database Explorer app, you can explore relational data without writing code and then generate MATLAB code to automate or operationalize database workflows. For large data workflows, you can split SQL queries and parallelize access to data (with Parallel Computing Toolbox™ and MATLAB Parallel Server™). 1 Before You Begin 1-2

Data Type Support You can import these data types into the MATLAB workspace and export them back to your database. Database Supported Data Type All databases • BOOLEAN • CHAR • DATE • DECIMAL • DOUBLE • FLOAT • INTEGER • NUMERIC • REAL • SMALLINT • TIME • TIMESTAMP Note When importing TIMESTAMP data into MATLAB, you can get an incorrect value at the daylight saving time change. To avoid an incorrect value, convert TIMESTAMP data to strings in your SQL query. Then, convert the strings back to the MATLAB data type you want. Or, connect to your database using a different driver. Microsoft® SQL Server® • NTEXT • TEXT • VARCHAR(MAX) • CHAR(8000) • NCHAR(4000) • NVARCHAR(MAX) • TINYINT MySQL • MEDIUMTEXT • LONGTEXT • TINYINT Oracle® • LONG • VARCHAR2(4000) • NCHAR(2000) • NVARCHAR2(4000) Data Type Support 1-3

To import data of another data type, manipulate the data before importing it into the MATLAB workspace. For details, see your database documentation. See Also fetch | sqlwrite | update More About • “Import Data from Database Table Using sqlread Function” on page 5-46 • “Insert Data into Database Table” on page 5-49 1 Before You Begin 1-4