Accessing Metadata :: 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: 1
                   AllTablesAreSelectable: 1
    DataDefinitionCausesTransactionCommit: 1
      DataDefinitionIgnoredInTransactions: 0
               DoesMaxRowSizeIncludeBlobs: 0
                                 Catalogs: {4x1 cell}
                         CatalogSeparator: '.'
                              CatalogTerm: 'DATABASE'
                      DatabaseProductName: 'ACCESS'
                   DatabaseProductVersion: '04.00.0000'
              DefaultTransactionIsolation: 2
                       DriverMajorVersion: 2
                       DriverMinorVersion: 1
                               DriverName: [1x31 char]
                            DriverVersion: '2.0001 (04.00.6200)'
                      ExtraNameCharacters: [1x29 char]
                    IdentifierQuoteString: '`'
                         IsCatalogAtStart: 1
                   MaxBinaryLiteralLength: 255
                     MaxCatalogNameLength: 260
                     MaxCharLiteralLength: 255
                      MaxColumnNameLength: 64
                      MaxColumnsInGroupBy: 10
                        MaxColumnsInIndex: 10
                      MaxColumnsInOrderBy: 10
                       MaxColumnsInSelect: 255
                        MaxColumnsInTable: 255
                           MaxConnections: 64
                      MaxCursorNameLength: 64
                           MaxIndexLength: 255
                   MaxProcedureNameLength: 64
                               MaxRowSize: 4052
                      MaxSchemaNameLength: 0
                       MaxStatementLength: 65000
                            MaxStatements: 0
                       MaxTableNameLength: 64
                        MaxTablesInSelect: 16
                        MaxUserNameLength: 0
                         NumericFunctions: [1x73 char]
                            ProcedureTerm: 'QUERY'
                                  Schemas: {}
                               SchemaTerm: ''
                       SearchStringEscape: '\'
                              SQLKeywords: [1x461 char]
                          StringFunctions: [1x91 char]
               StoresLowerCaseIdentifiers: 0
         StoresLowerCaseQuotedIdentifiers: 0
               StoresMixedCaseIdentifiers: 0
         StoresMixedCaseQuotedIdentifiers: 1
               StoresUpperCaseIdentifiers: 0
         StoresUpperCaseQuotedIdentifiers: 0
                          SystemFunctions: ''
                               TableTypes: {13x1 cell}
                        TimeDateFunctions: [1x111 char]
                                 TypeInfo: {16x1 cell}
                                      URL: 'jdbc:odbc:dbtoolboxdemo'
                                 UserName: 'admin'
                    NullPlusNonNullIsNull: 0
                      NullsAreSortedAtEnd: 0
                    NullsAreSortedAtStart: 0
                       NullsAreSortedHigh: 0
                        NullsAreSortedLow: 1
                    UsesLocalFilePerTable: 0
                           UsesLocalFiles: 1

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 {4x1 cell}. To view the actual Catalog information, type

```
v.Catalogs
```

MATLAB returns

ans = 
    'D:\Work\databasetoolboxfiles\Nwind'
    'D:\Work\databasetoolboxfiles\Nwind_orig'
    'D:\Work\databasetoolboxfiles\tutorial'
    'D:\Work\databasetoolboxfiles\tutorial_copy'

For more information about the database metadata properties returned by get, see the methods of the DatabaseMetaData object at
http://java.sun.com/j2se/1.4.2/docs/api/java/sql/DatabaseMetaData.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/j2se/1.4.2/docs/api/java/sql/DatabaseMetaData.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 = 
    'MSysAccessObjects'    'SYSTEM TABLE'
    'MSysIMEXColumns'      'SYSTEM TABLE'
    'MSysIMEXSpecs'        '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 a 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 view properties of the resultset object using get, 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.

Retrieving BINARY or OTHER Java SQL Data Types Performing Driver Functions