Database Toolbox™ User's Guide R2015a
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–2015 by The MathWorks, Inc. The software described in this document is furnished under a license agreement.
Revision History May 1998 July 1998 Online only December 1999 Online only June 2001 July 2002 November 2002 June 2004 October 2004 March 2005 September 2005 March 2006 September 2006 October 2006 March 2007 September 2007 March 2008 October 2008 March 2009 September 2009 March 2010 September 2010 reApril 2011 September 2011 March 2012 September 2012 March 2013 September 2013 March 2014 October 2014 March 2015 Online Only First Printing June 1999 Second printing September 2000 Third printing Online only Fo
Contents 1 2 Before You Begin Database Toolbox Product Description . . . . . . . . . . . . . . . . . Key Features . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-2 1-2 Working with Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Connecting to Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . Platform Support . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Database Support . . . . . . . . . . . . . . . . . . . . . . . .
vi Contents Initial Setup Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-9 Choosing Between ODBC and JDBC Drivers . . . . . . . . . . . . Defining Database Drivers . . . . . . . . . . . . . . . . . . . . . . . . . Deciding Between ODBC and JDBC Drivers . . . . . . . . . . . . 2-10 2-10 2-10 Configuring a Driver and Data Source . . . . . . . . . . . . . . . . . 2-13 Microsoft Access ODBC for Windows . . . . . . . . . . . . . . . . . . Step 1.
Step 3. Add the JDBC driver to the MATLAB static Java class path. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Step 4. Set up the data source using Database Explorer. . . . Step 5. Connect using Database Explorer or the command line. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-49 2-49 2-52 MySQL ODBC for Windows . . . . . . . . . . . . . . . . . . . . . . . . . . Step 1. Check the 32-bit and 64-bit compatibility. . . . . . . . .
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 command line. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-89 2-89 2-90 2-90 2-94 Sybase JDBC for Windows . . . . . . . . . . . . . . . . . . .
Step 3. Set up the data source using Database Explorer. . . Step 4. Connect using Database Explorer or the command line. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . MySQL JDBC for Mac OS X . . . . . . . . . . . . . . . . . . . . . . . . . Step 1. Verify the driver installation. . . . . . . . . . . . . . . . . Step 2. Add the JDBC driver to the MATLAB static Java class path. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Step 3.
SQLite JDBC for Linux . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Step 1. Verify the driver installation. . . . . . . . . . . . . . . . . Step 2. Add the JDBC driver to the MATLAB static Java class path. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Step 3. Set up the data source using Database Explorer. . . Step 4. Connect using Database Explorer or the command line. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
3 Running SQL Queries Saved in Scripts or Files . . . . . . . . . 2-196 Inserting Data Using the Command Line . . . . . . . . . . . . . . 2-197 Working 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 a DatabaseDatastore . . . . . . . . . . . . . . . . . . . . .
Compatibility and Limitations . . . . . . . . . . . . . . . . . . . . . . 4 5 xii Contents 3-21 Using Database Explorer Working with Database Explorer . . . . . . . . . . . . . . . . . . . . . . Getting Started with Database Explorer . . . . . . . . . . . . . . . . Migrate from VQB to Database Explorer . . . . . . . . . . . . . . . Set Database Explorer Preferences . . . . . . . . . . . . . . . . . . . . 4-2 4-2 4-2 4-3 Configure Data Sources and Connect to Databases . . . . . . .
6 Preference 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-19 5-20 5-20 5-21 Displaying Query Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . How to Display Query Results . . . . . . . . . . . . . . . . . . . . . . Displaying Data Relationally . . . . . . . . . . . . . . .
xiv Contents Import Data from Databases into MATLAB . . . . . . . . . . . . . . 6-4 Create a Query Using a Date . . . . . . . . . . . . . . . . . . . . . . . . . . 6-8 Create a Query Using a String . . . . . . . . . . . . . . . . . . . . . . . . 6-10 Create a Query Using a MATLAB Variable . . . . . . . . . . . . . 6-12 Create a Query Using Special Characters . . . . . . . . . . . . . . 6-14 Delete Data from Databases . . . . . . . . . . . . . . . . . . . . . . . . . .
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 . . . . . . . . . . . . . . . . . . . . . . . . . . .
Analyze Large Data Sets in a Database with MapReduce . .
1 Before You Begin • “Database Toolbox Product Description” on page 1-2 • “Working with Databases” on page 1-3 • “Data Type Support” on page 1-6 • “Data Retrieval Restrictions” on page 1-8 • “Creating and Running SQL Queries” on page 1-9
1 Before You Begin Database Toolbox Product Description Exchange data with relational databases Database Toolbox™ provides an app and functions for exchanging data between relational databases and MATLAB®. You can use SQL commands to read and write data or use the Database Explorer app to interact with a database without using SQL. The toolbox supports ODBC-compliant and JDBC-compliant databases, including Oracle®, MySQL®, Sybase®, Microsoft® SQL Server®, and Informix®.
Working with Databases Working with Databases In this section... “Connecting to Databases” on page 1-3 “Platform Support” on page 1-3 “Database Support” on page 1-3 “Driver Support” on page 1-4 “Structured Query Language (SQL)” on page 1-5 Connecting to Databases Before you can use this toolbox to connect to a database, you must set up the data sources. For details, see “Configuring a Driver and Data Source” on page 2-13.
1 Before You Begin • Microsoft Access™ • Microsoft Excel® • Microsoft SQL Server • MySQL • Oracle • PostgreSQL (Postgres) • Sybase SQL Anywhere® • Sybase SQL Server® If you are upgrading an earlier version of a database, you need not do anything special for this toolbox. Simply configure the data sources for the new version of the database application as you did for the original version. Driver Support This toolbox requires a database driver. Typically, you install a driver when you install a database.
Working with Databases Structured Query Language (SQL) This toolbox supports American National Standards Institute (ANSI®) standard SQL commands.
1 Before You Begin Data Type Support You can import the following data types into the MATLAB Workspace and export them back to your database: • BOOLEAN • CHAR • DATE • DECIMAL • DOUBLE • FLOAT • INTEGER • LONGCHAR • NUMERIC • REAL • SMALLINT • TIME • TIMESTAMP Note: When importing TIMESTAMP data into MATLAB, you might get an incorrect value near the daylight savings time change.
Data Type Support Note: Data types LONGCHAR and NTEXT are not supported for the native ODBC interface.
1 Before You Begin Data Retrieval Restrictions In this section... “Spaces in Table Names or Column Names” on page 1-8 “Quotation Marks in Table Names or Column Names” on page 1-8 “Reserved Words in Column Names” on page 1-8 Spaces in Table Names or Column Names Microsoft Access supports the use of spaces in table and column names, but most other databases do not. Queries that retrieve data from tables and fields whose names contain spaces require delimiters around table names and field names.
Creating and Running SQL Queries Creating and Running SQL Queries You can select data from your database and import it into MATLAB by doing any of the following: • Use Database Explorer or the command line. • Write queries using SQL. • Use MATLAB to generate the SQL. Then, if you want to repeat your tasks, then automate them by generating a MATLAB script. Writing a query requires knowledge of SQL and experience using the command line.
2 Getting Started with Database Toolbox • “Working with a Database and MATLAB” on page 2-3 • “Connection Options” on page 2-6 • “Initial Setup Requirements” on page 2-9 • “Choosing Between ODBC and JDBC Drivers” on page 2-10 • “Configuring a Driver and Data Source” on page 2-13 • “Microsoft Access ODBC for Windows” on page 2-15 • “Microsoft SQL Server ODBC for Windows” on page 2-23 • “Microsoft SQL Server JDBC for Windows” on page 2-33 • “Oracle ODBC for Windows” on page 2-44 • “Oracle JDBC for Windows” on
2 Getting Started with Database Toolbox • “PostgreSQL JDBC for Mac OS X” on page 2-146 • “PostgreSQL JDBC for Linux” on page 2-153 • “SQLite JDBC for Mac OS X” on page 2-160 • “SQLite JDBC for Linux” on page 2-167 • “Sybase JDBC for Mac OS X” on page 2-174 • “Sybase JDBC for Linux” on page 2-181 • “Other ODBC- or JDBC-Compliant Databases” on page 2-188 • “Connecting to a Database” on page 2-191 • “Selecting Data” on page 2-195 • “Inserting Data Using the Command Line” on page 2-197 • “Working with Large D
Working with a Database and MATLAB Working with a Database and MATLAB This tutorial shows how to use Database Toolbox. You must make many decisions to start using this toolbox. Use these steps and flowchart as a guide for choosing the right options to get maximum benefit from using this toolbox and for understanding its capabilities. 1 Install your database. For details, refer to your database administrator or your database documentation.
2 2-4 Getting Started with Database Toolbox
Working with a Database and MATLAB More About • “Working with Database Explorer” on page 4-2 2-5
2 Getting Started with Database Toolbox Connection Options In this section...
Connection Options Connection Option Why Use This Option? Database Explorer Use Database Explorer to: • Visually inspect the structure, or schema, of your database. • View the tables and columns and rows in a table to assess the general size of your database. • Select the data in a table and import it into a MATLAB variable. • Generate a MATLAB script. • Generate an SQL query. For details, see “Selecting Data” on page 2-195.
2 Getting Started with Database Toolbox Working with Multiple Databases You can connect to multiple databases using Database Explorer or the command line. For details, see “Work with Multiple Databases” on page 4-17.
Initial Setup Requirements Initial Setup Requirements Refer to the setup requirements below to establish the first connection to your database. • For ODBC drivers, ensure 32-bit or 64-bit compatibility across your driver, database, operating system, and MATLAB. For details, see “Configuring a Driver and Data Source” on page 2-13. • If you use Visual Query Builder (VQB) to explore the data in your database, you need to migrate to the Database Explorer app.
2 Getting Started with Database Toolbox Choosing Between ODBC and JDBC Drivers In this section... “Defining Database Drivers” on page 2-10 “Deciding Between ODBC and JDBC Drivers” on page 2-10 Defining Database Drivers Different database vendors, such as Microsoft or Oracle, might implement their database systems using various technologies depending on customer needs, market demands, and several other factors.
Choosing Between ODBC and JDBC Drivers Depending on your environment and what you want to accomplish, you need to decide whether using an ODBC driver or a JDBC driver suits your needs the best. Use the following to help you decide.
2 Getting Started with Database Toolbox For a list of native ODBC supported functionality and a full comparison of the JDBC/ ODBC bridge to native ODBC, see “Connecting to a Database Using the Native ODBC Interface”.
Configuring a Driver and Data Source Configuring a Driver and Data Source Connect to a database and interact with the data by first installing the driver for the database. Then, define a data source for ODBC or add the full path of the driver to the static Java class path for JDBC so your computer can establish a connection to the database.
2 Getting Started with Database Toolbox Database MySQL Platform Windows Mac OS X 64-bit Linux 64-bit “MySQL ODBC for Windows” on page 2-57 “MySQL JDBC for Mac OS X” on page 2-132 “MySQL JDBC for Linux” on page 2-139 “PostgreSQL JDBC for Mac OS X” on page 2-146 “PostgreSQL JDBC for Linux” on page 2-153 “MySQL JDBC for Windows” on page 2-63 PostgreSQL “PostgreSQL ODBC for Windows” on page 2-69 “PostgreSQL JDBC for Windows” on page 2-76 SQLite “SQLite JDBC for Windows” on page 2-82 “SQLite JDBC
Microsoft Access ODBC for Windows Microsoft Access ODBC for Windows This tutorial shows how to set up a data source and connect to your Microsoft Access database. This tutorial uses the 32-bit Microsoft Access Driver (*.mdb, *accdb) Version 14.00.6015.1000 to connect to the Microsoft Access 2010 Version 14.0.6129.5000 (32-bit) database. In this section... “Step 1. Check the 32-bit and 64-bit compatibility.” on page 2-15 “Step 2. Verify the driver installation.” on page 2-16 “Step 3.
2 Getting Started with Database Toolbox Database Explorer. The drivers listed in the Create New Data Source dialog box in the Microsoft ODBC Administrator are also 32-bit or 64-bit respectively. Step 2. Verify the driver installation. The ODBC driver is typically preinstalled on your computer. For details about the driver installation or troubleshooting the installation, contact your database administrator or refer to your database documentation on ODBC drivers.
Microsoft Access ODBC for Windows In the ODBC Data Source Administrator dialog box, you can define the ODBC data source. 4 Click the User DSN tab and then click Add. When setting up an ODBC data source, you can use a User DSN or System DSN. A User DSN is specific to the user on a machine. Any data sources a user defines under User DSN are seen only by that specific user. Conversely, a System DSN is not specific to the user on a machine.
2 Getting Started with Database Toolbox data sources a user defines under System DSN on a machine can be seen by any user who logs into that machine. Your ability to set up a User DSN or System DSN might depend on the database and ODBC driver you are using. For details, contact your database administrator or your database ODBC driver documentation. 2-18 5 A list of installed ODBC drivers appears in the Create New Data Source dialog box. Select Microsoft Access Driver (*.mdb, *.accdb) and click Finish.
Microsoft Access ODBC for Windows After you complete the data source setup, connect to the Microsoft Access database using Database Explorer or the command line with the native ODBC or ODBC connection. Step 4. Connect using Database Explorer or the command line. Connect to Microsoft Access using Database Explorer. 1 After setting up the data source, click Connect in the Database Explorer tab.
2 Getting Started with Database Toolbox 4 Close the connection using Database Explorer by hovering the cursor over the Close button ( ) next to the dbtoolboxdemo data source name on the database tab. The Close button turns into a red circle ( ). Click it to close the database connection. If you want to close Database Explorer and all database connections, click the Close button ( ) in the top-right corner. If Database Explorer is docked, click the Close button ( connections and Database Explorer.
Microsoft Access ODBC for Windows Connect to Microsoft Access using the native ODBC connection command line. 1 Connect to the database with the ODBC data source name. For example, the following code assumes you are connecting to a data source named dbtoolboxdemo with user name admin and password admin. conn = database.ODBCConnection('dbtoolboxdemo','admin','admin'); 2 Close the database connection conn. close(conn) Connect to Microsoft Access using the ODBC connection command line.
2 Getting Started with Database Toolbox See Also close | database More About • 2-22 “Working with Database Explorer” on page 4-2
Microsoft SQL Server ODBC for Windows Microsoft SQL Server ODBC for Windows This tutorial shows how to set up a data source and connect to your Microsoft SQL Server database. This tutorial uses the Microsoft SQL Server Native Client 11.0 Driver Version 2011.110.3000.00 to connect to the Microsoft SQL Server 2012 Express database. In this section... “Step 1. Check the 32-bit and 64-bit compatibility.” on page 2-23 “Step 2. Verify the driver installation.” on page 2-24 “Step 3.
2 Getting Started with Database Toolbox If you are running 32-bit or 64-bit MATLAB, the corresponding 32-bit or 64-bit Microsoft ODBC Data Source Administrator opens when you start creating a new ODBC data source using Database Explorer. The drivers listed in the Create New Data Source dialog box in the Microsoft ODBC Data Source Administrator are also 32-bit or 64-bit respectively. The following steps use 64-bit for MATLAB, the ODBC driver, the database, and the operating system. Step 2.
Microsoft SQL Server ODBC for Windows section in the apps gallery. Alternatively, enter dexplore at the command line. If no data sources are set up, a message box opens. Click OK to close it. Otherwise, the Connect to a Data Source dialog box opens. Click Cancel to close this dialog box. 2 Click the Database Explorer tab, and then select New > ODBC. Requirement: Ensure you use the correct 32-bit or 64-bit compatibility for MATLAB to complete the remaining steps.
2 Getting Started with Database Toolbox 2-26 3 Click the System DSN tab and then click Add. When setting up an ODBC data source, you can use a User DSN or System DSN. A User DSN is specific to the user on a machine. Any data sources a user defines under User DSN are seen only by that specific user. Conversely, a System DSN is not specific to the user on a machine. Any data sources a user defines under System DSN on a machine can be seen by any user who logs into that machine.
Microsoft SQL Server ODBC for Windows Or, if you want to connect to Microsoft SQL Server without Windows authentication, click the With SQL Server authentication using a login ID and password entered by the user radio button. Enter your user name in the Login ID field and your password in the Password field. Then click Next.
2 Getting Started with Database Toolbox 10 If the connection establishes successfully, this message appears in the SQL Server ODBC Data Source Test dialog box: TESTS COMPLETED SUCCESSFULLY! Click OK to close this dialog box. Click OK to close the ODBC Microsoft SQL Server Setup dialog box.
Microsoft SQL Server ODBC for Windows 11 The ODBC Data Source Administrator dialog box shows the new data source under System Data Sources in the System DSN tab. Click OK to close the ODBC Data Source Administrator dialog box. After you complete the data source setup, connect to the Microsoft SQL Server database using Database Explorer or the command line with the native ODBC connection. Step 4. Connect using Database Explorer or the command line. Connect to Microsoft SQL Server using Database Explorer.
2 Getting Started with Database Toolbox 3 Connect without operating system authentication by selecting the data source that you set up without Windows authentication. Enter a user name and password. Click Connect. Database Explorer connects to your database and displays its contents in a tab named with the data source name.
Microsoft SQL Server ODBC for Windows tab. The Close button turns into a red circle ( ). Click it to close the database connection. If you want to close Database Explorer and all database connections, click the Close button ( ) in the top-right corner. If Database Explorer is docked, click the Close button ( connections and Database Explorer. ) to close all database Connect to Microsoft SQL Server using the native ODBC connection command line.
2 Getting Started with Database Toolbox close(conn) See Also close | database More About • 2-32 “Working with Database Explorer” on page 4-2
Microsoft SQL Server JDBC for Windows Microsoft SQL Server JDBC for Windows This tutorial shows how to set up a data source and connect to your Microsoft SQL Server database. This tutorial uses the Microsoft JDBC Driver 4.0 for Microsoft SQL Server to connect to the Microsoft SQL Server 2012 Express database. In this section... “Step 1. Verify the driver installation.” on page 2-33 “Step 2. Verify the port number.” on page 2-33 “Step 3. Set up the operating system authentication.” on page 2-36 “Step 4.
2 Getting Started with Database Toolbox 2-34 2 In the Sql Server Configuration Manager window, click SQL Server Network Configuration on the left side. Double-click Protocols for SQLEXPRESS. 3 See if TCP/IP is enabled. If so, skip the steps for enabling TCP/IP and restarting the server. 4 If TCP/IP is disabled, right-click TCP/IP and select Enable. 5 To finish the process of enabling the TCP/IP protocol, restart the server. Click SQL Server Services on the left side of the window.
Microsoft SQL Server JDBC for Windows 6 The server restarts enabling TCP/IP. Click Protocols for SQLEXPRESS and rightclick TCP/IP. Select Properties. 7 In the TCP/IP Properties dialog box, scroll to the bottom in the IP Addresses tab until you see IP All group. The number next to the TCP Dynamic Ports field is the port number. Use this port number in the JDBC connection parameters for Database Explorer or the command line. Here, the port number is 53917.
2 Getting Started with Database Toolbox Step 3. Set up the operating system authentication. Windows authentication lets you to connect to your database using your Windows user account. In this case, Windows performs user validation and the database does not require a different user name and password. Windows authentication facilitates easy maintenance of database access credentials.
Microsoft SQL Server JDBC for Windows 3 Close MATLAB if it is running. 4 Navigate to the folder and create a file called javalibrarypath.txt in the folder. 5 Open javalibrarypath.txt and insert the path to the Java library file sqljdbc_auth.dll. In the entry, include the full path to the library file. Do not include the library file name. For example, C:\DB_Drivers\sqljdbc_4.0\enu \auth\x64. The sqljdbc_auth.
2 Getting Started with Database Toolbox Step 5. Set up the data source using Database Explorer. This step is required only for connecting to Database Explorer. If you want to use the command line to connect to your database, see “Connect to Microsoft SQL Server using the JDBC connection command line.” on page 2-42 1 Open Database Explorer by clicking the Apps tab on the MATLAB Toolstrip. Then, select Database Explorer from the Database Connectivity and Reporting section in the apps gallery.
Microsoft SQL Server JDBC for Windows 3 Select MICROSOFT SQL SERVER from the Vendor list. After selecting the vendor, if you did not add the JDBC driver file path to the Java class path, this dialog box displays this message at the bottom. Address this message by following the steps described in Step 4. 4 Enter the database server name in the Server Name field, port number in the Port Number field, user name in the Username field, password in the Password field, and database name in the Database field.
2 Getting Started with Database Toolbox 5 Create a data source without Windows authentication by setting the Authentication Type to Server. Or, create a data source with Windows authentication by setting the Authentication Type to Windows and leaving Username and Password blank. 6 Click Test to test the connection. If your connection succeeded, Database Explorer displays Connection Successful! 7 Enter a data source name in the Data Source Name field in the Create a New JDBC data source dialog box.
Microsoft SQL Server JDBC for Windows Database Explorer connects to your database and displays its contents in a tab named with the data source name. 3 Close the connection using Database Explorer by hovering the cursor over the Close button ( ) next to the MS SQL Server JDBC data source name on the database tab. The Close button turns into a red circle ( ).
2 Getting Started with Database Toolbox connection. If you want to close Database Explorer and all database connections, click the Close button ( ) in the top-right corner. If Database Explorer is docked, click the Close button ( connections and Database Explorer. ) to close all database Connect to Microsoft SQL Server using the JDBC connection command line. When using the command line, you do not have to set up a data source with Database Explorer.
Microsoft SQL Server JDBC for Windows Server. For example, the following code assumes you are connecting to a database named dbname with user name username and password pwd. conn = database('dbname','username','pwd',... 'Vendor','Microsoft SQL Server','Server','sname',... 'AuthType','Server','PortNumber',123456); 2 Close the database connection conn.
2 Getting Started with Database Toolbox Oracle ODBC for Windows This tutorial shows how to set up a data source and connect to your Oracle database. This tutorial uses the OraClient11g_home1 ODBC driver version 11.02.00.01 to connect to the Oracle 11g Enterprise Edition Release 11.2.0.1.0 database. In this section... “Step 1. Check the 32-bit and 64-bit compatibility.” on page 2-44 “Step 2. Verify the driver installation.” on page 2-45 “Step 3.
Oracle ODBC for Windows Step 2. Verify the driver installation. The ODBC driver is typically preinstalled on your computer. For details about the driver installation or troubleshooting the installation, contact your database administrator or refer to your database documentation on ODBC drivers. For information about the Microsoft ODBC Data Source Administrator, see Driver Installation. Step 3. Set up the data source using the ODBC Data Source Administrator.
2 Getting Started with Database Toolbox on a machine. Any data sources a user defines under User DSN are seen only by that specific user. Conversely, a System DSN is not specific to the user on a machine. Any data sources a user defines under System DSN on a machine can be seen by any user who logs into that machine. Your ability to set up a User DSN or System DSN might depend on the database and ODBC driver you are using.
Oracle ODBC for Windows 6 Click Test Connection to test the connection to your database. The Oracle ODBC Driver Connect dialog box opens. If you are establishing the data source with Windows authentication, the Testing Connection dialog box opens. 7 Your database name and user name are automatically entered in the Service Name and User Name fields. Enter your password in the Password field. Click OK.
2 Getting Started with Database Toolbox Oracle JDBC for Windows This tutorial shows how to set up a data source and connect to your Oracle database. This tutorial uses the Oracle Database 11g Release 2 (11.2.0.3) JDBC driver for use with JDK™ 1.6 to connect to the Oracle 11g Enterprise Edition Release 11.2.0.1.0 database. In this section... “Step 1. Verify the driver installation.” on page 2-48 “Step 2. Set up the operating system authentication.” on page 2-48 “Step 3.
Oracle JDBC for Windows 6 Add the Oracle OCI library full path to the Windows Path environment variable. 7 Open MATLAB. For details about Java libraries, see “Bringing Java Classes into MATLAB Workspace”. Step 3. Add the JDBC driver to the MATLAB static Java class path. 1 Run the prefdir command in the Command Window. The output is a file path to a folder on your computer. 2 Close MATLAB if it is running. 3 Navigate to the folder and create a file called javaclasspath.txt in the folder.
2 Getting Started with Database Toolbox The Create a New JDBC data source dialog box opens.
Oracle JDBC for Windows 3 Select ORACLE from the Vendor list. After selecting the vendor, if you did not add the JDBC driver file path to the Java class path, this dialog box displays this message at the bottom. Address this message by following the steps described in Step 3. 4 Enter the database server name in the Server Name field, port number in the Port Number field, user name in the Username field, password in the Password field, and database name in the Database field.
2 Getting Started with Database Toolbox 6 To establish the data source without Windows authentication, set Driver Type to thin. 7 Click Test to test the connection. If your connection succeeded, Database Explorer displays Connection Successful! 8 Enter a data source name in the Data Source Name field in the Create a New JDBC data source dialog box. Use a new data source name that does not appear in the existing list of data source names. Click Save.
Oracle JDBC for Windows Or, to connect with Windows authentication, select the data source that you set up. Leave the user name and password blank. Click Connect. Database Explorer connects to your database and displays its contents in a tab named with the data source name. You might need to select your database schema to display your database contents.
2 Getting Started with Database Toolbox 2 Close the connection using Database Explorer by hovering the cursor over the Close button ( ) next to the ORA JDBC data source name on the database tab. The Close button turns into a red circle ( ). Click it to close the database connection. If you want to close Database Explorer and all database connections, click the Close button ( ) in the top-right corner. If Database Explorer is docked, click the Close button ( connections and Database Explorer.
Oracle JDBC for Windows dbname can be the service name or the Oracle system identifier (SID) depending on your specific Oracle database setup. For details, see your tnsnames.ora file, which is often in \NETWORK\ADMIN where is the folder where the database or the Oracle client is installed. conn = database('dbname','','',... 'Vendor','Oracle','DriverType','oci',...
2 Getting Started with Database Toolbox • 2-56 “Bringing Java Classes into MATLAB Workspace”
MySQL ODBC for Windows MySQL ODBC for Windows This tutorial shows how to set up a data source and connect to your MySQL database. This tutorial uses the MySQL ODBC 5.2a Driver version 5.02.04.00 to connect to the MySQL Version 5.5.16 database. In this section... “Step 1. Check the 32-bit and 64-bit compatibility.” on page 2-57 “Step 2. Verify the driver installation.” on page 2-58 “Step 3. Set up the data source using Database Explorer.” on page 2-58 “Step 4.
2 Getting Started with Database Toolbox If you are running 32-bit or 64-bit MATLAB, the corresponding 32-bit or 64-bit Microsoft ODBC Administrator opens when you start creating a new ODBC data source using Database Explorer. The drivers listed in the Create New Data Source dialog box in the Microsoft ODBC Administrator are also 32-bit or 64-bit respectively. Step 2. Verify the driver installation. The ODBC driver is typically preinstalled on your computer.
MySQL ODBC for Windows 3 Click the System DSN tab and then click Add. When setting up an ODBC data source, you can use a User DSN or System DSN. A User DSN is specific to the user on a machine. Any data sources a user defines under User DSN are seen only by that specific user. Conversely, a System DSN is not specific to the user on a machine. Any data sources a user defines under System DSN on a machine can be seen by any user who logs into that machine.
2 Getting Started with Database Toolbox Enter your database name in the Database field. Leave all tabs under the Details button with default settings. 6 Click Test to test the connection to your database. If your computer successfully connects to the database, the Test Result dialog box displays this message: Connection successful. 7 Click OK in the MySQL Connector/ODBC Data Source Configuration dialog box. The ODBC Data Source Administrator dialog box shows the ODBC data source MySQL.
MySQL ODBC for Windows 2 In the Connect to a Data Source dialog box, connect to your database by selecting the data source name for the MySQL database from the Data Sources list. Enter a user name and password. Click Connect. Database Explorer connects to your database and displays its contents in a tab named with the data source name. 3 Close the connection using Database Explorer by hovering the cursor over the Close button ( ) next to the MySQL ODBC data source name on the database tab.
2 Getting Started with Database Toolbox Close button turns into a red circle ( ). Click it to close the database connection. If you want to close Database Explorer and all database connections, click the Close button ( ) in the top-right corner. If Database Explorer is docked, click the Close button ( connections and Database Explorer. ) to close all database Connect to MySQL using the native ODBC connection command line. 1 Connect to the database with the ODBC data source name.
MySQL JDBC for Windows MySQL JDBC for Windows This tutorial shows how to set up a data source and connect to your MySQL database. This tutorial uses the MySQL Connector/J 5.1.17 driver to connect to the MySQL Version 5.5.16 database. In this section... “Step 1. Verify the driver installation.” on page 2-63 “Step 2. Add the JDBC driver to the MATLAB static Java class path.” on page 2-63 “Step 3. Set up the data source using Database Explorer.” on page 2-64 “Step 4.
2 Getting Started with Database Toolbox Step 3. Set up the data source using Database Explorer. This step is required only for connecting to Database Explorer. If you want to use the command line to connect to your database, see “Connect to MySQL using the JDBC connection command line.” on page 2-68 1 Open Database Explorer by clicking the Apps tab on the MATLAB Toolstrip. Then, select Database Explorer from the Database Connectivity and Reporting section in the apps gallery.
MySQL JDBC for Windows 3 Select MYSQL from the Vendor list. After selecting the vendor, if you did not add the JDBC driver file path to the Java class path, this dialog box displays this message at the bottom. Address this message by following the steps described in Step 2. 4 Enter the database server name in the Server Name field, port number in the Port Number field, user name in the Username field, password in the Password field, and database name in the Database field.
2 Getting Started with Database Toolbox 6 Enter a data source name in the Data Source Name field in the Create a New JDBC data source dialog box. Use a new data source name that does not appear in the existing list of data source names. Click Save. The new JDBC data source appears in the list of data sources in the Connect to a Data Source dialog box.
MySQL JDBC for Windows Database Explorer connects to your database and displays its contents in a tab named with the data source name. 2 Close the connection using Database Explorer by hovering the cursor over the Close button ( ) next to the MySQL JDBC data source name on the database tab. The Close button turns into a red circle ( ). Click it to close the database connection. If you want to close Database Explorer and all database connections, click the Close button ( ) in the top-right corner.
2 Getting Started with Database Toolbox Connect to MySQL using the JDBC connection command line. 1 Use the Vendor name-value pair argument of database to specify a connection to a MySQL database. For example, the following code assumes you are connecting to a database named dbname on a database server named sname with user name username and password pwd. conn = database('dbname','username','pwd',... 'Vendor','MySQL',... 'Server','sname'); 2 Close the database connection conn.
PostgreSQL ODBC for Windows PostgreSQL ODBC for Windows This tutorial shows how to set up a data source and connect to your PostgreSQL database. This tutorial uses the PostgreSQL ANSI(x64) driver version 9.01.02.00 to connect to the PostgreSQL 9.2 database. In this section... “Step 1. Check the 32-bit and 64-bit compatibility.” on page 2-69 “Step 2. Verify the driver installation.” on page 2-70 “Step 3. Set up the data source using Database Explorer.” on page 2-70 “Step 4.
2 Getting Started with Database Toolbox If you are running 32-bit or 64-bit MATLAB, the corresponding 32-bit or 64-bit Microsoft ODBC Data Source Administrator opens when you start creating a new ODBC data source using Database Explorer. The drivers listed in the Create New Data Source dialog box in the Microsoft ODBC Data Source Administrator are also 32-bit or 64-bit respectively. The following steps use 64-bit for MATLAB, the ODBC driver, the database, and the operating system. Step 2.
PostgreSQL ODBC for Windows section in the apps gallery. Alternatively, enter dexplore at the command line. If no data sources are set up, a message box opens. Click OK to close it. Otherwise, the Connect to a Data Source dialog box opens. Click Cancel to close this dialog box. 2 Click the Database Explorer tab, and then select New > ODBC. In the ODBC Data Source Administrator dialog box, you can define the ODBC data source.
2 Getting Started with Database Toolbox 2-72 3 Click the System DSN tab and then click Add. When setting up an ODBC data source, you can use a User DSN or System DSN. A User DSN is specific to the user on a machine. Any data sources a user defines under User DSN are seen only by that specific user. Conversely, a System DSN is not specific to the user on a machine. Any data sources a user defines under System DSN on a machine can be seen by any user who logs into that machine.
PostgreSQL ODBC for Windows 7 Click Save in the PostgreSQL ANSI ODBC Driver (psqlODBC) Setup dialog box. The ODBC Data Source Administrator dialog box shows the ODBC data source PostgreSQL30. After you complete the data source setup, connect to the PostgreSQL database using Database Explorer or the native ODBC connection command line. Step 4. Connect using Database Explorer or the command line. Connect to PostgreSQL using Database Explorer.
2 Getting Started with Database Toolbox 3 Close the connection using Database Explorer by hovering the cursor over the Close button ( ) next to the PostgreSQL30 data source name on the database tab. The Close button turns into a red circle ( ). Click it to close the database connection. If you want to close Database Explorer and all database connections, click the Close button ( ) in the top-right corner. If Database Explorer is docked, click the Close button ( connections and Database Explorer.
PostgreSQL ODBC for Windows Connect to PostgreSQL using the native ODBC connection command line. 1 Connect to the database with the ODBC data source name. For example, the following code assumes you are connecting to a data source named PostgreSQL with user name username and password pwd. conn = database.ODBCConnection('PostgreSQL','username','pwd'); 2 Close the database connection conn.
2 Getting Started with Database Toolbox PostgreSQL JDBC for Windows This tutorial shows how to set up a data source and connect to your PostgreSQL database. This tutorial uses the JDBC4 PostgreSQL Driver, Version 8.4 to connect to the PostgreSQL 9.2 database. In this section... “Step 1. Verify the driver installation.” on page 2-76 “Step 2. Add the JDBC driver to the MATLAB static Java class path.” on page 2-76 “Step 3. Set up the data source using Database Explorer.” on page 2-77 “Step 4.
PostgreSQL JDBC for Windows Step 3. Set up the data source using Database Explorer. This step is required only for connecting to Database Explorer. If you want to use the command line to connect to your database, see “Connect to PostgreSQL using the JDBC connection command line.” on page 2-81 1 Open Database Explorer by clicking the Apps tab on the MATLAB Toolstrip. Then, select Database Explorer from the Database Connectivity and Reporting section in the apps gallery.
2 Getting Started with Database Toolbox 2-78 3 Select POSTGRESQL from the Vendor list. After selecting the vendor, if you did not add the JDBC driver file path to the Java class path, this dialog box displays this message at the bottom. Address this message by following the steps described in Step 2. 4 Enter the database server name in the Server Name field, port number in the Port Number field, user name in the Username field, password in the Password field, and database name in the Database field.
PostgreSQL JDBC for Windows 6 Enter a data source name in the Data Source Name field in the Create a New JDBC data source dialog box. Use a new data source name that does not appear in the existing list of data source names. Click Save. The new JDBC data source appears in the list of data sources in the Connect to a Data Source dialog box. 7 If this time is the first time that you are creating a data source using Database Explorer, the New file to store JDBC connection parameters dialog box opens.
2 Getting Started with Database Toolbox Database Explorer connects to your database and displays its contents in a tab named with the data source name. 2 Close the connection using Database Explorer by hovering the cursor over the Close button ( ) next to the Postgresql JDBC data source name on the database tab. The Close button turns into a red circle ( ). Click it to close the database connection.
PostgreSQL JDBC for Windows Connect to PostgreSQL using the JDBC connection command line. 1 Use the Vendor name-value pair argument of database to specify a connection to a PostgreSQL database. For example, the following code assumes you are connecting to a database named dbname on a database server named sname with user name username and password pwd. conn = database('dbname','username','pwd',... 'Vendor','PostgreSQL',... 'Server','sname'); 2 Close the database connection conn.
2 Getting Started with Database Toolbox SQLite JDBC for Windows This tutorial shows how to set up a data source and connect to your SQLite database. This tutorial uses the SQLite JDBC 3.7.2 Driver to connect to the SQLite Version 3.7.17 database. In this section... “Step 1. Verify the driver installation.” on page 2-82 “Step 2. Add the JDBC driver to the MATLAB static Java class path.” on page 2-82 “Step 3. Set up the data source using Database Explorer.” on page 2-83 “Step 4.
SQLite JDBC for Windows Step 3. Set up the data source using Database Explorer. This step is required only for connecting to Database Explorer. If you want to use the command line to connect to your database, see “Connect to SQLite using the JDBC connection command line.” on page 2-87 The driver and URL fields (in Database Explorer Create a New JDBC data source dialog box and in the database function) can vary depending on the type and version of the JDBC driver and the database you are working with.
2 Getting Started with Database Toolbox 2-84 3 Select OTHER from the Vendor list. 4 Enter the SQLite driver Java class object in the Driver field. Here, use org.sqlite.JDBC. After entering the driver, if you did not add the JDBC driver file path to the Java class path, this dialog box displays this message at the bottom. Address this message by following the steps described in Step 2. 5 Connect to the SQLite database by creating a URL string using the format jdbc:subprotocol:subname.
SQLite JDBC for Windows dbpath is the full path to your SQLite database on your computer. Enter your string into the URL field. 6 Enter your user name in the Username field and your password in the Password field, or leave them blank if your database does not need them. Click Test to test the connection. If your connection succeeded, Database Explorer displays Connection Successful! 7 Enter a data source name in the Data Source Name field in the Create a New JDBC data source dialog box.
2 Getting Started with Database Toolbox Database Explorer connects to your database and displays its contents in a tab named with the data source name. 2 Close the connection using Database Explorer by hovering the cursor over the Close button ( ) next to the SQLite data source name on the database tab. The Close button turns into a red circle ( ). Click it to close the database connection.
SQLite JDBC for Windows If Database Explorer is docked, click the Close button ( connections and Database Explorer. ) to close all database Connect to SQLite using the JDBC connection command line. 1 Create a URL string using the format jdbc:subprotocol:subname. The jdbc part of this string stays constant for any JDBC driver. subprotocol is a database type. In this case, subprotocol is sqlite. The last part of the URL string is subname. For SQLite, this contains the location of the database.
2 Getting Started with Database Toolbox See Also close | database | javaaddpath More About 2-88 • “Working with Database Explorer” on page 4-2 • “Bringing Java Classes into MATLAB Workspace”
Sybase ODBC for Windows Sybase ODBC for Windows This tutorial shows how to set up a data source and connect to your Sybase database. This tutorial uses the Adaptive Server Enterprise Version 15.07.00.401 ODBC Driver to connect to the Sybase Adaptive Server Enterprise 15.7 database. In this section... “Step 1. Check the 32-bit and 64-bit compatibility.” on page 2-89 “Step 2. Verify the driver installation.” on page 2-90 “Step 3. Set up the data source using Database Explorer.” on page 2-90 “Step 4.
2 Getting Started with Database Toolbox If you are running 32-bit or 64-bit MATLAB, the corresponding 32-bit or 64-bit Microsoft ODBC Administrator opens when you start creating a new ODBC data source using Database Explorer. The drivers listed in the Create New Data Source dialog box in the Microsoft ODBC Administrator are also 32-bit or 64-bit respectively. The following steps use 64-bit for MATLAB, the ODBC driver, the database, and the operating system. Step 2. Verify the driver installation.
Sybase ODBC for Windows section in the apps gallery. Alternatively, enter dexplore at the command line. If no data sources are set up, a message box opens. Click OK to close it. Otherwise, the Connect to a Data Source dialog box opens. Click Cancel to close this dialog box. 2 Click the Database Explorer tab, and then select New > ODBC. The ODBC Data Source Administrator dialog box to define the ODBC data source opens.
2 Getting Started with Database Toolbox 2-92 3 Click the System DSN tab and then click Add. When setting up an ODBC data source, you can use a User DSN or System DSN. A User DSN is specific to the user on a machine. Any data sources a user defines under User DSN are seen only by that specific user. Conversely, a System DSN is not specific to the user on a machine. Any data sources a user defines under System DSN on a machine can be seen by any user who logs into that machine.
Sybase ODBC for Windows 6 Click Test Connection to test the connection to your database. Another screen appears with login information. Enter your user name in the Logon ID field and your password in the Password field. The other three fields are prepopulated with your specific data. 7 Click OK. If your computer successfully connects to the database, the dialog box displays Login Succeeded. 8 Click OK in the Adaptive Server Enterprise dialog boxes to close them.
2 Getting Started with Database Toolbox After you complete the data source setup, connect to the Sybase database using Database Explorer or the command line using the native ODBC connection. Step 4. Connect using Database Explorer or the command line. Connect to Sybase using Database Explorer. If you experience issues connecting using Database Explorer, use the native ODBC interface with the command line or JDBC to connect to your database.
Sybase ODBC for Windows 3 Close the connection using Database Explorer by hovering the cursor over the Close button ( ) next to the Sybase data source name on the database tab. The Close button turns into a red circle ( ). Click it to close the database connection. If you want to close Database Explorer and all database connections, click the Close button ( ) in the top-right corner. If Database Explorer is docked, click the Close button ( connections and Database Explorer.
2 Getting Started with Database Toolbox Connect to Sybase using the native ODBC connection command line. 1 Connect to your database with the ODBC data source name. For example, the following code assumes you are connecting to a data source named Sybase with user name username and password pwd. conn = database.ODBCConnection('Sybase','username','pwd'); 2 Close the database connection conn.
Sybase JDBC for Windows Sybase JDBC for Windows This tutorial shows how to set up a data source and connect to your Sybase database. This tutorial uses the jConnect 4 JDBC Driver to connect to the Sybase Adaptive Server Enterprise 15.7 database. In this section... “Step 1. Verify the driver installation.” on page 2-97 “Step 2. Add the JDBC driver to the MATLAB static Java class path.” on page 2-97 “Step 3. Set up the data source using Database Explorer.” on page 2-98 “Step 4.
2 Getting Started with Database Toolbox Step 3. Set up the data source using Database Explorer. This step is required only for connecting to Database Explorer. If you want to use the command line to connect to your database, see “Connect to Sybase using the JDBC connection command line.
Sybase JDBC for Windows 3 Select OTHER from the Vendor list. 4 Enter the Sybase driver Java class object in the Driver field. Here, use com.sybase.jdbc4.jdbc.SybDriver. After entering the driver, if you did not add the JDBC driver file path to the Java class path, this dialog box displays this message at the bottom. Address this message by following the steps described in Step 2. 5 Connect to the Sybase database by creating a URL string using the format jdbc:subprotocol:subname.
2 Getting Started with Database Toolbox your server name, PortNumber is your port number, and dbname is your database name. Enter your full string into the URL field. 6 Enter your user name in the Username field and your password in the Password field. Click Test to test the connection. If your connection succeeded, Database Explorer displays Connection Successful! 7 Enter a data source name in the Data Source Name field in the Create a New JDBC data source dialog box.
Sybase JDBC for Windows Database Explorer connects to your database and displays its contents in a tab named with the data source name. 2 Close the connection using Database Explorer by hovering the cursor over the Close button ( ) next to the Sybase JDBC data source name on the database tab. The Close button turns into a red circle ( ). Click it to close the database connection.
2 Getting Started with Database Toolbox you want to close Database Explorer and all database connections, click the Close button ( ) in the top-right corner. If Database Explorer is docked, click the Close button ( connections and Database Explorer. ) to close all database Connect to Sybase using the JDBC connection command line. 1 Create a URL string using the format jdbc:subprotocol:subname. The jdbc part of this string stays constant for any JDBC driver. subprotocol is a database type.
Sybase JDBC for Windows 'com.sybase.jdbc4.jdbc.SybDriver','URL'); 3 Close the database connection conn.
2 Getting Started with Database Toolbox Microsoft SQL Server JDBC for Mac OS X This tutorial shows how to set up a data source and connect to your Microsoft SQL Server database. This tutorial uses the Microsoft JDBC Driver 4.0 for Microsoft SQL Server to connect to the Microsoft SQL Server 2012 Express database. In this section... “Step 1. Verify the driver installation.” on page 2-104 “Step 2. Add the JDBC driver to the MATLAB static Java class path.” on page 2-104 “Step 3.
Microsoft SQL Server JDBC for Mac OS X Step 3. Set up the data source using Database Explorer. This step is required only for connecting to Database Explorer. If you want to use the command line to connect to your database, see “Connect to Microsoft SQL Server using the JDBC connection command line.” on page 2-109 1 Open Database Explorer by clicking the Apps tab on the MATLAB Toolstrip. Then, select Database Explorer from the Database Connectivity and Reporting section in the apps gallery.
2 Getting Started with Database Toolbox 2-106 3 Select MICROSOFT SQL SERVER from the Vendor list. After selecting the vendor, if you did not add the JDBC driver file path to the Java class path, this dialog box displays this message at the bottom. Address this message by following the steps described in Step 2.
Microsoft SQL Server JDBC for Mac OS X field, and database name in the Database field. Set the Authentication Type to Server. 5 Click Test to test the connection. If your connection succeeded, Database Explorer displays Connection Successful! 6 Enter a data source name in the Data Source Name field in the Create a New JDBC data source dialog box. Use a new data source name that does not appear in the existing list of data source names. Click Save.
2 Getting Started with Database Toolbox Database Explorer connects to your database and displays its contents in a tab named with the data source name. 2 Close the connection using Database Explorer by hovering the cursor over the Close button ( ) next to the MS_SQL_Server data source name on the database tab. The Close button turns into a red circle ( 2-108 ). Click it to close the database connection.
Microsoft SQL Server JDBC for Mac OS X you want to close Database Explorer and all database connections, click the Close button ( ) in the top-left corner. If Database Explorer is docked, click the Close button ( connections and Database Explorer. ) to close all database Connect to Microsoft SQL Server using the JDBC connection command line. When using the command line, you do not have to set up a data source with Database Explorer.
2 Getting Started with Database Toolbox See Also close | database | javaaddpath More About 2-110 • “Working with Database Explorer” on page 4-2 • “Bringing Java Classes into MATLAB Workspace”
Microsoft SQL Server JDBC for Linux Microsoft SQL Server JDBC for Linux This tutorial shows how to set up a data source and connect to your Microsoft SQL Server database. This tutorial uses the Microsoft JDBC Driver 4.0 for Microsoft SQL Server to connect to the Microsoft SQL Server 2012 Express database. In this section... “Step 1. Verify the driver installation.” on page 2-111 “Step 2. Add the JDBC driver to the MATLAB static Java class path.” on page 2-111 “Step 3.
2 Getting Started with Database Toolbox Step 3. Set up the data source using Database Explorer. This step is required only for connecting to Database Explorer. If you want to use the command line to connect to your database, see “Connect to Microsoft SQL Server using the JDBC connection command line.” on page 2-116 1 Open Database Explorer by clicking the Apps tab on the MATLAB Toolstrip. Then, select Database Explorer from the Database Connectivity and Reporting section in the apps gallery.
Microsoft SQL Server JDBC for Linux 3 Select MICROSOFT SQL SERVER from the Vendor list. After selecting the vendor, if you did not add the JDBC driver file path to the Java class path, this dialog box displays this message at the bottom. Address this message by following the steps described in Step 2. 4 Enter the database server name in the Server Name field, port number in the Port Number field, user name in the Username field, password in the Password field, and database name in the Database field.
2 Getting Started with Database Toolbox 5 Click Test to test the connection. If your connection succeeded, Database Explorer displays Connection Successful! 6 Enter a data source name in the Data Source Name field in the Create a New JDBC data source dialog box. Use a new data source name that does not appear in the existing list of data source names. Click Save. The new JDBC data source appears in the list of data sources in the Connect to a Data Source dialog box.
Microsoft SQL Server JDBC for Linux Database Explorer connects to your database and displays its contents in a tab named with the data source name. 2 Close the connection using Database Explorer by hovering the cursor over the Close button ( ) next to the MS_SQL_Server data source name on the database tab. The Close button turns into a red circle ( ). Click it to close the database connection.
2 Getting Started with Database Toolbox Connect to Microsoft SQL Server using the JDBC connection command line. When using the command line, you do not have to set up a data source with Database Explorer. You can use the command line to pass all the required parameters for connection. 1 Use the Vendor name-value pair argument of database to specify a connection to a Microsoft SQL Server database. Set the AuthType name-value pair argument to Server.
Microsoft SQL Server JDBC for Linux • “Bringing Java Classes into MATLAB Workspace” 2-117
2 Getting Started with Database Toolbox Oracle JDBC for Mac OS X This tutorial shows how to set up a data source and connect to your Oracle database. This tutorial uses the Oracle Database 11g Release 2 (11.2.0.3) JDBC driver for use with JDK 1.6 to connect to the Oracle 11g Enterprise Edition Release 11.2.0.1.0 database. In this section... “Step 1. Verify the driver installation.” on page 2-118 “Step 2. Add the JDBC driver to the MATLAB static Java class path.” on page 2-118 “Step 3.
Oracle JDBC for Mac OS X Step 3. Set up the data source using Database Explorer. This step is required only for connecting to Database Explorer. If you want to use the command line to connect to your database, see “Connect to Oracle using the JDBC connection command line.” on page 2-123 1 Open Database Explorer by clicking the Apps tab on the MATLAB Toolstrip. Then, select Database Explorer from the Database Connectivity and Reporting section in the apps gallery.
2 Getting Started with Database Toolbox 2-120 3 Select ORACLE from the Vendor list. After selecting the vendor, if you did not add the JDBC driver file path to the Java class path, this dialog box displays this message at the bottom. Address this message by following the steps described in Step 2.
Oracle JDBC for Mac OS X and database name in the Database field. Select Driver Type of thin or oci. Use thin as the default driver. Use oci if you installed an OCI driver. 5 Click Test to test the connection. If your connection succeeded, Database Explorer displays Connection Successful! 6 Enter a data source name in the Data Source Name field in the Create a New JDBC data source dialog box. Use a new data source name that does not appear in the existing list of data source names. Click Save.
2 Getting Started with Database Toolbox Database Explorer connects to your database and displays its contents in a tab named with the data source name. 2 Close the connection using Database Explorer by hovering the cursor over the Close button ( ) next to the ORA data source name on the database tab. The Close button turns into a red circle ( 2-122 ). Click it to close the database connection.
Oracle JDBC for Mac OS X want to close Database Explorer and all database connections, click the Close button ( ) in the top-left corner. If Database Explorer is docked, click the Close button ( connections and Database Explorer. ) to close all database Connect to Oracle using the JDBC connection command line. When using the command line, you do not have to set up a data source with Database Explorer. You can use the command line to pass all the required parameters for connection.
2 Getting Started with Database Toolbox 'Server','sname','PortNumber',123456); Or, if you have trouble using the database function to connect to your Oracle database, try using the full entry in your tnsnames.ora file in the URL string as one consecutive line. Leave the first argument blank. For example, the following code assumes the value of the URL name-value pair argument is set to the following tnsnames.ora file entry for an Oracle database. conn = database('','username','pwd',...
Oracle JDBC for Linux Oracle JDBC for Linux This tutorial shows how to set up a data source and connect to your Oracle database. This tutorial uses the Oracle Database 11g Release 2 (11.2.0.3) JDBC driver for use with JDK 1.6 to connect to the Oracle 11g Enterprise Edition Release 11.2.0.1.0 database. In this section... “Step 1. Verify the driver installation.” on page 2-125 “Step 2. Add the JDBC driver to the MATLAB static Java class path.” on page 2-125 “Step 3.
2 Getting Started with Database Toolbox Step 3. Set up the data source using Database Explorer. This step is required only for connecting to Database Explorer. If you want to use the command line to connect to your database, see “Connect to Oracle using the JDBC connection command line.” on page 2-130 1 Open Database Explorer by clicking the Apps tab on the MATLAB Toolstrip. Then, select Database Explorer from the Database Connectivity and Reporting section in the apps gallery.
Oracle JDBC for Linux 3 Select ORACLE from the Vendor list. After selecting the vendor, if you did not add the JDBC driver file path to the Java class path, this dialog box displays this message at the bottom. Address this message by following the steps described in Step 2. 4 Enter the database server name in the Server Name field, port number in the Port Number field, user name in the Username field, password in the Password field, and database name in the Database field.
2 Getting Started with Database Toolbox 5 Click Test to test the connection. If your connection succeeded, Database Explorer displays Connection Successful! 6 Enter a data source name in the Data Source Name field in the Create a New JDBC data source dialog box. Use a new data source name that does not appear in the existing list of data source names. Click Save. The new JDBC data source appears in the list of data sources in the Connect to a Data Source dialog box.
Oracle JDBC for Linux Database Explorer connects to your database and displays its contents in a tab named with the data source name. 2 Close the connection using Database Explorer by hovering the cursor over the Close button ( ) next to the ORA data source name on the database tab. The Close button turns into a red circle ( ). Click it to close the database connection. If you want to close Database Explorer and all database connections, click the Close button ( ) in the top-right corner.
2 Getting Started with Database Toolbox Connect to Oracle using the JDBC connection command line. When using the command line, you do not have to set up a data source with Database Explorer. You can use the command line to pass all the required parameters for connection. 1 Use the Vendor name-value pair argument of database to specify a connection to an Oracle database. Set the DriverType name-value pair argument to thin.
Oracle JDBC for Linux code assumes the value of the URL name-value pair argument is set to the following tnsnames.ora file entry for an Oracle database. conn = database('','username','pwd',... 'Vendor','Oracle',... 'URL',['jdbc:oracle:thin:@(DESCRIPTION = '... '(ADDRESS = (PROTOCOL = TCP)(HOST = sname)'... '(PORT = 123456)) (CONNECT_DATA = '... '(SERVER = DEDICATED) (SERVICE_NAME = dbname) ) )']); 2 Close the database connection conn.
2 Getting Started with Database Toolbox MySQL JDBC for Mac OS X This tutorial shows how to set up a data source and connect to your MySQL database. This tutorial uses the MySQL Connector/J 5.1.17 driver to connect to the MySQL Version 5.5.16 database. In this section... “Step 1. Verify the driver installation.” on page 2-132 “Step 2. Add the JDBC driver to the MATLAB static Java class path.” on page 2-132 “Step 3. Set up the data source using Database Explorer.” on page 2-133 “Step 4.
MySQL JDBC for Mac OS X Step 3. Set up the data source using Database Explorer. This step is required only for connecting to Database Explorer. If you want to use the command line to connect to your database, see “Connect to MySQL using the JDBC connection command line.” on page 2-137 1 Open Database Explorer by clicking the Apps tab on the MATLAB Toolstrip. Then, select Database Explorer from the Database Connectivity and Reporting section in the apps gallery.
2 Getting Started with Database Toolbox 2-134 3 Select MYSQL from the Vendor list. After selecting the vendor, if you did not add the JDBC driver file path to the Java class path, this dialog box displays this message at the bottom. Address this message by following the steps described in Step 2. 4 Enter the database server name in the Server Name field, port number in the Port Number field, user name in the Username field, password in the Password field, and database name in the Database field.
MySQL JDBC for Mac OS X 6 Enter a data source name in the Data Source Name field in the Create a New JDBC data source dialog box. Use a new data source name that does not appear in the existing list of data source names. Click Save. The new JDBC data source appears in the list of data sources in the Connect to a Data Source dialog box. 7 If this time is the first time that you are creating a data source using Database Explorer, the New file to store JDBC connection parameters dialog box opens.
2 Getting Started with Database Toolbox Database Explorer connects to your database and displays its contents in a tab named with the data source name. 2 Close the connection using Database Explorer by hovering the cursor over the Close button ( ) next to the MySQL data source name on the database tab. The Close button turns into a red circle ( ). Click it to close the database connection.
MySQL JDBC for Mac OS X Connect to MySQL using the JDBC connection command line. When using the command line, you do not have to set up a data source with Database Explorer. You can use the command line to pass all the required parameters for connection. 1 Use the Vendor name-value pair argument of database to specify a connection to a MySQL database.
2 Getting Started with Database Toolbox • 2-138 “Bringing Java Classes into MATLAB Workspace”
MySQL JDBC for Linux MySQL JDBC for Linux This tutorial shows how to set up a data source and connect to your MySQL database. This tutorial uses the MySQL Connector/J 5.1.17 driver to connect to the MySQL Version 5.5.16 database. In this section... “Step 1. Verify the driver installation.” on page 2-139 “Step 2. Add the JDBC driver to the MATLAB static Java class path.” on page 2-139 “Step 3. Set up the data source using Database Explorer.” on page 2-140 “Step 4.
2 Getting Started with Database Toolbox Step 3. Set up the data source using Database Explorer. This step is required only for connecting to Database Explorer. If you want to use the command line to connect to your database, see “Connect to MySQL using the JDBC connection command line.” on page 2-144 1 Open Database Explorer by clicking the Apps tab on the MATLAB Toolstrip. Then, select Database Explorer from the Database Connectivity and Reporting section in the apps gallery.
MySQL JDBC for Linux 3 Select MYSQL from the Vendor list. After selecting the vendor, if you did not add the JDBC driver file path to the Java class path, this dialog box displays this message at the bottom. Address this message by following the steps described in Step 2. 4 Enter the database server name in the Server Name field, port number in the Port Number field, user name in the Username field, password in the Password field, and database name in the Database field.
2 Getting Started with Database Toolbox in the existing list of data source names. Click Save. The new JDBC data source appears in the list of data sources in the Connect to a Data Source dialog box. 7 If this time is the first time that you are creating a data source using Database Explorer, the New file to store JDBC connection parameters dialog box opens. Use this dialog box to create a MAT-file that saves your specified data source information for future Database Explorer sessions.
MySQL JDBC for Linux 2 Close the connection using Database Explorer by hovering the cursor over the Close button ( ) next to the MySQL data source name on the database tab. The Close button turns into a red circle ( ). Click it to close the database connection. If you want to close Database Explorer and all database connections, click the Close button ( ) in the top-right corner. If Database Explorer is docked, click the Close button ( connections and Database Explorer.
2 Getting Started with Database Toolbox Connect to MySQL using the JDBC connection command line. When using the command line, you do not have to set up a data source with Database Explorer. You can use the command line to pass all the required parameters for connection. 1 Use the Vendor name-value pair argument of database to specify a connection to a MySQL database.
MySQL JDBC for Linux • “Bringing Java Classes into MATLAB Workspace” 2-145
2 Getting Started with Database Toolbox PostgreSQL JDBC for Mac OS X This tutorial shows how to set up a data source and connect to your PostgreSQL database. This tutorial uses the JDBC4 PostgreSQL Driver, Version 8.4 to connect to the PostgreSQL 9.2 database. In this section... “Step 1. Verify the driver installation.” on page 2-146 “Step 2. Add the JDBC driver to the MATLAB static Java class path.” on page 2-146 “Step 3. Set up the data source using Database Explorer.” on page 2-147 “Step 4.
PostgreSQL JDBC for Mac OS X Step 3. Set up the data source using Database Explorer. This step is required only for connecting to Database Explorer. If you want to use the command line to connect to your database, see “Connect to PostgreSQL using the JDBC connection command line.” on page 2-151 1 Open Database Explorer by clicking the Apps tab on the MATLAB Toolstrip. Then, select Database Explorer from the Database Connectivity and Reporting section in the apps gallery.
2 Getting Started with Database Toolbox 2-148 3 Select POSTGRESQL from the Vendor list. After selecting the vendor, if you did not add the JDBC driver file path to the Java class path, this dialog box displays this message at the bottom. Address this message by following the steps described in Step 2. 4 Enter the database server name in the Server Name field, port number in the Port Number field, user name in the Username field, password in the Password field, and database name in the Database field.
PostgreSQL JDBC for Mac OS X 6 Enter a data source name in the Data Source Name field in the Create a New JDBC data source dialog box. Use a new data source name that does not appear in the existing list of data source names. Click Save. The new JDBC data source appears in the list of data sources in the Connect to a Data Source dialog box. 7 If this time is the first time that you are creating a data source using Database Explorer, the New file to store JDBC connection parameters dialog box opens.
2 Getting Started with Database Toolbox Database Explorer connects to your database and displays its contents in a tab named with the data source name. 2 Close the connection using Database Explorer by hovering the cursor over the Close button ( ) next to the PostgreSQL data source name on the database tab. The Close button turns into a red circle ( ). Click it to close the database connection.
PostgreSQL JDBC for Mac OS X Connect to PostgreSQL using the JDBC connection command line. When using the command line, you do not have to set up a data source with Database Explorer. You can use the command line to pass all the required parameters for connection. 1 Use the Vendor name-value pair argument of database to specify a connection to a PostgreSQL database.
2 Getting Started with Database Toolbox • 2-152 “Bringing Java Classes into MATLAB Workspace”
PostgreSQL JDBC for Linux PostgreSQL JDBC for Linux This tutorial shows how to set up a data source and connect to your PostgreSQL database. This tutorial uses the JDBC4 PostgreSQL Driver, Version 8.4 to connect to the PostgreSQL 9.2 database. In this section... “Step 1. Verify the driver installation.” on page 2-153 “Step 2. Add the JDBC driver to the MATLAB static Java class path.” on page 2-153 “Step 3. Set up the data source using Database Explorer.” on page 2-154 “Step 4.
2 Getting Started with Database Toolbox Step 3. Set up the data source using Database Explorer. This step is required only for connecting to Database Explorer. If you want to use the command line to connect to your database, see “Connect to PostgreSQL using the JDBC connection command line.” on page 2-158 1 Open Database Explorer by clicking the Apps tab on the MATLAB Toolstrip. Then, select Database Explorer from the Database Connectivity and Reporting section in the apps gallery.
PostgreSQL JDBC for Linux 3 Select POSTGRESQL from the Vendor list. After selecting the vendor, if you did not add the JDBC driver file path to the Java class path, this dialog box displays this message at the bottom. Address this message by following the steps described in Step 2. 4 Enter the database server name in the Server Name field, port number in the Port Number field, user name in the Username field, password in the Password field, and database name in the Database field.
2 Getting Started with Database Toolbox 6 Enter a data source name in the Data Source Name field in the Create a New JDBC data source dialog box. Use a new data source name that does not appear in the existing list of data source names. Click Save. The new JDBC data source appears in the list of data sources in the Connect to a Data Source dialog box.
PostgreSQL JDBC for Linux 2 Close the connection using Database Explorer by hovering the cursor over the Close button ( ) next to the PostgreSQL data source name on the database tab. The Close button turns into a red circle ( ). Click it to close the database connection. If you want to close Database Explorer and all database connections, click the Close button ( ) in the top-right corner. If Database Explorer is docked, click the Close button ( connections and Database Explorer.
2 Getting Started with Database Toolbox Connect to PostgreSQL using the JDBC connection command line. When using the command line, you do not have to set up a data source with Database Explorer. You can use the command line to pass all the required parameters for connection. 1 Use the Vendor name-value pair argument of database to specify a connection to a PostgreSQL database.
PostgreSQL JDBC for Linux • “Bringing Java Classes into MATLAB Workspace” 2-159
2 Getting Started with Database Toolbox SQLite JDBC for Mac OS X This tutorial shows how to set up a data source and connect to your SQLite database. This tutorial uses the SQLite JDBC 3.7.2 Driver to connect to the SQLite Version 3.7.17 database. In this section... “Step 1. Verify the driver installation.” on page 2-160 “Step 2. Add the JDBC driver to the MATLAB static Java class path.” on page 2-160 “Step 3. Set up the data source using Database Explorer.” on page 2-161 “Step 4.
SQLite JDBC for Mac OS X Step 3. Set up the data source using Database Explorer. This step is required only for connecting to Database Explorer. If you want to use the command line to connect to your database, see “Connect to SQLite using the JDBC connection command line.” on page 2-165 1 Open Database Explorer by clicking the Apps tab on the MATLAB Toolstrip. Then, select Database Explorer from the Database Connectivity and Reporting section in the apps gallery.
2 Getting Started with Database Toolbox 2-162 3 Select OTHER from the Vendor list. 4 Enter the SQLite driver Java class object in the Driver field. Here, use org.sqlite.JDBC. After entering the driver, if you did not add the JDBC driver file path to the Java class path, this dialog box displays this message at the bottom. Address this message by following the steps described in Step 2. 5 Connect to the SQLite database by creating a URL string using the format jdbc:subprotocol:subname.
SQLite JDBC for Mac OS X dbpath is the full path to your SQLite database on your computer. Enter your string into the URL field. 6 Enter your user name in the Username field and your password in the Password field, or leave them blank if your database does not need them. 7 Click Test to test the connection. If your connection succeeded, Database Explorer displays Connection Successful! 8 Enter a data source name in the Data Source Name field in the Create a New JDBC data source dialog box.
2 Getting Started with Database Toolbox Database Explorer connects to your database and displays its contents in a tab named with the data source name. 2 Close the connection using Database Explorer by hovering the cursor over the Close button ( ) next to the Sqlite data source name on the database tab. The Close button turns into a red circle ( ). Click it to close the database connection.
SQLite JDBC for Mac OS X If Database Explorer is docked, click the Close button ( connections and Database Explorer. ) to close all database Connect to SQLite using the JDBC connection command line. When using the command line, you do not have to set up a data source with Database Explorer. You can use the command line to pass all the required parameters for connection. 1 Create a URL string using the format jdbc:subprotocol:subname. The jdbc part of this string stays constant for any JDBC driver.
2 Getting Started with Database Toolbox close(conn) See Also close | database | javaaddpath More About 2-166 • “Working with Database Explorer” on page 4-2 • “Bringing Java Classes into MATLAB Workspace”
SQLite JDBC for Linux SQLite JDBC for Linux This tutorial shows how to set up a data source and connect to your SQLite database. This tutorial uses the SQLite JDBC 3.7.2 Driver to connect to the SQLite Version 3.7.17 database. In this section... “Step 1. Verify the driver installation.” on page 2-167 “Step 2. Add the JDBC driver to the MATLAB static Java class path.” on page 2-167 “Step 3. Set up the data source using Database Explorer.” on page 2-168 “Step 4.
2 Getting Started with Database Toolbox Step 3. Set up the data source using Database Explorer. This step is required only for connecting to Database Explorer. If you want to use the command line to connect to your database, see “Connect to SQLite using the JDBC connection command line.” on page 2-172 1 Open Database Explorer by clicking the Apps tab on the MATLAB Toolstrip. Then, select Database Explorer from the Database Connectivity and Reporting section in the apps gallery.
SQLite JDBC for Linux 3 Select OTHER from the Vendor list. 4 Enter the SQLite driver Java class object in the Driver field. Here, use org.sqlite.JDBC. After entering the driver, if you did not add the JDBC driver file path to the Java class path, this dialog box displays this message at the bottom. Address this message by following the steps described in Step 2. 5 Connect to the SQLite database by creating a URL string using the format jdbc:subprotocol:subname.
2 Getting Started with Database Toolbox 6 Enter your user name in the Username field and your password in the Password field, or leave them blank if your database does not need them. 7 Click Test to test the connection. If your connection succeeded, Database Explorer displays Connection Successful! 8 Enter a data source name in the Data Source Name field in the Create a New JDBC data source dialog box. Use a new data source name that does not appear in the existing list of data source names.
SQLite JDBC for Linux Database Explorer connects to your database and displays its contents in a tab named with the data source name. 2 Close the connection using Database Explorer by hovering the cursor over the Close button ( ) next to the Sqlite data source name on the database tab. The Close button turns into a red circle ( ). Click it to close the database connection. If you want to close Database Explorer and all database connections, click the Close button ( ) in the top-right corner.
2 Getting Started with Database Toolbox If Database Explorer is docked, click the Close button ( connections and Database Explorer. ) to close all database Connect to SQLite using the JDBC connection command line. When using the command line, you do not have to set up a data source with Database Explorer. You can use the command line to pass all the required parameters for connection. 1 Create a URL string using the format jdbc:subprotocol:subname.
SQLite JDBC for Linux See Also close | database | javaaddpath More About • “Working with Database Explorer” on page 4-2 • “Bringing Java Classes into MATLAB Workspace” 2-173
2 Getting Started with Database Toolbox Sybase JDBC for Mac OS X This tutorial shows how to set up a data source and connect to your Sybase database. This tutorial uses the jConnect 4 JDBC Driver to connect to the Sybase Adaptive Server Enterprise 15.7 database. In this section... “Step 1. Verify the driver installation.” on page 2-174 “Step 2. Add the JDBC driver to the MATLAB static Java class path.” on page 2-174 “Step 3. Set up the data source using Database Explorer.” on page 2-175 “Step 4.
Sybase JDBC for Mac OS X Step 3. Set up the data source using Database Explorer. This step is required only for connecting to Database Explorer. If you want to use the command line to connect to your database, see “Connect to Sybase using the JDBC connection command line.” on page 2-179 1 Open Database Explorer by clicking the Apps tab on the MATLAB Toolstrip. Then, select Database Explorer from the Database Connectivity and Reporting section in the apps gallery.
2 Getting Started with Database Toolbox 2-176 3 Select OTHER from the Vendor list. 4 Enter the Sybase driver Java class object in the Driver field. Here, use com.sybase.jdbc4.jdbc.SybDriver. After entering the driver, if you did not add the JDBC driver file path to the Java class path, this dialog box displays this message at the bottom. Address this message by following the steps described in Step 2.
Sybase JDBC for Mac OS X is jdbc:sybase:Tds:ServerName:PortNumber/dbname, where ServerName is your server name, PortNumber is your port number, and dbname is your database name. Enter your full string into the URL field. 6 Enter your user name in the Username field and your password in the Password field. 7 Click Test to test the connection.
2 Getting Started with Database Toolbox Database Explorer connects to your database and displays its contents in a tab named with the data source name. 2 Close the connection using Database Explorer by hovering the cursor over the Close button ( ) next to the Sybase data source name on the database tab. The Close button turns into a red circle ( 2-178 ). Click it to close the database connection.
Sybase JDBC for Mac OS X want to close Database Explorer and all database connections, click the Close button ( ) in the top-left corner. If Database Explorer is docked, click the Close button ( connections and Database Explorer. ) to close all database Connect to Sybase using the JDBC connection command line. When using the command line, you do not have to set up a data source with Database Explorer. You can use the command line to pass all the required parameters for connection.
2 Getting Started with Database Toolbox object. This code assumes the class object is com.sybase.jdbc4.jdbc.SybDriver. The last argument is the URL string URL. conn = database('dbname','username','pwd',... 'com.sybase.jdbc4.jdbc.SybDriver','URL'); 3 Close the database connection conn.
Sybase JDBC for Linux Sybase JDBC for Linux This tutorial shows how to set up a data source and connect to your Sybase database. This tutorial uses the jConnect 4 JDBC Driver to connect to the Sybase Adaptive Server Enterprise 15.7 database. In this section... “Step 1. Verify the driver installation.” on page 2-181 “Step 2. Add the JDBC driver to the MATLAB static Java class path.” on page 2-181 “Step 3. Set up the data source using Database Explorer.” on page 2-182 “Step 4.
2 Getting Started with Database Toolbox Step 3. Set up the data source using Database Explorer. This step is required only for connecting to Database Explorer. If you want to use the command line to connect to your database, see “Connect to Sybase using the JDBC connection command line.” on page 2-186 1 Open Database Explorer by clicking the Apps tab on the MATLAB Toolstrip. Then, select Database Explorer from the Database Connectivity and Reporting section in the apps gallery.
Sybase JDBC for Linux 3 Select OTHER from the Vendor list. 4 Enter the Sybase driver Java class object in the Driver field. Here, use com.sybase.jdbc4.jdbc.SybDriver. After entering the driver, if you did not add the JDBC driver file path to the Java class path, this dialog box displays this message at the bottom. Address this message by following the steps described in Step 2. 5 Connect to the Sybase database by creating a URL string using the format jdbc:subprotocol:subname.
2 Getting Started with Database Toolbox your server name, PortNumber is your port number, and dbname is your database name. Enter your full string into the URL field. 6 Enter your user name in the Username field and your password in the Password field. 7 Click Test to test the connection. If your connection succeeded, Database Explorer displays Connection Successful! 8 Enter a data source name in the Data Source Name field in the Create a New JDBC data source dialog box.
Sybase JDBC for Linux Database Explorer connects to your database and displays its contents in a tab named with the data source name. 2 Close the connection using Database Explorer by hovering the cursor over the Close button ( ) next to the Sybase data source name on the database tab. The Close button turns into a red circle ( ). Click it to close the database connection. If you want to close Database Explorer and all database connections, click the Close button ( ) in the top-right corner.
2 Getting Started with Database Toolbox If Database Explorer is docked, click the Close button ( connections and Database Explorer. ) to close all database Connect to Sybase using the JDBC connection command line. When using the command line, you do not have to set up a data source with Database Explorer. You can use the command line to pass all the required parameters for connection. 1 Create a URL string using the format jdbc:subprotocol:subname.
Sybase JDBC for Linux 'com.sybase.jdbc4.jdbc.SybDriver','URL'); 3 Close the database connection conn.
2 Getting Started with Database Toolbox Other ODBC- or JDBC-Compliant Databases This tutorial provides high-level workflows for using other ODBC- or JDBC-compliant databases. In this section... “ODBC-Compliant Databases” on page 2-188 “JDBC-Compliant Databases” on page 2-188 ODBC-Compliant Databases This tutorial shows how to configure your driver and connect to your ODBC-compliant database with MATLAB. Database Toolbox can connect to any ODBC-compliant database that is relational and that uses ANSI SQL.
Other ODBC- or JDBC-Compliant Databases Derby or Microsoft Windows Azure, here are some basic steps to follow. The details of the steps below can vary depending on your database and database version. For details about your database, contact your database administrator or refer to your database documentation.
2 Getting Started with Database Toolbox Related Examples • “MySQL ODBC for Windows” on page 2-57 • “Sybase JDBC for Windows” on page 2-97 More About 2-190 • “Bringing Java Classes into MATLAB Workspace” • “Working with Database Explorer” on page 4-2
Connecting to a Database Connecting to a Database To connect to your database, your ODBC or JDBC driver must be installed and your data source must be defined. For details about driver installation and data source setup, see “Configuring a Driver and Data Source” on page 2-13. In this section...
2 Getting Started with Database Toolbox • “Connect to Microsoft SQL Server using the native ODBC connection command line.” on page 2-31 • JDBC • “Connect to Microsoft SQL Server using Database Explorer.” on page 2-40 • “Connect to Microsoft SQL Server using the JDBC connection command line.” on page 2-42 Oracle • ODBC • Database Explorer cannot work with the Oracle ODBC driver because of an issue with the JDBC/ODBC bridge. For details, see “Database Explorer Error Messages” on page 3-14.
Connecting to a Database • “Connect to PostgreSQL using Database Explorer.” on page 2-73 • “Connect to PostgreSQL using the native ODBC connection command line.” on page 2-75 • JDBC • “Connect to PostgreSQL using Database Explorer.” on page 2-79 • “Connect to PostgreSQL using the JDBC connection command line.” on page 2-81 SQLite • JDBC • “Connect to SQLite using Database Explorer.” on page 2-85 • “Connect to SQLite using the JDBC connection command line.
2 Getting Started with Database Toolbox • 2-194 “Configuring a Driver and Data Source” on page 2-13
Selecting Data Selecting Data In this section... “Use Database Explorer to Select Data” on page 2-195 “Use the Command Line to Select Data” on page 2-195 “Working with Custom Data Types” on page 2-195 “Running SQL Queries Saved in Scripts or Files” on page 2-196 You can open two different connections to the same database, one using Database Explorer and another using the command line. If you are working with large data sets, use the command line instead of Database Explorer for maximum performance.
2 Getting Started with Database Toolbox Running SQL Queries Saved in Scripts or Files If you have SQL queries stored in .sql or text files that you want to run from MATLAB, you can use the runsqlscript function.
Inserting Data Using the Command Line Inserting Data Using the Command Line You can use datainsert, fastinsert, or insert to insert data using the command line. To understand which function is best for your purposes and setup, see this table.
2 Getting Started with Database Toolbox Working with Large Data Sets In this section...
Working with Large Data Sets Export Large Data Sets from MATLAB When inserting large volumes of data into a database, you might experience slow processing. To achieve the fastest performance, use the appropriate function to insert the data. If you are using native ODBC, use the fastinsert or insert function for fastest processing. If you are using a JDBC driver, use datainsert for the fastest processing to export your data from MATLAB.
2 Getting Started with Database Toolbox Deploying a Database Application with MATLAB Compiler In this section... “Create and Deploy a Database Application” on page 2-200 “About Driver Configurations” on page 2-200 If you want to share your MATLAB code with others in your organization, then you must create a standalone database application using MATLAB Compiler™. After compiling the database application, you can deploy it to the target machines.
Deploying a Database Application with MATLAB Compiler Native ODBC and ODBC Drivers After compiling your database application, you must define the data sources referenced in your code on the target machine using the ODBC Data Source Administrator. Then, you can run your database application on the target machine. JDBC Drivers For applications that use JDBC drivers, use either option to specify the JDBC driver on the target machine: • Use javaaddpath in your function code to add your JDBC driver JAR file.
3 Working with Data Sources • “Setting Up ODBC Data Sources” on page 3-2 • “Setting Up JDBC Data Sources” on page 3-3 • “Accessing Existing JDBC Data Sources” on page 3-4 • “Modifying Existing JDBC Data Sources” on page 3-5 • “Removing JDBC Data Sources” on page 3-6 • “Fetching Data Common Errors” on page 3-7 • “Database Connection Error Messages” on page 3-9 • “Database Explorer Error Messages” on page 3-14 • “Connecting to a Database Using the Native ODBC Interface” on page 3-17
3 Working with Data Sources Setting Up ODBC Data Sources For instructions on setting up ODBC data sources, see “Configuring a Driver and Data Source” on page 2-13.
Setting Up JDBC Data Sources Setting Up JDBC Data Sources For instructions on setting up JDBC data sources, see “Configuring a Driver and Data Source” on page 2-13.
3 Working with Data Sources Accessing Existing JDBC Data Sources To access an existing data source from Visual Query Builder in future MATLAB sessions: 1 In Visual Query Builder, select Query > Define JDBC data source. 2 In the Define JDBC data sources dialog box, click Use Existing File. 3 In the Specify Existing JDBC data source MAT-file dialog box, select the MAT-file that contains the data sources you want to use and click Open.
Modifying Existing JDBC Data Sources Modifying Existing JDBC Data Sources 1 Access the existing data source as described in “Accessing Existing JDBC Data Sources” on page 3-4. 2 Select the data source in the Define JDBC Data Sources dialog box. 3 Modify the data in the Driver and URL fields. 4 Click Add/Update. 5 Click OK to save your changes and close the Define JDBC data sources dialog box.
3 Working with Data Sources Removing JDBC Data Sources 3-6 1 Access the existing data source as described in “Accessing Existing JDBC Data Sources” on page 3-4. 2 Click Remove. 3 Click OK to save your changes and close the Define JDBC data sources dialog box.
Fetching Data Common Errors Fetching Data Common Errors This table describes how to address common errors you might encounter while working with Database Toolbox. These errors might occur in either Database Explorer or the command line when using exec or fetch. Vendor Error Message Probable Causes Resolution Microsoft SQL Server The statement did not return a result set. There are other SQL statements in the middle of the stored procedure.
3 Working with Data Sources Vendor Error Message Probable Causes error happens after executing fetch. This error happens only with the command line. PostgreSQL Java exception The JDBC driver occurred: caches results in the java.lang.OutOfMemoryError: memory. There is Java heap space not enough memory in the Java heap to store the large amount of data fetched from your database. This error happens after executing exec but before executing fetch.
Database Connection Error Messages Database Connection Error Messages This table describes how to address common errors you might encounter while connecting to the Database Toolbox using Database Explorer or the command line. Connection Error Messages and Probable Causes Vendor Error Message Probable Causes All Undefined variable • Database Toolbox 'database' or class software is not 'database.ODBCConnection'. installed. Resolution • Ensure that Database Toolbox software is installed.
3 Working with Data Sources Vendor 3-10 Error Message Probable Causes Resolution Microsoft [Microsoft][ODBC Access Microsoft Access Driver] ‘(unknown)’ is not a valid path. make sure that the path name is spelled correctly and that you are connected to the server on which the file resides Error occurs in the Connection Failure dialog box after clicking Connect in the Connect to a Data Source dialog box. Verify the location of the database file.
Database Connection Error Messages Vendor Error Message Microsoft Invalid string or buffer SQL length. Server or Sybase Probable Causes Resolution 64-bit ODBC driver error. Use a JDBC driver or the native ODBC interface instead. Microsoft JDBC Driver Error: The full path to Ensure that the path SQL com.microsoft.sqlserver.jdbc.SQLServerDriver.Driver the JAR file was to the JAR file is not Server Not Found/Loaded. not added to the misspelled. Ensure that javaclasspath.
3 Working with Data Sources Vendor Error Message Probable Causes not have enough rights to access the remote machine. This error also occurs when the database server is not configured to accept Integrated Windows Authentication login credentials. Resolution for appropriate access rights to your machines. Contact your database administrator to see if your database is set up with Windows Authentication. MySQL Access denied for user Incorrect user 'user'@'machinename' name and password combination.
Database Connection Error Messages Vendor Error Message Probable Causes Resolution with Windows authentication. Oracle The Network Adapter could not establish the connection. Either Server or Portnumber is not specified or has an incorrect value. Verify the server name and port number for your Oracle database. Oracle TNS:listener does not currently know of SID given in connect descriptor: Incorrect database name or incorrect URL. The service name for your database is incorrect.
3 Working with Data Sources Database Explorer Error Messages This table describes how to address common errors you might encounter while working with Database Explorer. For details about Database Toolbox connection errors, see “Database Connection Error Messages” on page 3-9.
Database Explorer Error Messages Vendor Error Error Message Location Database Browser pane. Probable Causes Resolution Oracle Error No tables found in occurs this schema Consider inside changing the schema. the Database Browser pane. Database Explorer has a conflict with the Oracle ODBC driver due to issues in the JDBC/ODBC bridge. Switch your database connection to use a JDBC driver. For details, see “Configuring a Driver and Data Source” on page 2-13.
3 Working with Data Sources More About • 3-16 “Working with Database Explorer”
Connecting to a Database Using the Native ODBC Interface Connecting to a Database Using the Native ODBC Interface In this section...
3 Working with Data Sources Note: The native ODBC interface has a default batch size of 100,000 that enables acceptable performance. To override this value, you must use setdbprefs as follows. Set FetchInBatches to yes and set FetchBatchSize to a specific batch size number . setdbprefs('FetchInBatches','yes') setdbprefs('FetchBatchSize','') Use fetch to import all data into the cursor object curs, and store the data in a cell array contained in the cursor object field curs.Data.
Connecting to a Database Using the Native ODBC Interface 'unitCost' 'productDescription' Define the data for the row to insert in the cell array coldata. coldata = {11,800999,1006,9.00,'Toy Car'} coldata = [11] [800999] [1006] [9] 'Toy Car' Insert the data in coldata into the productTable with the defined column names, colnames. insert(conn,'productTable',colnames,coldata); Caution: The Microsoft Access ODBC driver demonstrates unexpected behavior during large inserts.
3 Working with Data Sources Item Native ODBC • Query data (exec) JDBC/ODBC Bridge • Query data (exec) JDBC • Query data (exec) • Import data (fetch) • Import data (fetch) • Import data (fetch) • Run stored • Export data procedure (exec) (insert, fastinsert, • Export data datainsert, (insert, update) fastinsert) • Close connection (close) • Export data (insert, fastinsert, datainsert, update) • Run stored • Run stored procedure (exec, procedure (exec, runstoredprocedure) runstoredprocedure) • Retr
Connecting to a Database Using the Native ODBC Interface Item Native ODBC JDBC/ODBC Bridge JDBC Data type support Long data types are not supported (e.g. LONG, BLOB, etc.) Long data types are supported Long data types are supported Note: For details about the database.ODBCConnection syntax, see the native ODBC interface example in database.
4 Using Database Explorer • “Working with Database Explorer” on page 4-2 • “Configure Data Sources and Connect to Databases” on page 4-6 • “Modify and Delete Database Connections” on page 4-19 • “Refine Results Using Query Criteria and Rules” on page 4-21 • “Generate SQL and MATLAB Code” on page 4-25
4 Using Database Explorer Working with Database Explorer In this section... “Getting Started with Database Explorer” on page 4-2 “Migrate from VQB to Database Explorer” on page 4-2 “Set Database Explorer Preferences” on page 4-3 If you are using Database Explorer for the first time, migrate from Visual Query Builder (VQB) and set Database Explorer preferences. After performing these tasks, you are ready to configure data sources and connect to your database.
Working with Database Explorer • If you use VQB to export data from MATLAB to your database, use the command-line functions datainsert or fastinsert. • If you use VQB to generate reports, use MATLAB reporting and plotting functionality to generate reports. You can also use MATLAB Report Generator™ to generate reports. • If you use VQB to display charts, use the MATLAB plotting tools to generate charts and graphics.
4 Using Database Explorer 2 Specify the Preferences settings that apply to Database Explorer as described in the following table. Preference Allowable Values Description Data Preview size 5–10,000 rows Import batch size 1,000–1,000,000 The number of rows fetched at one time from a database. When importing large amounts of data using Database Explorer, tune rows this value for optimum performance. For details, see “Preference Settings for Large Data Import” on page 5-19.
Working with Database Explorer Select Database Toolbox to manage additional preferences for Database Toolbox. For details, see “Working with Preferences”. Alternatively, use setdbprefs to specify preferences for the retrieved data. 3 Click OK.
4 Using Database Explorer Configure Data Sources and Connect to Databases In this section... “Configure Your Environment” on page 4-6 “Work with Multiple Databases” on page 4-17 To make connections, Database Explorer uses data sources to identify your databases. Configure data sources to start exploring data in your databases. The data source setup differs depending on the database drivers that you are using for connection.
Configure Data Sources and Connect to Databases Tip Some Windows systems support both ODBC and JDBC drivers. On such systems, JDBC drivers generally provide better performance than ODBC drivers because the software does not use the JDBC/ODBC bridge to access databases. Before You Begin Before you can use Database Explorer with the examples, do the following: 1 Set up the data sources that are provided with Database Toolbox.
4 Using Database Explorer Configure ODBC Data Sources When setting up a data source for an ODBC driver, the target database can be on a PC running the Windows operating system. Or, the target database can be on another system to which the PC is networked. These instructions use the Microsoft ODBC Data Source Administrator Version 6.1 for the U.S. English version of Microsoft Access 2010 for Windows systems.
Configure Data Sources and Connect to Databases A list of installed ODBC drivers appears in the Create New Data Source dialog box. 5 Select Microsoft Access Driver (*.mdb, *.accdb) and click Finish.
4 Using Database Explorer The ODBC Microsoft Access Setup dialog box for your driver opens. The dialog box for your driver can differ from the following dialog box.
Configure Data Sources and Connect to Databases 6 Enter dbtoolboxdemo as the data source name and tutorial database as the description. 7 Select the database for this data source to use. For some drivers, you can skip this step. If you are unsure about skipping this step, consult your database administrator. a In the ODBC Microsoft Access Setup dialog box, click Select. b Specify the database that you want to use. For the dbtoolboxdemo data source, select tutorial.mdb.
4 Using Database Explorer 10 Click OK to close the dialog box. Configure JDBC Data Sources 1 Find the name of the JDBC driver file. This file is provided by your database vendor. The name and location of this file differ for each system. If you do not know the name or location of this file, consult your database administrator. Requirement: If you use Visual Query Builder (querybuilder) to access a JDBC data source, before starting Database Explorer for the first time, execute this command .
Configure Data Sources and Connect to Databases loading performance than the dynamic path. To add folders to the static path, create the file javaclasspath.txt, and then restart MATLAB. Create an ASCII file in your preferences folder. Name the file javaclasspath.txt. To view the location of the preferences folder, type: prefdir Each line in the file is the path of a folder or JAR file.
4 Using Database Explorer 6 Use the information in the following table to set up JDBC drivers for Database Explorer. a 4-14 Use the Create a New JDBC data source dialog box. This table describes the fields that you use to define your JDBC data source. For examples of syntax for these fields, see “JDBC Driver Name and Database Connection URL”.
Configure Data Sources and Connect to Databases Field Description Data Source Name The name that you assign to the data source. For some databases, Name must match the name of the database as recognized by the machine that it runs on. Vendor The vendor name for the data source. When using Other as a vendor: • Driver — The JDBC driver name (sometimes referred to as the class that implements the Java SQL driver for your database). • URL — The JDBC URL object, of the form jdbc:subprotocol:subname.
4 Using Database Explorer dialog box to create a MAT-file that saves your specified data source information for future Database Explorer sessions. Navigate to the folder where you want to put the MAT-file, specify a name for it that includes a .mat extension, and click Save. d To test the connection, click Test. If your database requires a user name and password, a dialog box opens prompting you to supply them. Enter values into these fields and click OK.
Configure Data Sources and Connect to Databases For details about potential errors, see “Database Connection Error Messages”. Work with Multiple Databases 1 If you have not defined the OBDC or JDBC connection for your new data source, click Open and select ODBC or JDBC. Complete the fields in the associated dialog box. For details, see “Configure ODBC Data Sources” on page 4-8 or “Configure JDBC Data Sources” on page 4-12. 2 Select Connect > Connect to select your new data source.
4 Using Database Explorer See Also database More About 4-18 • “Choosing Between ODBC and JDBC Drivers” • “Configuring a Driver and Data Source” • “Working with Database Explorer” on page 4-2
Modify and Delete Database Connections Modify and Delete Database Connections In this section... “ODBC Drivers” on page 4-19 “JDBC Drivers” on page 4-19 ODBC Drivers For data sources that you create with ODBC drivers, you can modify the data source using the ODBC Data Source Administrator. For details, see “Configuring a Driver and Data Source” on page 2-13. 1 Click Start. Select Administrative Tools > Data Sources (ODBC). The ODBC Data Source Administrator dialog box opens.
4 Using Database Explorer settings. If you do not want to overwrite the existing data source, enter a new data source name. Click Save. For data sources that you create with JDBC drivers, you can delete the data source using the Database Explorer. 4-20 1 After opening Database Explorer, select New > JDBC. 2 Select the data source name that you want to delete from the drop-down list. Click Delete.
Refine Results Using Query Criteria and Rules Refine Results Using Query Criteria and Rules In this section... “Define Query Criteria to Refine Results” on page 4-21 “Query Rules Using the SQL Criteria Panel” on page 4-22 To define a query without writing SQL code, use the SQL Criteria panel in Database Explorer. Define criteria to build your query within one table or join multiple tables in the database. Build your query using the SQL Criteria panel rules and control options.
4 Using Database Explorer Each row in the SQL Criteria panel has four columns to define your SQL query. Column 1 Column 2 Column 3 Column 4 Column 1 defines the SQL condition type where the supported values are: Column 2 defines the column names for every table that you select in the Database Browser pane.
Refine Results Using Query Criteria and Rules specifying your query criteria. You can add more rows for these options in the SQL Criteria panel by clicking +. You can remove a row by clicking -. • If you select one table in the Database Browser pane, the available options for the first query condition are WHERE and ORDER BY.
4 Using Database Explorer Note: If you click + to add a query condition between two previously entered conditions, the available query options do not always produce semantically correct SQL statements. In this case, ensure that your query options are semantically correct. For best results using the SQL Criteria panel, add and apply your conditions in sequence.
Generate SQL and MATLAB Code Generate SQL and MATLAB Code In this section... “Save Queries as SQL Code” on page 4-25 “Generate MATLAB Code” on page 4-26 Use Database Explorer to generate SQL or MATLAB code. Once you define a SQL query using the SQL Criteria panel, you can generate the SQL code for running a SQL script. You can also generate MATLAB code to automate connecting to the database, running a SQL query, and performing data analysis on the imported data.
4 Using Database Explorer Generate MATLAB Code You can generate MATLAB code to automate accessing data that you display in the Data Preview pane. 4-26 1 Connect to a data source. Use the SQL Criteria panel to create a query and display the results in the Data Preview pane. 2 Select Import > Generate Script to display MATLAB code in the MATLAB Editor.
Generate SQL and MATLAB Code 3 Save the MATLAB code to a file. You can run this code file at the command line to connect to a data source and run a query.
4 Using Database Explorer More About • 4-28 “Working with Database Explorer” on page 4-2
5 Using Visual Query Builder • “Getting Started with Visual Query Builder” on page 5-2 • “Working with Preferences” on page 5-15 • “Preference Settings for Large Data Import” on page 5-19 • “Displaying Query Results” on page 5-23 • “Fine-Tuning Queries Using Advanced Query Options” on page 5-34 • “Retrieving BINARY and OTHER Data Types” on page 5-55 • “Importing and Exporting Boolean Data” on page 5-57 • “Saving Queries in Files” on page 5-61
5 Using Visual Query Builder Getting Started with Visual Query Builder In this section... “What Is Visual Query Builder?” on page 5-2 “Using Queries to Import Data” on page 5-2 “Using Queries to Export Data” on page 5-9 “Clearing Variables from the VQB Data Area” on page 5-14 What Is Visual Query Builder? Visual Query Builder (VQB) is an easy-to-use graphical user interface (GUI) for exchanging data with your database. To start VQB, use querybuilder.
Getting Started with Visual Query Builder To start the Visual Query Builder, type querybuilder at the MATLAB prompt. *Required step 1* Specify Select. 2* Select data source. 12 View query results in table, chart, and report formats. 4* Select 3 Select tables. catalog and schema. 5* Select fields to retrieve. 8 Set preferences for data retrieval. 13 Save, load, and run queries, and generate M-files. 6 Refine query. 7 View SQL statement. 9* Assign variable for results.
5 Using Visual Query Builder • Visual Query Builder (querybuilder) • Database Explorer To create and run a query using Visual Query Builder to import data from a database into the MATLAB workspace: 1 Select data from a database by clicking the Select button under Data operation. The data sources that you defined in “Configuring a Driver and Data Source” on page 2-13 appear. 2 Select dbtoolboxdemo as the data source from which to import data.
Getting Started with Visual Query Builder 3 Accept the default values for the Catalog and Schema fields. Setting these fields to the default values indicates that you have not specified a catalog or schema. Tip To populate the VQB Schema and Catalog fields, you must associate your user name with schemas or catalogs before starting VQB. • To specify a Catalog, select one from the list, and then select a schema from within that catalog. The Schema field updates to reflect your selections.
5 Using Visual Query Builder • Alternatively, you can select a schema without specifying a catalog; that is, when the Catalog field set to . The Tables field updates to reflect the schema you selected. 4 In the Tables list, select salesVolume as the table that contains the data you want to import. The set of Fields (column names) in the table appears. 5 In the Fields list, select StockNumber, January, February, and March as the fields that contain the data you want to import.
Getting Started with Visual Query Builder You supply input to these fields. MATLAB displays output in this field. 8 Double-click A in the Data area. The contents of A appear in the Variables editor, where you can view and edit the data. In this example, sales for item 400876 are 3000 in January, 2400 in February, and 1500 in March. For details about using the Variables editor, see “View, Edit, and Copy Variables”.
5 Using Visual Query Builder Alternatively, you can view the contents of A by entering A in the Command Window.
Getting Started with Visual Query Builder Using Queries to Export Data The following steps summarize how to use VQB to export data.
5 Using Visual Query Builder To start the Visual Query Builder, type querybuilder at the MATLAB prompt. *Required step 1* Specify Insert. 2* Select data source. 3 Select 4* Select catalog tables. and schema. 5* Select fields to which to export data. 9 Save, load, and run queries, set preferences for exporting NULLs, and generate M-files. 7 View MATLAB statement. 6* Specify variable containing data to export. 8* Run query.
Getting Started with Visual Query Builder 1 Select Data Operation > Insert to select data to export. 2 Select dbtoolboxdemo as the data source to which to export data from the Data source list box. The Catalog, Schema, and Tables fields for dbtoolboxdemo appear. 3 Do not specify values for Catalog and Schema. 4 In the Tables list box, select inventoryTable as the table to which you want to export data from the MATLAB software. The set of Fields (column names) in your selected table appears.
5 Using Visual Query Builder VQB adds each field you select to the query in the MATLAB command field. 6 In the MATLAB workspace, assign the data you want to export to a cell array, export_data. export_data = {14,1500,18.50}; 7 In the MATLAB workspace variable field, enter the name of the variable containing data to export, export_data. Press Enter or Return to view the MATLAB command that exports the data. 8 Click Execute to run the query to export the data.
Getting Started with Visual Query Builder 9 View the inventoryTable table in the Microsoft Access database to verify the query results.
5 Using Visual Query Builder 10 To save this query, select Query > Save and name it export.qry. Clearing Variables from the VQB Data Area Variables in the Data area include those you assigned in the Command Window and those that contain query results. The variables do not appear in the Data area until you execute a query. They then remain in the Data area until you clear them. To clear the variables, run the clear function in the Command Window.
Working with Preferences Working with Preferences Database Toolbox preferences enable you to specify: • How NULL data in a database is represented after you import it into the MATLAB workspace • The format of data retrieved from databases • The method of error notification • The preference for fetching in batches 1 From Visual Query Builder, select Query > Preferences. The Preferences dialog box appears. Alternatively, from the MATLAB Toolstrip, click Preferences and select Database Toolbox.
5 Using Visual Query Builder 2 Specify the Preferences settings as described in this table. Preference Acceptable Values Description Read NULL strings as: null (default) Specifies how NULL strings appear after being fetched from a database. Read NULL Nan (default) Specifies how NULL numbers appear after being fetched from a database.
Working with Preferences Preference numbers as: Acceptable Values Description as NaN. Setting this field to 0 causes NULL data imported into the MATLAB workspace to appear as 0s. Write NULL strings as: null (default) Specifies how NULL strings appear after being exported to a database. This setting does not apply to Database Explorer. Write NULL numbers as: Nan (default) Specifies how NULL numbers appear after being exported to a database. This setting does not apply to Database Explorer.
5 Using Visual Query Builder Preference Acceptable Values Description Cursor Fetch Fetch In Batches and Batch Size Specifies if fetch retrieves data in batches with a user-defined Batch Size. The default Batch Size is 1,000. For details, see “Preference Settings for Large Data Import” on page 5-19. This setting does not apply to Database Explorer. If you are using Database Explorer, the import batch size is specified using Preferences on the Database Explorer Toolstrip. 3 5-18 Click OK.
Preference Settings for Large Data Import Preference Settings for Large Data Import In this section... “Will All Data (Size n) Fit in a MATLAB Variable?” on page 5-20 “Will All of This Data Fit in the JVM Heap?” on page 5-20 “How Do I Perform Batching?” on page 5-21 When using the setdbprefs to set 'FetchInBatches' and 'FetchBatchSize' or the Cursor Fetch option for the Preference dialog box, use the following guidelines to determine what batch size value to use.
5 Using Visual Query Builder results = runsqlscript(conn,'filename.sql','rowInc','m') • If you are using the native ODBC interface to import large amounts of data, you do not need to change these settings because the native ODBC interface always fetches data in batches of 100,000 rows. You can still override the default batch size by setting 'FetchInBatches' to 'yes' and 'FetchBatchSize' to a number of your choice.
Preference Settings for Large Data Import The value of your JVM heap can be determined by selecting MATLAB Preferences and General > Java Heap Memory. You can increase this value to an allowable size, but keep in mind that increasing JVM heap reduces the total memory available to MATLAB arrays. Instead, consider fetching data in small batches to keep a low to medium value for heap memory.
5 Using Visual Query Builder Method 2 — Data Does Fit In MATLAB Variable But Not in JVM Heap If your data (n) does fit in a MATLAB variable but not in a JVM heap, you need to find h such that h < n. To use automated batching to fetch where h rows fit in the JVM heap: setdbprefs('FetchInBatches','yes') setdbprefs('FetchBatchSize','h') If using exec, fetch, and the connection object conn: curs = exec(conn,'Select….'); curs = fetch(curs); If using runsqlscript to run a query from an SQL file 'filename.
Displaying Query Results Displaying Query Results In this section... “How to Display Query Results” on page 5-23 “Displaying Data Relationally” on page 5-23 “Charting Query Results” on page 5-27 “Displaying Query Results in an HTML Report” on page 5-29 “Displaying Query Results with MATLAB Report Generator” on page 5-29 How to Display Query Results To display query results, perform one of the following actions: • Enter the variable name to which to assign the query results in the Command Window.
5 Using Visual Query Builder This display shows only unique values for each field, so you should not read each row as a single record. In this example, there are 10 entries for StockNumber, eight entries for January and February, and 10 entries for March. The number of entries in each field corresponds to the number of unique values in the field. 3 Click a value in the figure window, for example, StockNumber 400876, to see its associated values.
Displaying Query Results 4 As another example, click 3000 under January. It shows three different items with sales of 3000 units in January: 400314, 400876, and 400999.
5 Using Visual Query Builder 5-26
Displaying Query Results Charting Query Results To chart the results of basic.qry: 1 Select Display > Chart. The Visual Query Builder Charting dialog box appears. 2 Select a type of chart from the Charts list. In this example, choose a pie chart by specifying pie. A preview of the pie chart, with each stock item displayed in a different color, appears at the bottom of the dialog box. 3 Select the data to display in the chart from the X data, Y data, and Z data list boxes.
5 Using Visual Query Builder The pie chart preview now shows percentages for March data. 4 To display a legend, which maps colors to the stock numbers, select the Show legend check box. The Legend labels field becomes active. 5 Select StockNumber from the Legend labels list box. A legend appears in the chart preview. Drag and move the legend in the preview as needed. 6 5-28 Click Close to close the Charting dialog box.
Displaying Query Results Displaying Query Results in an HTML Report To display results for basic.qry in an HTML report, select Display > Report. The query results appear as a table in a Web browser. Each row represents a record from the database. In this example, sales for item 400876 are 3000 in January, 2400 in February, and 1500 in March. Tip Because some browsers do not start automatically, you may need to open your Web browser before displaying the query results.
5 Using Visual Query Builder 5-30 1 Select Display > Report Generator. 2 The Report Explorer opens, listing sample report templates that you can use to create custom reports. Select the template matlabroot/toolbox/database/vqb/ databasetlbx.rpt from the Options pane in the middle of the Report Explorer window. 3 Open the report template for editing by clicking Open a Report file or stylesheet.
Displaying Query Results a In the Outline pane on the left, under Report Generator > databasetlbx.rpt, select Table. b In the Properties pane on the right, do the following: c 4 i In Table Content > Workspace Variable Name, enter the name of the variable to which you assigned the query results in VQB, for example, 'A'. ii Under Header/Footer Options, set Number of header rows to 0. Click Apply. Select File > Report to run the report. The report appears in a Web browser.
5 Using Visual Query Builder a Modify the workspace variable A as follows: A = [{'Stock Number', 'January', 'February', 'March'};A] b In the MATLAB Report Generator Properties pane, change Number of header rows to 1 and regenerate the report. The report now displays field names as headings. Each row represents a record from the database. For example, sales for item 400876 are 3000 in January, 2400 in February, and 1500 in March.
Displaying Query Results Tip Because some browsers are not configured to launch automatically, you may need to open your Web browser before displaying the report.
5 Using Visual Query Builder Fine-Tuning Queries Using Advanced Query Options In this section... “Retrieving All Occurrences vs.
Fine-Tuning Queries Using Advanced Query Options 8 9 To retrieve only unique occurrences of data: a In Advanced query options, select Distinct. b Assign the query results to a MATLAB workspace variable Distinct. c Click Execute to run the query. In the Command Window, enter All, Distinct to display the query results: The value 3000 appears three times in All, but appears only once in Distinct. Retrieving Data That Meets Specified Criteria Use basic.
5 Using Visual Query Builder 2 Set the Data return format preference to cellarray. 3 Set Read NULL numbers as to NaN. 4 In Advanced query options, click Where. The WHERE Clauses dialog box appears. 5 In Fields, select the field whose values you want to restrict, StockNumber. 6 In Condition, specify that StockNumber must be greater than 400000. a Select Relation. b In the drop-down list to the right of Relation, select >. c In the field to the right of the drop-down list, enter 400000.
Fine-Tuning Queries Using Advanced Query Options d Click Apply. The clause that you defined, StockNumber > 400000, appears in the Current clauses area. 7 Add the condition that StockNumber must also be less than 500000. a In Current clauses, select StockNumber > 400000. b In Current clauses, click Edit or double-click the StockNumber entry. c For Operator, select AND. d Click Apply.
5 Using Visual Query Builder The Current clauses field now displays: StockNumber > 400000 AND e In Fields, select StockNumber. f In Condition, select Relation. g In the drop-down list to the right of Relation, select <. h In the field to the right of the drop-down list, enter 500000. i Click Apply. The Current clauses field now displays: StockNumber > 400000 AND StockNumber < 500000 8 Click OK. The WHERE Clauses dialog box closes.
Fine-Tuning Queries Using Advanced Query Options To modify basic_where.qry: 1 Click Where in VQB. The WHERE Clauses dialog box appears. 2 Modify the query to retrieve data if sales in January, February, or March exceed 1500 units. a In Current clauses, select StockNumber < 500000 and click Edit. b For Operator, select OR and click Apply. c In Fields, select January. For Relation, select > and enter 1500 in its field. For Operator, select OR. Click Apply.
5 Using Visual Query Builder 3 Group the criteria that require sales in each month to exceed 1500 units. a In Current clauses, select the statement January > 1500 OR. Press Shift+click to select February > 1500 OR and March > 1500 also. b Click Group. An opening parenthesis is added before January and a closing parenthesis is added after March > 1500, indicating that these statements are evaluated as a group. 4 5-40 Modify the query to retrieve data if sales in each month exceed 1000 units.
Fine-Tuning Queries Using Advanced Query Options a Select March > 1500 ) in Current clauses and click Edit. b Select AND for Operator and click Apply. c Select January in Fields. Select > for Relation and enter 1000 in its field. Select AND for Operator. Click Apply. d Repeat step c twice, specifying February and March in Fields. The WHERE Clauses dialog box now looks as follows. e Click OK. The WHERE Clauses dialog box closes. The SQL statement dialog box displays the modified where clause.
5 Using Visual Query Builder Removing Grouping of Statements To use the WHERE Clauses dialog box to remove grouping criteria from the previous example: 1 In Current clauses, select (January > 1000 AND. 2 Press Shift+click to select February > 1000 AND and March > 1000) also. 3 Click Ungroup. The parentheses are removed from the statements, indicating that their grouping is removed.
Fine-Tuning Queries Using Advanced Query Options 4 In Advanced query options, select Order by. The ORDER BY Clauses dialog box appears. 5 Enter values for the Sort key number and Sort order fields for the appropriate Fields. To specify January as the primary sort field and display results in ascending order: a In Fields, select January. b For Sort key number, enter 1. c For Sort order, select Ascending. d Click Apply.
5 Using Visual Query Builder January ASC February ASC 7 To specify March as the third sort field and display results in descending order: a In Fields, select March. b For Sort key number, enter 3. c For Sort order, select Descending. d Click Apply. The Current clauses area now displays: January ASC February ASC March DESC 8 Click OK. The ORDER BY Clauses dialog box closes. The Order by field and the SQL statement in VQB display the specified Order By clause.
Fine-Tuning Queries Using Advanced Query Options For B, results are first sorted by January sales in ascending order. The lowest value for January sales, 1200 (for item number 400455), appears first. The highest value, 5000 (for item number for 400345), appears last. For items 400999, 400314, and 400876, January sales were 3000. Therefore, the second sort key February salesapplies. February sales appear in ascending order: 1500, 2400, and 2400 respectively.
5 Using Visual Query Builder 1 From the Fields list box, select the entry whose value to restrict. 2 Define the Condition for the selected field, as described in “Retrieving Data That Meets Specified Criteria” on page 5-35. 3 Select Operator to add another condition. 4 Click Apply to create the clause. The subquery appears in the Current clauses area. 5 Repeat steps 1 through 4 to add more conditions as needed.
Fine-Tuning Queries Using Advanced Query Options • To delete a clause, Select the clause from Current clauses and click Delete. Use Ctrl+click or Shift+click to select multiple clauses. 7 Specify a subquery in the HAVING Clauses dialog box, as needed. For details, see “Creating Subqueries for Values from Multiple Tables” on page 5-48. 8 Click OK. The HAVING Clauses dialog box closes. The SQL statement in the Visual Query Builder dialog box updates to reflect the specified having clause.
5 Using Visual Query Builder 4 Click OK. The HAVING Clauses dialog box closes. The SQL statement field in the VQB dialog box reflects the specified Having clause. 5 Assign a MATLAB workspace variable C, and click Execute to run the query. C = [3000] [5000] [2400] [3500] Compare these results to those in “Displaying Results in a Specified Order” on page 5-42. Creating Subqueries for Values from Multiple Tables Use the Where feature in Advanced query options to create subqueries.
Fine-Tuning Queries Using Advanced Query Options 1 Load basic.qry. 2 Set the Data return format Preference to cellarray and Read NULL numbers as to NaN. 3 Click Where in Advanced query options. The WHERE Clauses dialog box appears. 4 Click Subquery. The Subquery dialog box appears. 5 In Tables, select productTable, which includes the association between the stock number and the product description. The fields in that table appear.
5 Using Visual Query Builder The statement SELECT stockNumber FROM productTable is created in the SQL subquery statement. 7 Limit the query to product descriptions that are Building Blocks. a In Fields in Subquery WHERE clauses, select productDescription. b For Condition, select Relation. c In the drop-down list to the right of Relation, select =. d In the field to the right of the drop-down list, enter 'Building Blocks'. e Click Apply.
Fine-Tuning Queries Using Advanced Query Options This updates the Current clauses area using the subquery criteria specified in steps 3 through 8. 10 In the WHERE Clauses dialog box, click OK. The WHERE Clauses dialog box closes. The SQL statement in the VQB dialog box updates. 11 Assign the query results to the MATLAB workspace variable C. 12 Click Execute. 13 Type C at the prompt in the Command Window to see the results.
5 Using Visual Query Builder e Click Execute. f Type P at the prompt in the Command Window to view the results. The results show that item 400345 has the product description Building Blocks. In the next section, you create a query that includes product description in the results. Note: You can include only one subquery in a query using VQB; you can include multiple subqueries using Database Toolbox functions.
Fine-Tuning Queries Using Advanced Query Options 1 Set the Data return format preference to cellarray and the Read NULL numbers as preference to NaN. 2 For Data operation, click Select. 3 For Data source, select dbtoolboxdemo. The Catalog, Schema, and Tables for dbtoolboxdemo appear. Do not specify Catalog or Schema. 4 In Tables, select the tables from which you want to retrieve data. For this example, press Ctrl+click and select both productTable and salesVolume.
5 Using Visual Query Builder f Click OK to close the WHERE Clauses dialog box. The Where field and SQL statement in VQB display the Where clause. 8 Assign the query results to the MATLAB workspace variable P1. 9 Click Execute to run the query. 10 Type P1 in the Command Window.
Retrieving BINARY and OTHER Data Types Retrieving BINARY and OTHER Data Types This example shows how to retrieve data of types BINARY and OTHER, which may require manipulation before it can undergo MATLAB processing. To retrieve images using the dbtoolboxdemo data source and a sample file that parses image data, matlabroot/ toolbox/database/vqb/parsebinary.m: 1 For Data Operation, select Select. 2 In Data source, select dbtoolboxdemo. 3 In Tables, select Invoive.
5 Using Visual Query Builder For details about parsebinary, enter help parsebinary, or view the parsebinary file in the MATLAB Editor/Debugger by entering open parsebinary in the Command Window.
Importing and Exporting Boolean Data Importing and Exporting Boolean Data In this section... “Import Boolean Data from Databases” on page 5-57 “Exporting Boolean Data to Databases” on page 5-60 Import Boolean Data from Databases BOOLEAN data is imported from databases into the MATLAB workspace as data type logical. This data has a value of 0 (false) or 1 (true), and is stored in a cell array or structure.
5 Using Visual Query Builder [61178] [62145] 9 [0] [1] Compare these results to the data in Microsoft Access. 10 In the VQB Data area, double-click D to view its contents in the Variables editor.
Importing and Exporting Boolean Data 5-59
5 Using Visual Query Builder Exporting Boolean Data to Databases Logical data is exported from the MATLAB workspace to a database as type BOOLEAN. This example adds two rows of data to the Invoice table in the dbtoolboxdemo database. 1 In the MATLAB workspace, create I, the structure you want to export. I.InvoiceNumber{1,1}=456789; I.Paid{1,1}=logical(0); I.InvoiceNumber{2,1}=987654; I.Paid{2,1}=logical(1); 2 For Data operation, choose Insert. 3 In Data source, select dbtoolboxdemo.
Saving Queries in Files Saving Queries in Files In this section... “About Generated Files” on page 5-61 “VQB Query Elements in Generated Files” on page 5-62 “Saving Queries” on page 5-62 “Running Saved Queries” on page 5-62 “Editing Queries” on page 5-63 About Generated Files Select Query > Generate MATLAB File to create a file that contains the equivalent Database Toolbox functions required to run an existing query that was created in VQB.
5 Using Visual Query Builder VQB Query Elements in Generated Files The following VQB query elements do not appear in generated files: • Generated code files do not include MATLAB workspace variables to which you assigned query results in the VQB query. The file assigns the query results to e; access these results using the variable e.Data. For example, you can add a statement to the file that assigns a variable name to e.Data as follows: myVar = e.
Saving Queries in Files • To run a Select query, use the MATLAB workspace variable field to assign a variable to the data and click Execute. • For an Insert query, the saved query may include a workspace variable, which appears as part of the MATLAB command field. Type that variable name or enter a new name in the MATLAB workspace variable field. Press Return or Enter to see the updated MATLAB command. 4 Click Execute to run the query.
6 Using Database Toolbox Functions • “Getting Started with Database Toolbox Functions” on page 6-3 • “Import Data from Databases into MATLAB” on page 6-4 • “Create a Query Using a Date” on page 6-8 • “Create a Query Using a String” on page 6-10 • “Create a Query Using a MATLAB Variable” on page 6-12 • “Create a Query Using Special Characters” on page 6-14 • “Delete Data from Databases” on page 6-16 • “Exporting Data to New Record in Database” on page 6-18 • “Replacing Existing Database Data with Exported Da
6 Using Database Toolbox Functions • “Retrieving Object Properties Using the get Function” on page 6-70 • “Setting Database Preferences Using the setdbprefs Function” on page 6-75 • “Working with a DatabaseDatastore” on page 6-79 • “Import Data Using a DatabaseDatastore” on page 6-81 • “Analyze Large Data Sets in a Database with MapReduce” on page 6-85 6-2
Getting Started with Database Toolbox Functions Getting Started with Database Toolbox Functions The following sections provide examples of how to use Database Toolbox functions. MATLAB files that include functions used in some of these examples are available in matlab/toolbox/database/dbdemos. Follow these simple examples consecutively when you first start using the product. Once you are familiar with Database Toolbox usage, refer to these examples as needed.
6 Using Database Toolbox Functions Import Data from Databases into MATLAB This example shows how to import data from a Microsoft Access database called dbtoolboxdemo into the MATLAB workspace. Connect to the Database Connect to the Microsoft Access database with the data source name dbtoolboxdemo using native ODBC. conn = database.ODBCConnection('dbtoolboxdemo','',''); If you are connecting to a database using a JDBC connection, then specify a different syntax for the database function.
Import Data from Databases into MATLAB [ 3] [10] 'Slinky' 'Teddy Bear' Close the cursor object and database connection. close(curs) close(conn) Import Data Using Multiple Joins in the SQL Query Connect to the Microsoft Access database with the data source name dbtoolboxdemo using the JDBC/ODBC bridge. conn = database('dbtoolboxdemo','',''); Create an SQL script file named salesvolume.sql with this SQL query.
6 Using Database Toolbox Functions Run the SQL script file named salesvolume.sql using the runsqlscript function. results = runsqlscript(conn,'salesvolume.sql') results = Attributes: Data: DatabaseObject: RowLimit: SQLQuery: Message: Type: ResultSet: Cursor: Statement: Fetch: [] {6x13 cell} [1x1 database] 0 'SELECT salesvolume.January , salesvolume.February , '' 'Database Cursor Object' [1x1 sun.jdbc.odbc.JdbcOdbcResultSet] [1x1 com.mathworks.toolbox.database.sqlExec] [1x1 sun.jdbc.odbc.
Import Data from Databases into MATLAB close(conn) See Also close | database | exec | fetch | runsqlscript | setdbprefs More About • “Connecting to a Database Using the Native ODBC Interface” on page 3-17 6-7
6 Using Database Toolbox Functions Create a Query Using a Date This example shows how to format a date in an SQL query. When you want to write an SQL statement that selects data from your database using a date, you need to format the date according to your database specifications. Consult your database documentation for the right formatting. This example shows date formatting for an Oracle database. Connect to the Database Connect to Oracle using native ODBC.
Create a Query Using a Date The query returns the records where the date in the column test_dt is after June 9, 2013.
6 Using Database Toolbox Functions Create a Query Using a String This example shows how to include a string in your SQL query using a Microsoft Access database. Connect to the Database Connect to Microsoft Access using native ODBC. For example, the following code assumes you are connecting to a data source named dbtoolboxdemo with a blank user name and password. conn = database.
Create a Query Using a String close(conn) See Also close | database | exec | fetch More About • “Connecting to a Database Using the Native ODBC Interface” 6-11
6 Using Database Toolbox Functions Create a Query Using a MATLAB Variable This example shows how to include a MATLAB variable in your SQL query. This example uses a Microsoft SQL Server database. Connect to the Database Connect to the Microsoft SQL Server database using a JDBC driver without operating system authentication. For example, this code assumes you are connecting to a database named dbname with the user name username, password pwd, database server name sname, and port number 123456.
Create a Query Using a MATLAB Variable [2101.00] '2010-08-01 00:00...' [1.00] [0] [1948410x1 int8] The fetch function returns the invoice data record for the first product.
6 Using Database Toolbox Functions Create a Query Using Special Characters This example shows how to write an SQL query for table names or columns names with special characters. These characters require using escape characters that are specific to your database. Consult your database documentation for the right escape characters. This example uses a Microsoft SQL Server database.
Create a Query Using Special Characters 'some text' Close the Cursor and Database Connection close(curs) close(conn) See Also close | database | exec | fetch 6-15
6 Using Database Toolbox Functions Delete Data from Databases This example shows how to delete data from your database using MATLAB. Create the SQL string with your deletion SQL statement. Consult your database documentation for the correct SQL syntax. Execute the delete operation on your database using exec with your SQL string. This example demonstrates deleting data records in a Microsoft Access database.
Delete Data from Databases ... [10] [11] [12] [ 723] [ 567] [1278] [ [ [ 24] 0] 0] '2012-03-14 13:13...' '2012-09-11 00:30...' '2010-10-29 18:17...' The record with product number 13 is missing. Delete a Record Using a MATLAB Variable Define a MATLAB variable productID by setting it to the product number 12. productID = 12; Delete the data using the MATLAB variable productID. Build an SQL statement string that combines the SQL for the delete operation with the MATLAB variable.
6 Using Database Toolbox Functions Exporting Data to New Record in Database This example does the following: 1 Retrieves sales data from a salesVolume table. 2 Calculates the sum of sales for 1 month. 3 Stores this data in a cell array. 4 Exports this data to a yearlySales table. You learn to use the following Database Toolbox functions: • get • fastinsert • setdbprefs For details about these functions, see matlab\toolbox\database\dbdemos \dbinsertdemo.m.
Exporting Data to New Record in Database 2800 800 1500 1000 821 5 Calculate the sum of the March sales and assign the result to the variable sumA: sumA = sum(AA(:)) sumA = 14606 6 Assign the month and sum of sales to a cell array to export to a database. Put the month in the first cell of exdata: exdata(1,1) = {'March'} exdata = 'March' Put the sum in the second cell of exdata: exdata(1,2) = {sumA} exdata = 'March' [14606] 7 Define the names of the columns to which to export data.
6 Using Database Toolbox Functions • conn, the connection object for the database • yearlySales, the name of the table to which you are exporting data • The cell arrays colnames and exdata fastinsert(conn, 'yearlySales', colnames, exdata) fastinsert appends the data as a new record at the end of the yearlySales table. 10 In Microsoft Access, view the yearlySales table to verify the results. 11 Close the cursor.
Replacing Existing Database Data with Exported Data Replacing Existing Database Data with Exported Data This example updates the Month field that you previously imported (“Exporting Data to New Record in Database” on page 6-18) into the yearlySales table of the dbtoolboxdemo data source using the following Database Toolbox functions: • close • update For details about these functions, see matlab\toolbox\database\dbdemos \dbupdatedemo.m. 1 Change the month in yearlySales table from March to March2010.
6 Using Database Toolbox Functions Exporting Multiple Records from the MATLAB Workspace This example does the following: 1 Imports monthly sales figures for all products from the dbtoolboxdemo data source into the MATLAB workspace. 2 Computes total sales for each month. 3 Exports the totals to a new table. You use the following Database Toolbox functions: • fastinsert • setdbprefs For details about these functions, see matlab\toolbox\database\dbdemos \dbinsert2demo.m.
Exporting Multiple Records from the MATLAB Workspace curs = fetch(curs); 5 Use columnnames to view the column names in the fetched data set: columnnames(curs) ans = 'StockNumber', 'January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December' 6 View the data for January (column 2). curs.Data(:,2) ans = 1400 2400 1800 3000 4300 5000 1200 3000 3000 0 7 Assign the dimensions of the matrix containing the fetched data set to m and n.
6 Using Database Toolbox Functions 25100 15621 14606 11944 9965 8643 6525 5899 8632 13170 48345 172000 9 Create a string array containing the column names into which you want to insert the data, and assign the array to the variable colnames.
Exporting Multiple Records from the MATLAB Workspace 12 Close the cursor and the database connection.
6 Using Database Toolbox Functions Exporting Data Using Bulk Insert In this section... “About Bulk Insert Functionality” on page 6-26 “Bulk Insert into Oracle” on page 6-26 “Bulk Insert into Microsoft SQL Server 2005” on page 6-28 “Bulk Insert into MySQL” on page 6-30 About Bulk Insert Functionality Many ways exist to insert data into your database using the command line. You can use datainsert, fastinsert, or insert. For best performance with large volumes of data, use datainsert or fastinsert.
Exporting Data Using Bulk Insert 3 Enter data records. A sample record appears as follows. A = {100000.00,'KGreen','06/22/2011','Challengers'}; 4 Expand A to a 10,000-record data set. A = A(ones(10000,1),:); 5 Write data to a file for bulk insert. Tip When connecting to a database on a remote machine, you must write this file to the remote machine. Oracle has problems trying to read files that are not on the same machine as the instance of the database. fid = fopen('c:\temp\tmp.
6 Using Database Toolbox Functions results = Attributes: Data: DatabaseObject: RowLimit: SQLQuery: Message: Type: ResultSet: Cursor: Statement: Fetch: [] {10000x4 cell} [1x1 database] 0 'select * from BULKTEST' [] 'Database Cursor Object' [1x1 oracle.jdbc.driver.OracleResultSetImpl] [1x1 com.mathworks.toolbox.database.sqlExec] [1x1 oracle.jdbc.driver.OracleStatementWrapper] [1x1 com.mathworks.toolbox.database.
Exporting Data Using Bulk Insert Tip When connecting to a database on a remote machine, you must write this file to the remote machine. Microsoft SQL Server has problems trying to read files that are not on the same machine as the instance of the database. fid = fopen('c:\temp\tmp.txt','wt'); for i = 1:size(A,1) fprintf(fid,'%10.2f \t %s \t %s \t %s \n',A{i,1},... A{i,2},A{i,3},A{i,4}); end fclose(fid); 6 Run the bulk insert. e = exec(conn,['bulk insert BULKTEST from '... '''c:\temp\tmp.
6 Using Database Toolbox Functions close(conn) Bulk Insert into MySQL 1 Connect to the MySQL server. For JDBC driver use, add the JAR file to the MATLAB Java class path. javaaddpath 'path\mysql-connector-java-5.1.13-bin.jar'; conn = database('databasename', 'user', 'password',... 'com.mysql.jdbc.Driver',... 'jdbc:mysql://machine:port/databasename'); 2 Create a table named BULKTEST. e = exec(conn,['create table BULKTEST (salary decimal, '... 'player varchar(25), signed_date varchar(25), '...
Exporting Data Using Bulk Insert 7 Confirm the number of rows and columns in BULKTEST. e = exec(conn, 'select * from BULKTEST'); results = fetch(e) results = Attributes: Data: DatabaseObject: RowLimit: SQLQuery: Message: Type: ResultSet: Cursor: Statement: Fetch: [] {10000x4 cell} [1x1 database] 0 'select * from BULKTEST' [] 'Database Cursor Object' [1x1 com.mysql.jdbc.JDBC4ResultSet] [1x1 com.mathworks.toolbox.database.sqlExec] [1x1 com.mysql.jdbc.StatementImpl] [1x1 com.mathworks.toolbox.database.
6 Using Database Toolbox Functions Retrieve Image Data Types This example retrieves images from the dbtoolboxdemo data source using a sample file that parses image data, matlabroot/toolbox/database/vqb/parsebinary.m. 1 Connect to the dbtoolboxdemo data source. conn = database('dbtoolboxdemo','',''); Alternatively, you can use the native ODBC interface for an ODBC connection. For details, see database. 2 Specify cellarray as the data return format preference.
Retrieve Image Data Types receipt = curs.Data{1,2}; 6 Run parsebinary. This program writes the retrieved data to a file, strips ODBC header information from it, and displays receipt as a bitmap image in a figure window. Ensure that your current folder is writable so that the output of parsebinary can be written to it. cd 'I:\MATLABFiles\myfiles' parsebinary(receipt,'BMP'); For details about parsebinary, enter help parsebinary or view its file in the MATLAB Editor/Debugger by entering open parsebinary.
6 Using Database Toolbox Functions Display Database Metadata This example shows how to display database information for database connection objects using the command line. To view the database structure quickly, use Database Explorer to explore the tables and column names. Here, metadata refers to the information about the database structure and various database properties. Create the Database Connection Create a connection conn using the dbtoolboxdemo data source.
Display Database Metadata props = AlterTableWithAddColumn: 1 AlterTableWithDropColumn: 1 ANSI92EntryLevelSQL: 1 ... A 1 for a given property indicates that the database supports that property. 0 means that the database does not support the property. For details about properties that the database supports, see the methods of the DatabaseMetaData object on the Oracle Java website: http://docs.oracle.com/ javase/7/docs/api/java/sql/DatabaseMetaData.html.
6 Using Database Toolbox Functions More About • 6-36 “Working with Database Explorer”
Using Driver Functions Using Driver Functions This example uses the following Database Toolbox functions to create driver and drivermanager objects, and to get and set their properties: • driver • drivermanager • get • isdriver • set Note There is no equivalent MATLAB example available because this example relies on a specific system-to-JDBC connection and database. Your configuration is different from the one in this example, so you cannot run these examples exactly as written.
6 Using Database Toolbox Functions This result shows that d is a valid JDBC driver object. If it is a not valid JDBC driver object, the returned result is 0. 5 Use the drivermanager function to create a drivermanager object dm. dm = drivermanager 6 Get properties of the drivermanager object. v = get(dm) v = Drivers: {'sun.jdbc.odbc.JdbcOdbcDriver@761630'... [1x38 char]} LoginTimeout: 0 LogStream: [] 7 Set the LoginTimeout value to 10 for all drivers loaded during this session.
About Database Toolbox Objects and Methods About Database Toolbox Objects and Methods This toolbox is an object-oriented application. You do not need to be familiar with the product's object-oriented implementation to use it; this information is provided for reference purposes.
6 Using Database Toolbox Functions Selecting Data Using the exec Function In this section... “About the exec Function” on page 6-40 “Using Cursor Objects” on page 6-40 “Working with Microsoft Excel” on page 6-41 “Database Considerations” on page 6-41 About the exec Function Use the exec function to execute an SQL statement and return the database cursor object. Here are some general points about using exec: • Use Database Explorer to query databases as an alternative to using exec.
Selecting Data Using the exec Function • A cursor stays open until you close it using the close function. Working with Microsoft Excel For Microsoft Excel, tables in sqlquery are Excel worksheets. By default, some worksheet names include $. To select data from a worksheet with this name format, use an SQL statement of the form select * from "Sheet1$" (or 'Sheet1$') . Database Considerations • The order of records in your database is not constant. Use values in column names to identify records.
6 Using Database Toolbox Functions Call a Stored Procedure That Returns Data This example shows how to call a stored procedure that returns data using the exec function. Use the JDBC interface to connect to a Microsoft SQL Server database, call a stored procedure, and return data. For this example, the stored procedure getSupplierInfo is defined in the Microsoft SQL Server database. This stored procedure returns the supplier information for suppliers of a given city. This code defines the procedure.
Call a Stored Procedure That Returns Data Driver: URL: Constructor: Message: Handle: TimeOut: AutoCommit: Type: [] [] [1x1 com.mathworks.toolbox.database.databaseConnect] [] [1x1 com.microsoft.sqlserver.jdbc.SQLServerConnection] 0 'on' 'Database Object' database returns conn, a connection Database Object for the 'test_db' database. Alternatively, you can use the native ODBC interface for an ODBC connection. For details, see database.
6 Using Database Toolbox Functions curs = fetch(curs) curs = Attributes: Data: DatabaseObject: RowLimit: SQLQuery: Message: Type: ResultSet: Cursor: Statement: Fetch: [] [3x5 table] [1x1 database] 0 '{call getSupplierInfo('New York')}' [] 'Database Cursor Object' [1x1 com.microsoft.sqlserver.jdbc.SQLServerResultSet] [1x1 com.mathworks.toolbox.database.sqlExec] [1x1 com.microsoft.sqlserver.jdbc.SQLServerStatement] [1x1 com.mathworks.toolbox.database.
Call a Stored Procedure That Returns Data close(conn) See Also database | exec | fetch | runstoredprocedure 6-45
6 Using Database Toolbox Functions Run a Custom Database Function This example shows how to run a custom database function on Microsoft SQL Server. Consider a database function get_prodCount that retrieves row counts in the table productTable. The table productTable contains 30 rows where each row represents a product. This code defines this database function and assumes a schema name dbo. CREATE FUNCTION dbo.
Run a Custom Database Function Close the Database Connection Close the cursor and database connection.
6 Using Database Toolbox Functions Importing Data Using the fetch Function In this section... “About the fetch Function” on page 6-48 “fetch Workflow” on page 6-48 “Using fetch with a Cursor Object” on page 6-49 “Using fetch with Cursor and Database Connection Objects” on page 6-50 “Database Consideration” on page 6-51 About the fetch Function Use the fetch function to import data into the MATLAB workspace.
Importing Data Using the fetch Function Note: You can pass conn as an input argument to fetch when using an JDBC/ODBC bridge or a JDBC interface. For the native ODBC interface, use curs as the input argument. To create a database connection using the native ODBC interface, use database.ODBCConnection. conn = database.ODBCConnection(...) fetch then runs when you pass a native ODBC cursor object, curs, to retrieve as an argument.
6 Using Database Toolbox Functions fetch(curs) curs2 = fetch(curs) • The next time fetch is run, records are imported starting with the row following the specified number of rows in rowlimit. If you specify a row limit of 0, all the rows of data are fetched. • Fetching large amounts of data can result in memory or speed issues. Use rowlimit to limit how much data you retrieve at once.
Importing Data Using the fetch Function Difference Cursor Object Database Connection Object Fetching in batches You can fetch data in batches by using setdbprefs. This syntax always fetches data in batches despite the value of the database preference property 'FetchInBatches'. This syntax uses the value of the database preference property 'FetchBatchSize' as the default number of records to fetch in a single batch. Batch size You can change the batch size by using setdbprefs.
6 Using Database Toolbox Functions Fetch Data Incrementally Using the Cursor Object This example shows how to work with large data sets by retrieving data incrementally to avoid Java heap errors. Create the Database Connection Create a connection conn using the native ODBC interface and the dbtoolboxdemo data source. conn = database.
Fetch Data Incrementally Using the Cursor Object • Size per row being retrieved • Java heap memory value • Driver’s default fetch size • System architecture Hence, the optimal value can vary across sites. If 'FetchInBatches' is set to 'yes' and the total number of rows fetched is less than 'FetchBatchSize', MATLAB shows a warning message and then fetches all the rows. The message is: Batch size specified was larger than the number of rows fetched.
6 Using Database Toolbox Functions 6-54 • “Connecting to a Database Using the Native ODBC Interface” • “Preference Settings for Large Data Import”
Display Information About Imported Data Display Information About Imported Data This example shows how to import data and display information about the imported data using a cursor object. Alternatively, you can retrieve metadata of cursor objects by creating resultset objects using resultset. Display information about resultset objects using rsmd. Here, metadata refers to the information about the cursor object that contains the imported data after running exec.
6 Using Database Toolbox Functions 1 Retrieve the Column Name in the Imported Data Retrieve the column name colname using curs. colname = columnnames(curs) colname = 'productDescription' Retrieve the Column Width in the Imported Data Retrieve the column width colsize, or size of the field, for the first column using curs. colsize = width(curs,1) colsize = 50 Display Attributes in the Imported Data Display the attributes for the product description column using curs.
Display Information About Imported Data close(curs) See Also attr | cols | columnnames | database | fetch | rows | setdbprefs | width 6-57
6 Using Database Toolbox Functions Importing Data Using a Scrollable Cursor In this section... “About Scrollable Cursors” on page 6-58 “Differences Between Native ODBC and JDBC Scrollable Cursors” on page 6-59 About Scrollable Cursors A basic cursor lets you fetch the data in your SQL query sequentially. With a scrollable cursor, you can fetch data sequentially or scroll up or down in the data without rerunning the query. The cursor changes position based on an absolute or relative offset value.
Importing Data Using a Scrollable Cursor position of 5000 after fetching data in Example 5, Example 6 fetches 3000 records using a relative cursor position offset of 7000. A positive position offset moves the scrollable cursor forward in the data set. The fetch function adds 7000 to the current cursor position of 5000 to start fetching data from 12,000. To use a scrollable cursor, first you need to create it by using the exec function.
6 Using Database Toolbox Functions • The fetch Action column describes the rows of data to retrieve based on the specified input arguments. For example, this code demonstrates the syntax for calling fetch shown in the second row of either table.
Importing Data Using a Scrollable Cursor Initial Scrollable Cursor Position Row Limit Scrollable Offset Cursor Position Type Ending fetch Action Scrollable Cursor Position from the end of the data set Before result Not specified set 'relativePosition' 1 After the result set Retrieves all rows in the cursor starting from the first row in the data set Before result Any set 'relativePosition' Any Varies Retrieving with a relative position that starts before the result set causes behavior to vary ba
6 Using Database Toolbox Functions JDBC 6-62 Initial Scrollable Cursor Position Row Limit Scrollable Offset Cursor Position Type Ending fetch Action Scrollable Cursor Position Any Not specified 'absolutePosition' 1 0 Retrieves all rows in the cursor starting from the first row in the data set Any 2 'absolutePosition' 1 2 Retrieves two rows in the cursor starting from the first row in the data set Any 2 'absolutePosition' 5 6 Retrieves two rows in the cursor starting from the fifth row
Importing Data Using a Scrollable Cursor Initial Scrollable Cursor Position Row Limit Scrollable Offset Cursor Position Type Ending fetch Action Scrollable Cursor Position 0 Not specified 'relativePosition' 1 0 Retrieves all rows in the cursor starting from the first row in the data set 0 2 'relativePosition' 1 2 Retrieves the first two rows in the data set 5 2 'relativePosition' 5 11 Retrieves two rows in the data set starting from five rows from the initial position of five, which is ni
6 Using Database Toolbox Functions See Also exec | fetch | get 6-64
Import Data Using a Scrollable Cursor with a Relative Position Offset Import Data Using a Scrollable Cursor with a Relative Position Offset This example shows how to use a scrollable cursor to import data using both absolute and relative position offsets. This example assumes you are connecting to a MySQL database that contains a table called productTable. This table contains 15 records, where each record represents one product.
6 Using Database Toolbox Functions The columns in curs.Data are: • Product number • Stock number • Supplier number • Unit cost • Product description Display the position of the cursor. curs.Position ans = 5 The position of the cursor stays at the absolute position offset 5. Retrieve Data Using a Relative Position Offset Import the data for three products in the data set using the relative position offset 5.
Import Data Using a Scrollable Cursor with a Relative Position Offset Close the Cursor Object After finishing with the cursor object, close it.
6 Using Database Toolbox Functions Inserting Data Using the fastinsert Function In this section... “About the fastinsert Function” on page 6-68 “Database Considerations” on page 6-69 About the fastinsert Function • When working with a JDBC driver connection or a JDBC/ODBC bridge connection established using the database function, fastinsert offers improved performance over insert. insert creates and executes an SQL insert query for each row of data.
Inserting Data Using the fastinsert Function • Use update to replace existing data in a database. Database Considerations • The order of records in your database is not constant. Use values in column names to identify records. • If an error message like the following appears when you run fastinsert, the table might be open in edit mode. [Vendor][ODBC Product Driver] The database engine could not lock table 'TableName' because it is already in use by another person or process.
6 Using Database Toolbox Functions Retrieving Object Properties Using the get Function In this section... “Database Connection Objects” on page 6-70 “Cursor Objects” on page 6-71 “Driver Objects” on page 6-72 “Database Metadata Objects” on page 6-72 “Drivermanager Objects” on page 6-73 “Resultset Objects” on page 6-73 “Resultset Metadata Objects” on page 6-73 Database Connection Objects Allowable property names and returned values for database connection objects appear in the following table.
Retrieving Object Properties Using the get Function Property Value 'Type' Object type, specifically Database Object. 'URL' For JDBC connections only, the JDBC URL object jdbc:subprotocol:subname, as specified by database. 'UserName' User name required to connect to a given database, as specified by database. 'Warnings' Warnings returned by database. You cannot use the get function to retrieve the password property.
6 Using Database Toolbox Functions Property Value 'Fetch' 0 for the cursor created using exec; fetchTheData for the cursor created using fetch. 'Scrollable' Contains a logical value to identify the cursor object as scrollable or basic. This property is set to 1 for a scrollable cursor and 0 otherwise. This property is hidden and read-only. 'Position' Contains a number that specifies the current position of the cursor in the data set. This property is only available for a scrollable cursor.
Retrieving Object Properties Using the get Function Property Example of Value 'URL' 'jdbc:odbc:dbtoolboxdemo' 'NullsAreSortedLow' 1 Drivermanager Objects Allowable property names and examples of values for drivermanager objects appear in the following table. Property Example of Value 'Drivers' {'oracle.jdbc.driver.
6 Using Database Toolbox Functions Property Example of Value 'ColumnTypeName' {'TEXT' 'LONG'} 'isNullable' {[1] [1]} 'isReadOnly' {[0] [0]} 'TableName' {'' ''} The empty strings for CatalogName and TableName indicate that databases do not return these values.
Setting Database Preferences Using the setdbprefs Function Setting Database Preferences Using the setdbprefs Function In this section... “About the setdbprefs Function” on page 6-75 “Allowable Properties” on page 6-75 About the setdbprefs Function • From the Database Explorer Toolstrip, select Preferences to open the Database Toolbox Preferences dialog box. • Preferences are retained across MATLAB sessions.
6 Using Database Toolbox Functions Property 'ErrorHandling' Allowable Values Description 'numeric' Import data into MATLAB matrix of doubles. Nonnumeric data types are considered NULL and appear as specified in the NullNumberRead property. Use only when data to retrieve is in numeric format, or when nonnumeric data to retrieve is not relevant. 'structure' Import data into a MATLAB structure. Use for all data types. Facilitates working with returned columns.
Setting Database Preferences Using the setdbprefs Function Property Allowable Values Description 'NullStringRead' Input value, for example, 'null' (default) Specify how NULL strings appear after being imported from a database into the MATLAB workspace. Set this parameter before running fetch. 'NullStringWrite' Input value, for example, 'null' (default) Strings in the specified format, for example, 'NULL', appear as NULL after being exported from the MATLAB workspace to a database.
6 Using Database Toolbox Functions Property Allowable Values Description 'DefaultRowPreFetch' Input numeric value, default value is '10000' Number of rows fetched from the Database server at a time for any query. The higher the number, the fewer the number of trips to the server. Caution This property is applicable only for databases that allow setting this number, such as Oracle.
Working with a DatabaseDatastore Working with a DatabaseDatastore In this section... “About DatabaseDatastore Objects” on page 6-79 “Advantages of DatabaseDatastore Objects Over Basic Fetching” on page 6-79 About DatabaseDatastore Objects A DatabaseDatastore object is a type of datastore in MATLAB. DatabaseDatastore objects let you import data into MATLAB from relational databases. You can connect to your database using native ODBC or JDBC interfaces and create a DatabaseDatastore object.
6 Using Database Toolbox Functions MapReduce” on page 6-85. For more MapReduce examples, see Building Effective Algorithms with MapReduce.
Import Data Using a DatabaseDatastore Import Data Using a DatabaseDatastore This example shows how to import data into MATLAB using a DatabaseDatastore. You can use a DatabaseDatastore to access collections of data stored in a relational database. After creating a DatabaseDatastore object, you can preview data, read data in chunks, and read every record in the data set. After importing data from a DatabaseDatastore object, you can run calculations on the data using MapReduce.
6 Using Database Toolbox Functions _____________ ___________ ______________ ________ __________________ 1.00 2.00 3.00 4.00 5.00 6.00 7.00 8.00 400345.00 400314.00 400999.00 400339.00 400455.00 400876.00 389123.00 212569.00 1001.00 1002.00 1009.00 1008.00 1005.00 1004.00 1007.00 1001.00 14.00 9.00 17.00 21.00 3.00 8.00 16.00 5.
Import Data Using a DatabaseDatastore 'No Data' read returns the cell array containing the string 'No Data' when you connect to the database using the native ODBC interface. Reset the Cursor Position in the DatabaseDatastore Reset the cursor position to the start of the data set. reset(dbds) Read Every Record in the DatabaseDatastore Read every record in the DatabaseDatastore object dbds.
6 Using Database Toolbox Functions • Building Effective Algorithms with MapReduce More About • 6-84 “Working with a DatabaseDatastore” on page 6-79
Analyze Large Data Sets in a Database with MapReduce Analyze Large Data Sets in a Database with MapReduce This example shows how to analyze large data sets that are stored in a database. You can access large data sets using a DatabaseDatastore object with Database Toolbox. After creating a DatabaseDatastore, you can run algorithms on large data sets by integrating with MapReduce. This example uses MapReduce to calculate the mean arrival delay of a large flight data set that is stored in a database.
6 Using Database Toolbox Functions Define the Mapper and Reducer Functions You can write your own mapper function to process large data sets in chunks. This example uses the mapper function meanArrivalDelayMapper.m. This mapper function reads in arrival delay data from the DatabaseDatastore object, calculates the number of delays and the total arrival delay in the chunk, and stores both values in KeyValueStore. Display the code for this function using the command type. type meanArrivalDelayMapper.
Analyze Large Data Sets in a Database with MapReduce % intermKey is 'PartialCountSumDelay' count = 0; sum = 0; while hasnext(intermValIter) countSum = getnext(intermValIter); count = count + countSum(1); sum = sum + countSum(2); end meanDelay = sum/count; % The key-value pair added to outKVStore will become the output of mapreduce add(outKVStore,'MeanArrivalDelay',meanDelay); Run MapReduce Using the Mapper and Reducer Functions Run MapReduce with the DatabaseDatastore object dbds, mapper function meanArri
6 Using Database Toolbox Functions close(dbds) See Also close | database | datastore | mapreduce | readall | setdbprefs Related Examples • “Import Data Using a DatabaseDatastore” on page 6-81 • Building Effective Algorithms with MapReduce More About 6-88 • “Working with a DatabaseDatastore” on page 6-79 • “Getting Started with MapReduce”
7 Functions — Alphabetical List
7 Functions — Alphabetical List attr Retrieve attributes of columns in fetched data set Syntax attributes = attr(curs) attributes = attr(curs,colnum) Description attributes = attr(curs) retrieves attribute information for all columns in the fetched data set curs. attributes = attr(curs,colnum) retrieves attribute information for the column number colnum in the fetched data set curs. Examples Retrieve Attribute Data for a Fetched Data Set Connect to an Oracle database using an ODBC connection.
attr attributes = 1x4 struct array with fields: fieldName typeName typeValue columnWidth precision scale currency readOnly nullable Message attributes contains a structure array for three columns in the table inventoryTable. Display the attribute data for the first column in the table inventoryTable. attributes(1) ans = fieldName: typeName: typeValue: columnWidth: precision: scale: currency: readOnly: nullable: Message: 'PRODUCTNUMBER' 'NUMBER' 2.00 39.00 38.
7 Functions — Alphabetical List inventoryTable with these columns: productNumber, Quantity, Price, and inventoryDate. conn = database(dbname,username,pwd); Fetch all the data from the table inventoryTable into a fetched data set curs. curs = exec(conn,'select * from inventoryTable'); curs = fetch(curs); Retrieve attribute information for the third column in the table inventoryTable in the fetched data curs.
attr colnum — Column number scalar Column number, specified as a scalar to denote the column in the fetched data set curs for retrieving attribute information. Data Types: double Output Arguments attributes — Attribute data structure array Attribute data, returned as a structure array containing attribute information for each column in the fetch data set curs. The following attributes are available. Attribute Description fieldName Name of the column. typeName Data type.
7 Functions — Alphabetical List bestrowid Unique identifier for row in database table Syntax b = bestrowid(dbmeta,'cata','sch') b = bestrowid(dbmeta,'cata','sch','tab') Description b = bestrowid(dbmeta,'cata','sch') returns the optimal set of columns in a table that uniquely identifies a row in the schema sch, in the catalog cata, for the database whose database metadata object is dbmeta.
bestrowid See Also columns | dmd | get | tables 7-7
7 Functions — Alphabetical List clearwarnings Clear warnings for database connection or resultset Syntax clearwarnings(conn) clearwarnings(rset) Description clearwarnings(conn) clears warnings reported for the database connection object conn. clearwarnings(rset) clears warnings reported for the resultset object rset.
close close Close database connection, DatabaseDatastore, cursor, or resultset object Syntax close(object) Description close(object) closes the database and driver resource utilizer object to free up database and driver resources. Examples Close the Database Connection Object Using the native ODBC interface, connect to the database with the ODBC data source name dbtoolboxdemo with the user name admin and password admin. conn = database.
7 Functions — Alphabetical List dbds = datastore(conn,sqlquery); Close the DatabaseDatastore object dbds. close(dbds) Close the Cursor Object Using the native ODBC interface, connect to the database with the ODBC data source name dbtoolboxdemo with the user name admin and password admin. conn = database.ODBCConnection('dbtoolboxdemo','admin','admin'); Select data from productTable that you access using the database.ODBCConnection object conn. Assign the returned cursor object to the variable curs.
close close(rset) Close the cursor object curs before closing the database connection. close(curs) Close the database connection conn.
7 Functions — Alphabetical List See Also database | datastore | exec | fetch | resultset 7-12
cols cols Retrieve number of columns in fetched data set Syntax numcols = cols(curs) Description numcols = cols(curs) returns the number of columns in the fetched data set curs. Examples Display the Number of Columns in a Data Set Create a database connection conn using the dbtoolboxdemo data source. conn = database('dbtoolboxdemo','',''); Working with the dbtoolboxdemo data source, use fetch to import all data into Database Cursor Object curs.
7 Functions — Alphabetical List [ 6] [ 3] [10] [400876] [400999] [888652] [1004] [1009] [1006] [ 8] [17] [24] 'Sail Boat' 'Slinky' 'Teddy Bear' Data contains the productTable data. Display the number of columns in the Data element in the cursor object. numcols = cols(curs) numcols = 5 The data in the cursor object contains five columns. After finishing with the cursor object, close it. close(curs) Close the database connection.
cols See Also attr | close | columnnames | columnprivileges | columns | database | fetch | get | rows | width 7-15
7 Functions — Alphabetical List columnnames Retrieve names of columns in fetched data set Syntax columnlist = columnnames(curs) columnlist = columnnames(curs,returnCellArray) Description columnlist = columnnames(curs) returns the column names of the data selected from a database table in the cursor object curs. The columnnames function is not supported for a cursor object returned by the fetchmulti function.
columnnames 'SupplierNumber','SupplierName','City','Country','FaxNumber' columnlist contains one long string with the column names in the suppliers table in quotes and separated by commas. After finishing with the cursor object, close it. close(curs) Close the database connection. close(conn) Return Column Names as a Cell Array Create a database connection conn using the dbtoolboxdemo data source.
7 Functions — Alphabetical List close(conn) Input Arguments curs — Database cursor database cursor object Database cursor, specified as an open SQL database cursor object created using exec. returnCellArray — Return format true | false Return format, specified as Boolean values true or false. When set to true, columnnames returns the column names as a cell array. When set to false, columnnames returns the column names as a long string.
columnprivileges columnprivileges List database column privileges Syntax lp = columnprivileges(dbmeta,'cata','sch','tab') lp = columnprivileges(dbmeta,'cata','sch','tab','l') Description lp = columnprivileges(dbmeta,'cata','sch','tab') returns a list of privileges for all columns in the table tab, in the schema sch, in the catalog cata for the database whose database metadata object is dbmeta.
7 Functions — Alphabetical List columns Return database table column names Syntax columnlist = columns(conn,catalog) columnlist = columns(conn,catalog,schema) columnlist = columns(conn,catalog,schema,tablename) columnlist = columns(dbmeta,catalog) columnlist = columns(dbmeta,catalog,schema) columnlist = columns(dbmeta,catalog,schema,tablename) Description columnlist = columns(conn,catalog) returns a list of all column names in the catalog catalog for the database with the database connection conn.
columns Examples Retrieve the Column List for a Catalog Using the Database Connection Create a database connection conn. This code uses database name dbname, user name username, password pwd, database server name sname, and port number 123456 to connect to a Microsoft SQL Server database. conn = database('dbname','username','pwd',... 'Vendor','Microsoft SQL Server',... 'Server','sname',... 'portnumber',123456); Retrieve the column names for each table in a catalog.
7 Functions — Alphabetical List conn = database('dbname','username','pwd',... 'Vendor','Microsoft SQL Server',... 'Server','sname',... 'portnumber',123456); Retrieve the column names for each table in a schema. Here, this code assumes that the database contains the catalog name toy_store and the schema name schema. columnlist = columns(conn,'toy_store','schema') columnlist = 'inserttest' 'inventoryTable' 'largedata' ... {1x3 {1x4 {1x9 cell} cell} cell} columns returns a cell array.
columns Retrieve the column names in a database table. Here, this code assumes that the database contains the catalog name toy_store, the schema name schema, and the table name inventoryTable. columnlist = columns(conn,'toy_store','schema','inventoryTable') columnlist = 'productNumber' 'Quantity' 'Price' 'inventoryDate' columns returns a cell array with the column names as strings. Close the database connection.
7 Functions — Alphabetical List Display the column names for the suppliers table. columnlist{2,2} ans = 'SupplierNumber' 'SupplierName' 'City' 'Country' 'FaxNumber' Close the database connection. close(conn) Retrieve the Column List for a Catalog and Schema Using the Database Metadata Object Create a database connection conn. This code uses database name dbname, user name username, password pwd, database server name sname, and port number 123456 to connect to a Microsoft SQL Server database.
columns 'productNumber' 'Quantity' 'Price' 'inventoryDate' Close the database connection. close(conn) Retrieve the Column List for a Catalog, Schema, and Table Name Using the Database Metadata Object Create a database connection conn. This code uses database name dbname, user name username, password pwd, database server name sname, and port number 123456 to connect to a Microsoft SQL Server database. conn = database('dbname','username','pwd',... 'Vendor','Microsoft SQL Server',... 'Server','sname',...
7 Functions — Alphabetical List Database connection, specified as a database connection object created using database. dbmeta — Database metadata object Database metadata, specified as a database metadata object created using dmd. catalog — Database catalog name string Database catalog name, specified as a string. Data Types: char schema — Database schema name string Database schema name, specified as a string.
commit commit Make database changes permanent Syntax commit(conn) Description commit(conn) makes permanent changes made to the database connection conn since the last commit or rollback function was run. To run this function, the AutoCommit flag for conn must be off. Examples Example 1 — Check the Status of the Autocommit Flag Check that the status of the AutoCommit flag for connection conn is off.
7 Functions — Alphabetical List See Also database | exec | fastinsert | get | rollback | update 7-28
confds confds Configure JDBC data source for Visual Query Builder Alternatives Select Define JDBC data sources from the Visual Query Builder Query menu. Syntax confds Description confds displays the VQB Define JDBC data sources dialog box. Use confds only to build and run queries using Visual Query Builder with JDBC drivers. For information about how to use the Define JDBC data sources dialog box to configure JDBC drivers, see “Configuring a Driver and Data Source” on page 2-13.
7 Functions — Alphabetical List Tip Use the database function to define JDBC data sources programmatically.
crossreference crossreference Retrieve information about primary and foreign keys Syntax f = crossreference(dbmeta, 'pcata', 'psch', 'ptab', 'fcata', 'fsch', 'ftab') Description f = crossreference(dbmeta, 'pcata', 'psch', 'ptab', 'fcata', 'fsch', 'ftab') returns information about the relationship between foreign keys and primary keys for the database whose database metadata object is dbmeta. The primary key information is for the table ptab in the primary schema psch. The primary catalog is pcata.
7 Functions — Alphabetical List Column Description Value 1 Catalog that contains primary key, referenced by orcl foreign imported key 2 Schema that contains primary key, referenced by SCOTT foreign imported key 3 Table that contains primary key, referenced by foreign imported key DEPT 4 Column name of primary key, referenced by foreign imported key DEPTNO 5 Catalog that has foreign key orcl 6 Schema that has foreign key SCOTT 7 Table that has foreign key EMP 8 Foreign key column nam
crossreference See Also dmd | exportedkeys | get | importedkeys | primarykeys 7-33
7 Functions — Alphabetical List cursor.fetch Import data into MATLAB Workspace from cursor object created by exec Alternatives Retrieve data using Database Explorer. Syntax curs = fetch(curs,rowLimit) curs = fetch(curs) Description curs = fetch(curs,rowLimit) imports rows of data into the object curs from the open SQL cursor curs, up to the maximum rowLimit. curs = fetch(curs) imports rows of data from the open SQL cursor curs into the object curs, up to rowLimit.
cursor.fetch Fetching large amounts of data can result in memory or speed issues. In this case, use rowLimit to limit how much data you retrieve at once. Caution: Leaving cursor and connection objects open or overwriting open objects can result in unexpected behavior. Once you are finished working with these objects, you must close them using close. Examples Import All Rows of Data Using the Native ODBC Interface Create a connection conn using the native ODBC interface and the dbtoolboxdemo data source.
7 Functions — Alphabetical List Statement: [1x1 database.internal.ODBCStatementHandle] With the native ODBC interface, curs returns an ODBCCursor Object instead of a Database Cursor Object. View the contents of the Data element in the cursor object. curs.Data ans = 'Victorian Doll' 'Train Set' 'Engine Kit' 'Painting Set' 'Space Cruiser' 'Building Blocks' 'Tin Soldier' 'Sail Boat' 'Slinky' 'Teddy Bear' Close the cursor object.
cursor.fetch View the contents of the Data element in the cursor object. curs.Data ans = 'New York' 'London' 'Adelaide' 'Dublin' 'Boston' 'New York' 'Wellesley' 'Nashua' 'London' 'Belfast' Close the cursor object. close(curs) Import a Specified Number of Rows Working with the dbtoolboxdemo data source, use the rowLimit argument to retrieve only the first three rows of data.
7 Functions — Alphabetical List ans = 'Victorian Doll' 'Train Set' 'Engine Kit' Rerun the fetch function to return the second three rows of data. curs = fetch(curs,3); View the data. curs.Data ans = 'Painting Set' 'Space Cruiser' 'Building Blocks' Close the cursor object.
cursor.fetch ans = 'Victorian Doll' 'Train Set' ans = 'Engine Kit' 'Painting Set' ans = 'Space Cruiser' 'Building Blocks' ans = 'Tin Soldier' 'Sail Boat' ans = 'Slinky' 'Teddy Bear' ans = 'No Data' Close the cursor object. close(curs) Import Numeric Data Working with the dbtoolboxdemo data source, import a column of numeric data, using the setdbprefs function to specify numeric as the format for the retrieved data.
7 Functions — Alphabetical List ans = 13 5 16 Close the cursor object. close(curs) Import Boolean Data Import data that includes a BOOLEAN field, using the setdbprefs function to specify cellarray as the format for the retrieved data. curs = exec(conn,['select InvoiceNumber, '... 'Paid from Invoice']); setdbprefs('DataReturnFormat','cellarray') curs = fetch(curs,5); A = curs.Data A = [ 2101] [ 3546] [33116] [34155] [34267] [0] [1] [1] [0] [1] View the class of the second column of A.
cursor.fetch setdbprefs('FetchInBatches’,'yes') setdbprefs('FetchBatchSize’,'2') conn = database('dbtoolboxdemo','',''); curs = exec(conn,'select * from productTable'); curs = fetch(curs); A = curs.
7 Functions — Alphabetical List [9] [8] [7] [125970] [212569] [389123] [1003] [1001] [1007] [13] [ 5] [16] 'Victorian Doll' 'Train Set' 'Engine Kit' In this case, cursor.fetch retrieves the first three rows of productTable, two rows at a time. Close the cursor object. close(curs) • “Retrieving BINARY and OTHER Data Types” • “Connecting to a Database Using the Native ODBC Interface” More About Tips • This page documents fetch for a cursor object. For details about using fetch, cursor.
database database Connect to database Syntax conn = database(instance,username,password) conn = database.ODBCConnection(instance,username,password) conn = database(instance,username,password,driver,databaseurl) conn = database(instance,username,password,Name,Value) Description conn = database(instance,username,password) returns a database connection object for the connection to the ODBC data source setup instance using an ODBC driver. conn = database.
7 Functions — Alphabetical List conn = database.ODBCConnection('dbtoolboxdemo','username','pwd') conn = ODBCConnection with properties: Instance: UserName: Message: Handle: TimeOut: AutoCommit: Type: 'dbtoolboxdemo' 'username' [] [1x1 database.internal.ODBCConnectHandle] 0 0 'ODBCConnection Object' database.ODBCConnection returns conn as database.ODBCConnection object. conn has an empty Message property, which indicates a successful connection. Close the database connection conn.
database close(conn) Connect to Microsoft SQL Server Using Windows Authentication Connect to a Microsoft SQL Server database with integrated Windows Authentication using a JDBC driver. Use the AuthType parameter to establish a Windows Authentication connection. For details about how to set up Windows Authentication and find your port number, see “Microsoft SQL Server JDBC for Windows” on page 2-33. conn = database('test_db','','',... 'Vendor','Microsoft SQL Server','Server','servername',...
7 Functions — Alphabetical List Instance: UserName: Driver: URL: Constructor: Message: Handle: TimeOut: AutoCommit: Type: 'dbname' 'username' 'com.sybase.jdbc4.jdbc.SybDriver' 'URL' [1x1 com.mathworks.toolbox.database.databaseConnect] [] [1x1 com.sybase.jdbc4.jdbc.SybConnection] 0 'on' 'Database Object' conn has an empty Message property, which indicates a successful connection. Close the database connection conn.
database Close the database connection conn. close(conn) Connect to MySQL Using a JDBC Driver Connect to a MySQL database using name-value pair arguments to specify the vendor and connection options. Connect to the database test_db on the machine remotehost. Use the user name username and password pwd. For details, see “MySQL JDBC for Windows” on page 2-63. conn = database('test_db','username','pwd','Vendor','MySQL',...
7 Functions — Alphabetical List Fetch data from the database. curs = exec(conn,'SELECT ALL January FROM salesVolume'); curs = fetch(curs); data = curs.Data; Close the database connection conn. close(conn) Connect to PostgreSQL Using a JDBC Driver Connect to a PostgreSQL database using name-value pair arguments to specify the vendor and connection options. Connect to the database test_db using the user name username and password pwd on the machine remotehost.
database Data source setup or database name, specified as a string. Specify a data source for ODBC connection, and the database name for JDBC connection. For an ODBC driver, instance is the name you provide for your data source when you create a data source using the Microsoft ODBC Administrator. For a JDBC driver, instance is the name of your database. The name might differ for different database systems.
7 Functions — Alphabetical List quotes (' '). You can specify several name and value pair arguments in any order as Name1,Value1,...,NameN,ValueN. Example: 'Vendor','MySQL','Server','remotehost' connects to a MySQL database on a machine named remotehost.
database • 'Server' • 'Windows' Specify 'Windows' for Windows Authentication. Example: 'AuthType','Windows' 'DriverType' — Driver type 'thin' | 'oci' Driver type (required only for Oracle), specified as the comma-separated pair consisting of 'DriverType' and one of the following strings: • 'thin' • 'oci' Specify 'oci' for Windows Authentication. Example: 'DriverType','thin' 'URL' — Connection URL string Connection URL, specified as the comma-separated pair consisting of 'URL' and a string value.
7 Functions — Alphabetical List Property Description Constructor Internal Java or C++ representation of database connection object Message Database connection status message that is empty when a successful connection is established Handle Internal Java or C++ representation of database connection object TimeOut Number of seconds that the driver waits while trying to establish a database connection before throwing an error AutoCommit Set to on to apply updates to the database automatically and s
database Database JDBC Driver Name and Database URL Example Syntax Database URL: jdbc:sqlserver:// localhost:port;database=databasename MySQL JDBC driver: twz1.jdbc.mysql.jdbcMysqlDriver Database URL: jdbc:z1MySQL://natasha:3306/metrics JDBC driver: com.mysql.jdbc.Driver Database URL: jdbc:mysql://devmetrics.mrkps.com/testing To insert or select characters with encodings that are not default, append the string useUnicode=true&characterEncoding=... to the URL, where ...
7 Functions — Alphabetical List Database JDBC Driver Name and Database URL Example Syntax PostgreSQL with SSL Connection JDBC driver: org.postgresql.Driver Database URL: jdbc:postgresql:servername:dbname:ssl= true&sslfactory=org.postgresql.ssl.NonValidatingFactory& The trailing & is required. Sybase SQL JDBC driver: com.sybase.jdbc4.jdbc.
database.catalogs database.catalogs Get database catalog names Syntax P = catalogs(conn) Description P = catalogs(conn) returns the catalogs for the database connection conn. See Also get | database.columns | database.schemas | database.
7 Functions — Alphabetical List database.columns Get database table column names Syntax P P P P = = = = columns(conn) columns(conn,C) columns(conn,C,S) columns(conn,C,S,T) Description P = columns(conn) returns all columns for all tables given the database connection conn. P = columns(conn,C) returns all columns for all tables of all schemas for the given catalog C. P = columns(conn,C,S) returns the columns for all tables for the given catalog C and schema S.
database.fetch database.fetch Execute SQL statement to import data into MATLAB workspace Syntax results = fetch(conn,sqlquery) results = fetch(conn,sqlquery,fetchbatchsize) Description results = fetch(conn,sqlquery) executes the SQL statement sqlquery, imports data for the open connection object conn, and returns the data to results. (For details about SQL statements, see exec.) results = fetch(conn,sqlquery,fetchbatchsize) imports fetchbatchsize rows of data at a time.
7 Functions — Alphabetical List Output Arguments results A cell array, table, dataset array, structure, or numeric matrix depending on specifications set by setdbprefs. Examples Import Data Import the productDescription column from the productTable table in the dbtoolboxdemo database.
database.fetch 10 1 Import Two Columns of Data and View Information About the Data Import the InvoiceNumber and Paid columns from the Invoice table in the dbtoolboxdemo database. conn = database('dbtoolboxdemo','',''); setdbprefs('DataReturnFormat','cellarray') results = fetch(conn,['select InvoiceNumber, '... 'Paid from Invoice']); View the size of the cell array into which the results were returned. size(results) ans = 12 2 View the results for the first row of data.
7 Functions — Alphabetical List • The order of records in your database does not remain constant. Use the SQL ORDER BY command in your sqlquery statement to sort data. • “Preference Settings for Large Data Import” See Also cursor.
database.schemas database.schemas Get database schema names Syntax P = schemas(conn) Description P = schemas(conn) returns the schema names for the database connection conn. See Also get | database.catalogs | database.columns | database.
7 Functions — Alphabetical List database.tables Get database table names Syntax T = tables(conn) T = tables(conn,C) T = tables(conn,C,S) Description T = tables(conn) returns all tables and table types for the database connection object conn. T = tables(conn,C) returns all tables and table types for all schemas of the given catalog name C. T = tables(conn,C,S) returns the list of tables and table types for the database with the catalog name C and schema name S. See Also get | database.
datastore datastore Create datastore to access collection of data in a database This datastore function creates a DatabaseDatastore object. You can use this object to read large volumes of data in a relational database. A DatabaseDatastore is one of the available datastore types. You can create other types of datastores using the MATLAB function datastore. After creating any datastore, you can analyze data by writing custom functions to run MapReduce using the mapreduce function.
7 Functions — Alphabetical List sqlquery = 'select * from productTable'; dbds = datastore(conn,sqlquery) dbds = DatabaseDatastore with properties: Connection: [1x1 database.ODBCConnection] Cursor: [1x1 database.ODBCCursor] Query: 'select * from productTable' datastore executes the SQL query sqlquery and creates a cursor object with the resulting data.
datastore Data: RowLimit: SQLQuery: Message: Type: Statement: 0 0 'select * from productTable' [] 'ODBCCursor Object' [1x1 database.internal.ODBCStatementHandle] The Message property is blank when the SQL query executes successfully. Close the DatabaseDatastore, cursor, and database connection. close(dbds) • “Import Data Using a DatabaseDatastore” Input Arguments conn — Database connection database connection object Database connection, specified as a database connection object created using database.
7 Functions — Alphabetical List • “Working with a DatabaseDatastore” • “Getting Started with Datastore” See Also close | database | datastore | exec | preview | read | reset 7-66
hasdata hasdata Determine if cursor in DatabaseDatastore contains more data Syntax tf = hasdata(dbds) Description tf = hasdata(dbds) returns logical 1 (true) if additional data exists in the cursor object of the DatabaseDatastore object dbds. Otherwise, it returns logical 0 (false). For JDBC drivers, hasdata uses the driver to check if the cursor position is located at the end of the data set.
7 Functions — Alphabetical List sqlquery = ['select * from [toy_store].[dbo].[productTable] '... 'order by productNumber']; dbds = datastore(conn,sqlquery); Read the first five records. read(dbds,5) ans = productNumber _____________ stockNumber ___________ supplierNumber ______________ unitCost ________ productDescription __________________ 1.00 2.00 3.00 4.00 5.00 400345.00 400314.00 400999.00 400339.00 400455.00 1001.00 1002.00 1009.00 1008.00 1005.00 14.00 9.00 17.00 21.00 3.
hasdata 11.00 12.00 13.00 14.00 15.00 408143.00 210456.00 470816.00 510099.00 899752.00 1004.00 1010.00 1012.00 1011.00 1011.00 11.00 22.00 16.50 19.00 20.00 'Convertible' 'Hugsy' 'Pancakes' 'Shawl' 'Snacks' ans = 'No Data' read returns the cell array containing the string 'No Data' when you connect to the database using the native ODBC interface. Close the DatabaseDatastore, cursor, and database connection.
7 Functions — Alphabetical List preview Display first eight records of data in DatabaseDatastore Syntax data = preview(dbds) Description data = preview(dbds) displays the first eight records of data in the DatabaseDatastore object dbds. Examples Preview Data in a DatabaseDatastore The default output data type of any datastore is a table. Set the database preference for the data return format 'DataReturnFormat' to table for consistency across data types.
preview Preview the first eight records in the data set returned by executing the SQL query sqlquery. preview(dbds) ans = productNumber _____________ stockNumber ___________ supplierNumber ______________ unitCost ________ productDescription __________________ 1.00 2.00 3.00 4.00 5.00 6.00 7.00 8.00 400345.00 400314.00 400999.00 400339.00 400455.00 400876.00 389123.00 212569.00 1001.00 1002.00 1009.00 1008.00 1005.00 1004.00 1007.00 1001.00 14.00 9.00 17.00 21.00 3.00 8.00 16.00 5.
7 Functions — Alphabetical List More About • Using DatabaseDatastore Objects • “Working with a DatabaseDatastore” See Also close | database | datastore | read | readall | reset 7-72
read read Read data in DatabaseDatastore Syntax data = read(dbds) data = read(dbds,rowcount) [data,info] = read( ___ ) Description data = read(dbds) retrieves data from the DatabaseDatastore object in increments specified using setdbprefs and in the format specified using setdbprefs. data = read(dbds,rowcount) retrieves data from the DatabaseDatastore object in increments specified by rowcount and in the format specified using setdbprefs.
7 Functions — Alphabetical List Create a DatabaseDatastore object dbds using the database connection conn and SQL query sqlquery. This SQL query retrieves all products from the product table productTable ordered by product number. sqlquery = ['select * from [toy_store].[dbo].[productTable] '... 'order by productNumber']; dbds = datastore(conn,sqlquery); Read the data in the DatabaseDatastore object dbds.
read database with the data source named MS SQL Server Auth. MS SQL Server Auth contains the table named productTable with 15 product records. conn = database.ODBCConnection('MS SQL Server Auth','',''); Create a DatabaseDatastore object dbds using the database connection conn and SQL query sqlquery. This SQL query retrieves all products from the product table productTable ordered by product number. sqlquery = ['select * from [toy_store].[dbo].[productTable] '...
7 Functions — Alphabetical List database with the data source named MS SQL Server Auth. MS SQL Server Auth contains the table named productTable with 15 product records. conn = database.ODBCConnection('MS SQL Server Auth','',''); Create a DatabaseDatastore object dbds using the database connection conn and SQL query sqlquery. This SQL query retrieves all products from the product table productTable ordered by product number. sqlquery = ['select * from [toy_store].[dbo].[productTable] '...
read close(dbds) Retrieve Data and Database Information Using a Row Count The default output data type of any datastore is a table. Set the database preference for the data return format 'DataReturnFormat' to table for consistency across data types. setdbprefs('DataReturnFormat','table') Create a database connection conn using the native ODBC interface with Windows Authentication. This code assumes you are connecting to a Microsoft SQL Server database with the data source named MS SQL Server Auth.
7 Functions — Alphabetical List Close the DatabaseDatastore, cursor, and database connection. close(dbds) • “Import Data Using a DatabaseDatastore” • “Analyze Large Data Sets in a Database with MapReduce” Input Arguments dbds — Datastore containing data in database DatabaseDatastore object Datastore containing data in database, specified as a DatabaseDatastore object created using datastore.
read Field Description datasource Data source name for ODBC drivers or a database name for JDBC drivers offset Current cursor position in the returned data set More About • Using DatabaseDatastore Objects • “Working with a DatabaseDatastore” See Also close | database | datastore | hasdata | preview | readall | reset | setdbprefs 7-79
7 Functions — Alphabetical List readall Read every record in DatabaseDatastore Syntax data = readall(dbds) Description data = readall(dbds) retrieves data from the DatabaseDatastore object dbds in the format specified using setdbprefs. Examples Read Every Record in a DatabaseDatastore The default output data type of any datastore is a table. Set the database preference for the data return format 'DataReturnFormat' to table for consistency across data types.
readall Read every record in the DatabaseDatastore object dbds. data = readall(dbds) data = productNumber _____________ stockNumber ___________ supplierNumber ______________ unitCost ________ productDescription __________________ 1.00 2.00 3.00 4.00 5.00 6.00 7.00 8.00 9.00 10.00 11.00 12.00 13.00 14.00 15.00 400345.00 400314.00 400999.00 400339.00 400455.00 400876.00 389123.00 212569.00 125970.00 888652.00 408143.00 210456.00 470816.00 510099.00 899752.00 1001.00 1002.00 1009.00 1008.00 1005.
7 Functions — Alphabetical List Query results, returned as a cell array, matrix, table, structure, or dataset array of the records in the data set. Executing the SQL statement specified in the DatabaseDatastore object creates the data set. The format of the data is specified by the preference setting 'DataReturnFormat' in setdbprefs.
reset reset Reset cursor position in DatabaseDatastore Syntax reset(dbds) Description reset(dbds) repositions the cursor object in the DatabaseDatastore object dbds to the start of the data set by executing the SQL query again. Examples Reset the Cursor Position The default output data type of any datastore is a table. Set the database preference for the data return format 'DataReturnFormat' to table for consistency across data types.
7 Functions — Alphabetical List dbds = datastore(conn,sqlquery); Read the data in the data set. readall(dbds) ans = productNumber _____________ stockNumber ___________ supplierNumber ______________ unitCost ________ productDescription __________________ 1.00 2.00 3.00 4.00 5.00 6.00 7.00 8.00 9.00 10.00 11.00 12.00 13.00 14.00 15.00 400345.00 400314.00 400999.00 400339.00 400455.00 400876.00 389123.00 212569.00 125970.00 888652.00 408143.00 210456.00 470816.00 510099.00 899752.00 1001.00 1002.
reset 15.00 899752.00 1011.00 20.00 'Snacks' Close the DatabaseDatastore, cursor, and database connection. close(dbds) • “Import Data Using a DatabaseDatastore” • “Analyze Large Data Sets in a Database with MapReduce” Input Arguments dbds — Datastore containing data in database DatabaseDatastore object Datastore containing data in database, specified as a DatabaseDatastore object created using datastore.
7 Functions — Alphabetical List Using DatabaseDatastore Objects Access collection of data stored in database MATLAB has various datastores that let you import large data sets into MATLAB for analysis. A DatabaseDatastore object is a type of datastore that contains the resulting data from executing an SQL query in a relational database. With a DatabaseDatastore object, you can perform these tasks: • Preview data. • Read data in chunks. • Read every record in the data set.
Using DatabaseDatastore Objects dbds = datastore(conn,sqlquery) dbds = DatabaseDatastore with properties: Connection: [1x1 database.ODBCConnection] Cursor: [1x1 database.ODBCCursor] Query: 'select * from [toy_store].[dbo].[productTable] order by productNumber' datastore executes the SQL query sqlquery and creates a cursor object with the resulting data.
7 Functions — Alphabetical List Object Functions hasdata previewreadreadall resetclose Create Object Create a DatabaseDatastore object using datastore.
datainsert datainsert Export MATLAB data into database table To export MATLAB data into a database, use these functions: datainsert, fastinsert, and insert. For maximum performance, use datainsert. To use datainsert, ensure that your input data is a cell array or numeric matrix with a specific format. When your input data is a structure, dataset array, or table, or you are using a native ODBC database connection, use fastinsert.
7 Functions — Alphabetical List conn = database('MySQL','username','pwd'); Display the last rows in inventoryTable before insertion of data. curs = exec(conn,'select * from inventoryTable'); curs = fetch(curs); curs.Data ans = ... [14] [15] [16] [2000] [1200] [1400] [19.1000] [20.3000] [34.3000] '2014-10-22 10:52...' '2014-10-22 10:52...' '1999-12-31 00:00...' Create cell array of column names for the database table inventoryTable.
datainsert close(conn) Export MATLAB Numeric Matrix Data Establish connection conn to a MySQL database with user name username and password pwd. This database contains the table salesVolume with the column stockNumber and columns for each month of the year. conn = database('MySQL','username','pwd'); Display the last rows in salesVolume before inserting data. curs = exec(conn,'select * from salesVolume'); curs = fetch(curs); curs.Data ans = Columns 1 through 8 ...
7 Functions — Alphabetical List tablename = 'salesVolume'; datainsert(conn,tablename,colnames,data) Display inserted data in salesVolume. curs = exec(conn,'select * from salesVolume'); curs = fetch(curs); curs.Data ans = Columns 1 through 8 ... [510099] [899752] [777666] [ 235] [ 123] [ 0] [1800] [1700] [ 350] [1040] [ 823] [ 400] [ 900] [ 701] [ 450] [ 750] [ 689] [ 250] [ 700] [ 621] [ 450] [ 400] [ 545] [ 500] Columns 9 through 13 ...
datainsert Data Types: char colnames — Database table column names cell array of strings Database table column names, specified as a cell array of one or more strings to denote the columns in the existing database table tablename. Example: {'col1','col2','col3'} Data Types: cell data — Insert data cell array | numeric matrix Insert data, specified as a MATLAB cell array or numeric matrix.
7 Functions — Alphabetical List Database Explorer Configure, explore, and import database data Description Using the Database Explorer app, you can quickly connect to a database, explore the database data, and import data from the database to the MATLAB workspace. If you have minimal proficiency writing SQL queries or want to browse the data in your database quickly, use this app to interact with your database. You can: • Create and configure JDBC and ODBC data sources.
Database Explorer Examples Display Data from a Single Database Table Set up the data source for the tutorial.mdb database and connect to this database. For details, see “Microsoft Access ODBC for Windows”. Display data in the Data Preview pane by opening the database table of interest in the Database Browser pane on the Database Explorer Toolstrip. When you select a database table in the Database Browser pane, the table is highlighted and a corresponding entry displays in the SQL Criteria panel.
7 Functions — Alphabetical List Select (All) to choose all database columns or select check boxes for specific table columns.
Database Explorer To change your display, select or clear check boxes in the Database Browser pane. The data updates in the Data Preview pane. The Data Preview pane displays a limited number of rows. The total number of rows selected in the database appears at the right of the display. You can change the display size by clicking Preferences and adjusting the Data Preview size. Close the database connection. For details, see “Configuring a Driver and Data Source”.
7 Functions — Alphabetical List When you select additional tables in the Database Browser pane, the SQL Criteria panel updates.
Database Explorer Display the contents for the selected tables using the SQL Criteria panel to define a join of the selected tables. Click the drop-down lists to specify the table column for joining the selected tables. The join results appear in the Data Preview pane.
7 Functions — Alphabetical List 7-100
Database Explorer Close the database connection. For details, see “Microsoft Access ODBC for Windows” Query Data Using a Left Outer Join Set up the data source for the tutorial.mdb database and connect to this database. For details, see “Microsoft Access ODBC for Windows” Expand the table suppliers and select the fields SupplierName, City, and Country. Expand the table producttable and select the fields productDescription and unitCost.
7 Functions — Alphabetical List condition. There are two empty conditions in the SQL Criteria panel on the Database Explorer Toolstrip. From the SQL Criteria panel, in the first condition at the top, change the first combo box for condition type to LEFT JOIN. Change the second combo box to suppliers.SupplierNumber. Change the last combo box to producttable.SupplierNumber. A left join, with the suppliers table on the left, implies that all the rows in the suppliers table are included in the final result.
Database Explorer rows in suppliers that do not have a match with any row in producttable are padded with null values in the final result. In the Data Preview, there are 11 rows that match the query conditions. There is a null in productDescription and a NaN in unitCost because the supplier The Great Teddy Bear Company supplies no products. If the condition type is set to INNER JOIN instead of LEFT JOIN, this row does not appear in the final result.
7 Functions — Alphabetical List suppliers.Country, and the third to NOT LIKE. In the last text box, enter United States and then enter the new condition using the Enter or Tab keys. The query results appear in the Data Preview pane. Enter the variable name as data in the text box untitled located above the table preview. Select Import > Import to import the data displayed in the Data Preview pane into MATLAB as a variable named data.
Database Explorer Close the database connection. For details, see “Microsoft Access ODBC for Windows” Import Data to the MATLAB Workspace Set up the data source for the tutorial.mdb database and connect to this database. For details, see “Microsoft Access ODBC for Windows”. Select data using the Database Browser pane from a single table. Or, create a query using the SQL Criteria panel. Display the results in the Data Preview pane.
7 Functions — Alphabetical List Define the data type for a MATLAB variable in the Imported Data panel to store the data displayed in the Data Preview pane. Supported data types are: • Cell Array • Numeric • Structure • Table • Dataset (requires Statistics and Machine Learning Toolbox) Select Import > Import to import the data displayed in the Data Preview pane.
Database Explorer Note: When importing large amounts of data, Database Explorer imports data in batches. The batch size is set to 1,000 rows by default. To change the batch size, click Preferences and adjust Import batch size. Optionally, display the imported data in the MATLAB workspace using the Variables editor. For details about using the Variables editor, see “View, Edit, and Copy Variables”.
7 Functions — Alphabetical List Optionally, manipulate the data using MATLAB functions. Close the database connection. For details, see “Microsoft Access ODBC for Windows”.
Database Explorer See Also Functions close | database | exec | fetch 7-109
7 Functions — Alphabetical List dmd Construct database metadata object Syntax dbmeta = dmd(conn) Description dbmeta = dmd(conn) constructs a database metadata object for the database connection conn. Use get and supports to obtain properties of dbmeta. Use dmd and get(dbmeta) to obtain information you need about a database, such as table names required to retrieve data.
driver driver Construct database driver object Syntax d = driver('s') Description d = driver('s') constructs a database driver object d from s, where s is a database URL string of the form jdbc:odbc:name or name. The driver object d is the first driver that recognizes s. Examples d = driver('jdbc:odbc:thin:@144.212.123.24:1822:') creates driver object d.
7 Functions — Alphabetical List drivermanager Construct database drivermanager object Syntax dm = drivermanager Description dm = drivermanager constructs a database drivermanager object which comprises the properties for all loaded database drivers. Use get and set to obtain and change the properties of dm. Examples Create a database drivermanager object and return its properties.
exec exec Execute SQL statement and open cursor Syntax curs = exec(conn,sqlquery) curs = exec(conn,sqlquery,qTimeOut) curs = exec(conn,sqlquery,Name,Value) Description curs = exec(conn,sqlquery) executes the SQL statement sqlquery for the database connection conn and returns the cursor object curs. curs = exec(conn,sqlquery,qTimeOut) executes the SQL statement with a timeout value qTimeOut. curs = exec(conn,sqlquery,Name,Value) executes the SQL statement and creates a scrollable cursor.
7 Functions — Alphabetical List Data: RowLimit: SQLQuery: Message: Type: Statement: 0 0 'select * from productTable' [] 'ODBCCursor Object' [1x1 database.internal.ODBCStatementHandle] With the native ODBC interface, exec returns curs a database.ODBCCursor object instead of a Database Cursor Object. After finishing with the cursor object, close it.
exec Connect to the MySQL database. This code assumes you are connecting to a data source named MySQL with user name username and password pwd. conn = database.ODBCConnection('MySQL','username','pwd'); Select all rows from the productTable table and create a scrollable cursor. Assign the SQL statement to the variable sqlquery. Assign the returned cursor object to the variable curs.
7 Functions — Alphabetical List sqlquery = 'select * from productTable'; curs = exec(conn,sqlquery,10) curs = Attributes: Data: DatabaseObject: RowLimit: SQLQuery: Message: Type: ResultSet: Cursor: Statement: Fetch: [] 0 [1x1 database] 0 'select * from productTable' [] 'Database Cursor Object' [1x1 sun.jdbc.odbc.JdbcOdbcResultSet] [1x1 com.mathworks.toolbox.database.sqlExec] [1x1 sun.jdbc.odbc.JdbcOdbcStatement] 0 After finishing with the cursor object, close it.
exec curs.Data ans = [8] [212569] [1001] [5] 'Train Set' The select statement is created by using square brackets to concatenate the two strings select * from productTable where productDescription = and 'productdesc'. The pairs of four quotation marks are needed to create the pair of single quotation marks that appears in the SQL statement around productdesc. The outer two marks delineate the next string to concatenate, and two marks are required inside them to denote a quotation mark inside a string.
7 Functions — Alphabetical List Commit the data. sqlquery = 'commit'; exec(conn,sqlquery); After finishing with the cursor object, close it. curs = ans; close(curs) Change the Database Connection Catalog Change the catalog for the database connection conn to intlprice. sqlquery = 'Use intlprice'; curs = exec(conn,sqlquery); After finishing with the cursor object, close it. close(curs) Create a Table and Add a New Column Use the SQL CREATE command to create the table.
exec Define a stored procedure named create_table that creates a table named test_table by executing this code. This procedure has no input or output arguments. This code assumes you are using a Microsoft SQL Server database. CREATE PROCEDURE create_table AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements.
7 Functions — Alphabetical List close(curs) • “Call a Stored Procedure That Returns Data” • “Run a Custom Database Function” Input Arguments conn — Database connection database connection object Database connection, specified as a database connection object created using database. sqlquery — SQL statement SQL string SQL statement, specified as an SQL string to execute.
exec Output Arguments curs — Database cursor database cursor object Database cursor, returned as a database cursor object. The properties of this object are different based on the database connection object. For a JDBC/ODBC bridge or a JDBC driver database connection, the cursor object has the following properties. Property Description Attributes Not used. Data Contains the resulting data after executing fetch. DatabaseObject Database connection object or database.
7 Functions — Alphabetical List For a native ODBC connection, the cursor object has only these properties from the previous list: Data, RowLimit, SQLQuery, Message, Type, Statement, Scrollable, and Position.
exportedkeys exportedkeys Retrieve information about exported foreign keys Syntax e = exportedkeys(dbmeta, 'cata', 'sch') e = exportedkeys(dbmeta, 'cata', 'sch', 'tab') Description e = exportedkeys(dbmeta, 'cata', 'sch') returns foreign exported key information (that is, information about primary keys that are referenced by other tables) for the schema sch, of the catalog cata, for the database whose database metadata object is dbmeta.
7 Functions — Alphabetical List Column Description Value 2 Schema containing primary key that is exported SCOTT 3 Table containing primary key that is exported DEPT 4 Column name of primary key that is exported DEPTNO 5 Catalog that has foreign key null 6 Schema that has foreign key SCOTT 7 Table that has foreign key EMP 8 Foreign key column name, that is the column name that DEPTNO references the primary key in another table 9 Sequence number within the foreign key 1 10 Update
fastinsert fastinsert Add MATLAB data to database table To export MATLAB data into a database, use these functions: fastinsert, datainsert, and insert. For maximum performance, use datainsert. To use datainsert, ensure that your input data is a cell array or numeric matrix with a specific format. When your input data is a structure, dataset array, or table, or you are using a native ODBC database connection, use fastinsert.
7 Functions — Alphabetical List • supplierNumber • unitCost • productDescription conn = database.ODBCConnection('dbtoolboxdemo','admin','admin') conn = ODBCConnection with properties: Instance: UserName: Message: Handle: TimeOut: AutoCommit: Type: 'dbtoolboxdemo' 'admin' [] [1x1 database.internal.ODBCConnectHandle] 0 0 'ODBCConnection Object' conn has an empty Message property, which means a successful connection. Select and display the data from the productTable.
fastinsert • unitCost equal to $20 • productDescription equal to 'Cooking Set' Then, convert the cell array to the table data_table. data = {11,500565,1010,20,'Cooking Set'}; data_table = cell2table(data,'VariableNames',colnames) data_table = productNumber ------------11 stockNumber ----------500565 supplierNumber -------------1010 unitCost -------20 productDescription -----------------'Cooking Set' Insert the table data into the productTable.
7 Functions — Alphabetical List • Price • inventoryDate conn = database('dbtoolboxdemo','admin','admin'); Alternatively, you can use the native ODBC interface for an ODBC connection. For details, see database. Display the data in the inventoryTable table before insertion. curs = exec(conn,'select * from inventoryTable'); curs = fetch(curs); curs.Data ans = ... [11] [12] [13] [ 567] [ 1278] [ 1700] [ 0] [ 0] [14.5000] '2012-09-11 00:30...' '2010-10-29 18:17...' '2009-05-24 10:58...
fastinsert curs = fetch(curs); curs.Data ans = ... [ 12] [ 13] [7777] [ 1278] [ 1700] [ 100] [ 0] [14.5000] [ 50] '2010-10-29 18:17...' '2009-05-24 10:58...' '2014-10-23 10:01...' The last row contains the inserted data. Close the cursor and database connection. close(curs) close(conn) Insert Multiple Records Connect to the data source dbtoolboxdemo. This data source identifies a Microsoft Access database.
7 Functions — Alphabetical List [13] [ 1700] [14.5000] '2009-05-24 10:58...' Assign multiple rows of data to the cell array data. Each row contains data for productNumber, Quantity, and Price. The first row data for insertion is productNumber equals 7778, Quantity equals 125, and Price equals 23.00. data = {7778,125,23.00,datestr(now,'yyyy-mm-dd HH:MM:SS');... 7779,1160,14.7,datestr(now,'yyyy-mm-dd HH:MM:SS');... 7780,150,54.
fastinsert [7779] [7780] [ 1160] [ 150] [14.7000] [54.5000] '2014-10-23 10:21...' '2014-10-23 10:21...' The last three rows contain the inserted data. Close the cursor and database connection. close(curs) close(conn) Import Records, Perform Calculations, and Export Data Connect to the data source dbtoolboxdemo. This data source identifies a Microsoft Access database. This database contains the tables salesVolume and yearlySales.
7 Functions — Alphabetical List 821 Calculate the sum of the March sales and assign the result to the variable sumA. sumA = sum(AA(:)) sumA = 14606 Assign the month and sum of sales to a cell array to export to a database. Put the month in the first cell of data. data(1,1) = {'March'} data = 'March' Put the sum in the second cell of data. data(1,2) = {sumA} data = 'March' [14606] Define the names of the columns to which to export data.
fastinsert Use fastinsert to export the data into the yearlySales table. fastinsert(conn,tablename,colnames,data) fastinsert appends the data as a new record at the end of the yearlySales table. In Microsoft Access, view the yearlySales table to verify the results. Close the cursor and database connection. close(curs) close(conn) Insert Numeric Data Connect to the data source dbtoolboxdemo. This data source identifies a Microsoft Access database. This database contains the table salesVolume.
7 Functions — Alphabetical List ... [400876] [400999] [888652] [3000] [3000] [ NaN] [2400] [1500] [ 900] [1500] [1000] [ 821] [1500] [ 900] [ 701] [1300] [ 750] [ 689] [1100] [ 700] [ 621] [ 900] [ 400] [ 545] Columns 9 through 13 ... [867] [350] [421] [ 923] [ 500] [ 495] [1100] [1100] [ 550] [ 4000] [ 3000] [ 4200] [32000] [12000] [12000] Insert data using the columns denoted by colnames into the salesVolume table. tablename = 'salesVolume'; colnames = {'stockNumber','January','February'...
fastinsert The last row contains the inserted data. Close the cursor and database connection. close(curs) close(conn) Insert and Commit Data Connect to the data source dbtoolboxdemo. This data source identifies a Microsoft Access database. This database contains the table inventoryTable with these columns: • productNumber • Quantity • Price • inventoryDate conn = database('dbtoolboxdemo','admin','admin'); Alternatively, you can use the native ODBC interface for an ODBC connection.
7 Functions — Alphabetical List close(curs) close(conn) Insert Boolean Data Connect to the data source dbtoolboxdemo. This data source identifies a Microsoft Access database. This database contains the table invoice with these columns: • InvoiceNumber • InvoiceDate • productNumber • Paid • Receipt conn = database('dbtoolboxdemo','admin','admin'); Alternatively, you can use the native ODBC interface for an ODBC connection. For details, see database. Display the data in the invoice table before insertion.
fastinsert colnames = {'InvoiceNumber';'InvoiceDate';'productNumber';'Paid'}; tablename = 'invoice'; fastinsert(conn,tablename,colnames,data) View the new record in the database to verify that the Paid field is Boolean. In some databases, the MATLAB logical value 0 shows as a Boolean false, No, or a cleared check box. curs = exec(conn,'select * from invoice'); curs = fetch(curs); curs.Data ans = ... [61178] [62145] [ 2101] '2012-01-15 00:00...' '2012-01-23 00:00...' '2014-10-23 11:14...
7 Functions — Alphabetical List Database table column names, specified as a cell array of one or more strings to denote the columns in the existing database table tablename. Example: {'col1','col2','col3'} Data Types: cell data — Insert data numeric matrix | cell array | table | dataset | structure Insert data, specified as a numeric matrix, cell array, table, dataset array, or structure, that contains all data for insertion into the existing database table tablename.
fetch fetch Import data into MATLAB workspace from cursor object or from execution of SQL statement Syntax curs curs curs curs = = = = fetch(curs) fetch(curs,rowlimit) fetch(curs,Name,Value) fetch(curs,rowlimit,Name,Value) results = fetch(conn,sqlquery) results = fetch(conn,sqlquery,fetchbatchsize) Description curs = fetch(curs) imports all rows of data into the cursor object curs from the open SQL cursor object curs.
7 Functions — Alphabetical List Examples Import All Data Using the Native ODBC Interface and Cursor Object Create a connection conn using the native ODBC interface and the dbtoolboxdemo data source. conn = database.ODBCConnection('dbtoolboxdemo','admin','admin') conn = ODBCConnection with properties: Instance: UserName: Message: Handle: TimeOut: AutoCommit: Type: 'dbtoolboxdemo' 'admin' [] [1x1 database.internal.
fetch ans = 'Victorian Doll' 'Train Set' 'Engine Kit' 'Painting Set' 'Space Cruiser' 'Building Blocks' 'Tin Soldier' 'Sail Boat' 'Slinky' 'Teddy Bear' After finishing with the cursor object, close it. close(curs) Import All Data Using the Cursor Object Working with the dbtoolboxdemo data source, use exec to select data in column City in the table suppliers.
7 Functions — Alphabetical List 'London' 'Adelaide' 'Dublin' 'Boston' 'New York' 'Wellesley' 'Nashua' 'London' 'Belfast' After finishing with the cursor object, close it. close(curs) Import Specified Rows Using the Cursor Object Working with the dbtoolboxdemo data source, use the rowlimit argument to retrieve only the first three rows of data.
fetch Rerun the fetch function to return the second three rows of data. curs = fetch(curs, 3); View the data. curs.Data ans = 'Painting Set' 'Space Cruiser' 'Building Blocks' After finishing with the cursor object, close it. close(curs) Import Data Iteratively Using the Cursor Object Working with the dbtoolboxdemo data source, use the rowlimit argument to retrieve the first two rows of data, and then rerun the import using a while loop, retrieving two rows at a time.
7 Functions — Alphabetical List 'Engine Kit' 'Painting Set' ans = 'Space Cruiser' 'Building Blocks' ans = 'Tin Soldier' 'Sail Boat' ans = 'Slinky' 'Teddy Bear' ans = 'No Data' After finishing with the cursor object, close it. close(curs) Import Data with an Absolute Position Offset Using the Scrollable Cursor This example assumes you are connecting to a MySQL database that contains a table called productTable. This table contains 15 records, where each record represents one product.
fetch curs = exec(conn,'select * from productTable order by productNumber',... 'cursorType','scrollable'); Import the last five products in the data set using the absolute position offset 11. curs = fetch(curs,'absolutePosition',11); Display the data for the five products. curs.Data ans = [11] [12] [13] [14] [15] [408143] [210456] [470816] [510099] [899752] [1004] [1010] [1012] [1011] [1011] [ 11] [ 22] [16.5000] [ 19] [ 20] 'Convertible' 'Hugsy' 'Pancakes' 'Shawl' 'Snacks' The columns in curs.
7 Functions — Alphabetical List conn = database.ODBCConnection('MySQL','username','pwd'); Select all products from the productTable table and sort them in ascending order by product number. Create a scrollable cursor using the name-value pair argument 'cursorType'. curs = exec(conn,'select * from productTable order by productNumber',... 'cursorType','scrollable'); Import the data for two products in the middle of the data set. Use the row limit 2 to import data for two products.
fetch close(curs) Import Data with Different Formats Using the Cursor Object Import data that includes a BOOLEAN field, using the setdbprefs function to specify cellarray as the format for the retrieved data. curs = exec(conn,['select InvoiceNumber, '... 'Paid from Invoice']); setdbprefs('DataReturnFormat','cellarray') curs = fetch(curs,5); A = curs.Data A = [ 2101] [ 3546] [33116] [34155] [34267] [0] [1] [1] [0] [1] View the class of the second column of A.
7 Functions — Alphabetical List sqlquery = 'select productdescription from productTable'; results = fetch(conn, sqlquery) results = 'Victorian Doll' 'Train Set' 'Engine Kit' 'Painting Set' 'Space Cruiser' 'Building Blocks' 'Tin Soldier' 'Sail Boat' 'Slinky' 'Teddy Bear' View the size of the cell array into which the results were returned. size(results) ans = 10 1 Close the database connection.
fetch fetch returns all the data by importing it in batches of five rows at a time. Close the database connection. close(conn) • “Fetch Data Incrementally Using the Cursor Object” • “Display Information About Imported Data” • “Import Data Using a Scrollable Cursor with a Relative Position Offset” • “ Retrieve Image Data Types” Input Arguments curs — Database cursor database cursor object Database cursor, specified as an open SQL database cursor object created using exec.
7 Functions — Alphabetical List Fetch batch size, specified as a scalar denoting the number of rows of data to batch at a time. Use fetchbatchsize when importing large amounts of data. Retrieving data in batches reduces overall retrieval time. If fetchbatchsize is not provided, a default value of 'FetchBatchSize' is used. 'FetchBatchSize' is set using setdbprefs. Data Types: double Name-Value Pair Arguments Specify optional comma-separated pairs of Name,Value arguments.
fetch Output Arguments curs — Database cursor database cursor object Database cursor, returned as a database cursor object populated with fetched data in the Data property. You can specify the output data format in the Data property by using setdbprefs. results — Result data cell array | table | dataset | structure | numeric matrix Result data, returned as a cell array, table, dataset array, structure, or numeric matrix as specified by 'DataReturnFormat' in setdbprefs.
7 Functions — Alphabetical List fetchmulti Import data from multiple resultsets Syntax curs = fetchmulti(curs) Description curs = fetchmulti(curs) imports data from the open SQL cursor object curs into the object curs, where the open SQL cursor object contains multiple resultsets. Multiple resultsets are retrieved via exec with a sqlquery statement that runs a stored procedure consisting of two select statements. cursmulti.Data contains data from each resultset associated with cursmulti.Statement.
fetchmulti ResultSet: [1x1 sun.jdbc.odbc.JdbcOdbcResultSet] [1x1 sun.jdbc.odbc.JdbcOdbcResultSet] Cursor: ... [1x1 com.mathworks.toolbox.database.sqlExec] Statement: [1x1 sun.jdbc.odbc.JdbcOdbcStatement] [1x1 sun.jdbc.odbc.JdbcOdbcStatement] Fetch: ... [1x1 com.mathworks.toolbox.database.fetchTheData] Alternatively, you can use the native ODBC interface for an ODBC connection. For details, see database.
7 Functions — Alphabetical List get Retrieve object properties Syntax s = get(object) v = get(object,property) Description s = get(object) returns a structure that contains object and its corresponding properties, and assigns the structure to s. v = get(object,property) retrieves the value of property for object and assigns the value to v. Examples Get Database Metadata Object Properties Retrieve the properties of a database metadata object created using a database connection object.
get v = get(dbmeta) v = AllProceduresAreCallable: AllTablesAreSelectable: DataDefinitionCausesTransactionCommit: DataDefinitionIgnoredInTransactions: DoesMaxRowSizeIncludeBlobs: Catalogs: CatalogSeparator: CatalogTerm: DatabaseProductName: DatabaseProductVersion: DefaultTransactionIsolation: DriverMajorVersion: DriverMinorVersion: DriverName: DriverVersion: ExtraNameCharacters: IdentifierQuoteString: IsCatalogAtStart: MaxBinaryLiteralLength: MaxCatalogNameLength: MaxCharLiteralLength: MaxColumnNameLength:
7 Functions — Alphabetical List NullsAreSortedAtEnd: NullsAreSortedAtStart: NullsAreSortedHigh: NullsAreSortedLow: UsesLocalFilePerTable: UsesLocalFiles: 0 0 0 1 0 1 Display the contents of the Catalogs property of v. v.Catalogs ans = 'D:\matlab\toolbox\database\dbdemos\db1' 'D:\matlab\toolbox\database\dbdemos\origtutorial' 'D:\matlab\toolbox\database\dbdemos\tutorial' 'D:\matlab\toolbox\database\dbdemos\tutorial1' Close the connection.
get • Database connection object, which is created using database • Cursor object, which is created using exec or fetch • Driver object, which is created using driver • Database metadata object, which is created using dmd • Drivermanager object, which is created using drivermanager • Resultset object, which is created using resultset • Resultset metadata object, which is created using rsmd For a list of properties for each object, see “Retrieving Object Properties Using the get Function” on page 6-70.
7 Functions — Alphabetical List getdatasources Return names of ODBC and JDBC data sources on system Syntax d = getdatasources Description d = getdatasources returns the names of valid ODBC and JDBC data sources on the system as a cell array d of strings. The function gets the names of ODBC data sources from the ODBC.INI file located in the folder returned by running: myODBCdir = getenv('WINDIR') d is empty when the ODBC.INI file is valid, but no data sources are defined. d equals -1 when the ODBC.
getdatasources See Also database | get | setdbprefs 7-159
7 Functions — Alphabetical List importedkeys Return information about imported foreign keys Syntax i = importedkeys(dbmeta, 'cata', 'sch') i = importedkeys(dbmeta, 'cata', 'sch', 'tab') Description i = importedkeys(dbmeta, 'cata', 'sch') returns foreign imported key information, that is, information about fields that reference primary keys in other tables, in the schema sch, of the catalog cata, for the database whose database metadata object is dbmeta.
importedkeys Column Description Value 1 Catalog containing primary key, referenced by foreign imported key orcl 2 Schema containing primary key, referenced by foreign imported key SCOTT 3 Table containing primary key, referenced by foreign imported key DEPT 4 Column name of primary key, referenced by foreign imported key DEPTNO 5 Catalog that has foreign imported key orcl 6 Schema that has foreign imported key SCOTT 7 Table that has foreign imported key EMP 8 Foreign key column nam
7 Functions — Alphabetical List See Also crossreference | dmd | exportedkeys | get | primarykeys 7-162
indexinfo indexinfo Return indices and statistics for database tables Syntax x = indexinfo(dbmeta, 'cata', 'sch', 'tab') Description x = indexinfo(dbmeta, 'cata', 'sch', 'tab') returns indices and statistics for the table tab, in the schema sch, of the catalog cata, for the database whose database metadata object is dbmeta. Examples Get index and statistics information for the table DEPT in the schema SCOTT of the catalog orcl, for dbmeta.
7 Functions — Alphabetical List Column Description Value 4 Not unique: 0 if index values can be not unique, 1 otherwise 0 5 Index catalog null 6 Index name null 7 Index type 0 8 Column sequence number within index 0 9 Column name null 10 Column sort sequence null 11 Number of rows in the index table or number of unique values in the index 4 12 Number of pages used for the table or number of 1 pages used for the current index 13 Filter condition null For details about the in
insert insert Add MATLAB data to database tables To export MATLAB data into a database, use these functions: insert, datainsert, and fastinsert. For maximum performance, use datainsert. To use datainsert, ensure that your input data is a cell array or numeric matrix with a specific format. When your input data is a structure, dataset array, or table, or you are using a native ODBC database connection, use fastinsert.
7 Functions — Alphabetical List • stockNumber • supplierNumber • unitCost • productDescription conn = database.ODBCConnection('dbtoolboxdemo','admin','admin'); Select and display the data from the productTable table. curs = exec(conn,'select * from productTable'); curs = fetch(curs); curs.
insert data_table = productNumber ------------11 stockNumber ----------400565 supplierNumber -------------1010 unitCost -------10 productDescription -----------------'Rubik's Cube' Insert the table data into productTable. tablename = 'productTable'; insert(conn,tablename,colnames,data_table) Display the data from productTable again. curs = exec(conn,'select * from productTable'); curs = fetch(curs); curs.
7 Functions — Alphabetical List Select and display the data from the yearlySales table. curs = exec(conn,'select * from yearlySales'); curs = fetch(curs); curs.Data ans = Month --------'January' 'Feb' salesTotal ---------130 25 Revenue ------1200 250 Store the column names of yearlySales in a cell array. colnames = {'Month','salesTotal','Revenue'}; Store the data for the insert in a cell array, data. The data contains Month equal to 'March', salesTotal equal to $50, and Revenue equal to $2000.
insert Close the database connection. close(conn) Input Arguments conn — Database connection database connection object Database connection, specified as a database connection object created using database. tablename — Database table name string Database table name, specified as a string denoting the name of a table in your database.
7 Functions — Alphabetical List More About Tips • When working with a JDBC driver connection or a JDBC/ODBC bridge connection established using database, fastinsert offers improved performance over insert. insert creates and executes an SQL insert query for each row of data. fastinsert creates the insert query only once and then allows for the data values to be plugged in. All rows of data get inserted as a batch resulting in an overall faster performance over insert.
isconnection isconnection Determine if database connections are valid Syntax a = isconnection(conn) Description a = isconnection(conn) returns 1 if the database connection conn is valid, or returns 0 otherwise. Examples Check if the database connection conn is valid.
7 Functions — Alphabetical List isdriver Detect whether driver is valid JDBC driver object Syntax a = isdriver(d) Description a = isdriver(d) returns 1 if d is a valid JDBC driver object. It returns 0 otherwise. Examples Check if d is a valid JDBC driver object.
isjdbc isjdbc Detect whether driver is JDBC compliant Syntax a = isjdbc(d) Description a = isjdbc(d) returns 1 if the driver object d is JDBC compliant. It returns 0 otherwise. Examples Verify whether the database driver object d is JDBC compliant.
7 Functions — Alphabetical List isnullcolumn Determine if last record read in resultset is NULL Syntax a = isnullcolumn(rset) Description a = isnullcolumn(rset) returns 1 if the last record read in the resultset rset is NULL. It returns 0 otherwise. Examples Example 1 — Result Is Not NULL isnullcolumn returns not null. 1 Run: curs = fetch(curs,1); rset = resultset(curs); isnullcolumn(rset) ans = 0 2 Verify this result. curs.Data ans = [1400] Example 2 — Result Is NULL isnullcolumn returns null.
isnullcolumn 1 Run: curs = fetch(curs,1); rset = resultset(curs); isnullcolumn(rset) ans = 1 2 Verify this result. curs.
7 Functions — Alphabetical List isreadonly Determine if database connection is read only Syntax a = isreadonly(conn) Description a = isreadonly(conn) returns 1 if the database connection conn is read only. It returns 0 otherwise. Examples Check whether conn is read only. a = isreadonly(conn) The result indicates that the database connection conn is read only: a = 1 Therefore, you cannot run fastinsert, insert, or update functions on this database.
isurl isurl Detect whether database URL is valid Syntax a = isurl(d, 's') Description a = isurl(d, 's') returns 1 if the database URL s for the driver object d is valid. It returns 0 otherwise. The URL s is of the form jdbc:odbc:name or name. Examples Check whether the database URL jdbc:odbc:thin:@144.212.123.24:1822: is valid for driver object d. a = isurl(d, 'jdbc:odbc:thin:@144.212.123.24:1822:') a = 1 This indicates that the database URL is valid for d.
7 Functions — Alphabetical List logintimeout Set or get time allowed to establish database connection Syntax timeout timeout timeout timeout = = = = logintimeout('driver', time) logintimeout(time) logintimeout('driver') logintimeout Description timeout = logintimeout('driver', time) sets the amount of time, in seconds, for a MATLAB session to connect to a database via a given JDBC driver. Use logintimeout before running the database function.
logintimeout Note: Apple Mac OS platforms do not support logintimeout. Examples Example 1 — Get Timeout Value for ODBC Connection View the current connection timeout value. logintimeout ans = 0 This indicates that you have not specified a timeout value. Example 2 — Set Timeout Value for ODBC Connection Set the timeout value to 5 seconds.
7 Functions — Alphabetical List logintimeout('oracle.jdbc.driver.
namecolumn namecolumn Map resultset column name to resultset column index Syntax x = namecolumn(rset, n) Description x = namecolumn(rset, n) maps a resultset column name n to its resultset column index. rset is the resultset and n is a string or cell array of strings containing the column names. Examples 1 Get the indices for the column names DNAME and LOC resultset object rset. x = x = namecolumn(rset, {'DNAME';'LOC'}) 2 3 The results show that DNAME is column 2 and LOC is column 3.
7 Functions — Alphabetical List ping Retrieve status information about database connection Syntax ping(conn) Description ping(conn) retrieves the status of the database connection conn. Examples Retrieve Status of an ODBC Connection Create an Oracle connection using an ODBC driver. For example, the following code assumes you are connecting a data source named dbname with user name username and password pwd. conn = database(dbname,username,pwd); Retrieve the status of the Oracle connection.
ping ping returns the database name, database version, JDBC driver name, JDBC driver version, maximum number of database connection allowed, user name for the current connection, and the database URL. The last field denotes if the current database connection allows automatic commit of transactions. Close the connection. close(conn) Retrieve Status of an JDBC Connection Create a Microsoft SQL Server connection using a JDBC driver.
7 Functions — Alphabetical List close(conn) Input Arguments conn — Database connection database connection object Database connection, specified as a database connection object created using database. More About Tips • When you use a connection object that is already closed in the ping function, the function returns the following error: Invalid connection. Create another connection to your database and try the ping function again.
primarykeys primarykeys Get primary key information for database table or schema Syntax k = primarykeys(dbmeta, 'cata', 'sch') k = primarykeys(dbmeta, 'cata', 'sch', 'tab') Description k = primarykeys(dbmeta, 'cata', 'sch') returns primary key information for all tables in the schema sch, of the catalog cata, for the database whose database metadata object is dbmeta.
7 Functions — Alphabetical List The results show the primary key information as described in the following table.
procedurecolumns procedurecolumns Get stored procedure parameters and result columns of catalogs Syntax pc = procedurecolumns(dbmeta, 'cata', 'sch') pc = procedurecolumns(dbmeta, 'cata') Description pc = procedurecolumns(dbmeta, 'cata', 'sch') returns the stored procedure parameters and result columns for the schema sch, of the catalog cata, for the database whose database metadata object is dbmeta.
7 Functions — Alphabetical List The results show stored procedure parameter and result information. Because two rows of data are returned, there are two columns of data in the results. The results show that running the stored procedure display returns the Month and Day columns.
procedurecolumns Following is a full description of the procedurecolumns results for the first row (Month).
7 Functions — Alphabetical List procedures Get stored procedures for catalogs Syntax p = procedures(dbmeta, 'cata') p = procedures(dbmeta, 'cata', 'sch') Description p = procedures(dbmeta, 'cata') returns stored procedures in the catalog cata for the database whose database metadata object is dbmeta. p = procedures(dbmeta, 'cata', 'sch') returns the stored procedures in the schema sch, of the catalog cata, for the database whose database metadata object is dbmeta.
procedures curs = exec(conn,'sp_customer_list'); curs = fetch(curs) curs = Attributes: [] Data: {10x2 cell} DatabaseObject: [1x1 database] RowLimit: 0 SQLQuery: 'sp_customer_list' Message: [] Type: 'Database Cursor Object' ResultSet: [1x1 sun.jdbc.odbc.JdbcOdbcResultSet] Cursor: ... [1x1 com.mathworks.toolbox.database.sqlExec] Statement: [1x1 sun.jdbc.odbc.JdbcOdbcStatement] Fetch: ... [1x1 com.mathworks.toolbox.database.fetchTheData] View the results: curs.
7 Functions — Alphabetical List querybuilder Start Visual Query Builder GUI to import and export data Compatibility The querybuilder function will be removed in a future release. Use Database Explorer instead. Syntax querybuilder Description querybuilder starts Visual Query Builder (VQB), which is the Database Toolbox GUI. Tip To populate the VQB Schema and Catalog fields, you must associate your user name with schemas or catalogs before starting VQB.
querytimeout querytimeout Get time specified for SQL queries to succeed Syntax timeout = querytimeout(curs) Description timeout = querytimeout(curs) returns the amount of time, in seconds, allowed for SQL queries of the open cursor curs to succeed. If a given query cannot complete in the specified time, the toolbox stops trying to perform the query. The database administrator defines timeout values. If the timeout value is zero, queries must complete immediately.
7 Functions — Alphabetical List register Load database driver Syntax register(d) Description register(d) loads the database driver object d. Use unregister to unload the driver. Although database automatically loads a driver, register allows you to use get to view properties of the driver before connecting to the database. The register function also allows you to run drivermanager with set and get on properties for loaded drivers. Examples 1 register(d) loads the database driver object d.
resultset resultset Construct resultset object Syntax rset = resultset(curs) Description rset = resultset(curs) creates a resultset object rset for the cursor curs. To get properties of rset, create a resultset metadata object using rsmd, or make calls to rset using applications based on Oracle Java. Run clearwarnings, isnullcolumn, and namecolumn on rset. Use close to close the resultset, which frees up resources. Examples Construct a resultset object rset.
7 Functions — Alphabetical List rollback Undo database changes Syntax rollback(conn) Description rollback(conn) reverses changes made to a database using fastinsert, insert, or update via the database connection conn. The rollback function reverses all changes made since the last commit or rollback operation. To use rollback, the AutoCommit flag for conn must be off. Note: rollback does not roll back data in MySQL databases if the database engine is not InnoDB.
rollback See Also commit | database | fastinsert | get | insert | update 7-197
7 Functions — Alphabetical List rows Return number of rows in fetched data set Syntax numrows = rows(curs) Description numrows = rows(curs) returns the number of rows in the fetched data set curs. Examples Return the Number of Rows in the Cursor After executing an SQL statement, return the number of rows in the database cursor object generated by fetch. Establish connection conn to a MySQL database with user name username and password pwd.
rows Return the number of rows in the Data property of curs. numrows = rows(curs) numrows = 5 Display the rows of data in the Data property of curs. curs.Data ans = [2] [4] [1] [5] [3] [400314] [400339] [400345] [400455] [400999] [1002] [1008] [1001] [1005] [1009] [ 9] [21] [14] [ 3] [17] 'Painting Set' 'Space Cruiser' 'Building Blocks' 'Tin Soldier' 'Slinky' Close the connection.
7 Functions — Alphabetical List rsmd Construct resultset metadata object Syntax rsmeta = rsmd(rset) Description rsmeta = rsmd(rset) creates a resultset metadata object rsmeta, for the resultset object rset. Get properties of rsmeta using get or make calls to rsmeta using applications that are based on Oracle Java. Examples Create a resultset metadata object rsmeta. rsmeta=rsmd(rset) rsmeta = Handle: [1x1 sun.jdbc.odbc.JdbcOdbcResultSetMetaData] Use v = get(rsmeta) and v.
runsqlscript runsqlscript Run SQL script on database Syntax results = runsqlscript(connect,sqlfilename) results = runsqlscript(connect,sqlfilename,Name,Value) Description results = runsqlscript(connect,sqlfilename) runs the SQL commands in the file sqlfilename on the connected database, and returns a cursor array. results = runsqlscript(connect,sqlfilename,Name,Value) uses additional options specified by one or more Name,Value pairs.
7 Functions — Alphabetical List 1x2 array of cursor objects The SQL script has two queries, and returns two results when executed. Display the results for the second query. results(2) ans = Attributes: Data: DatabaseObject: RowLimit: SQLQuery: Message: Type: ResultSet: Cursor: Statement: Fetch: [] {4x6 cell} [1x1 database] 0 [1x309 char] '' 'Database Cursor Object' [1x1 sun.jdbc.odbc.JdbcOdbcResultSet] [1x1 com.mathworks.toolbox.database.sqlExec] [1x1 sun.jdbc.odbc.JdbcOdbcStatement] [1x1 com.mathworks.
runsqlscript To get the file of SQL commands, navigate to \toolbox\database\dbdemos \compare_sales.sql in your MATLAB root folder, or copy and paste the path into your current working folder. Create the connection object to the data source, dbtoolboxdemo. conn = database('dbtoolboxdemo','',''); User names and passwords are not required for this connection. Alternatively, you can use the native ODBC interface for an ODBC connection. For details, see database. Run the SQL script, compare_sales.
7 Functions — Alphabetical List close(res2) close(conn) Run SQL Script to Fetch Data in Batches Run SQL commands from a file on a connected data source with automated batching. Use this method to avoid Java heap memory issues when the SQL script returns a large amount of data. To get the file of SQL commands, navigate to \toolbox\database\dbdemos \compare_sales.sql in your MATLAB root folder, or copy and paste the path into your current working folder.
runsqlscript results = runsqlscript(conn, 'compare_sales.sql') results = 1x2 array of cursor objects Batching occurs internally within fetch, in that it fetches in increments of two rows at a time. The batching preferences are applied to all the queries in the SQL script. • “Configuring a Driver and Data Source” on page 2-13 Input Arguments connect — Database connection connection object Database connection, specified as a connection object.
7 Functions — Alphabetical List importing large amounts of data. Retrieving data in increments helps reduce overall retrieval time. Example: 'rowInc',5 Data Types: double 'QTimeOut' — Query timeout 0 implies unlimited time (default) | positive scalar Query timeout (in seconds), specified as the comma-separated pair consisting of 'QTimeOut' and a positive scalar value.
runsqlscript • An SQL script containing more than 25,000 characters causes runsqlscript to return an error. More About Batch One or more SQL statements terminated by either a semicolon or the keyword GO. Tips • Any values assigned to rowInc or QTimeOut apply to all queries in the SQL script. For example, if rowInc is set to 5, then all queries in the script return at most five rows in their respective resultsets. • You can set preferences for the resultsets using setdbprefs.
7 Functions — Alphabetical List runstoredprocedure Call stored procedure with and without input and output arguments This function calls a stored procedure that has no input arguments, no output arguments, or any combination of input and output arguments. Define and instantiate this stored procedure in your database. You can use this function if you connect to your database using a JDBC driver or the ODBC/JDBC bridge. For details, see “Connecting to a Database”.
runstoredprocedure Examples Call a Stored Procedure Without Input and Output Arguments Define a stored procedure named create_table that creates a table named test_table by executing this code. This procedure has no input or output arguments. This code assumes you are using a Microsoft SQL Server database. CREATE PROCEDURE create_table AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements.
7 Functions — Alphabetical List close(conn) Call a Stored Procedure with Input Arguments Define a stored procedure named insert_data that inserts a category description into a table named test_create by executing this code. This procedure has one input argument data. This code assumes you are using a Microsoft SQL Server database. CREATE PROCEDURE insert_data @data varchar(50) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements.
runstoredprocedure Close the database connection conn. close(conn) Call a Stored Procedure with Output Arguments Define a stored procedure named maxDecVolume that selects the maximum sales volume in December by executing this code. This procedure has one output argument data and no input arguments. This code assumes you are using a Microsoft SQL Server database.
7 Functions — Alphabetical List results{1} ans = 35000 The maximum sales volume in December is 35,000. Close the database connection conn. close(conn) Call a Stored Procedure with Input and Output Arguments Define a stored procedure named getSuppCount that counts the number of suppliers for a specified city by executing this code. This procedure has one input argument cityName and one output argument suppCount. This code assumes you are using a Microsoft SQL Server database.
runstoredprocedure [1x1 java.math.BigDecimal] results is a cell array that contains the supplier count as a Java decimal data type. Display the value in results. results{1} ans = 6.0000 There are six suppliers in New York. Close the database connection conn.
7 Functions — Alphabetical List GO Create a Microsoft SQL Server database connection conn using the JDBC driver. For details, see “Connecting to a Database”. Then, call the stored procedure using: • Database connection conn • Stored procedure productsWithinUnitCost • Input arguments inputargs to specify a unit cost between 19 and 21 • Output Java data types outputtypes to specify numeric and string data types for product number and description inputargs = {19,21}; outputtypes = {java.sql.Types.
runstoredprocedure Here, the narrow unit cost range returns only one product. If the unit cost range is wider, then more than one product might satisfy this condition. To return a data set with numerous products, use exec and fetch to call this stored procedure. Otherwise, runstoredprocedure returns only the last row in the data set. Close the database connection conn.
7 Functions — Alphabetical List Example: {java.sql.Types.NUMERIC} Data Types: cell Output Arguments results — Stored procedure results logical | cell array Stored procedure results, returned as a logical or cell array. runstoredprocedure returns a logical 1 when calling the stored procedure returns a data set. Otherwise, runstoredprocedure returns a logical 0. If the stored procedure returns a data set, use exec and fetch to call the stored procedure and retrieve the data set.
set set Set properties for database, cursor, or drivermanager object Syntax set(object, 'property', value) set(object) Description set(object, 'property', value) sets the value of property to value for the specified object. set(object) displays all properties for object.
7 Functions — Alphabetical List Database Connection Objects The allowable values for property and value for a database connection object appear in the following table. Property Value Description 'AutoCommit' 'on' Database data is written and automatically committed when you run fastinsert, insert, or exec. You cannot use rollback to reverse this process. 'off' Database data is not committed automatically when you run fastinsert, insert, or update. Use rollback to reverse this process.
set Property Value Description set, fetch behaves differently depending on what type of database you are using. Drivermanager Objects The allowable property and value for a drivermanager object appear in the following table. Property Value Description 'LoginTimeout' positive integer Sets the logintimeout value for all loaded database drivers.
7 Functions — Alphabetical List SQLQuery: Message: Type: ResultSet: Cursor: Statement: Fetch: 'select * from EMP' [] 'Database Cursor Object' [1x1 oracle.jdbc.driver.OracleResultSet] [1x1 com.mathworks.toolbox.database.sqlExec] [1x1 oracle.jdbc.driver.OracleStatement] [1x1 com.mathworks.toolbox.database.fetchTheData] The RowLimit property of curs is 5 and the Data property is 5x8 cell, indicating that fetch returned five rows of data.
set Example 2 — Set the AutoCommit Flag to On This example shows what happens when you run a database update function on a database whose AutoCommit flag is set to on. 1 Determine the status of the AutoCommit flag for the database connection conn. get(conn, 'AutoCommit') ans = off The flag is off. 2 Set the flag status to on and verify its value. set(conn, 'AutoCommit', 'on'); get(conn, 'AutoCommit') ans = on 3 Insert a cell array exdata into column names colnames in the table Growth.
7 Functions — Alphabetical List Example 4 — Set the AutoCommit Flag to Off and Roll Back Data This example runs update to insert data into a database whose AutoCommit flag is off. It then uses rollback to roll back the data. 1 Set the AutoCommit flag to off for database connection conn. set(conn, 'AutoCommit', 'off'); 2 Update the data in colnames in the Avg_Freight_Weight table, for the record selected by whereclause, with data from the cell array exdata.
setdbprefs setdbprefs Set preferences for retrieval format, errors, NULLs, and more Syntax setdbprefs v = setdbprefs setdbprefs(property) setdbprefs(property,value) setdbprefs(s) Description setdbprefs returns current values for database preferences. v = setdbprefs returns current values for database preferences to the structure v. setdbprefs(property) returns the current value for the specified property. setdbprefs(property,value) sets the specified property to value.
7 Functions — Alphabetical List NullNumberRead: NullNumberWrite: NullStringRead: NullStringWrite: JDBCDataSourceFile: UseRegistryForSources: TempDirForRegistryOutput: DefaultRowPreFetch: FetchInBatches: FetchBatchSize: '0' 'NaN' 'null' 'null' 'C:\hold_x\jdbcConfig_test.mat' 'yes' 'C:\Work' '10000' 'no' '1000' For details about what each property and value mean, see “Setting Database Preferences Using the setdbprefs Function” on page 6-75. Change a Preference Set a database preference to another value.
setdbprefs conn = database('MySQL','username','pwd'); Alternatively, you can use the native ODBC interface for an ODBC connection. For details, see the database function. Import data into the MATLAB workspace. curs = exec(conn,... 'select productnumber,productdescription from producttable'); curs = fetch(curs,3); curs.Data ans = [9] [8] [7] 'Victorian Doll' 'Train Set' 'Engine Kit' Resulting data displays as a cell array. Change the data return format from cellarray to numeric.
7 Functions — Alphabetical List setdbprefs('DataReturnFormat','structure') Import data into the MATLAB workspace. curs = exec(conn,... 'select productnumber,productdescription from producttable'); curs = fetch(curs,3); curs.Data ans = productnumber: [3x1 double] productdescription: {3x1 cell} Resulting data displays as a structure. View the contents of the structure curs.Data to see the data. curs.Data.productdescription curs.Data.
setdbprefs conn = database('MySQL','username','pwd'); Specify NaN for the NullNumberWrite format. setdbprefs('NullNumberWrite','NaN') Numbers represented as NaN in the MATLAB workspace are exported to databases as NULL. Select data in the table inventoryTable. curs = exec(conn,'select * from inventoryTable'); curs = fetch(curs); curs.Data ans = ... [14] [15] [16] [2000] [1200] [1400] [19.1000] [20.3000] [34.3000] '2014-10-22 10:52...' '2014-10-22 10:52...' '1999-12-31 00:00...
7 Functions — Alphabetical List [24] [ NaN] [ 30] '2014-10-22 11:19...' Close the connection. close(conn) Specify Error Handling Settings Changing the error handling database preferences affects the display of errors in MATLAB. Specify the store format for the ErrorHandling preference. setdbprefs('ErrorHandling','store') With the ErrorHandling property set to store, errors generated by running database or exec are stored in the Message field of the returned connection or cursor object.
setdbprefs Specify the report format for the ErrorHandling preference. setdbprefs('ErrorHandling','report') With the ErrorHandling property set to report, errors generated by running database or exec appear immediately in the Command Window. Fetch data from a closed cursor. curs = exec(conn,'select productdescription from producttable'); close(curs) curs = fetch(curs,3); Error using cursor/fetch>errorhandling (line 491) Invalid fetch cursor. Error in cursor/fetch (line 460) errorhandling(outCursor.
7 Functions — Alphabetical List Statement: 0 Fetch: 0 The error appears in the cursor object Message field. Furthermore, the Data field contains empty handles because no attributes could be created. If the ErrorHandling property is set to store, the Data field contains 0. Close the connection. close(conn) Change Multiple Settings Change multiple database preference simultaneously using setdbprefs. Specify that NULL strings are read from the database into a MATLAB matrix of doubles as 'NaN'.
setdbprefs setdbprefs DataReturnFormat: ErrorHandling: NullNumberRead: NullNumberWrite: NullStringRead: NullStringWrite: JDBCDataSourceFile: UseRegistryForSources: TempDirForRegistryOutput: DefaultRowPreFetch: FetchInBatches: FetchBatchSize: 'numeric' 'store' '0' 'NaN' 'null' 'null' '' 'yes' 'C:\Work' '10000' 'no' '1000' Return Values to a Structure Capture all preferences and their values in a structure. Assign values for all preferences to s.
7 Functions — Alphabetical List ans = yes Save Preferences You can save your preferences to a MAT-file to use them in future MATLAB sessions. Suppose you want to reuse preferences that you set for fetching large data. Assign the preferences to the variable FetchLargeData and save them to a MAT-file FetchLargeDataPrefs in your current folder. FetchLargeData = setdbprefs; save FetchLargeDataPrefs.mat FetchLargeData Later, load the data and restore the preferences. load FetchLargeDataPrefs.
setdbprefs Database preference value, specified as a string to denote a value for a particular database preference property. To set multiple database preferences, enter the preference values in a cell array and match the order with the corresponding preferences in the property argument. For the complete list of allowable values, see “Setting Database Preferences Using the setdbprefs Function” on page 6-75.
7 Functions — Alphabetical List sql2native Convert JDBC SQL grammar to SQL grammar native to system Syntax n = sql2native(conn, 'sqlquery') Description n = sql2native(conn, 'sqlquery') converts the SQL statement string sqlquery from JDBC SQL grammar into the database system's native SQL grammar for the connection conn. The native SQL statement is assigned to n.
supports supports Detect whether property is supported by database metadata object Syntax a = supports(dbmeta) a = supports(dbmeta, 'property') Description a = supports(dbmeta) returns a structure that contains the properties of dbmeta and its property values, 1 or 0. A value of 1 indicates that the property is supported, and 0 indicates that the property is not supported. a = supports(dbmeta, 'property') returns 1 or 0 for the property field of dbmeta.
7 Functions — Alphabetical List See Also database | dmd | get | ping 7-236
tableprivileges tableprivileges Return database table privileges Syntax tp = tableprivileges(dbmeta, 'cata') tp = tableprivileges(dbmeta, 'cata', 'sch') tp = tableprivileges(dbmeta, 'cata', 'sch', 'tab') Description tp = tableprivileges(dbmeta, 'cata') returns a list of table privileges for all tables in the catalog cata for the database whose database metadata object is dbmeta resulting from a database connection object.
7 Functions — Alphabetical List tables Return database table names Syntax t = tables(dbmeta, 'cata') t = tables(dbmeta, 'cata', 'sch') Description t = tables(dbmeta, 'cata') returns a list of tables and table types in the catalog cata for the database whose database metadata object is dbmeta resulting from a database connection object.
tables See Also attr | bestrowid | dmd | indexinfo | tableprivileges 7-239
7 Functions — Alphabetical List unregister Unload database driver Syntax unregister(d) Description unregister(d) unloads the database driver object d, freeing up system resources. If you do not unload a registered driver, it automatically unloads when you end your MATLAB session. Examples unregister(d) unloads the database driver object d.
update update Replace data in database table with MATLAB data Syntax update(conn,tablename,colnames,data,whereclause) Description update(conn,tablename,colnames,data,whereclause) exports the MATLAB variable data in its current format into the database table tablename using the database connection conn. Existing records in the database table are replaced as specified by the SQL whereclause command. Examples Update an Existing Record Create a database connection conn using the dbtoolboxdemo data source.
7 Functions — Alphabetical List curs.Data ans = [ 1] [ 2] [ 3] ... [1700] [1200] [ 356] [14.5000] [ 9] [ 17] '2014-09-23 09:38...' '2014-07-08 22:50...' '2014-05-14 07:14...' Define a cell array containing the column name that you are updating called Quantity. colnames = {'Quantity'}; Define a cell array containing the new data 2000. data = {2000}; Update the column Quantity in the inventoryTable for the product with productNumber equal to 1.
update Close the database connection. close(conn) Update Multiple Records with Multiple Conditions Create a database connection conn using the dbtoolboxdemo data source. This database contains the table inventoryTable that contains these columns: • productNumber • Quantity • Price • inventoryDate conn = database('dbtoolboxdemo','',''); Import all data from the inventoryTable using conn. Store the data in a cell array contained in the cursor object property curs.Data.
7 Functions — Alphabetical List Update the column Quantity in the inventoryTable for the products with product numbers equal to 5 and 8. Create a cell array whereclause that contains two WHERE clauses for both products. tablename = 'inventoryTable'; whereclause = {'where productNumber = 5';'where productNumber = 8'}; update(conn,tablename,colnames,data,whereclause) Fetch the data again and view the updated contents in inventoryTable.
update • inventoryDate conn = database('dbtoolboxdemo','',''); Import all data from inventoryTable using conn. Store the data in a cell array contained in the cursor object property curs.Data. Display the data from inventoryTable in this property. curs = exec(conn,'select * from inventoryTable'); curs = fetch(curs); curs.Data ans = ... [ 5] [ 6] [ 7] [ 8] ... [9000] [4540] [6034] [8350] [ [ [ [ 3] 8] 16] 5] '2012-09-14 '2013-12-25 '2014-08-06 '2011-06-18 15:00...' 19:45...' 08:38...' 11:45...
7 Functions — Alphabetical List curs.Data ans = ... [ 5] [ 6] [ 7] [ 8] ... [10000] [ 4540] [ 6034] [ 9000] [ 5.5000] [ 8] [ 16] [ 10] '2012-09-14 '2013-12-25 '2014-08-06 '2011-06-18 15:00...' 19:45...' 08:38...' 11:45...' The product with the product number equal to 5 has an updated quantity of 10000 units and price equal to 5.50. The product with the product number equal to 8 has an updated quantity of 9000 units and price equal to 10. After finishing with the cursor object, close it.
update ans = [ 1] [ 2] [ 3] ... [ 1700] [ 1200] [ 356] [14.5000] [ 9.3000] [17.2000] '2014-10-20 00:00...' '2014-10-20 00:00...' '2014-10-20 00:00...' Define a cell array for the new price of the first product. data(1,1) = {30.00}; Define the WHERE clause for the first product. whereclause = 'where productNumber = 1'; Update the Price column in the inventoryTable for the first product.
7 Functions — Alphabetical List curs = fetch(curs); curs.Data ans = [ 1] [ 2] [ 3] ... [ 1700] [ 1200] [ 356] [14.5000] [ 9.3000] [17.2000] '2014-10-20 00:00...' '2014-10-20 00:00...' '2014-10-20 00:00...' The first product has the old price of 14.50. After finishing with the cursor object, close it. close(curs) Close the database connection.
update data — Update data cell array | numeric matrix | structure Update data, specified as a MATLAB variable with cell array, numeric matrix, or structure format. If data is a structure, field names in the structure must match field names in colnames. Data Types: double | struct | cell whereclause — SQL WHERE clause string | cell array SQL WHERE clause, specified as a string for one condition or a cell array of strings for multiple conditions. Example: 'WHERE productTable.
7 Functions — Alphabetical List • Running an update operation that matches the one that you just ran can cause this error message to appear. ??? Error using ==> database.
versioncolumns versioncolumns Automatically update table columns Syntax vl = versioncolumns(dbmeta, 'cata') vl = versioncolumns(dbmeta, 'cata', 'sch') vl = versioncolumns(dbmeta, 'cata', 'sch', 'tab') Description vl = versioncolumns(dbmeta, 'cata') returns a list of columns that automatically update when a row value updates in the catalog cata, in the database whose database metadata object is dbmeta resulting from a database connection object.
7 Functions — Alphabetical List See Also columns | dmd | get 7-252
width width Return field size of column in fetched data set Syntax colsize = width(cursor, colnum) Description colsize = width(cursor, colnum) returns the field size of the specified column number colnum in the fetched data set curs. Examples Get the width of the first column of the fetched data set, curs: colsize = width(curs, 1) colsize = 11 The field size of column one is 11 characters (bytes).
