Using Functions in the Database Toolbox (Database Toolbox)

Database Toolbox

Accessing Metadata

In this example, you access information about the database, which is called the metadata. You use these Database Toolbox functions:

dmd
get
supports
tables

Connect to the dbtoolboxdemo data source. Type

conn = database('dbtoolboxdemo', '', '')

MATLAB returns information about the database object.

conn =
       Instance: 'dbtoolboxdemo'
       UserName: ''
         Driver: []
            URL: []
    Constructor: [1x1
                  com.mathworks.toolbox.database.databaseConnect]
        Message: []
         Handle: [1x1 sun.jdbc.odbc.JdbcOdbcConnection]
        TimeOut: 0
     AutoCommit: 'on'
           Type: 'Database Object'

To view additional information about the database, you first construct a database metadata object using the dmd function. Type

```
dbmeta = dmd(conn)
```

MATLAB returns the handle (identifier) for the metadata object.

dbmeta =
    DMDHandle: [1x1 sun.jdbc.odbc.JdbcOdbcDatabaseMetaData]

To view a list of properties associated with the database, use the get function for the metadata object you just created, dbmeta.

```
v = get(dbmeta)
```

MATLAB returns a long list of properties associated with the database.

v = 
                 AllProceduresAreCallable: [1x1 logical]
                   AllTablesAreSelectable: [1x1 logical]
    DataDefinitionCausesTransactionCommit: [1x1 logical]
      DataDefinitionIgnoredInTransactions: [1x1 logical]
               DoesMaxRowSizeIncludeBlobs: [1x1 logical]
                                 Catalogs: {1x1 cell}
                         CatalogSeparator: [1x1 char]
                              CatalogTerm: [1x8 char]
                      DatabaseProductName: [1x6 char]
                   DatabaseProductVersion: [1x10 char]
              DefaultTransactionIsolation: [1x1 double]
                       DriverMajorVersion: [1x1 double]
                       DriverMinorVersion: [1x1 double]
                               DriverName: [1x31 char]
                            DriverVersion: [1x19 char]
                      ExtraNameCharacters: [1x29 char]
                    IdentifierQuoteString: [1x1 char]
                         IsCatalogAtStart: [1x1 logical]
                   MaxBinaryLiteralLength: [1x1 double]
                     MaxCatalogNameLength: [1x1 double]
                     MaxCharLiteralLength: [1x1 double]
                      MaxColumnNameLength: [1x1 double]
                      MaxColumnsInGroupBy: [1x1 double]
                        MaxColumnsInIndex: [1x1 double]
                      MaxColumnsInOrderBy: [1x1 double]
                       MaxColumnsInSelect: [1x1 double]
                        MaxColumnsInTable: [1x1 double]
                           MaxConnections: [1x1 double]
                      MaxCursorNameLength: [1x1 double]
                           MaxIndexLength: [1x1 double]
                   MaxProcedureNameLength: [1x1 double]
                               MaxRowSize: [1x1 double]
                      MaxSchemaNameLength: [1x1 double]
                       MaxStatementLength: [1x1 double]
                            MaxStatements: [1x1 double]
                       MaxTableNameLength: [1x1 double]
                        MaxTablesInSelect: [1x1 double]
                        MaxUserNameLength: [1x1 double]
                         NumericFunctions: [1x73 char]
                            ProcedureTerm: [1x5 char]
                                  Schemas: {}
                               SchemaTerm: ''
                       SearchStringEscape: [1x1 char]
                              SQLKeywords: [1x461 char]
                          StringFunctions: [1x91 char]
               StoresLowerCaseIdentifiers: [1x1 logical]
         StoresLowerCaseQuotedIdentifiers: [1x1 logical]
               StoresMixedCaseIdentifiers: [1x1 logical]
         StoresMixedCaseQuotedIdentifiers: [1x1 logical]
               StoresUpperCaseIdentifiers: [1x1 logical]
         StoresUpperCaseQuotedIdentifiers: [1x1 logical]
                          SystemFunctions: ''
                               TableTypes: {4x1 cell}
                        TimeDateFunctions: [1x111 char]
                                 TypeInfo: {16x1 cell}
                                      URL: [1x23 char]
                                 UserName: [1x5 char]
                    NullPlusNonNullIsNull: [1x1 logical]
                      NullsAreSortedAtEnd: [1x1 logical]
                    NullsAreSortedAtStart: [1x1 logical]
                       NullsAreSortedHigh: [1x1 logical]
                        NullsAreSortedLow: [1x1 logical]
                    UsesLocalFilePerTable: [1x1 logical]
                           UsesLocalFiles: [1x1 logical]

You can see much of the information in the list directly, for example, the UserName, which is 'admin'.

Some information is too long to fit in the field's display area and instead the size of the information in the field is reported. For example, the Catalogs element is shown as a {1x1 cell}. To view the actual Catalog information, type

```
v.Catalogs
```

MATLAB returns
- ```
ans = 
    'D:\matlab\toolbox\database\dbdemos\tutorial'
```
For more information about the database metadata properties returned by get, see the methods of the DatabaseMetaData object at
http://java.sun.com/products/jdk/1.2/docs/api/java/sql/
package-summary.html.

To see the properties that this database supports, use the supports function. Type

```
a = supports(dbmeta)
```

MATLAB returns

a =
                          AlterTableWithAddColumn: 1
                         AlterTableWithDropColumn: 1
                              ANSI92EntryLevelSQL: 1
                                    ANSI92FullSQL: 0
                            ANSI92IntermediateSQL: 0
                       CatalogsInDataManipulation: 1
                       CatalogsInIndexDefinitions: 1
                   CatalogsInPrivilegeDefinitions: 0
                         CatalogsInProcedureCalls: 0
                       CatalogsInTableDefinitions: 1
                                   ColumnAliasing: 1
                                          Convert: 1
                                   CoreSQLGrammar: 0
                             CorrelatedSubqueries: 1
    DataDefinitionAndDataManipulationTransactions: 1
                 DataManipulationTransactionsOnly: 0
                   DifferentTableCorrelationNames: 0
                             ExpressionsInOrderBy: 1
                               ExtendedSQLGrammar: 0
                                   FullOuterJoins: 0
                                          GroupBy: 1
                              GroupByBeyondSelect: 1
                                 GroupByUnrelated: 0
                     IntegrityEnhancementFacility: 0
                                 LikeEscapeClause: 0
                                LimitedOuterJoins: 0
                                MinimumSQLGrammar: 1
                             MixedCaseIdentifiers: 1
                       MixedCaseQuotedIdentifiers: 0
                               MultipleResultSets: 0
                             MultipleTransactions: 1
                               NonNullableColumns: 0
                          OpenCursorsAcrossCommit: 0
                        OpenCursorsAcrossRollback: 0
                       OpenStatementsAcrossCommit: 1
                     OpenStatementsAcrossRollback: 1
                                 OrderByUnrelated: 0
                                       OuterJoins: 1
                                 PositionedDelete: 0
                                 PositionedUpdate: 0
                        SchemasInDataManipulation: 0
                        SchemasInIndexDefinitions: 0
                    SchemasInPrivilegeDefinitions: 0
                          SchemasInProcedureCalls: 0
                        SchemasInTableDefinitions: 0
                                  SelectForUpdate: 0
                                 StoredProcedures: 1
                          SubqueriesInComparisons: 1
                               SubqueriesInExists: 1
                                  SubqueriesInIns: 1
                          SubqueriesInQuantifieds: 1
                            TableCorrelationNames: 1
                                     Transactions: 1
                                            Union: 1
                                         UnionAll: 1

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
http://java.sun.com/products/jdk/1.2/docs/api/java/sql/
package-summary.html.

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'             'SYSTEM TABLE'
    'MSysIMEXColumns'      'SYSTEM TABLE'
    'MSysIMEXSpecs'        'SYSTEM TABLE'
    'MSysModules'          'SYSTEM TABLE'
    'MSysModules2'         'SYSTEM TABLE'
    'MSysObjects'          'SYSTEM TABLE'
    'MSysQueries'          'SYSTEM TABLE'
    'MSysRelationships'    'SYSTEM TABLE'
    'inventoryTable'       'TABLE'       
    'productTable'         'TABLE'       
    'salesVolume'          'TABLE'       
    'suppliers'            'TABLE'       
    'yearlySales'          'TABLE'       
    'display'              'VIEW'

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. Some databases do not support all of these functions.

Close the database connection. Type

```
close(conn)
```

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.

Exporting Multiple New Records from MATLAB Performing Driver Functions