Call Level Interface Guide and Reference

SQLForeignKeys - Get the List of Foreign Key Columns

Purpose

Specification: DB2 CLI 2.1 ODBC 1.0

SQLForeignKeys() returns information about foreign keys for the specified table. The information is returned in an SQL result set which can be processed using the same functions that are used to retrieve a result generated by a query.

Syntax

SQLRETURN   SQLForeignKeys   (
                SQLHSTMT          StatementHandle,   /* hstmt */
                SQLCHAR      *FAR PKCatalogName,     /* szPkCatalogName */
                SQLSMALLINT       NameLength1,       /* cbPkCatalogName */
                SQLCHAR      *FAR PKSchemaName,      /* szPkSchemaName */
                SQLSMALLINT       NameLength2,       /* cbPkSchemaName */
                SQLCHAR      *FAR PKTableName,       /* szPkTableName */
                SQLSMALLINT       NameLength3,       /* cbPkTableName */
                SQLCHAR      *FAR FKCatalogName,     /* szFkCatalogName */
                SQLSMALLINT       NameLength4,       /* cbFkCatalogName */
                SQLCHAR      *FAR FKSchemaName,      /* szFkSchemaName */
                SQLSMALLINT       NameLength5,       /* cbFkSchemaName */
                SQLCHAR      *FAR FKTableName,       /* szFkTableName */
                SQLSMALLINT       NameLength6);      /* cbFkTableName */

Function Arguments

Table 79. SQLForeignKeys Arguments

Data Type Argument Use Description
SQLHSTMT StatementHandle input Statement handle.
SQLCHAR * PKCatalogName input Catalog qualifier of the primary key table. This must be a NULL pointer or a zero length string.
SQLSMALLINT NameLength1 input Length of PKCatalogName. This must be set to 0.
SQLCHAR * PKSchemaName input Schema qualifier of the primary key table.
SQLSMALLINT NameLength2 input Length of PKSchemaName
SQLCHAR * PKTableName input Name of the table name containing the primary key.
SQLSMALLINT NameLength3 input Length of PKTableName
SQLCHAR * FKCatalogName input Catalog qualifier of the table containing the foreign key. This must be a NULL pointer or a zero length string.
SQLSMALLINT NameLength4 input Length of FKCatalogName. This must be set to 0.
SQLCHAR * FKSchemaName input Schema qualifier of the table containing the foreign key.
SQLSMALLINT NameLength5 input Length of FKSchemaName
SQLCHAR * FKTableName input Name of the table containing the foreign key.
SQLSMALLINT NameLength6 input Length of FKTableName

Data Type	Argument	Use	Description
SQLHSTMT	StatementHandle	input	Statement handle.
SQLCHAR *	PKCatalogName	input	Catalog qualifier of the primary key table. This must be a NULL pointer or a zero length string.
SQLSMALLINT	NameLength1	input	Length of `PKCatalogName`. This must be set to 0.
SQLCHAR *	PKSchemaName	input	Schema qualifier of the primary key table.
SQLSMALLINT	NameLength2	input	Length of `PKSchemaName`
SQLCHAR *	PKTableName	input	Name of the table name containing the primary key.
SQLSMALLINT	NameLength3	input	Length of `PKTableName`
SQLCHAR *	FKCatalogName	input	Catalog qualifier of the table containing the foreign key. This must be a NULL pointer or a zero length string.
SQLSMALLINT	NameLength4	input	Length of `FKCatalogName`. This must be set to 0.
SQLCHAR *	FKSchemaName	input	Schema qualifier of the table containing the foreign key.
SQLSMALLINT	NameLength5	input	Length of `FKSchemaName`
SQLCHAR *	FKTableName	input	Name of the table containing the foreign key.
SQLSMALLINT	NameLength6	input	Length of `FKTableName`

Usage

If PKTableName contains a table name, and FKTableName is an empty string, SQLForeignKeys() returns a result set containing the primary key of the specified table and all of the foreign keys (in other tables) that refer to it.

If FKTableName contains a table name, and PKTableName is an empty string, SQLForeignKeys() returns a result set containing all of the foreign keys in the specified table and the primary keys (in other tables) to which they refer.

If both PKTableName and FKTableName contain table names, SQLForeignKeys() returns the foreign keys in the table specified in FKTableName that refer to the primary key of the table specified in PKTableName. This should be one key at the most.

If the schema qualifier argument associated with a table name is not specified, then the schema name defaults to the one currently in effect for the current connection.

Table 80 lists the columns of the result set generated by the SQLForeignKeys() call. If the foreign keys associated with a primary key are requested, the result set is ordered by FKTABLE_CAT, FKTABLE_SCHEM, FKTABLE_NAME, and ORDINAL_POSITION. If the primary keys associated with a foreign key are requested, the result set is ordered by PKTABLE_CAT, PKTABLE_SCHEM, PKTABLE_NAME, and ORDINAL_POSITION.

The VARCHAR columns of the catalog functions result set have been declared with a maximum length attribute of 128 to be consistent with SQL92 limits. Since DB2 names are less than 128, the application can choose to always set aside 128 characters (plus the null-terminator) for the output buffer, or alternatively, call SQLGetInfo() with the SQL_MAX_CATALOG_NAME_LEN, SQL_MAX_SCHEMA_NAME_LEN, SQL_MAX_TABLE_NAME_LEN, and SQL_MAX_COLUMN_NAME_LEN to determine respectively the actual lengths of the associated TABLE_CAT, TABLE_SCHEM, TABLE_NAME, and COLUMN_NAME columns supported by the connected DBMS.

Although new columns may be added and the names of the existing columns changed in future releases, the position of the current columns will not change.

Table 80. Columns Returned By SQLForeignKeys

Column Number/Name Data Type Description
1 PKTABLE_CAT VARCHAR(128) This is always NULL.
2 PKTABLE_SCHEM VARCHAR(128) The name of the schema containing PKTABLE_NAME.
3 PKTABLE_NAME VARCHAR(128) not NULL Name of the table containing the primary key.
4 PKCOLUMN_NAME VARCHAR(128) not NULL Primary key column name.
5 FKTABLE_CAT VARCHAR(128) This is always NULL.
6 FKTABLE_SCHEM VARCHAR(128) The name of the schema containing FKTABLE_NAME.
7 FKTABLE_NAME VARCHAR(128) not NULL The name of the table containing the Foreign key.
8 FKCOLUMN_NAME VARCHAR(128) not NULL Foreign key column name.
9 ORDINAL_POSITION SMALLINT not NULL The ordinal position of the column in the key, starting at 1.
10 UPDATE_RULE SMALLINT Action to be applied to the foreign key when the SQL operation is UPDATE:

SQL_RESTRICT
SQL_NO_ACTION

The update rule for IBM DB2 DBMSs is always either RESTRICT or SQL_NO_ACTION. However, ODBC applications may encounter the following UPDATE_RULE values when connected to non-IBM RDBMSs:

SQL_CASCADE
SQL_SET_NULL

11 DELETE_RULE SMALLINT Action to be applied to the foreign key when the SQL operation is DELETE:

SQL_CASCADE
SQL_NO_ACTION
SQL_RESTRICT
SQL_SET_DEFAULT
SQL_SET_NULL

12 FK_NAME VARCHAR(128) Foreign key identifier. NULL if not applicable to the data source.
13 PK_NAME VARCHAR(128) Primary key identifier. NULL if not applicable to the data source.
14 DEFERRABILITY SMALLINT One of:

SQL_INITIALLY_DEFERRED
SQL_INITIALLY_IMMEDIATE
SQL_NOT_DEFERRABLE

Note: The column names used by DB2 CLI follow the X/Open CLI CAE specification style. The column types, contents and order are identical to those defined for the SQLForeignKeys() result set in ODBC.

Return Codes

SQL_SUCCESS
SQL_SUCCESS_WITH_INFO
SQL_STILL_EXECUTING
SQL_ERROR
SQL_INVALID_HANDLE

Diagnostics

Table 81. SQLForeignKeys SQLSTATEs

SQLSTATE Description Explanation
24000 Invalid cursor state. A cursor is already opened on the statement handle.
40003 08S01 Communication link failure. The communication link between the application and data source failed before the function completed.
HY001 Memory allocation failure. DB2 CLI is unable to allocate memory required to support execution or completion of the function.
HY009 Invalid argument value. The arguments PKTableName and FKTableName were both NULL pointers.
HY010 Function sequence error.
The function was called while in a data-at-execute (SQLParamData(), SQLPutData()) operation.
The function was called while within a BEGIN COMPOUND and END COMPOUND SQL operation.
An asynchronously executing function (not this one) was called for the StatementHandle and was still executing when this function was called.
HY014 No more handles. DB2 CLI was unable to allocate a handle due to internal resources.
HY090 Invalid string or buffer length. The value of one of the name length arguments was less than 0, but not equal SQL_NTS.
The length of the table or owner name is greater than the maximum length supported by the server. Refer to SQLGetInfo - Get General Information.
HYC00 Driver not capable. DB2 CLI does not support catalog as a qualifier for table name.
HYT00 Timeout expired. The timeout period expired before the data source returned the result set. Timeouts are only supported on non-multitasking systems such as Windows 3.1 and Macintosh System 7. The timeout period can be set using the SQL_ATTR_QUERY_TIMEOUT attribute for SQLSetConnectAttr().

SQLSTATE	Description	Explanation
24000	Invalid cursor state.	A cursor is already opened on the statement handle.
40003 08S01	Communication link failure.	The communication link between the application and data source failed before the function completed.
HY001	Memory allocation failure.	DB2 CLI is unable to allocate memory required to support execution or completion of the function.
HY009	Invalid argument value.	The arguments `PKTableName` and `FKTableName` were both NULL pointers.
HY010	Function sequence error.	The function was called while in a data-at-execute (`SQLParamData()`, `SQLPutData()`) operation. The function was called while within a BEGIN COMPOUND and END COMPOUND SQL operation. An asynchronously executing function (not this one) was called for the `StatementHandle` and was still executing when this function was called.
HY014	No more handles.	DB2 CLI was unable to allocate a handle due to internal resources.
HY090	Invalid string or buffer length.	The value of one of the name length arguments was less than 0, but not equal SQL_NTS. The length of the table or owner name is greater than the maximum length supported by the server. Refer to SQLGetInfo - Get General Information.
HYC00	Driver not capable.	DB2 CLI does not support `catalog` as a qualifier for table name.
HYT00	Timeout expired.	The timeout period expired before the data source returned the result set. Timeouts are only supported on non-multitasking systems such as Windows 3.1 and Macintosh System 7. The timeout period can be set using the SQL_ATTR_QUERY_TIMEOUT attribute for `SQLSetConnectAttr()`.

Restrictions

None.

CLI Sample tbconstr.c

(The complete sample tbconstr.c is also available here .)

 
/* From the CLI sample tbconstr.c */
/* ... */
 
    /* fetch each row, and display */
    printf("    Fetch each row and display.\n");   
    sqlrc = SQLFetch( hstmt );

References

[ Top of Page | Previous Page | Next Page ]