/**********************************************************************
* FILENAME : ListTableKeyStructure.c
*
* DESCRIPTION :
* Example to display primary and foreign key structures within tables.
*
* For a given src table,
* it shows the primary key field details
* it shows foreign keys in other tables that reference primary
* key of the src table
* it shows foreign keys in the src table that refer to the primary
* keys of other tables
*
* ODBC USAGE :
* SQLBindCol to bind columns 3,4,5,7 and 8 of the results sets
* SQLPrimaryKeys to get the primary key field detail on the src table
* Displays results
* SQLFreeStmt to close the cursor a SQLBindCol to rebind column 5
* (from KEY_SEQ to FKTABLE_CAT)
* SQLForeignKeys and SQLFetch to retrieve the foreign keys in other
* tables that reference primary key of the src table
* SQLFreeStmt to close the cursor again
* SQLForeignKeys and SQLFetch to retrieve the foreign keys in the
* src table that refer to the primary keys of other tables
*
* NOTE: Uses the same results set bindings between SQLPrimaryKeys and
* SQLForeignKeys but Column 5 is different and is re-bound.
* SQL_C_SSHORT in SQLPrimaryKeys and SQL_C_CHAR in SQLForeignKeys
*/
#include <stdio.h>
#include <stdlib.h>
#include <sql.h>
#include <sqlext.h>
#include <string.h>
#include "util.c"
#define TAB_LEN SQL_MAX_TABLE_NAME_LEN + 1
#define COL_LEN SQL_MAX_COLUMN_NAME_LEN + 1
int main () {
SQLHENV henv = SQL_NULL_HENV; // Environment
SQLHDBC hdbc = SQL_NULL_HDBC; // Connection handle
SQLHSTMT hstmt = SQL_NULL_HSTMT; // Statement handle
SQLRETURN retcode;
//
// Four tables used in this example TestTBL5,TestTBL6,TestTBL7 and TestTBL8
// Table 5 has a primary key and a foreign key linked to the primary key of
// table 8. Table 6 has primary key and foreign key linked to the primary
// key of table 5. Table 7 has primary key and foreign key linked to the
// primary key of table 5. Table 8 has primary key
//
char strTable[] = "TestTBL5";
UCHAR strPkTable[TAB_LEN]; // Primary key table name
UCHAR strFkTable[TAB_LEN]; // Foreign key table name
UCHAR strFkTabCat[TAB_LEN]; // Foreign Key table catalog
// Column 5 in SQLForeignKey call is char
UCHAR strPkCol[COL_LEN]; // Primary key column
UCHAR strFkCol[COL_LEN]; // Foreign key column
SQLLEN lenPkTable, lenPkCol, lenFkTabCat, lenFkTable, lenFkCol, lenKeySeq;
// Column key sequence (Note: Column 5 in SQLPrimaryKey call is small int) */
SQLSMALLINT iKeySeq;
retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_ENV)", henv, SQL_HANDLE_ENV);
retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
(SQLCHAR *)(void*)SQL_OV_ODBC3, -1);
CHECK_ERROR(retcode, "SQLSetEnvAttr(SQL_ATTR_ODBC_VERSION)",
henv, SQL_HANDLE_ENV);
retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_DBC)", hdbc, SQL_HANDLE_DBC);
retcode = SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)10, 0);
CHECK_ERROR(retcode, "SQLSetConnectAttr(SQL_LOGIN_TIMEOUT)",
hdbc, SQL_HANDLE_DBC);
retcode = SQLConnect(hdbc, (SQLCHAR*) "DATASOURCE",
SQL_NTS, (SQLCHAR*) NULL, 0, NULL, 0);
CHECK_ERROR(retcode, "SQLConnect(DATASOURCE)", hdbc, SQL_HANDLE_DBC);
retcode = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_STMT)",
hstmt, SQL_HANDLE_STMT);
// Bind the columns that describe the primary and foreign keys.
// Ignore the table schema, name, and catalog for this example.
retcode = SQLBindCol(hstmt, 3, SQL_C_CHAR, strPkTable,
TAB_LEN, &lenPkTable);
CHECK_ERROR(retcode, "SQLBindCol(3)", hstmt, SQL_HANDLE_STMT);
retcode = SQLBindCol(hstmt, 4, SQL_C_CHAR, strPkCol,
COL_LEN, &lenPkCol);
CHECK_ERROR(retcode, "SQLBindCol(4)", hstmt, SQL_HANDLE_STMT);
retcode = SQLBindCol(hstmt, 5, SQL_C_SSHORT, &iKeySeq,
TAB_LEN, &lenKeySeq);
CHECK_ERROR(retcode, "SQLBindCol(5)", hstmt, SQL_HANDLE_STMT);
retcode = SQLBindCol(hstmt, 7, SQL_C_CHAR, strFkTable,
TAB_LEN, &lenFkTable);
CHECK_ERROR(retcode, "SQLBindCol(7)", hstmt, SQL_HANDLE_STMT);
retcode = SQLBindCol(hstmt, 8, SQL_C_CHAR, strFkCol,
COL_LEN, &lenFkCol);
CHECK_ERROR(retcode, "SQLBindCol(8)", hstmt, SQL_HANDLE_STMT);
//
// Get primary keys in the TestTBL5 table
//
retcode = SQLPrimaryKeys(hstmt,
NULL, 0, // Catalog name
NULL, 0, // Schema name
strTable, SQL_NTS); // Table name
printf ("\nGet primary key of the %s table\n", strTable);
while ((retcode == SQL_SUCCESS) || (retcode == SQL_SUCCESS_WITH_INFO)) {
// Fetch and display the result set. This will be a list of the
// columns in the primary key of the ORDERS table.
retcode = SQLFetch(hstmt);
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
printf("Table: %s Column: %s Key Seq: %hd \n",
strPkTable, strPkCol, iKeySeq);
}
if (retcode != SQL_NO_DATA) {
extract_error("SQLFetch : A", hstmt, SQL_HANDLE_STMT);
goto exit;
} else {
printf ("...End of Data\n\n");
}
// Close the cursor (the hstmt is still allocated).
retcode = SQLFreeStmt(hstmt, SQL_CLOSE);
// Re-bind column 5 because its different between
// SQLForeignKey() and SQLPrimaryKey()
retcode = SQLBindCol(hstmt, 5, SQL_C_CHAR, &strFkTabCat,
TAB_LEN, &lenFkTabCat);
CHECK_ERROR(retcode, "SQLBindCol(5)", hstmt, SQL_HANDLE_STMT);
//
// Get the foreign keys in the other tables that reference the primary
// key of the TestTBL5 table
//
retcode = SQLForeignKeys(hstmt,
NULL, 0, // Primary catalog
NULL, 0, // Primary schema
strTable, SQL_NTS, // Primary table
NULL, 0, // Foreign catalog
NULL, 0, // Foreign schema
NULL, 0); // Foreign table
printf ("Get foreign keys in other tables that reference primary key");
printf (" of the %s table\n", strTable);
while ((retcode == SQL_SUCCESS) || (retcode == SQL_SUCCESS_WITH_INFO)) {
// Fetch and display the result set. This will be all of the
// foreign keys in other tables that refer to the TestTBL5
// primary key.
retcode = SQLFetch(hstmt);
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
printf("%-s ( %-s ) <-- %-s ( %-s )\n",
strPkTable, strPkCol, strFkTable, strFkCol);
}
// Should finish with SQL_NO_DATA status
if (retcode != SQL_NO_DATA) {
extract_error("SQLFetch : B", hstmt, SQL_HANDLE_STMT);
goto exit;
} else {
printf ("...End of Data\n\n");
}
// Close the cursor (the hstmt is still allocated).
retcode = SQLFreeStmt(hstmt, SQL_CLOSE);
//
// Get the foreign keys in TestTBL5 that refer to the primary keys of
// other tables
//
retcode = SQLForeignKeys(hstmt,
NULL, 0, // Primary catalog
NULL, 0, // Primary schema
NULL, 0, // Primary table
NULL, 0, // Foreign catalog
NULL, 0, // Foreign schema
strTable, SQL_NTS); // Foreign table
printf ("Get foreign keys in the %s table that refer to the primary keys");
printf (" of other tables\n", strTable);
while ((retcode == SQL_SUCCESS) || (retcode == SQL_SUCCESS_WITH_INFO)) {
// Fetch and display the result set. This will be all of the
// primary keys in other tables that are referred to by foreign
// keys in the TestTBL5 table.
retcode = SQLFetch(hstmt);
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
printf("%-s ( %-s )--> %-s ( %-s )\n",
strFkTable, strFkCol, strPkTable, strPkCol);
}
if (retcode != SQL_NO_DATA) {
extract_error("SQLFetch : C", hstmt, SQL_HANDLE_STMT);
goto exit;
} else {
printf ("...End of Data\n\n");
}
exit:
printf ("\nComplete.\n");
// Free handles
// Statement
if (hstmt != SQL_NULL_HSTMT)
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
// Connection
if (hdbc != SQL_NULL_HDBC) {
SQLDisconnect(hdbc);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
}
// Environment
if (henv != SQL_NULL_HENV)
SQLFreeHandle(SQL_HANDLE_ENV, henv);
return 0;
}
See Also
