| Database Toolbox | |
Accessing Metadata
In this part of the tutorial, you access information about the database; this information is called the metadata. You use these Database Toolbox functions:
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'
dmd function. Typedbmeta = dmd(conn)
MATLAB returns the handle (identifier) for the metadata object.
dbmeta =
DMDHandle: [1x1 sun.jdbc.odbc.JdbcOdbcDatabaseMetaData]
get command 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
DataDefinitionCausesTransaction: 1
DataDefinitionIgnoredInTransact: 0
DoesMaxRowSizeIncludeBlobs: 0
Catalogs: {[1x46 char]}
CatalogSeparator: '.'
CatalogTerm: 'DATABASE'
DatabaseProductName: 'ACCESS'
DatabaseProductVersion: '03.50.00'
DefaultTransactionIsolation: 2
DriverMajorVersion: 1
DriverMinorVersion: 1001
DriverName: 'JDBC-ODBC Bridge
(odbcjt32.dll)'
DriverVersion: '1.1001 (04.00.4202)'
ExtraNameCharacters: '~@#$%^&*_-+=\}{"';:?/><,'
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: 2096
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
StoresLowerCaseQuotedIdentifier: 0
StoresMixedCaseIdentifiers: 0
StoresMixedCaseQuotedIdentifier: 1
StoresUpperCaseIdentifiers: 0
StoresUpperCaseQuotedIdentifier: 0
SystemFunctions: ''
TableTypes: {4x1 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
You can see much of the information in the list directly, for example, the UserName, which is 'admin'.
Catalogs element is shown as {[1x46 char]}. To view the actual Catalog information, typev.Catalogs
ans =
'D:\matlabr12\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.
supports function. Typea = supports(dbmeta)
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
DataDefinitionAndDataManipulati: 1
DataManipulationTransactionsOnl: 0
DifferentTableCorrelationNames: 0
ExpressionsInOrderBy: 1
ExtendedSQLGrammar: 0
FullOuterJoins: 0
GroupBy: 1
GroupByBeyondSelect: 1
GroupByUnrelated: 0
IntegrityEnhancementFacility: 0
LikeEscapeClause: 0
LimitedOuterJoins: 0
MinimumSQLGrammar: 1
MixedCaseIdentifiers: 0
MixedCaseQuotedIdentifiers: 1
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.
tables function. Typet = 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 other Database Toolbox functions you can perform for the database metadata object, type
help dmd/Contents
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.
| | Exporting Multiple Records from MATLAB | Performing Driver Functions | |