User manual MATLAB DATABASE TOOLBOX 3

[. . . ] Database ToolboxTM 3 User's Guide How to Contact The MathWorks Web Newsgroup www. mathworks. com/contact_TS. html Technical Support www. mathworks. com comp. soft-sys. matlab suggest@mathworks. com bugs@mathworks. com doc@mathworks. com service@mathworks. com info@mathworks. com Product enhancement suggestions Bug reports Documentation error reports Order status, license renewals, passcodes Sales, pricing, and general information 508-647-7000 (Phone) 508-647-7001 (Fax) The MathWorks, Inc. 3 Apple Hill Drive Natick, MA 01760-2098 For contact information about worldwide offices, see the MathWorks Web site. Database ToolboxTM User's Guide © COPYRIGHT 1998­2010 by The MathWorks, Inc. The software described in this document is furnished under a license agreement. The software may be used or copied only under the terms of the license agreement. [. . . ] Note The MATLAB software session closes open cursors and connections when exiting, but the database might not free up the cursors and connections. Tip For command-line help on close, use the overloaded methods: help database/close help cursor/close help resultset/close 7-7 close Examples Close the cursor curs and the connection conn. close(curs) close(conn) See Also cursor. fetch, database, exec, resultset 7-8 cols Purpose Syntax Description Examples Retrieve number of columns in fetched data set numcols = cols(curs) numcols = cols(curs) returns the number of columns in the fetched data set curs. Display three columns in the fetched data set curs. numcols = cols(curs) numcols = 3 See Also attr, columnnames, columnprivileges, columns, cursor. fetch, get, rows, width 7-9 columnnames Purpose Syntax Description Retrieve names of columns in fetched data set FIELDSTRING = columnnames(CURSOR) FIELDSTRING = columnnames(CURSOR, BCELLARRAY) FIELDSTRING = columnnames(CURSOR) returns the column names of the data selected from a database table. The column names are enclosed in quotes and separated by commas. FIELDSTRING = columnnames(CURSOR, BCELLARRAY) returns the column names as a cell array of strings when BCELLARRAY is set to true. Examples 1 Run a SQL query to return all columns from the Microsoft Access Northwind database employees table: 'select * from employees' 2 Use columnnames to retrieve all column names for the selected columns: fieldString = columnnames(cursor) fieldString = 'EmployeeID', 'LastName', 'FirstName', 'Title', 'TitleOfCourtesy', 'BirthDate', 'HireDate', 'Address', 'City', 'Region', 'PostalCode', 'Country', 'HomePhone', See Also attr, cols, columnprivileges, columns, cursor. fetch, get, width 7-10 columnprivileges Purpose Syntax Description List database column privileges lp = columnprivileges(dbmeta, 'cata', 'sch', 'tab') lp = columnprivileges(dbmeta, 'cata', 'sch', 'tab', 'l') · lp = columnprivileges(dbmeta, 'cata', 'sch', 'tab') returns a list of privileges for: Examples All columns in the table tab In the schema sch In the catalog cata For the database whose database metadata object is dbmeta · lp = columnprivileges(dbmeta, 'cata', 'sch', 'tab', 'l') returns a list of privileges for: column l in the table tab In the schema sch In the catalog cata For the database whose database metadata object is dbmeta 1 Use columnprivileges, passing in the following arguments: · The database metadata object. dbmeta · The catalog msdb · The schema geck · The table builds · The column name build_id lp = columnprivileges(dbmeta, 'msdb', 'geck', 'builds', . . . 'build_id') lp = 'builds' 'build_id' {1x4 cell} 7-11 columnprivileges This result shows: · The table name, builds, in column 1 · The column name, build_id, in column 2 · The column privileges, lp, in column 3 2 View the contents of the third column in lp. lp{1, 3} ans = 'INSERT' 'REFERENCES' 'SELECT' 'UPDATE' See Also cols, columns, columnnames, dmd, get 7-12 columns Purpose Syntax Return database table column names l = columns(dbmeta, 'cata') l = columns(dbmeta, 'cata', 'sch') l = columns(dbmeta, 'cata', 'sch', 'tab') Description · l = columns(dbmeta, 'cata') returns a list of: Examples All column names in the catalog cata For the database whose database metadata object is dbmeta All column names in the schema sch In the catalog cata For the database whose database metadata object is dbmeta · l = columns(dbmeta, 'cata', 'sch') returns a list of: · l = columns(dbmeta, 'cata', 'sch', 'tab') returns a list of columns for: The table tab In the schema sch In the catalog cata For the database whose database metadata object is dbmeta 1 Run columns, passing it the following arguments: · The database metadata object dbmeta · The catalog orcl · The schema schSCOTT l = columns(dbmeta, 'orcl', 'SCOTT') l= 'BONUS' {1x4 cell} 'DEPT' {1x3 cell} 7-13 columns 'EMP' 'SALGRADE' 'TRIAL' {1x8 cell} {1x3 cell} {1x3 cell} The results show the names of the five tables in dbmeta, and cell arrays containing the column names in each table. 2 View the column names for the BONUS table: l{1, 2} ans = 'ENAME' 'JOB' 'SAL' 'COMM' See Also attr, bestrowid, cols, columnnames, columnprivileges, dmd, get, versioncolumns 7-14 commit Purpose Syntax Description Make database changes permanent commit(conn) 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. get(conn, 'AutoCommit') ans = off Example 2: Commit Data to a Database 1 Insert exdata into the columns DEPTNO, DNAME, and LOC in the table DEPT, for the data source conn. fastinsert(conn, 'DEPT', {'DEPTNO';'DNAME';'LOC'}, . . . exdata) 2 Commit this data. commit(conn) See Also database, exec, fastinsert, get, rollback, update 7-15 confds Purpose GUI Alternatives Syntax Description Configure JDBC data source for Visual Query Builder Select Define JDBC data sources from the Visual Query Builder Query menu. confds 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 "Setting Up Data Sources for Use with JDBC Drivers" in the Database Toolbox Getting Started Guide. Tip Use the database function to define JDBC data sources programmatically. 7-16 confds See Also database, querybuilder 7-17 crossreference Purpose Syntax Description Retrieve information about primary and foreign keys f = crossreference(dbmeta, 'pcata', 'psch', 'ptab', 'fcata', 'fsch', 'ftab') 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 · Of the primary catalog pcata The foreign key information is for: · The foreign table ftab · In the foreign schema fsch · Of the foreign catalog fcata Examples Run crossreference to get primary and foreign key information given the following arguments: · The database metadata object. dbmeta · The primary and foreign catalog orcl · The primary and foreign schema SCOTT · The table DEPT that contains the referenced primary key · The table EMP that contains the foreign key f = crossreference(dbmeta, 'orcl', 'SCOTT', 'DEPT', . . . 'orcl', 'SCOTT', 'EMP') f = Columns 1 through 7 7-18 crossreference 'orcl' 'SCOTT' 'SCOTT' 'EMP' Columns 8 through 13 'DEPTNO' '1' 'PK_DEPT' 'DEPT' 'DEPTNO' 'orcl' . . . 'null' '1' 'FK_DEPTNO'. . . The results show the following primary and foreign key information. Column 1 2 3 4 5 6 7 8 Description Catalog that contains primary key, referenced by foreign imported key Schema that contains primary key, referenced by foreign imported key Table that contains primary key, referenced by foreign imported key Column name of primary key, referenced by foreign imported key Catalog that has foreign key Schema that has foreign key Table that has foreign key Foreign key column name that references the primary key in another table Sequence number within foreign key Update rule, that is, what happens to the foreign key when the primary key updates Delete rule, that is, what happens to the foreign key when the primary key is deleted Value orcl SCOTT DEPT DEPTNO orcl SCOTT EMP DEPTNO 9 10 1 null 11 1 7-19 crossreference Column 12 13 Description Foreign imported key name Primary key name in referenced table Value FK_DEPTNO PK_DEPT There is only one foreign key in the schema SCOTT. The table DEPT contains a primary key DEPTNO that is referenced by the field DEPTNO in the table EMP. The field DEPTNO in the table EMP table is a foreign key. Tip For a description of the codes for update and delete rules, see the getCrossReference property on the Sun Java Web site at http://java. sun. com/j2se/1. 4. 2/docs/api/java/sql/ DatabaseMetaData. html. See Also dmd, exportedkeys, get, importedkeys, primarykeys 7-20 cursor. fetch Purpose GUI Alternatives Syntax Description Import data into MATLAB workspace from cursor object created by exec Retrieve data using Visual Query Builder. For more information about Visual Query Builder, see Chapter 4, "Using Visual Query Builder". curs = fetch(curs, RowLimit) curs = fetch(curs) · 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. Use the set function to specifyRowLimit. Data is stored in a MATLAB cell array, structure, or numeric matrix. It is a best practice to assign the object returned by fetch to the variable curs from the open SQL cursor. This practice results in only one open cursor object, which consumes less memory than multiple open cursor objects. [. . . ] {datA, datAA, . . . ; datB, datBB, . . . ; datn, datNN}, . . . {'where col1 = val1'; where col2 = val2'; . . . 'where coln = valn'} Description update(conn, 'tab', colnames, exdata, 'whereclause') exports the MATLAB variable exdata in its current format into the database table tab using the database connection conn. exdata can be a cell array, numeric matrix, or structure. Existing records in the database table are replaced as specified by the SQL whereclause command. Specify column names for tab as strings in the MATLAB cell array colnames. [. . . ]