/*******************************************************************************
**
** Source File Name = browser.c 1.4
**
** Licensed Materials - Property of IBM
**
** (C) COPYRIGHT International Business Machines Corp. 1995, 1999
** All Rights Reserved.
**
** US Government Users Restricted Rights - Use, duplication or
** disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
**
**
** PURPOSE :
** Prompts for a Schema and table search pattern string.
** Display a list of tables that macth the search pattern.
** Allows the user to select a varitey of catalog information,
** for each of the tables.
**
** For more information about these samples see the README file.
**
** For more information on programming in CLI see the:
** - "Building CLI Applications" section of the Application Building Guide, and the
** - CLI Guide and Reference.
**
** For more information on the SQL language see the SQL Reference.
**
*******************************************************************************/
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlcli1.h>
#include "samputil.h" /* Header file for CLI sample code */
/* For the Macintosh environment when generating 68K applications */
#ifdef DB268K
/* Need to include ASLM for 68K applications */
#include <LibraryManager.h>
#endif
#define MAX_STMT_LEN 255
#ifdef DB2WIN
#define MAX_TABLES 50
#else
#define MAX_TABLES 255
#endif
SQLRETURN init_tables( SQLHANDLE ) ;
SQLRETURN print_tables(void) ;
SQLRETURN list_table_privileges( SQLHANDLE, SQLCHAR *, SQLCHAR * ) ;
SQLRETURN list_columns( SQLHANDLE, SQLCHAR *, SQLCHAR * ) ;
SQLRETURN list_primary_keys( SQLHANDLE, SQLCHAR *, SQLCHAR * ) ;
SQLRETURN list_index_columns( SQLHANDLE, SQLCHAR *, SQLCHAR * ) ;
SQLRETURN list_column_privileges( SQLHANDLE, SQLCHAR *, SQLCHAR * ) ;
SQLRETURN list_foreign_keys( SQLHANDLE, SQLCHAR *, SQLCHAR * ) ;
SQLRETURN list_stats( SQLHANDLE, SQLCHAR *, SQLCHAR * ) ;
/*
Global Variables for user id and password.
To keep samples simple, not a recommended practice.
*/
extern SQLCHAR server[SQL_MAX_DSN_LENGTH + 1] ;
extern SQLCHAR uid[MAX_UID_LENGTH + 1] ;
extern SQLCHAR pwd[MAX_PWD_LENGTH + 1] ;
/* Global Table structure */
typedef struct {
SQLINTEGER schem_l ;
SQLCHAR schem[129] ;
SQLINTEGER name_l ;
SQLCHAR name[129] ;
SQLINTEGER type_l ;
SQLCHAR type[129] ;
SQLINTEGER remarks_l ;
SQLCHAR remarks[255] ;
} table_info ;
table_info table[MAX_TABLES] ;
SQLSMALLINT num_tables ;
/*******************************************************************
** main
** - initialize
** - terminate
*******************************************************************/
int main( int argc, char * argv[] ) {
SQLHANDLE henv, hdbc, hstmt ;
SQLRETURN rc ;
SQLINTEGER tnum;
int state ;
char line[256];
char action;
/* For the Macintosh environment when generating 68K applications */
#ifdef DB268K
/* Before making any API calls for 68K environment, need to initialize the
Library Manager */
InitLibraryManager(0,kCurrentZone,kNormalMemory);
atexit(CleanupLibraryManager);
#endif
/* macro to initalize server, uid and pwd */
INIT_UID_PWD ;
/* allocate an environment handle */
rc = SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv ) ;
if ( rc != SQL_SUCCESS ) return( terminate( henv, rc ) ) ;
/* allocate a connect handle, and connect */
rc = DBconnect( henv, &hdbc ) ;
if ( rc != SQL_SUCCESS ) return( terminate( henv, rc ) ) ;
rc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt ) ;
CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;
if ( init_tables( hstmt ) != SQL_SUCCESS) exit( -1 ) ;
rc = SQLEndTran( SQL_HANDLE_DBC, hdbc, SQL_COMMIT ) ;
CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;
state = 0 ;
while ( state == 0 ) {
rc = print_tables();
printf("Enter a table Number and an action:"
"(n [Q | C | P | I | F | T |O | L]) \n");
printf(
"|Q=Quit C=cols P=Primary Key I=Index F=Foreign Key |\n"
"|T=Tab Priv O=Col Priv S=Stats L=List Tables |\n");
fgets(line, 255, stdin);
if (sscanf(line, "%i %c", &tnum, &action) == 2) {
switch ( action ) {
case 'q' : case 'Q' : state = 1; break;
case 'c' : case 'C' : {
rc = list_columns( hstmt, table[tnum-1].schem,
table[tnum-1].name);
state = 0;
break;
}
case 'p' : case 'P' : {
rc = list_primary_keys( hstmt, table[tnum-1].schem,
table[tnum-1].name);
state = 0;
break;
}
case 't' : case 'T' : {
rc = list_table_privileges( hstmt, table[tnum-1].schem,
table[tnum-1].name);
state = 0;
break;
}
case 'o' : case 'O' : {
rc = list_column_privileges( hstmt, table[tnum-1].schem,
table[tnum-1].name);
state = 0;
break;
}
case 'i' : case 'I' : {
rc = list_index_columns( hstmt, table[tnum-1].schem,
table[tnum-1].name);
state = 0;
break;
}
case 'f' : case 'F' : {
rc = list_foreign_keys( hstmt, table[tnum-1].schem,
table[tnum-1].name);
state = 0;
break;
}
case 's' : case 'S' : {
rc = list_stats( hstmt, table[tnum-1].schem,
table[tnum-1].name);
state = 0;
break;
}
case 'l' : case 'L' : {
rc = init_tables( hstmt );
state = 0;
break;
}
default :
{ printf(">> Hit Enter to Continue<<\n");
fgets(line, 255, stdin);
break;
}
} /* switch statement */
} /* if statement */
if ((rc = sscanf(line, "%c", &action)) == 1)
{ switch ( action ) {
case 'q' : case 'Q' : state = 1; break;
case 'l' : case 'L' : {
rc = init_tables( hstmt );
state = 0;
break;
}
default :
{ printf(">> Hit Enter to Continue<<\n");
fgets(line, 255, stdin);
break;
}
}
}
}
rc = SQLEndTran( SQL_HANDLE_DBC, hdbc, SQL_COMMIT ) ;
CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;
rc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
printf( "\n>Disconnecting .....\n" ) ;
rc = SQLDisconnect( hdbc ) ;
CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;
rc = SQLFreeHandle( SQL_HANDLE_DBC, hdbc ) ;
CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;
rc = SQLFreeHandle( SQL_HANDLE_ENV, henv ) ;
if ( rc != SQL_SUCCESS ) return( terminate( henv, rc ) ) ;
return( SQL_SUCCESS ) ;
} /* end main */
/**************************************************************************/
/* init_tables */
/* - prompts for schema and table search patterns, initializes array */
/* */
/**************************************************************************/
/*--> SQLL1X50.SCRIPT */
SQLRETURN init_tables( SQLHANDLE hstmt ) {
SQLRETURN rc ;
SQLUSMALLINT rowstat[MAX_TABLES];
SQLUINTEGER pcrow;
/* SQL_ROWSET_SIZE sets the max number of result rows to fetch each time */
rc = SQLSetStmtAttr( hstmt,
SQL_ATTR_ROW_ARRAY_SIZE,
( SQLPOINTER ) MAX_TABLES,
0
) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
/* Set Size of One row, Used for Row-Wise Binding Only */
rc = SQLSetStmtAttr( hstmt,
SQL_ATTR_ROW_BIND_TYPE,
( SQLPOINTER ) sizeof( table_info ),
0
) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLSetStmtAttr( hstmt,
SQL_ATTR_ROW_STATUS_PTR,
( SQLPOINTER ) rowstat,
0
) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLSetStmtAttr( hstmt,
SQL_ATTR_ROWS_FETCHED_PTR,
( SQLPOINTER ) &pcrow,
0
) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
printf("Enter Search Pattern for Table Schema Name:\n");
gets((char *)table->schem);
printf("Enter Search Pattern for Table Name:\n");
gets((char *)table->name);
rc = SQLTables(hstmt, NULL, 0, table->schem, SQL_NTS,
table->name, SQL_NTS, NULL, 0);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindCol(hstmt, 2, SQL_C_CHAR, (SQLPOINTER) table[0].schem, 129,
&table[0].schem_l);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindCol(hstmt, 3, SQL_C_CHAR, (SQLPOINTER) table[0].name, 129,
&table[0].name_l);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindCol(hstmt, 4, SQL_C_CHAR, (SQLPOINTER) table[0].type, 129,
&table[0].type_l);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindCol(hstmt, 5, SQL_C_CHAR, (SQLPOINTER) table[0].remarks, 255,
&table[0].remarks_l);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
/* Now fetch the result set */
/*<-- */
/* Fetch up to MAX_TABLES into table array */
rc = SQLFetchScroll( hstmt, SQL_FETCH_NEXT, 0 ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
num_tables = pcrow; /* Keep track of the number of valid rows in table */
/* free statement resources */
rc = SQLSetStmtAttr( hstmt,
SQL_ATTR_ROW_ARRAY_SIZE,
( SQLPOINTER ) 1,
0
) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLSetStmtAttr( hstmt,
SQL_ATTR_ROW_BIND_TYPE,
( SQLPOINTER ) NULL,
0
) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLSetStmtAttr( hstmt,
SQL_ATTR_ROW_STATUS_PTR,
( SQLPOINTER ) NULL,
0
) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLSetStmtAttr( hstmt,
SQL_ATTR_ROWS_FETCHED_PTR,
( SQLPOINTER ) NULL,
0
) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLFreeStmt( hstmt, SQL_UNBIND ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLFreeStmt( hstmt, SQL_RESET_PARAMS ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLFreeStmt( hstmt, SQL_CLOSE ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
return( rc ) ;
} /* end init_tables */
SQLRETURN print_tables() {
SQLSMALLINT tnum ;
printf("### TABLE SCHEMA TABLE_NAME TABLE_TYPE\n");
printf(" ------------------------- ------------------------- ----------\n");
/* Display each Row from table array */
for (tnum = 0; tnum < num_tables; tnum++)
{
printf("%-3d %-25s %-25s %-11s\n", tnum + 1, table[tnum].schem,
table[tnum].name, table[tnum].type);
if (table[tnum].remarks_l > 0 ) {
printf(" (remarks) %s\n", table[tnum].remarks);
}
}
return (SQL_SUCCESS);
} /* end print tables */
/**************************************************************************/
/* list_table_privileges */
/* - This sample function assumes that schema and tablename are exact */
/* strings, NOT search patterns */
/* */
/**************************************************************************/
/*--> SQLL1X51.SCRIPT */
SQLRETURN list_table_privileges( SQLHANDLE hstmt,
SQLCHAR * schema,
SQLCHAR * tablename
) {
SQLRETURN rc;
struct { SQLINTEGER ind; /* Length & Indicator variable */
SQLCHAR s[129]; /* String variable */
} grantor, grantee, privilege;
struct { SQLINTEGER ind;
SQLCHAR s[4];
}is_grantable;
SQLCHAR cur_name[512] = ""; /* Used when printing the */
SQLCHAR pre_name[512] = ""; /* Result set */
/* Create Table Privilges result set */
rc = SQLTablePrivileges(hstmt, NULL, 0, schema, SQL_NTS,
tablename, SQL_NTS);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindCol(hstmt, 4, SQL_C_CHAR, (SQLPOINTER) grantor.s, 129,
&grantor.ind);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
/* Continue Binding, then fetch and display result set */
/*<-- */
rc = SQLBindCol(hstmt, 5, SQL_C_CHAR, (SQLPOINTER) grantee.s, 129,
&grantee.ind);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindCol(hstmt, 6, SQL_C_CHAR, (SQLPOINTER) privilege.s, 129,
&privilege.ind);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindCol(hstmt, 7, SQL_C_CHAR, (SQLPOINTER) is_grantable.s, 4,
&is_grantable.ind);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
printf("Current User's Privileges for: %s.%s\n", schema, tablename);
printf(" Grantor Grantee Privilege Grantable\n");
printf(" --------------- --------------- ---------- ---\n");
/* Fetch each row, and display */
while ((rc = SQLFetch(hstmt)) == SQL_SUCCESS) {
printf(" %-15s", grantor.s);
printf(" %-15s", grantee.s);
printf(" %-10s", privilege.s);
printf(" %-3s\n", is_grantable.s);
} /* endwhile */
if (rc != SQL_NO_DATA_FOUND)
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
/* free statement resources */
rc = SQLFreeStmt( hstmt, SQL_UNBIND ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLFreeStmt( hstmt, SQL_RESET_PARAMS ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLFreeStmt( hstmt, SQL_CLOSE ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
return( SQL_SUCCESS ) ;
}
/**************************************************************************/
/* list_columns */
/* - schema and tablename should be exact strings, NOT search patterns */
/* */
/**************************************************************************/
/*--> SQLL1X52.SCRIPT */
SQLRETURN list_columns( SQLHANDLE hstmt,
SQLCHAR * schema,
SQLCHAR * tablename
) {
/*<-- */
SQLRETURN rc;
struct { SQLINTEGER ind;
SQLCHAR s[129];
} column_name, type_name, remarks;
SQLINTEGER length;
SQLINTEGER length_ind;
SQLSMALLINT scale;
SQLINTEGER scale_ind;
SQLSMALLINT nullable;
SQLINTEGER nullable_ind;
/*--> */
rc = SQLColumns(hstmt, NULL, 0, schema, SQL_NTS,
tablename, SQL_NTS, (SQLCHAR *)"%", SQL_NTS);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindCol(hstmt, 4, SQL_C_CHAR, (SQLPOINTER) column_name.s, 129,
&column_name.ind);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindCol(hstmt, 6, SQL_C_CHAR, (SQLPOINTER) type_name.s, 129,
&type_name.ind);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindCol(hstmt, 7, SQL_C_LONG, (SQLPOINTER) &length,
sizeof(length), &length_ind);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindCol(hstmt, 9, SQL_C_SHORT, (SQLPOINTER) &scale,
sizeof(scale), &scale_ind);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindCol(hstmt, 12, SQL_C_CHAR, (SQLPOINTER) remarks.s, 129,
&remarks.ind);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindCol(hstmt, 11, SQL_C_SHORT, (SQLPOINTER) & nullable,
sizeof(nullable), &nullable_ind);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
printf("Schema: %s Table Name: %s\n", schema, tablename);
/* Fetch each row, and display */
while ((rc = SQLFetch(hstmt)) == SQL_SUCCESS) {
printf(" %s", column_name.s);
if (nullable == SQL_NULLABLE) {
printf(", NULLABLE");
} else {
printf(", NOT NULLABLE");
}
printf(", %s", type_name.s);
if (length_ind != SQL_NULL_DATA) {
printf(" (%ld", length);
} else {
printf("(\n");
}
if (scale_ind != SQL_NULL_DATA) {
printf(", %d)\n", scale);
} else {
printf(")\n");
}
} /* endwhile */
/*<-- */
if ( rc != SQL_NO_DATA_FOUND )
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
/* free statement resources */
rc = SQLFreeStmt( hstmt, SQL_UNBIND ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLFreeStmt( hstmt, SQL_RESET_PARAMS ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLFreeStmt( hstmt, SQL_CLOSE ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
return( SQL_SUCCESS ) ;
} /* list_columns */
/**************************************************************************/
/* list_primary_keys */
/* - schema and tablename should be exact strings, NOT search patterns */
/* */
/**************************************************************************/
/*--> SQLL1X57.SCRIPT */
SQLRETURN list_primary_keys( SQLHANDLE hstmt,
SQLCHAR * schema,
SQLCHAR * tablename
) {
/*<-- */
SQLRETURN rc;
struct { SQLINTEGER ind;
SQLCHAR s[129];
} column_name, pk_name;
SQLSMALLINT ord_pos;
/*--> */
rc = SQLPrimaryKeys(hstmt, NULL, 0, schema, SQL_NTS,
tablename, SQL_NTS);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindCol(hstmt, 4, SQL_C_CHAR, (SQLPOINTER) column_name.s, 129,
&column_name.ind);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindCol(hstmt, 5, SQL_C_SHORT, (SQLPOINTER) &ord_pos,
0, NULL);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindCol(hstmt, 6, SQL_C_CHAR, (SQLPOINTER) pk_name.s, 129,
&pk_name.ind);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
printf("Primary Keys for %s.%s\n", schema, tablename);
/* Fetch each row, and display */
while ((rc = SQLFetch(hstmt)) == SQL_SUCCESS) {
printf(" %ld Column: %-18s ",
ord_pos, column_name.s);
if (pk_name.ind == SQL_NULL_DATA)
printf(" Primary Key Name: = NULL\n");
else
printf(" Primary Key Name: = %ld\n", pk_name.s);
} /* endwhile */
/*<-- */
if ( rc != SQL_NO_DATA_FOUND )
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
/* free statement resources */
rc = SQLFreeStmt( hstmt, SQL_UNBIND ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLFreeStmt( hstmt, SQL_RESET_PARAMS ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLFreeStmt( hstmt, SQL_CLOSE ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
return( SQL_SUCCESS ) ;
} /* list_primary_keys*/
/**************************************************************************/
/* list_column_privileges */
/* - schema and tablename must be exact strings, NOT search patterns */
/**************************************************************************/
/*--> SQLL1X53.SCRIPT */
SQLRETURN list_column_privileges( SQLHANDLE hstmt,
SQLCHAR * schema,
SQLCHAR * tablename
) {
/*<--*/
SQLRETURN rc;
struct { SQLINTEGER ind;
SQLCHAR s[129];
} columnname, grantor, grantee, privilege;
struct { SQLINTEGER ind;
SQLCHAR s[4];
}is_grantable;
SQLCHAR cur_name[512] = "";
SQLCHAR pre_name[512] = "";
printf("Enter Search Pattern for Column Name:\n");
gets((char *)columnname.s);
/*--> */
rc = SQLColumnPrivileges(hstmt, NULL, 0, schema, SQL_NTS,
tablename, SQL_NTS, columnname.s, SQL_NTS);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindCol(hstmt, 4, SQL_C_CHAR, (SQLPOINTER) columnname.s, 129,
&columnname.ind);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindCol(hstmt, 5, SQL_C_CHAR, (SQLPOINTER) grantor.s, 129,
&grantor.ind);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindCol(hstmt, 6, SQL_C_CHAR, (SQLPOINTER) grantee.s, 129,
&grantee.ind);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindCol(hstmt, 7, SQL_C_CHAR, (SQLPOINTER) privilege.s, 129,
&privilege.ind);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindCol(hstmt, 8, SQL_C_CHAR, (SQLPOINTER) is_grantable.s, 4,
&is_grantable.ind);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
printf("Column Privileges for %s.%s\n", schema, tablename);
/* Fetch each row, and display */
while ((rc = SQLFetch(hstmt)) == SQL_SUCCESS) {
sprintf((char *)cur_name, " Column: %s\n", columnname.s);
if (strcmp((char *)cur_name, (char *)pre_name) != 0) {
printf("\n%s\n", cur_name);
printf(" Grantor Grantee Privilege Grantable\n");
printf(" --------------- --------------- ---------- ---\n");
}
strcpy((char *)pre_name, (char *)cur_name);
printf(" %-15s", grantor.s);
printf(" %-15s", grantee.s);
printf(" %-10s", privilege.s);
printf(" %-3s\n", is_grantable.s);
} /* endwhile */
/*<-- */
if ( rc != SQL_NO_DATA_FOUND )
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
/* free statement resources */
rc = SQLFreeStmt( hstmt, SQL_UNBIND ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLFreeStmt( hstmt, SQL_RESET_PARAMS ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLFreeStmt( hstmt, SQL_CLOSE ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
return( SQL_SUCCESS ) ;
} /* list_column_privileges */
/**************************************************************************/
/* list_Foreign_Keys */
/* - schema and tablename must be exact strings, NOT search patterns */
/**************************************************************************/
/*--> SQLL1X54.SCRIPT */
SQLRETURN list_foreign_keys( SQLHANDLE hstmt,
SQLCHAR * schema,
SQLCHAR * tablename
) {
/*<-- */
SQLRETURN rc;
struct { SQLINTEGER ind;
SQLCHAR s[129];
} pktable_schem, pktable_name, pkcolumn_name, pkey_name;
struct { SQLINTEGER ind;
SQLCHAR s[129];
} fktable_schem, fktable_name, fkcolumn_name, fkey_name;
SQLSMALLINT delete_rule;
SQLINTEGER update_ind;
SQLSMALLINT update_rule;
SQLINTEGER delete_ind;
/*--> */
rc = SQLForeignKeys(hstmt, NULL, 0,
schema, SQL_NTS, tablename, SQL_NTS,
NULL, 0,
NULL, SQL_NTS, NULL, SQL_NTS);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindCol(hstmt, 2, SQL_C_CHAR, (SQLPOINTER) pktable_schem.s, 129,
&pktable_schem.ind);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindCol(hstmt, 3, SQL_C_CHAR, (SQLPOINTER) pktable_name.s, 129,
&pktable_name.ind);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindCol(hstmt, 4, SQL_C_CHAR, (SQLPOINTER) pkcolumn_name.s, 129,
&pkcolumn_name.ind);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindCol(hstmt, 6, SQL_C_CHAR, (SQLPOINTER) fktable_schem.s, 129,
&fktable_schem.ind);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindCol(hstmt, 7, SQL_C_CHAR, (SQLPOINTER) fktable_name.s, 129,
&fktable_name.ind);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindCol(hstmt, 8, SQL_C_CHAR, (SQLPOINTER) fkcolumn_name.s, 129,
&fkcolumn_name.ind);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindCol(hstmt, 10, SQL_C_SHORT, (SQLPOINTER) &update_rule,
0, &update_ind);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindCol(hstmt, 11, SQL_C_SHORT, (SQLPOINTER) &delete_rule,
0, &delete_ind);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindCol(hstmt, 12, SQL_C_CHAR, (SQLPOINTER) fkey_name.s, 129,
&fkey_name.ind);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindCol(hstmt, 13, SQL_C_CHAR, (SQLPOINTER) pkey_name.s, 129,
&pkey_name.ind);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
printf("Primary Key and Foreign Keys for %s.%s\n", schema, tablename);
/* Fetch each row, and display */
while ((rc = SQLFetch(hstmt)) == SQL_SUCCESS) {
printf(" %s %s.%s.%s\n Update Rule ",
pkcolumn_name.s, fktable_schem.s, fktable_name.s, fkcolumn_name.s);
if (update_rule == SQL_RESTRICT) {
printf("RESTRICT "); /* always for IBM DBMSs */
} else {
if (update_rule == SQL_CASCADE) {
printf("CASCADE "); /* non-IBM only */
} else {
printf("SET NULL ");
}
}
printf(", Delete Rule: ");
if (delete_rule== SQL_RESTRICT) {
printf("RESTRICT "); /* always for IBM DBMSs */
} else {
if (delete_rule == SQL_CASCADE) {
printf("CASCADE "); /* non-IBM only */
} else {
if (delete_rule == SQL_NO_ACTION) {
printf("NO ACTION "); /* non-IBM only */
} else {
printf("SET NULL ");
}
}
}
printf("\n");
if (pkey_name.ind > 0 ) {
printf(" Primary Key Name: %s\n", pkey_name.s);
}
if (fkey_name.ind > 0 ) {
printf(" Foreign Key Name: %s\n", fkey_name.s);
}
}
/*<-- */
if ( rc != SQL_NO_DATA_FOUND )
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
/* free statement resources */
rc = SQLFreeStmt( hstmt, SQL_UNBIND ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLFreeStmt( hstmt, SQL_RESET_PARAMS ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLFreeStmt( hstmt, SQL_CLOSE ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
return( SQL_SUCCESS ) ;
} /* End List Foreign Keys */
/**************************************************************************/
/* list_index_columns */
/* - schema and tablename must be exact strings, NOT search patterns */
/**************************************************************************/
/*--> SQLL1X55.SCRIPT */
SQLRETURN list_index_columns( SQLHANDLE hstmt,
SQLCHAR * schema,
SQLCHAR * tablename
) {
/*<-- */
SQLRETURN rc;
struct { SQLINTEGER ind;
SQLCHAR s[129];
} column_name, type_name;
struct { SQLINTEGER ind;
SQLCHAR s[255];
} remarks;
SQLINTEGER precision;
SQLINTEGER precision_ind;
SQLSMALLINT scale;
SQLINTEGER scale_ind;
SQLSMALLINT nullable;
/*--> */
rc = SQLSpecialColumns(hstmt, SQL_BEST_ROWID, NULL, 0, schema, SQL_NTS,
tablename, SQL_NTS, SQL_SCOPE_CURROW, SQL_NULLABLE);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindCol(hstmt, 2, SQL_C_CHAR, (SQLPOINTER) column_name.s, 129,
&column_name.ind);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindCol(hstmt, 4, SQL_C_CHAR, (SQLPOINTER) type_name.s, 129,
&type_name.ind);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindCol(hstmt, 5, SQL_C_LONG, (SQLPOINTER) & precision,
sizeof(precision), &precision_ind);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindCol(hstmt, 7, SQL_C_SHORT, (SQLPOINTER) & scale,
sizeof(scale), &scale_ind);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
printf("Primary Key or Unique Index for %s.%s\n", schema, tablename);
/* Fetch each row, and display */
while ((rc = SQLFetch(hstmt)) == SQL_SUCCESS) {
printf(" %s, %s ", column_name.s, type_name.s);
if (precision_ind != SQL_NULL_DATA) {
printf(" (%ld", precision);
} else {
printf("(\n");
}
if (scale_ind != SQL_NULL_DATA) {
printf(", %d)\n", scale);
} else {
printf(")\n");
}
}
/*<-- */
if ( rc != SQL_NO_DATA_FOUND )
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
/* free statement resources */
rc = SQLFreeStmt( hstmt, SQL_UNBIND ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLFreeStmt( hstmt, SQL_RESET_PARAMS ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLFreeStmt( hstmt, SQL_CLOSE ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
return( SQL_SUCCESS ) ;
} /* end list_index_columns */
/**************************************************************************/
/* list_stats */
/* - schema and tablename should be exact strings, NOT search patterns */
/**************************************************************************/
/*--> SQLL1X56.SCRIPT */
SQLRETURN list_stats( SQLHANDLE hstmt,
SQLCHAR * schema,
SQLCHAR * tablename
) {
/*<-- */
SQLRETURN rc;
struct { SQLINTEGER ind;
SQLCHAR s[129];
} index_name, column_name;
SQLSMALLINT non_unique;
SQLINTEGER non_unique_ind;
SQLSMALLINT type;
SQLINTEGER type_ind;
SQLINTEGER cardinality;
SQLINTEGER card_ind;
SQLINTEGER pages;
SQLINTEGER pages_ind;
/*--> */
rc = SQLStatistics(hstmt, NULL, 0, schema, SQL_NTS,
tablename, SQL_NTS, SQL_INDEX_UNIQUE, SQL_QUICK);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindCol(hstmt, 4, SQL_C_SHORT,
&non_unique, 2, &non_unique_ind);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindCol(hstmt, 6, SQL_C_CHAR,
index_name.s, 129, &index_name.ind);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindCol(hstmt, 7, SQL_C_SHORT,
&type, 2, &type_ind);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindCol(hstmt, 9, SQL_C_CHAR,
column_name.s, 129, &column_name.ind);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindCol(hstmt, 11, SQL_C_LONG,
&cardinality, 4, &card_ind);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindCol(hstmt, 12, SQL_C_LONG,
&pages, 4, &pages_ind);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
printf("Statistics for %s.%s\n", schema, tablename);
while ((rc = SQLFetch(hstmt)) == SQL_SUCCESS)
{ if (type != SQL_TABLE_STAT)
{ printf(" Column: %-18s Index Name: %-18s\n",
column_name.s, index_name.s);
}
else
{ printf(" Table Statistics:\n");
}
if (card_ind != SQL_NULL_DATA)
printf(" Cardinality = %13ld", cardinality);
else
printf(" Cardinality = (Unavailable)");
if (pages_ind != SQL_NULL_DATA)
printf(" Pages = %13ld\n", pages);
else
printf(" Pages = (Unavailable)\n");
}
/*<-- */
if ( rc != SQL_NO_DATA_FOUND )
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
/* free statement resources */
rc = SQLFreeStmt( hstmt, SQL_UNBIND ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLFreeStmt( hstmt, SQL_RESET_PARAMS ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLFreeStmt( hstmt, SQL_CLOSE ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
return( SQL_SUCCESS ) ;
}