Catalogue Command (ODBC Only)
Returns meta data about indexes used with a tables.
primarykeys DATABASE SCHEMA TABLE
DATABASE
Catalog/database name.
SCHEMA
Schema name or Table Owner
TABLE
Table name of the table to be processed.
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
Certain database management systems do not support different "Database" (or Catalogue) or "Schema" (or Table Owner), if so, a null string needs to be passed as the first and/or second parameter using the syntax '' (i.e. two single quotes).
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 name; NULL if not applicable to the data source. |
TABLE_NAME |
3 |
Table name. |
COLUMN_NAME |
4 |
Primary key column name. The driver returns an empty string for a column that does not have a name. |
KEY_SEQ |
5 |
Column sequence number in key (starting with 1). |
PK_NAME |
6 |
Primary key name. NULL if not applicable to the data source. |
If the parameters are invalid, then an error message "ERROR: Failed to obtain primary key information" will be returned.
Example
subroutine GetPrimaryKeys(string: TableOwner_SV, string: Table_SV, string: KeyList_SV) is
string Column_LSV
copy "" to KeyList_SV
begin guarded
response to start
send "primarykeys '' " TableOwner_SV " " Table1_SV "\n" to SQL
response to "SQL>" from SQL
leave block
response to line from SQL
extract from input
skip by "~" # Catalog
skip by "~" # Schema
skip by "~" # Table
take to "~" Column_LSV
append Column_LSV "\n" to KeyList_SV
end
