Transcription
Database Toolbox User's GuideR2015a
How to Contact MathWorksLatest news:www.mathworks.comSales and services:www.mathworks.com/sales and servicesUser community:www.mathworks.com/matlabcentralTechnical support:www.mathworks.com/support/contact usPhone:508-647-7000The MathWorks, Inc.3 Apple Hill DriveNatick, MA 01760-2098Database Toolbox User's Guide COPYRIGHT 1998–2015 by The MathWorks, Inc.The software described in this document is furnished under a license agreement. The software may be usedor copied only under the terms of the license agreement. No part of this manual may be photocopied orreproduced in any form without prior written consent from The MathWorks, Inc.FEDERAL ACQUISITION: This provision applies to all acquisitions of the Program and Documentationby, for, or through the federal government of the United States. By accepting delivery of the Programor Documentation, the government hereby agrees that this software or documentation qualifies ascommercial computer software or commercial computer software documentation as such terms are usedor defined in FAR 12.212, DFARS Part 227.72, and DFARS 252.227-7014. Accordingly, the terms andconditions of this Agreement and only those rights specified in this Agreement, shall pertain to andgovern the use, modification, reproduction, release, performance, display, and disclosure of the Programand Documentation by the federal government (or other entity acquiring for or through the federalgovernment) and shall supersede any conflicting contractual terms or conditions. If this License failsto meet the government's needs or is inconsistent in any respect with federal procurement law, thegovernment agrees to return the Program and Documentation, unused, to The MathWorks, Inc.TrademarksMATLAB and Simulink are registered trademarks of The MathWorks, Inc. Seewww.mathworks.com/trademarks for a list of additional trademarks. Other product or brandnames may be trademarks or registered trademarks of their respective holders.PatentsMathWorks products are protected by one or more U.S. patents. Please seewww.mathworks.com/patents for more information.
Revision HistoryMay 1998July 1998Online onlyDecember 1999Online onlyJune 2001July 2002November 2002June 2004October 2004March 2005September 2005March 2006September 2006October 2006March 2007September 2007March 2008October 2008March 2009September 2009March 2010September 2010reApril 2011September 2011March 2012September 2012March 2013September 2013March 2014October 2014March 2015Online OnlyFirst PrintingJune 1999Second printingSeptember 2000Third printingOnline onlyFourth printingFifth printingOnline onlyOnline onlyOnline onlyOnline onlyOnline onlySixth printingOnline onlySeventh printingOnline onlyOnline onlyOnline onlyOnline onlyOnline onlyOnline onlyOnline onlyOnline onlyOnline onlyOnline onlyOnline onlyOnline onlyOnline onlyOnline onlyOnline onlyNew for Version 1 for MATLAB 5.2For Version 1Revised for Version 2 (Release 11)For Version 2 (Release 11)Revised for Version 2.1 (Release 12)Revised for Version 2.2 (Release 12.1)Revised for Version 2.2.1 (Release 13)Version 2.2.1Revised for Version 3.0 (Release 14)Revised for Version 3.1 (Release 14SP1)Revised for Version 3.0.2 (Release 14SP2)Revised for Version 3.1 (Release 14SP3)Revised for Version 3.1.1 (Release 2006a)Revised for Version 3.2 (Release 2006b)Revised for Version 3.2 (Release 2006b)Revised for Version 3.3 (Release 2007a)Revised for Version 3.4 (Release 2007b)Revised for Version 3.4.1 (Release 2008a)Revised for Version 3.5 (Release 2008b)Revised for Version 3.5.1 (Release 2009a)Revised for Version 3.6 (Release 2009b)Revised for Version 3.7 (Release 2010a)Revised for Version 3.8 (Release 2010b)Revised for Version 3.9 (Release 2011a)Revised for Version 3.10 (Release 2011b)Revised for Version 3.11 (Release 2012a)Revised for Version 4.0 (Release 2012b)Revised for Version 4.1 (Release 2013a)Revised for Version 5.0 (Release 2013b)Revised for Version 5.1 (Release 2014a)Revised for Version 5.2 (Release 2014b)Revised for Version 5.2.1 (Release 2015a)
Contents12Before You BeginDatabase Toolbox Product Description . . . . . . . . . . . . . . . . .Key Features . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .1-21-2Working with Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Connecting to Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . .Platform Support . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Database Support . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Driver Support . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Structured Query Language (SQL) . . . . . . . . . . . . . . . . . . . .1-31-31-31-31-41-5Data Type Support . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .1-6Data Retrieval Restrictions . . . . . . . . . . . . . . . . . . . . . . . . . . .Spaces in Table Names or Column Names . . . . . . . . . . . . . .Quotation Marks in Table Names or Column Names . . . . . . .Reserved Words in Column Names . . . . . . . . . . . . . . . . . . . .1-81-81-81-8Creating and Running SQL Queries . . . . . . . . . . . . . . . . . . . .1-9Getting Started with Database ToolboxWorking with a Database and MATLAB . . . . . . . . . . . . . . . . .2-3Connection Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Creating or Connecting to a Data Source . . . . . . . . . . . . . . .Defining Operating System Authentication . . . . . . . . . . . . . .Connection Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Working with Multiple Databases . . . . . . . . . . . . . . . . . . . . .2-62-62-62-62-8v
viContentsInitial Setup Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . .2-9Choosing Between ODBC and JDBC Drivers . . . . . . . . . . . .Defining Database Drivers . . . . . . . . . . . . . . . . . . . . . . . . .Deciding Between ODBC and JDBC Drivers . . . . . . . . . . . .2-102-102-10Configuring a Driver and Data Source . . . . . . . . . . . . . . . . .2-13Microsoft Access ODBC for Windows . . . . . . . . . . . . . . . . . .Step 1. Check the 32-bit and 64-bit compatibility. . . . . . . . .Step 2. Verify the driver installation. . . . . . . . . . . . . . . . . .Step 3. Set up the data source using Database Explorer. . . .Step 4. Connect using Database Explorer or the commandline. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .2-152-152-162-16Microsoft SQL Server ODBC for Windows . . . . . . . . . . . . . .Step 1. Check the 32-bit and 64-bit compatibility. . . . . . . . .Step 2. Verify the driver installation. . . . . . . . . . . . . . . . . .Step 3. Set up the data source using Database Explorer. . . .Step 4. Connect using Database Explorer or the commandline. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .2-232-232-242-24Microsoft SQL Server JDBC for Windows . . . . . . . . . . . . . .Step 1. Verify the driver installation. . . . . . . . . . . . . . . . . .Step 2. Verify the port number. . . . . . . . . . . . . . . . . . . . . . .Step 3. Set up the operating system authentication. . . . . . .Step 4. Add the JDBC driver to the MATLAB static Java classpath. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Step 5. Set up the data source using Database Explorer. . . .Step 6. Connect using Database Explorer or the commandline. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .2-332-332-332-36Oracle ODBC for Windows . . . . . . . . . . . . . . . . . . . . . . . . . . .Step 1. Check the 32-bit and 64-bit compatibility. . . . . . . . .Step 2. Verify the driver installation. . . . . . . . . . . . . . . . . .Step 3. Set up the data source using the ODBC Data SourceAdministrator. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Step 4. Connect using the native ODBC connection commandline. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .2-442-442-45Oracle JDBC for Windows . . . . . . . . . . . . . . . . . . . . . . . . . . .Step 1. Verify the driver installation. . . . . . . . . . . . . . . . . .Step 2. Set up the operating system authentication. . . . . . .2-482-482-482-192-292-372-382-402-452-47
Step 3. Add the JDBC driver to the MATLAB static Java classpath. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Step 4. Set up the data source using Database Explorer. . . .Step 5. Connect using Database Explorer or the commandline. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .2-492-492-52MySQL ODBC for Windows . . . . . . . . . . . . . . . . . . . . . . . . . .Step 1. Check the 32-bit and 64-bit compatibility. . . . . . . . .Step 2. Verify the driver installation. . . . . . . . . . . . . . . . . .Step 3. Set up the data source using Database Explorer. . . .Step 4. Connect using Database Explorer or the commandline. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .2-572-572-582-58MySQL JDBC for Windows . . . . . . . . . . . . . . . . . . . . . . . . . . .Step 1. Verify the driver installation. . . . . . . . . . . . . . . . . .Step 2. Add the JDBC driver to the MATLAB static Java classpath. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Step 3. Set up the data source using Database Explorer. . . .Step 4. Connect using Database Explorer or the commandline. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .2-632-63PostgreSQL ODBC for Windows . . . . . . . . . . . . . . . . . . . . . .Step 1. Check the 32-bit and 64-bit compatibility. . . . . . . . .Step 2. Verify the driver installation. . . . . . . . . . . . . . . . . .Step 3. Set up the data source using Database Explorer. . . .Step 4. Connect using Database Explorer or the commandline. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .2-692-692-702-70PostgreSQL JDBC for Windows . . . . . . . . . . . . . . . . . . . . . . .Step 1. Verify the driver installation. . . . . . . . . . . . . . . . . .Step 2. Add the JDBC driver to the MATLAB static Java classpath. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Step 3. Set up the data source using Database Explorer. . . .Step 4. Connect using Database Explorer or the commandline. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .2-762-76SQLite JDBC for Windows . . . . . . . . . . . . . . . . . . . . . . . . . . .Step 1. Verify the driver installation. . . . . . . . . . . . . . . . . .Step 2. Add the JDBC driver to the MATLAB static Java classpath. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Step 3. Set up the data source using Database Explorer. . . .Step 4. Connect using Database Explorer or the commandline. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . -85vii
Sybase ODBC for Windows . . . . . . . . . . . . . . . . . . . . . . . . . . .Step 1. Check the 32-bit and 64-bit compatibility. . . . . . . . .Step 2. Verify the driver installation. . . . . . . . . . . . . . . . . .Step 3. Set up the data source using Database Explorer. . . .Step 4. Connect using Database Explorer or the commandline. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .2-892-892-902-902-94Sybase JDBC for Windows . . . . . . . . . . . . . . . . . . . . . . . . . . .2-97Step 1. Verify the driver installation. . . . . . . . . . . . . . . . . .2-97Step 2. Add the JDBC driver to the MATLAB static Java classpath. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .2-97Step 3. Set up the data source using Database Explorer. . . .2-98Step 4. Connect using Database Explorer or the commandline. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .2-100Microsoft SQL Server JDBC for Mac OS X . . . . . . . . . . . . .Step 1. Verify the driver installation. . . . . . . . . . . . . . . . .Step 2. Add the JDBC driver to the MATLAB static Java classpath. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Step 3. Set up the data source using Database Explorer. . .Step 4. Connect using Database Explorer or the commandline. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .2-1042-104Microsoft SQL Server JDBC for Linux . . . . . . . . . . . . . . . .Step 1. Verify the driver installation. . . . . . . . . . . . . . . . .Step 2. Add the JDBC driver to the MATLAB static Java classpath. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Step 3. Set up the data source using Database Explorer. . .Step 4. Connect using Database Explorer or the commandline. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .2-1112-111Oracle JDBC for Mac OS X . . . . . . . . . . . . . . . . . . . . . . . . . .Step 1. Verify the driver installation. . . . . . . . . . . . . . . . .Step 2. Add the JDBC driver to the MATLAB static Java classpath. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Step 3. Set up the data source using Database Explorer. . .Step 4. Connect using Database Explorer or the commandline. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92-121Oracle JDBC for Linux . . . . . . . . . . . . . . . . . . . . . . . . . . . . .2-125Step 1. Verify the driver installation. . . . . . . . . . . . . . . . .2-125Step 2. Add the JDBC driver to the MATLAB static Java classpath. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .2-125viiiContents
Step 3. Set up the data source using Database Explorer. . .Step 4. Connect using Database Explorer or the commandline. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .MySQL JDBC for Mac OS X . . . . . . . . . . . . . . . . . . . . . . . . .Step 1. Verify the driver installation. . . . . . . . . . . . . . . . .Step 2. Add the JDBC driver to the MATLAB static Java classpath. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Step 3. Set up the data source using Database Explorer. . .Step 4. Connect using Database Explorer or the commandline. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .MySQL JDBC for Linux . . . . . . . . . . . . . . . . . . . . . . . . . . . .Step 1. Verify the driver installation. . . . . . . . . . . . . . . . .Step 2. Add the JDBC driver to the MATLAB static Java classpath. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Step 3. Set up the data source using Database Explorer. . .Step 4. Connect using Database Explorer or the commandline. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .PostgreSQL JDBC for Mac OS X . . . . . . . . . . . . . . . . . . . . .Step 1. Verify the driver installation. . . . . . . . . . . . . . . . .Step 2. Add the JDBC driver to the MATLAB static Java classpath. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Step 3. Set up the data source using Database Explorer. . .Step 4. Connect using Database Explorer or the commandline. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .PostgreSQL JDBC for Linux . . . . . . . . . . . . . . . . . . . . . . . .Step 1. Verify the driver installation. . . . . . . . . . . . . . . . .Step 2. Add the JDBC driver to the MATLAB static Java classpath. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Step 3. Set up the data source using Database Explorer. . .Step 4. Connect using Database Explorer or the commandline. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .SQLite JDBC for Mac OS X . . . . . . . . . . . . . . . . . . . . . . . . .Step 1. Verify the driver installation. . . . . . . . . . . . . . . . .Step 2. Add the JDBC driver to the MATLAB static Java classpath. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Step 3. Set up the data source using Database Explorer. . .Step 4. Connect using Database Explorer or the commandline. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32-1542-1562-1602-1602-1602-1612-163ix
SQLite JDBC for Linux . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Step 1. Verify the driver installation. . . . . . . . . . . . . . . . .Step 2. Add the JDBC driver to the MATLAB static Java classpath. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Step 3. Set up the data source using Database Explorer. . .Step 4. Connect using Database Explorer or the commandline. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Sybase JDBC for Mac OS X . . . . . . . . . . . . . . . . . . . . . . . . .Step 1. Verify the driver installation. . . . . . . . . . . . . . . . .Step 2. Add the JDBC driver to the MATLAB static Java classpath. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Step 3. Set up the data source using Database Explorer. . .Step 4. Connect using Database Explorer or the commandline. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Sybase JDBC for Linux . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Step 1. Verify the driver installation. . . . . . . . . . . . . . . . .Step 2. Add the JDBC driver to the MATLAB static Java classpath. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Step 3. Set up the data source using Database Explorer. . .Step 4. Connect using Database Explorer or the commandline. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-1752-1772-1812-1812-1812-1822-184Other ODBC- or JDBC-Compliant Databases . . . . . . . . . . .ODBC-Compliant Databases . . . . . . . . . . . . . . . . . . . . . . .JDBC-Compliant Databases . . . . . . . . . . . . . . . . . . . . . . .2-1882-1882-188Connecting to a Database . . . . . . . . . . . . . . . . . . . . . . . . . . .Connection Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Microsoft Access . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Microsoft SQL Server . . . . . . . . . . . . . . . . . . . . . . . . . . . .Oracle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .PostgreSQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .SQLite . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Sybase . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Other ODBC- or JDBC-Compliant Databases . . . . . . . . . 3Selecting Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Use Database Explorer to Select Data . . . . . . . . . . . . . . . .Use the Command Line to Select Data . . . . . . . . . . . . . . .Working with Custom Data Types . . . . . . . . . . . . . . . . . . .2-1952-1952-1952-195
3Running SQL Queries Saved in Scripts or Files . . . . . . . . .2-196Inserting Data Using the Command Line . . . . . . . . . . . . . .2-197Working with Large Data Sets . . . . . . . . . . . . . . . . . . . . . . .Connect to a Database with Maximum Performance . . . . .Import Large Data Sets into MATLAB . . . . . . . . . . . . . . .Export Large Data Sets from MATLAB . . . . . . . . . . . . . . .Access Data Stored in a Database Using aDatabaseDatastore . . . . . . . . . . . . . . . . . . . . . . . . . . . .2-1982-1982-1982-199Deploying a Database Application with MATLAB CompilerCreate and Deploy a Database Application . . . . . . . . . . . .About Driver Configurations . . . . . . . . . . . . . . . . . . . . . . .2-2002-2002-2002-199Working with Data SourcesSetting Up ODBC Data Sources . . . . . . . . . . . . . . . . . . . . . . . .3-2Setting Up JDBC Data Sources . . . . . . . . . . . . . . . . . . . . . . . .3-3Accessing Existing JDBC Data Sources . . . . . . . . . . . . . . . . .3-4Modifying Existing JDBC Data Sources . . . . . . . . . . . . . . . . .3-5Removing JDBC Data Sources . . . . . . . . . . . . . . . . . . . . . . . . .3-6Fetching Data Common Errors . . . . . . . . . . . . . . . . . . . . . . . .3-7Database Connection Error Messages . . . . . . . . . . . . . . . . . .3-9Database Explorer Error Messages . . . . . . . . . . . . . . . . . . . .3-14Connecting to a Database Using the Native ODBCInterface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .About the Native ODBC Interface . . . . . . . . . . . . . . . . . . . .Native ODBC Interface Workflow . . . . . . . . . . . . . . . . . . . .Native ODBC, JDBC/ODBC Bridge and JDBC InterfaceComparison . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3-173-173-173-19xi
Compatibility and Limitations . . . . . . . . . . . . . . . . . . . . . .45xiiContents3-21Using Database ExplorerWorking with Database Explorer . . . . . . . . . . . . . . . . . . . . . .Getting Started with Database Explorer . . . . . . . . . . . . . . . .Migrate from VQB to Database Explorer . . . . . . . . . . . . . . .Set Database Explorer Preferences . . . . . . . . . . . . . . . . . . . .4-24-24-24-3Configure Data Sources and Connect to Databases . . . . . . .Configure Your Environment . . . . . . . . . . . . . . . . . . . . . . . .Work with Multiple Databases . . . . . . . . . . . . . . . . . . . . . .4-64-64-17Modify and Delete Database Connections . . . . . . . . . . . . . .ODBC Drivers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .JDBC Drivers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .4-194-194-19Refine Results Using Query Criteria and Rules . . . . . . . . .Define Query Criteria to Refine Results . . . . . . . . . . . . . . .Query Rules Using the SQL Criteria Panel . . . . . . . . . . . . .4-214-214-22Generate SQL and MATLAB Code . . . . . . . . . . . . . . . . . . . . .Save Queries as SQL Code . . . . . . . . . . . . . . . . . . . . . . . . .Generate MATLAB Code . . . . . . . . . . . . . . . . . . . . . . . . . . .4-254-254-26Using Visual Query BuilderGetting Started with Visual Query Builder . . . . . . . . . . . . . .What Is Visual Query Builder? . . . . . . . . . . . . . . . . . . . . . . .Using Queries to Import Data . . . . . . . . . . . . . . . . . . . . . . . .Using Queries to Export Data . . . . . . . . . . . . . . . . . . . . . . . .Clearing Variables from the VQB Data Area . . . . . . . . . . . .5-25-25-25-95-14Working with Preferences . . . . . . . . . . . . . . . . . . . . . . . . . . .5-15
6Preference Settings for Large Data Import . . . . . . . . . . . . .Will All Data (Size n) Fit in a MATLAB Variable? . . . . . . . .Will All of This Data Fit in the JVM Heap? . . . . . . . . . . . .How Do I Perform Batching? . . . . . . . . . . . . . . . . . . . . . . . .5-195-205-205-21Displaying Query Results . . . . . . . . . . . . . . . . . . . . . . . . . . . .How to Display Query Results . . . . . . . . . . . . . . . . . . . . . .Displaying Data Relationally . . . . . . . . . . . . . . . . . . . . . . . .Charting Query Results . . . . . . . . . . . . . . . . . . . . . . . . . . .Displaying Query Results in an HTML Report . . . . . . . . . .Displaying Query Results with MATLAB Report Generator .5-235-235-235-275-295-29Fine-Tuning Queries Using Advanced Query Options . . . .Retrieving All Occurrences vs. Unique Occurrences of Data .Retrieving Data That Meets Specified Criteria . . . . . . . . . .Grouping Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Displaying Results in a Specified Order . . . . . . . . . . . . . . .Using Having Clauses to Refine Group by Results . . . . . . .Creating Subqueries for Values from Multiple Tables . . . . .Creating Queries That Include Results from Multiple TablesAdditional Advanced Query Options . . . . . . . . . . . . . . . . . .5-345-345-355-385-425-455-485-525-54Retrieving BINARY and OTHER Data Types . . . . . . . . . . . .5-55Importing and Exporting Boolean Data . . . . . . . . . . . . . . . .Import Boolean Data from Databases . . . . . . . . . . . . . . . . .Exporting Boolean Data to Databases . . . . . . . . . . . . . . . . .5-575-575-60Saving Queries in Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .About Generated Files . . . . . . . . . . . . . . . . . . . . . . . . . . . .VQB Query Elements in Generated Files . . . . . . . . . . . . . .Saving Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Running Saved Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . .Editing Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .5-615-615-625-625-625-63Using Database Toolbox FunctionsGetting Started with Database Toolbox Functions . . . . . . . .6-3xiii
xivContentsImport Data from Databases into MATLAB . . . . . . . . . . . . . .6-4Create a Query Using a Date . . . . . . . . . . . . . . . . . . . . . . . . . .6-8Create a Query Using a String . . . . . . . . . . . . . . . . . . . . . . . .6-10Create a Query Using a MATLAB Variable . . . . . . . . . . . . .6-12Create a Query Using Special Characters . . . . . . . . . . . . . .6-14Delete Data from Databases . . . . . . . . . . . . . . . . . . . . . . . . . .6-16Exporting Data to New Record in Database . . . . . . . . . . . .6-18Replacing Existing Database Data with Exported Data . . .6-21Exporting Multiple Records from the MATLAB Workspace6-22Exporting Data Using Bulk Insert . . . . . . . . . . . . . . . . . . . . .About Bulk Insert Functionality . . . . . . . . . . . . . . . . . . . . .Bulk Insert into Oracle . . . . . . . . . . . . . . . . . . . . . . . . . . . .Bulk Insert into Microsoft SQL Server 2005 . . . . . . . . . . . .Bulk Insert into MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . .6-266-266-266-286-30Retrieve Image Data Types . . . . . . . . . . . . . . . . . . . . . . . . . .6-32Display Database Metadata . . . . . . . . . . . . . . . . . . . . . . . . . .6-34Using Driver Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . .6-37About Database Toolbox Objects and Methods . . . . . . . . . .6-39Selecting Data Using the exec Function . . . . . . . . . . . . . . . .About the exec Function . . . . . . . . . . . . . . . . . . . . . . . . . . .Using Cursor Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Working with Microsoft Excel . . . . . . . . . . . . . . . . . . . . . . .Database Considerations . . . . . . . . . . . . . . . . . . . . . . . . . . .6-406-406-406-416-41Call a Stored Procedure That Returns Data . . . . . . . . . . . .6-42Run a Custom Database Function . . . . . . . . . . . . . . . . . . . . .6-46
Importing Data Using the fetch Function . . . . . . . . . . . . . .About the fetch Function . . . . . . . . . . . . . . . . . . . . . . . . . . .fetch Workflow . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Using fetch with a Cursor Object . . . . . . . . . . . . . . . . . . . .Using fetch with Cursor and Database Connection Objects .Database Consideration . . . . . . . . . . . . . . . . . . . . . . . . . . .6-486-486-486-496-506-51Fetch Data Incrementally Using the Cursor Object . . . . . .6-52Display Information About Imported Data . . . . . . . . . . . . .6-55Importing Data Using a Scrollable Cursor . . . . . . . . . . . . . .About Scrollable Cursors . . . . . . . . . . . . . . . . . . . . . . . . . . .Differences Between Native ODBC and JDBC ScrollableCursors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .6-586-586-59Import Data Using a Scrollable Cursor with a RelativePosition Offset . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .6-65Inserting Data Using the fastinsert Function . . . . . . . . . . .About the fastinsert Function . . . . . . . . . . . . . . . . . . . . . . .Database Considerations . . . . . . . . . . . . . . . . . . . . . . . . . . .6-686-686-69Retrieving Object Properties Using the get Function . . . . .Database Connection Objects . . . . . . . . . . . . . . . . . . . . . . .Cursor Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Driver Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Database Metadata Objects . . . . . . . . . . . . . . . . . . . . . . . . .Drivermanager Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . .Resultset Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Resultset Metadata Objects . . . . . . . . . . . . . . . . . . . . . . . . .6-706-706-716-726-726-736-736-73Setting Database Preferences Using the setdbprefsFunction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .About the setdbprefs Function . . . . . . . . . . . . . . . . . . . . . .Allowable Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .6-756-756-75Working with a DatabaseDatastore . . . . . . . . . . . . . . . . . . . .About DatabaseDatastore Objects . . . . . . . . . . . . . . . . . . . .Advantages of DatabaseDatastore Objects Over BasicFetching . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .6-796-79Import Data Using a DatabaseDatastore . . . . . . . . . . . . . . .6-816-79xv
Analyze Large Data Sets in a Database with MapReduce . .7xviContents6-85Functions — Alphabetical List page
Revision History May 1998 Online Only New for Version 1 for MATLAB 5.2 July 1998 First Printing For Version 1
