Querying Metadata (FireDAC)
Go Up to Working with Metadata (FireDAC)
Contents
Using TFDConnection
TFDConnection offers GetXxxxNames simple to use methods returning the database object names list:
- GetCatalogNames - the catalog list;
- GetSchemaNames - the schema list;
- GetTableNames - the table and view list;
- GetFieldNames - the table field list;
- GetKeyFieldNames - the table primary key list;
- GetGeneratorNames - the generator/sequence list;
- GetPackageNames - the package list;
- GetStoredProcNames - the stored procedure list.
These methods are useful when:
- The application needs object names;
- The application does not need additional info about these objects.
For example, GetTableNames method retrieves the table, view and synonym names from the current database and schema. To call it use the code:
FDConnection1.GetTableNames('Northwind', 'dbo', '', Memo1.Lines);
Use the ACatalogName and/or ASchemaName arguments to restrict the returned list to the specified catalog and/or schema. If they are not specified, then all "visible" objects or the objects in the current database/schema will be returned. That depends on the DBMS. Note that some DBMSs (like MS Access) may return errors if the schema/catalog is specified and is not supported by the DBMS.
Additionally, the application may restrict object lists using AScopes argument, based on the object scope. The APattern argument, which is the LIKE search mask, will be applied to object names.
You can find a similar method in the TFDCustomManager class.
See the FireDAC demo for more details: FireDAC\Samples\Comp Layer\TFDConnection\GetFieldNames.
Using TFDMetaInfoQuery
The TFDMetaInfoQuery is the dataset component allowing to query and browse the metadata lists. To do that set the Connection, MetaInfoKind and optionally the CatalogName, SchemaName, BaseObjectName, and ObjectName properties and open the dataset. To get the tables list use the code:
FDMetaInfoQuery1.Connection := FDConnection1;
FDMetaInfoQuery1.MetaInfoKind := mkTables;
FDMetaInfoQuery1.Open;
For a description of the dataset structure see the Metadata Structure topic. If a DBMS does not support some particular metadata type, for example SQL Server does not support mkGenerators, an empty dataset will be returned. The metadata dataset is read-only and cannot be edited.
Metadata caching
The fetched metadata is cached by FireDAC per each connection. That is controlled by including fiMeta into FetchOptions.Cache.
Excluding fiMeta from FetchOptions.Cache does not invalidate the metadata cache. All cached metadata remains in cache until its next usage. To refresh the metadata cache for a specified object or in full, use TFDConnection.RefreshMetadataCache.
Current catalog and schema
To get the current catalog and schema name, use TFDConnection.ConnectionIntf.CurrentCatalog and CurrentSchema. To change the current catalog or schema, assign new values to these properties.
MetaCurCatalog and MetaCurSchema allow you to override the information returned by the database session. For some databases the use of these parameters may be required to specify correct values, because API returns incorrect ones.