Database Toolbox 3

Exchange data with relational databases
The Database Toolbox enables you to use MATLAB data analysis and visualization tools to analyze information stored in databases. Working within the MATLAB environment, you can use structured query language (SQL) commands to read and write data to and from a database and apply simple and advanced conditions to database queries. You can interact with most popular databases from within MATLAB, including Oracle, Sybase, Microsoft SQL Server, and Informix. The toolbox also enables simultaneous access to multiple databases within a single MATLAB session and enables transactions involving large data sets. Using the Visual Query Builder, you can interact with a database without knowing SQL.

KEY FEATURES

Connects MATLAB to ODBC/JDBC-compliant databases, including Oracle, Sybase, Microsoft SQL Server, Microsoft Access, Informix, and Ingres Directly executes SQL queries against a database from within MATLAB and preserves data types during all data import and export activities Lets you import data from one or multiple databases, perform calculations on that data, and then export the data to another database Retrieves large data sets in a single transaction or through multiple transactions by discretely segmenting the data Streamlines data import and export by keeping the database connection open during the MATLAB session Enables you to access and query a database without knowing SQL
The Visual Query Builder GUI (left) lets you quickly access data by automatically generating SQL statements for you. Display options let you create charts (right), graphs, and reports.

Sample Functions

% connect to specified database, specifying username and password connectionA = database (database, user, password) % open cursor and issue SQL statement to select data cursorA = exec(connectionA, select X from Y) % retrieve R rows of data cursorA = fetch(cursorA, R)
Database Access Connect to database
Execute SQL statement and open cursor Get database property Export MATLAB cell array data to database table Replace data in database table with data from MATLAB cell array
Database Cursor Access Get attributes of columns in fetched data set
Import data into MATLAB matrix, cell array, or structure
A set of commands used to import a column of data, X, from table Y.
Database Metadata Construct database metadata object

Get database table names

Importing Data into MATLAB
The Database Toolbox supports standard SQL commands, including where clauses for further defining the data to be imported. The fetch statement can retrieve all data at once or a subset of the selected rows. This process can be automated if you save the commands in an M-file. Once the data is in MATLAB, you can view attributes of the imported data, as well as the data itself, and use MATLAB commands to process the data.
Visual Query Builder Start visual SQL query builder
Exporting Data to a Database
After completing calculations or data manipulation in MATLAB, you can export the results by putting the data into a matrix, cell array, or structure and defining the database column names to which you will be writing. You can add the results as new data or update the original data completely or conditionally. For added security, you can require a commit command in order to write to the database.

Required Products

MATLAB
For demos, application examples, tutorials, user stories, and pricing: Visit www.mathworks.com Contact The MathWorks directly US & Canada 508-647-7000 Benelux France Germany Italy Korea Spain Sweden Switzerland UK +31 (0)+33 (0)14 +49 (0)750 +39 (011) +82 (0)5114 +00 +46 (8)00 +41 (0)+44 (0)200

Related Products

Data Acquisition Toolbox. Acquire and send out data from plug-in data acquisition boards Datafeed Toolbox. Acquire real-time financial data from data service providers Excel Link. Use MATLAB with Microsoft Excel

Platform and System Requirements
For platform and system requirements, visit www.mathworks.com/products/database
Visit www.mathworks.com to obtain contact information for authorized MathWorks representatives in countries throughout Asia Pacific, Latin America, the Middle East, Africa, and the rest of Europe.
Tel: 508.647.7000 info@mathworks.com www.mathworks.com

9566v05 05/04

2004 by The MathWorks, Inc. MATLAB, Simulink, Stateflow, Handle Graphics, and Real-Time Workshop are registered trademarks, and TargetBox is a trademark of The MathWorks, Inc. Other product or brand names are trademarks or registered trademarks of their respective holders.

Menu and dialog box titles New terms and for emphasis Omitted input arguments
Choose the File Options menu. An array is an ordered collection of information.

[c,ia,ib] = union(.)

Italics (.) ellipsis denotes all of the input/output arguments from preceding syntaxes.

Monospace italics

String variables (from a finite list)
sysc = d2c(sysd,'method')
System Requirements (p. 1-2)
Supported platforms, MATLAB versions, databases, drivers, SQL standard, and data types.
Installing the Database Toolbox (p. 1-6) Follow standard installation instructions for MATLAB and toolboxes on your platform. Setting Up a Data Source (p. 1-7) Before connecting to a database, set up the data source, either local or remote for ODBC drivers, or for JDBC drivers.
Starting the Database Toolbox (p. 1-13) Start using functions or the Visual Query Builder.

System Requirements

The Database Toolbox 2.2.1 works with the following systems and applications: Platforms on page 1-2 MATLAB Version on page 1-2 Databases on page 1-3 Drivers on page 1-4 Structured Query Language (SQL) on page 1-4 Data Types on page 1-5

Platforms

The Database Toolbox 2.2.1 runs on all of the platforms that support MATLAB Release 13 and Java. The Database Toolbox 2.2.1 does not run on the Hewlett-Packard 10.2 platform.

MATLAB Version

The Database Toolbox 2.2.1 requires MATLAB Version 6.5 (Release 13) or later. You can see the system requirements for MATLAB online at http://www.mathworks.com/products/sysreq/.

Databases

Your system must have access to an installed database. The Database Toolbox supports import/export of data from any ODBC/JDBC-compliant database management system, including the following. IBM DB2 Informix Ingres Microsoft Access Microsoft SQL Server Oracle Sybase SQL Server Sybase SQL Anywhere If you are upgrading from an earlier version of a database, such as Microsoft SQL Server Version 6.5, to a newer version, there is nothing special you need to do for the Database Toolbox. Just be sure to configure the data sources for the new version of the database application as you did for the original version.
Note We recommend that you do not include spaces in table and column names. Although Access supports the use of spaces in table and column names, most other databases do not. To retrieve data from tables whose names contain spaces, use delimiters around the table name when building the query. For example, some databases use brackets, [ ]. The Visual Query Builder does not allow spaces in table names. Also, be sure not to name columns using the databases reserved words, such as DATE in Microsoft Access, or you will not be able to import or export the data using the Database Toolbox.

Drivers

For Windows platforms, the Database Toolbox supports Open Database Connectivity (ODBC) drivers used with the supported databases. For UNIX and Windows platforms, the Database Toolbox supports Java Database Connectivity (JDBC) drivers. If you use JDBC drivers on a Windows platform, you cannot use the Visual Query Builder. The driver for your database must be installed in order to use the Database Toolbox. Most users (or their database administrators) install the driver when they install the database. Consult your database documentation if you need instructions to install a database driver. If your database does not ship with JDBC drivers, you can download drivers from the Sun JDBC web site, http://industry.java.sun.com/products/jdbc/drivers.
About Drivers for the Database Toolbox
An ODBC driver is a standard PC interface that enables communication between database management systems and SQL-based applications. A JDBC driver is a standard interface that enables communication between Java-based applications and database management systems. The Database Toolbox is a Java-based application. To connect the Database Toolbox to a databases ODBC driver, the toolbox uses a JDBC/ODBC bridge, which is supplied and automatically installed as part of the toolbox. The following illustrates the use of drivers with the Database Toolbox.
Windows platforms UNIX and Windows platforms Database Toolbox Database Toolbox JDBC/ODBC Bridge JDBC Driver ODBC Driver Database

Database

Structured Query Language (SQL)
The Database Toolbox supports American National Standards Institute (ANSI) standard SQL commands.

Data Types

You can import the following data types into MATLAB and export them back to your database: BOOLEAN CHAR DATE DECIMAL DOUBLE FLOAT INTEGER LONGCHAR (This is called the Memo data type in Microsoft Access.) NUMERIC REAL SMALLINT TIME TIMESTAMP TINYINT VARCHAR Any other type of data that is imported is treated as a VARCHAR by MATLAB. If you import a data type that cannot be treated as a VARCHAR, you see an unsupported data message from MATLAB. If you try to export MATLAB data types not on this list, you see a syntax error from the database.
Installing the Database Toolbox

Customized Display of Results in the Report Generator
2 After executing a query, select Report Generator from the Display menu.
The Setup File List dialog box appears.
3 Select $matlabroot\toolbox\database\vqb\databasetlbx.rpt from the
4 To modify the report format, click Edit. Click the Help button in the dialog
box for more information about this and other features of the Report Generator.
5 To view the report, click Report.
The report appears in your systems default Web browser.
This example shows a report of sales volume over 3 months by product stock number. From the report, you can see that sales for item 400876 are 3000 in January, 2400 in February, and 1500 in March.
Fine-Tuning Queries Using Advanced Query Options
Use advanced query options in the Visual Query Builder for Retrieving Unique Occurrences on page 2-27. Retrieving Information That Meets Specified Criteria on page 2-29. Presenting Results in Specified Order on page 2-38. Creating Subqueries for Values from Multiple Tables on page 2-42. Creating Queries for Results from Multiple Tables on page 2-47. Other Features in Advanced Query Options on page 2-51. For more information about advanced query options, select Help in any of the dialog boxes for the options.
Retrieving Unique Occurrences
In the Visual Query Builder Advanced query options, select Distinct to limit results to only unique occurrences. Select All to retrieve all occurrences. For example:
1 Set Preferences; for this example, set Data return format to cellarray
and Read NULL numbers as to NaN.
2 Select the Data source; for this example, dbtoolboxdemo. 3 Select the Tables; for this example, SalesVolume. 4 Select the Fields; for this example, January. 5 Run the query to retrieve all occurrences. a In Advanced query options, select All. b Assign a MATLAB workspace variable; for this example, All. c

Click Execute.

6 Run the query to retrieve only unique occurrences. a In Advanced query options, select Distinct. b Assign a MATLAB workspace variable, for this example, Distinct. c
7 In the Data area, the Workspace variable - Size shows 10x1 for All and 8x1

for Distinct.

8 In the Command Window, type All, Distinct to display the query results.

4 Assign a MATLAB workspace variable, for example, AA. 5 Click Execute.
The results are a 7-by-4 matrix.
6 To view the results, type AA in the Command Window.
Removing Grouping. To remove grouping criteria in the Where Clauses dialog box, in Current clauses, select all of the statements in the group, and then click Ungroup. The parentheses are removed from the statements.
For the above example, to remove the grouping, select

(January > 1000 AND

and then Shift+click to also select
February > 1000 AND March > 1000)
Then click Ungroup. The three statements are no longer grouped.
Presenting Results in Specified Order
By default, the order of the rows in the query results depends on their order in the database, which is effectively random. Use the Order by field in Advanced query options to specify the order in which results appear. This example uses basic_where.qry that was created and saved in the example presented in Retrieving Information That Meets Specified Criteria on page 2-29. This example sorts the results of basic_where.qry, so that January is the primary sort field, February the secondary, and March the last. Results for January and February are ascending, and results for March are descending:
1 Load basic_where.qry. For instructions, see Using a Saved Query on

page 2-11.

2 Set Preferences. For this example, set Data return format to cellarray
3 In Advanced query options, click Order by.
The Order By Clauses dialog box appears.
4 For the Fields whose results you want to specify the order of, specify the
Sort key number and Sort order. For example, specify January as the primary sort field, with results displayed in ascending order.
a From Fields, select January. b For Sort key number, type 1. c
For Sort order, select Ascending.
The Current clauses area now shows

January ASC

5 Specify February as the second sort field, with results displayed in

ascending order.

a From Fields, select February. b For Sort key number, type 2. c

January ASC February ASC

6 Specify March as the third sort field, with results displayed in descending

order.

a From Fields, select March. b For Sort key number, type 3. c
For Sort order, select Descending.
January ASC February ASC March DESC

7 Click OK.

The Order By Clauses dialog box closes. The Order by field and the SQL statement in the Visual Query Builder reflect the order by clause you specified.
8 Assign a MATLAB workspace variable, for example, B. 9 Click Execute. 1 To view the results, type B in the Command Window. Compare these to the 0

OuterJoins: PositionedDelete: PositionedUpdate: SchemasInDataManipulation: SchemasInIndexDefinitions: SchemasInPrivilegeDefinitions: SchemasInProcedureCalls: SchemasInTableDefinitions: SelectForUpdate: StoredProcedures: SubqueriesInComparisons: SubqueriesInExists: SubqueriesInIns: SubqueriesInQuantifieds: TableCorrelationNames: Transactions: Union: UnionAll:
A 1 means the database supports that property, while a 0 means the database does not support that property. For the above example, the GroupBy property has a value of 1, meaning the database supports the SQL group by feature. For more information about the properties supported by the database, see the methods of the DatabaseMetaData object at
6 There are other Database Toolbox functions you can use to access additional
database metadata. For example, to retrieve the names of the tables in a catalog in the database, use the tables function. Type
t = tables(dbmeta, 'tutorial')
where dbmeta is the name of the database metadata object you created for the database using dmd in step 2, and tutorial is the name of the catalog for which you want to retrieve table names. (You retrieved catalog names in step 4.)
MATLAB returns the names and types for each table.
t = 'MSysACEs' 'MSysIMEXColumns' 'MSysIMEXSpecs' 'MSysModules' 'MSysModules2' 'MSysObjects' 'MSysQueries' 'MSysRelationships' 'inventoryTable' 'productTable' 'salesVolume' 'suppliers' 'yearlySales' 'display' 'SYSTEM 'SYSTEM 'SYSTEM 'SYSTEM 'SYSTEM 'SYSTEM 'SYSTEM 'SYSTEM 'TABLE' 'TABLE' 'TABLE' 'TABLE' 'TABLE' 'VIEW' TABLE' TABLE' TABLE' TABLE' TABLE' TABLE' TABLE' TABLE'
Two of these tables were used in the previous example: salesVolume and yearlySales. For a list of all of the database metadata functions, see Database Metadata Object on page 4-5. Some databases do not support all of these functions.
7 Close the database connection. Type
Resultset Metadata Object
Similar to the dmd function are the resultset and rsmd functions. Use resultset to create a resultset object for a cursor object that you created using exec or fetch. You can then get properties of the resultset object, create a resultset metadata object using rsmd and get its properties, or make calls to the resultset object using your own Java-based applications. For more information, see the reference pages for resultset and rsmd, or see the lists of related functions, Resultset Object and Resultset Metadata Object on page 4-6.
Performing Driver Functions
This example demonstrates how to create database driver and drivermanager objects so that you can get and set the object properties. You use these Database Toolbox functions: drivermanager driver get isdriver set
Note There is no equivalent M-file demo to run because the example relies on a specific PC to JDBC connection and database. Your configuration will be different than the one in this example so you cannot run these examples exactly as written. Instead, use values for your own system. See your database administrator for address information

The returned attributes are listed in the following table.
Attribute fieldName typeName typeValue columnWidth precision Description
Name of the column Data type Numerical representation of the data type Size of the field Precision value for floating and double data types; an empty value is returned for strings Precision value for real and numeric data types; an empty value is returned for strings If true, data format is currency If true, the data cannot be overwritten If true, the data can be NULL Error message returned by fetch
currency readOnly nullable Message

Examples

Example 1Get Attributes for One Column
Get the column attributes for the fourth column of a fetched data set.
attr(curs, 4) ans = fieldName: typeName: typeValue: columnWidth: precision: scale: currency: readOnly: nullable: Message: 'Age' 'LONG' [] [] 'false' 'false' 'true' []
Example 2Get Attributes for All Columns
Get the column attributes for curs, and assign them to attributes.
View the attributes of column 4.

attributes(4)

MATLAB returns the attributes of column 4.
ans = fieldName: typeName: typeValue: columnWidth: precision: scale: currency: readOnly: nullable: Message: 'Age' 'LONG' [] [] 'false' 'false' 'true' []

See Also

cols, columnnames, columns, dmd, fetch, get, tables, width

4bestrowid

Get database table unique row identifier
b = bestrowid(dbmeta, 'cata', 'sch') b = bestrowid(dbmeta, 'cata', 'sch', 'tab') b = bestrowid(dbmeta, 'cata', 'sch') determines and returns the optimal set of columns in a table that uniquely identifies a row, in the schema sch, of the catalog cata, for the database whose database metadata object is dbmeta, where dbmeta was created using dmd. b = bestrowid(dbmeta, 'cata', 'sch', 'tab') determines and returns the optimal set of columns that uniquely identifies a row in table tab, in the schema sch, of the catalog cata, for the database whose database metadata object is dbmeta, where dbmeta was created using dmd.
b = bestrowid(dbmeta,'msdb','geck','builds')

b = 'build_id'

In this example: dbmeta is the database metadata object. msdb is the catalog cata. geck is the schema sch. builds is the table tab. The results is build_id, which means that every entry in the build_id column is unique and can be used to identify the row.

Allowable property names and examples of values for a driver object are listed in the following table.
Property 'MajorVersion' 'MinorVersion' Example of Value 1 1001
There are dozens of properties for a database metadata object. Some of the allowable property names and examples of their values are listed in the following table.
Property 'Catalogs' 'DatabaseProductName' 'DatabaseProductVersion' 'DriverName' 'MaxColumnNameLength' 'MaxColumnsInOrderBy' 'URL' 'NullsAreSortedLow' Example of Value {4x1 cell} 'ACCESS' '03.50.0000' 'JDBC-ODBC Bridge (odbcjt32.dll)' 'jdbc:odbc:dbtoolboxdemo' 1
Allowable property names and examples of values for a drivermanager object are listed in the following table.
Property 'Drivers' 'LoginTimeout' 'LogStream' Example of Value {'oracle.jdbc.driver.OracleDriver@1d8e09ef' [1x37 char]} 0 []
Some of the allowable property names for a resultset object and examples of their values are listed in the following table.
Property 'CursorName' 'MetaData' 'Warnings' Example of Value {'SQL_CUR92535700x' {1x2 cell} {[] []} 'SQL_CUR92535700x'}
Allowable property names for a resultset metadata object and examples of values are listed in the following table.
Property 'CatalogName' 'ColumnCount' 'ColumnName' 'ColumnTypeName' 'TableName' 'isNullable' 'isReadOnly' Example of Value {'' 2 {'Calc_Date' {'TEXT' {'' {[1] {[0] ''} [1]} [0]} 'Avg_Cost'} ''}

'LONG'}

The empty strings for CatalogName and TableName indicate that the database does not return these values.
For command line help on get, use the overloaded methods.
help help help help help help help cursor/get database/get dmd/get driver/get drivermanager/get resultset/get rsmd/get
Example 1Get Connection Property, Data Source Name
Connect to the database, SampleDB. Then get the name of the data source for the connection and assign it to v.
conn = database('SampleDB', '', ''); v = get(conn, 'Instance')

v = SampleDB

Example 2Get Connection Property, AutoCommit Flag Status
Determine the status of the AutoCommit flag for the database connection conn.
get(conn, 'AutoCommit') ans = on
Example 3Display Data in Cursor

Display the data in the cursor object, curs by typing

get(curs, 'Data')

or by typing
ans = 'Germany' 'Mexico' 'France' 'Canada'
In this example, curs contains one column with four records.
Example 4Get Database Metadata Object Properties
View the properties of the database metadata object for connection conn. Type
dbmeta = dmd(conn); v = get(dbmeta)
MATLAB returns a list of properties, some of which are shown here.
v = AllProceduresAreCallable: AllTablesAreSelectable: DataDefinitionCausesTransaction: DataDefinitionIgnoredInTransact: DoesMaxRowSizeIncludeBlobs: Catalogs:. NullPlusNonNullIsNull: NullsAreSortedAtEnd: NullsAreSortedAtStart: NullsAreSortedHigh: NullsAreSortedLow: UsesLocalFilePerTable: UsesLocalFiles: 0 {4x1 cell} 1
To view the names of the catalogs in the database, type
MATLAB returns the catalog names
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'
columns, database, dmd, driver, drivermanager, exec, fetch, resultset, rows, rsmd, set

importedkeys

4importedkeys
Get information about imported foreign keys
i = importedkeys(dbmeta, 'cata', 'sch') i = importedkeys(dbmeta, 'cata', 'sch', 'tab') i = importedkeys(dbmeta, 'cata', 'sch') returns the 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, where dbmeta was created using dmd. i = importedkeys(dbmeta, 'cata', 'sch', 'tab') returns the foreign imported key information, that is, information about fields in the table tab, that reference primary keys in other tables, in the schema sch, of the catalog cata, for the database whose database metadata object is dbmeta, where dbmeta was created using dmd.
i = importedkeys(dbmeta,'orcl','SCOTT')
i = Columns 1 through 7 'orcl' 'SCOTT' 'DEPT' 'DEPTNO' Columns 8 through 13 'DEPTNO' '1' 'null' '1'
In this example: dbmeta is the database metadata object. orcl is the catalog cata. SCOTT is the schema sch.
The results show the foreign imported key information as described in the following table.
Catalog containing primary key, referenced by foreign imported key Schema containing primary key, referenced by foreign imported key Table containing primary key, referenced by foreign imported key Column name of primary key, referenced by foreign imported key Catalog that has foreign imported key Schema that has foreign imported key Table that has foreign imported key Foreign key column name, that is the 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 is updated. Delete rule, that is, what happens to the foreign key when the primary key is deleted. Foreign imported key name Primary key name in referenced table

get, resultset

isreadonly

4isreadonly
Detect if database connection is read only
a = isreadonly(conn) a = isreadonly(conn) returns 1 if the database connection conn is read only, or returns 0 otherwise, where conn was created using database.

a = isreadonly(conn)

indicating that the database connection conn is read only. Therefore, you cannot perform insert or update functions for this database.

database, isconnection

4isurl
Detect if the database URL is valid
a = isurl('s', d) a = isurl('s', d) returns 1 if the database URL s, for the driver object d, is valid, or returns 0 otherwise. The URL s is of the form jdbc:odbc:<name> or <name>, and d is the driver object created using driver.
a = isurl('jdbc:odbc:thin:@144.212.123.24:1822:', d)
indicating that the database URL, jdbc:odbc:thin:@144.212.123.24:1822:, is valid for driver object d.
driver, get, isdriver, isjdbc

logintimeout

4logintimeout
Set or get time allowed to establish database connection
timeout timeout timeout timeout = = = = logintimeout('driver', time) logintimeout(time) logintimeout('driver') logintimeout
timeout = logintimeout('driver', time) sets the amount of time, in seconds, allowed for a MATLAB session to try to connect to a database via the specified JDBC driver. Use logintimeout before running the database function. If MATLAB cannot connect within the allowed time, it stops trying. timeout = logintimeout(time) sets the amount of time, in seconds, allowed for a MATLAB session to try to connect to a database via an ODBC connection. Use logintimeout before running the database function. If MATLAB cannot connect within the allowed time, it stops trying. timeout = logintimeout('driver') returns the time, in seconds, you set previously using logintimeout for the JDBC connection specified by driver. A
returned value of zero means that the time-out value has not been set previously; MATLAB stops trying to make a connection if it is not immediately successful.

dmd, exec, get, procedurecolumns

4querybuilder

Start visual SQL query builder
querybuilder querybuilder starts the Visual Query Builder (VQB), an easy to use interface for building and running SQL queries to retrieve data from databases.
For examples of and more information about using the Visual Query Builder, use the VQB Help menu or see Visual Query Builder on page 2-1. You can also get help in any of the Visual Query Builder dialog boxes by clicking the Help button in the dialog box.

querytimeout

4querytimeout
Get time allowed for a database SQL query to succeed
timeout = querytimeout(curs) timeout = querytimeout(curs) returns the amount of time, in seconds, allowed for an SQL query of curs to succeed, where curs is created by running exec. If a query cannot be completed in the allowed time, MATLAB stops trying to perform the exec. The time-out value is defined for a database by the database administrator. If the time-out value is zero, a query must be completed immediately.
Get the current database time-out setting for curs.
querytimeout(curs) ans = 10

Limitations

If a database does not have a database time-out feature, MATLAB returns
[Driver]Driver not capable
The Microsoft Access ODBC driver and Oracle ODBC driver do not support querytimeout.

register

4register

Load database driver

register(d) register(d) loads the database driver object d, which was created using driver. Use unregister to unload the driver.
Although database automatically loads the driver, register allows you to get properties of the driver before connecting. The register function also allows you to use drivermanager to set and get properties for all loaded drivers.
register(d) loads the database driver object d. get(d) returns properties of the driver object.
driver, drivermanager, get, unregister

4resultset

Construct resultset object
rset = resultset(curs) r = resultset(curs) creates a resultset object rset, for the cursor curs, where curs was created using exec or fetch. You can get properties of rset, create a resultset metadata object using rsmd, or make calls to rset using your own Java-based applications. You can also perform other functions on rset: clearwarnings, isnullcolumn, and namecolumn. Use close to close the resultset, which frees up resources.

conn = database('SampleDB', '', ''); curs=exec(conn, 'select all ProductName,UnitsInStock from. Products'); curs=fetch(curs,3); curs.Data ans = 'Chai' [39] 'Chang' [17] 'Aniseed Syrup' [13]
Numeric: Specify the numeric format by typing
setdbprefs ('DataReturnFormat','numeric')
Performing the same set of import functions results in
curs.Data ans = NaN 39 NaN 17 NaN 13
In the database, the values for ProductName are all character strings, as seen in the previous results when DataReturnFormat is set to cellarray. The ProductName values cannot be read when they are imported using the numeric format. Therefore, MATLAB treats them as NULL numbers and assigns them as NaN, which is the current value for the NullNumberRead property of setdbprefs in this example.
Structure: Specify the structure format by typing
setdbprefs ('DataReturnFormat','structure')
curs.Data ans = ProductName: {3x1 cell} UnitsInStock: [3x1 double]
View the contents of the structure to see the data.
curs.Data.ProductName ans = 'Chai' 'Chang' 'Aniseed Syrup' curs.Data.UnitsInStock ans = 13
Example 4Change the ErrorHandling
Store: To specify the store format, type
setdbprefs ('ErrorHandling','store')
which means that any errors from running database or exec are stored in the Message field of the returned connection or cursor object.
The following illustrates an example of trying to fetch from a closed cursor.
conn = database('SampleDB', '', ''); curs=exec(conn, 'select all ProductName from Products'); close(curs) curs=fetch(curs,3); curs= Attributes: [] Data: 0 DatabaseObject: [1x1 database] RowLimit: 0 SQLQuery: 'select all ProductName from Products' Message: 'Error: Invalid cursor' Type: 'Database Cursor Object' ResultSet: 0 Cursor: 0 Statement: [1x1 sun.jdbc.odbc.JdbcOdbcStatement] Fetch: [1x1. com.mathworks.toolbox.database.fetchTheData]
The error indication appears in the Message field.
Report: To specify the report format, type

4versioncolumns
Get automatically updated table columns
vl = versioncolumns(dbmeta, 'cata') vl = versioncolumns(dbmeta, 'cata', 'sch') vl = versioncolumns(dbmeta, 'cata', 'sch', 'tab') vl = versioncolumns(dbmeta, 'cata') returns the list of all columns that are automatically updated when any row value is updated, for the catalog cata, for the database whose database metadata object is dbmeta, where dbmeta was created using dmd. vl = versioncolumns(dbmeta, 'cata', 'sch') returns the list of all columns that are automatically updated when any row value is updated, for the schema sch, in the catalog cata, for the database whose database metadata object is dbmeta, where dbmeta was created using dmd. vl = versioncolumns(dbmeta, 'cata', 'sch', 'tab') returns the list of all columns that are automatically updated when any row value is updated, in the table tab, for the schema sch, in the catalog cata, for the database whose database metadata object is dbmeta, where dbmeta was created using dmd.
vl = versioncolumns(dbmeta,'orcl','SCOTT','BONUS','SAL')

vl = {}

In this example: dbmeta is the database metadata object. orcl is the catalog cata. SCOTT is the schema sch. BONUS is the table tab. SAL is the column name l. The results show an empty set, meaning no columns automatically update when any row value is updates.

columns, dmd, get

4width
Get field size of column in fetched data set
colsize = width(curs, colnum) colsize = width(cursor, colnum) returns the field size of the specified column number colnum, in the fetched data set curs.
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).
attr, cols, columnnames, fetch, get

Symbols

[ ] 3-40 { } 3-40, 3-41 cell arrays assigning values to cells 3-13 Database Toolbox 3-36 for exporting data 3-14 for query results 3-5 celldisp 3-40 Charting dialog box 2-20 data (x, y, z, and color) 2-21 Display 2-22 legend 2-21 preview 2-21 types of charts 2-20 charting query results 2-20 classpath.txt file 1-11 clearing variables from Data area 2-15 clearwarnings 4-13 close 3-10, 3-18, 4-14 cols 3-9, 4-16 ColumnCount 4-47 ColumnName 4-47 columnnames 3-9, 3-20, 4-17 columnprivileges 4-18 columns attributes 3-10 automatically updated 4-109 cross reference 4-24 exported keys 4-36 foreign key information 4-51 imported key information 4-51 names 3-9, 3-14, 4-10, 4-17, 4-20 number 4-16 optimal set to identify row 4-12 primary key information 4-73 privileges 4-18 width 3-9, 4-111 columns 4-20