Fetching Metadata into a dbExpress Dataset

From RAD Studio
Jump to: navigation, search

Go Up to Accessing dbExpress Schema Information


To populate a unidirectional dataset with metadata from the database server, you must first indicate what data you want to see, using the SetSchemaInfo method. SetSchemaInfo takes three parameters:

  • The type of schema information (metadata) you want to fetch. This can be a list of tables (stTables), a list of system tables (stSysTables), a list of stored procedures (stProcedures), a list of fields in a table (stColumns), a list of indexes (stIndexes), or a list of parameters used by a stored procedure (stProcedureParams). Each type of information uses a different set of fields to describe the items in the list. For details on the structures of these datasets, see The structure of metadata datasets.
  • If you are fetching information about fields, indexes, or stored procedure parameters, the name of the table or stored procedure to which they apply. If you are fetching any other type of schema information, this parameter is nil.
  • A pattern that must be matched for every name returned. This pattern is an SQL pattern such as 'Cust%', which uses the wildcards '%' (to match a string of arbitrary characters of any length) and '_' (to match a single arbitrary character). To use a literal percent or underscore in a pattern, the character is doubled (%% or __). If you do not want to use a pattern, this parameter can be nil.

If you are fetching schema information about tables (stTables), the resulting schema information can describe ordinary tables, system tables, views, and/or synonyms, depending on the value of the SQL connection's TableScope property.

The following call requests a table listing all system tables (server tables that contain metadata): 

SQLDataSet1.SetSchemaInfo(stSysTable, "", "");

SQLDataSet1->SetSchemaInfo(stSysTable, "", "");

When you open the dataset after this call to SetSchemaInfo, the resulting dataset has a record for each table, with columns giving the table name, type, schema name, and so on. If the server does not use system tables to store metadata (for example MySQL), when you open the dataset it contains no records.

The previous example used only the first parameter. Suppose, Instead, you want to obtain a list of input parameters for a stored procedure named 'MyProc'. Suppose, further, that the person who wrote that stored procedure named all parameters using a prefix to indicate whether they were input or output parameters ('inName', 'outValue' and so on). You could call SetSchemaInfo as follows: 

SQLDataSet1.SetSchemaInfo(stProcedureParams, "MyProc", "in%");

SQLDataSet1->SetSchemaInfo(stProcedureParams, "MyProc", "in%");

The resulting dataset is a table of input parameters with columns to describe the properties of each parameter.

Fetching data after using the dataset for metadata

There are two ways to return to executing queries or stored procedures with the dataset after a call to SetSchemaInfo:

  • Change the CommandText property, specifying the query, table, or stored procedure from which you want to fetch data.
  • Call SetSchemaInfo, setting the first parameter to stNoSchema. In this case, the dataset reverts to fetching the data specified by the current value of CommandText.

See Also

Retrieved from "https://docwiki.embarcadero.com/RADStudio/Athens/e/index.php?title=Fetching_Metadata_into_a_dbExpress_Dataset&oldid=245358"