Obtaining Metadata

All database connection components can retrieve lists of metadata on the database server, although they vary in the types of metadata they retrieve. The methods that retrieve metadata fill a string list with the names of various entities available on the server. You can then use this information, for example, to let your users dynamically select a table at runtime.

You can use a TADOConnection component to retrieve metadata about the tables and stored procedures available on the ADO data store. You can then use this information, for example, to let your users dynamically select a table or stored procedure at runtime.

Listing available tables

The GetTableNames method copies a list of table names to an already-existing string list object. This can be used, for example, to fill a list box with table names that the user can then use to choose a table to open. The following line fills a listbox with the names of all tables on the database:

Copy Code

MyDBConnection.GetTableNames(ListBox1.Items, False);

Copy Code

MyDBConnection->GetTableNames(ListBox1->Items, false);

GetTableNames has two parameters: the string list to fill with table names, and a boolean that indicates whether the list should include system tables, or ordinary tables. Note that not all servers use system tables to store metadata, so asking for system tables may result in an empty list.

Note: For most database connection components, GetTableNames returns a list of all available non-system tables when the second parameter is False. For TSQLConnection, however, you have more control over what type is added to the list when you are not fetching only the names of system tables. When using TSQLConnection, the types of names added to the list are controlled by the TableScope property. TableScope indicates whether the list should contain any or all of the following: ordinary tables, system tables, synonyms, and views.

Listing the fields in a table

The GetFieldNames method fills an existing string list with the names of all fields (columns) in a specified table. GetFieldNames takes two parameters, the name of the table for which you want to list the fields, and an existing string list to be filled with field names:

Copy Code

MyDBConnection.GetFieldNames('Employee', ListBox1.Items);

Copy Code

MyDBConnection->GetTableNames("Employee", ListBox1->Items);

Listing available stored procedures

To get a listing of all of the stored procedures contained in the database, use the GetProcedureNames method. This method takes a single parameter: an already-existing string list to fill:

Copy Code

MyDBConnection.GetProcedureNames(ListBox1.Items);

Copy Code

MyDBConnection->GetProcedureNames(ListBox1->Items);

Note: GetProcedureNames is only available for TADOConnection and TSQLConnection.

Listing available indexes

To get a listing of all indexes defined for a specific table, use the GetIndexNames method. This method takes two parameters: the table whose indexes you want, and an already-existing string list to fill:

Copy Code

SQLConnection1.GetIndexNames('Employee', ListBox1.Items);

Copy Code

MyDBConnection1->GetIndexNames("Employee", ListBox1->Items);

Note: GetIndexNames is only available for TSQLConnection, although most table-type datasets have an equivalent method.

Listing stored procedure parameters

To get a list of all parameters defined for a specific stored procedure, use the GetProcedureParams method. GetProcedureParams fills a TList object with pointers to parameter description records, where each record describes a parameter of a specified stored procedure, including its name, index, parameter type, field type, and so on.

GetProcedureParams takes two parameters: the name of the stored procedure, and an already-existing TList object to fill:

Copy Code

SQLConnection1.GetProcedureParams('GetInterestRate', List1);

Copy Code

MyDBConnection1->GetIndexNames("GetInterestRate", List1);

To convert the parameter descriptions that are added to the list into the more familiar TParams object, call the global LoadParamListItems procedure. Because GetProcedureParams dynamically allocates the individual records, your application must free them when it is finished with the information. The global FreeProcParams routine can do this for you.

Note: GetProcedureParams is only available for TSQLConnection.