Catalogue Command (ODBC Only)
Returns list of Database objects.
tables DATABASE SCHEMA OBJECT_TYPE
DATABASE
Catalog/database name.
SCHEMA
Schema name or Table Owner
OBJECT_TYPE
Type of database object (See TABLE_TYPE below).
Description
Use this command to obtain information about the primary keys for a table. The application must have been logged into a data source using the login command, otherwise the following error message will be displayed:
Error: Not connected to database
To obtain a list of all database objects for the current data source, use the search character "%" as the database name and miss off the remaining parameters. This can be used to return a list of Catalogues (AKA. Databases). If there are no parameters, all the objects for the current Catalogue are returned.
If the parameters are valid, a result set will be returned containing one row as there must be only a single key column that connects two tables.The row contains a number of fields, the actual number may increase, dependent of the ODBC version, but will always start with the following:
Column Name |
Column Number |
Comment |
TABLE_CAT |
1 |
Table catalog name; NULL if not applicable to the data source. |
TABLE_SCHEM |
2 |
Table schema/owner name; NULL if not applicable to the data source. |
TABLE_NAME |
3 |
Table/Object name. |
TABLE_TYPE |
4 |
Table type name; one of the following: "TABLE", "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", "SYNONYM", or a data source–specific type name. The meanings of "ALIAS" and "SYNONYM" are driver-specific. |
REMARKS |
5 |
A description of the table. |
If the parameters are invalid, then an error message "ERROR: Failed to obtain table information" will be returned.
Example
subroutine GetCatalogsAndSchemas(string: CatalogList_SV, string: SchemaList_SV) is
string Catalog_LSV
Schema_LSV
Key_LSV
copy "\n" to CatalogList_SV
copy "\n" to SchemaList_SV
begin guarded
response to start
send "tables %\n" to SQL
response to "SQL>" from SQL
leave block
response to line from SQL
extract from input
take to "~" Catalog_LSV
skip by "~"
take to "~" Schema_LSV
# Remove NULLs and Duplicates #
if (Catalog_LSV != "Null") then
copy "\n" Catalog_LSV "\n" to Key_LSV
if (SearchStringFunction(CatalogList_SV, Key_LSV) = 0) then
append Catalog_LSV "\n" to CatalogList_SV
end if
end if
if (Schema_LSV != "Null") then
copy "\n" Schema_LSV "\n" to Key_LSV
if (SearchStringFunction(SchemaList_SV, Key_LSV) = 0) then
append Schema_LSV "\n" to SchemaList_SV
end if
end if
end
extract from CatalogList_SV
skip 1 # The leading line feed
take to last CatalogList_SV
extract from SchemaList_SV
skip 1 # The leading line feed
take to last SchemaList_SV
