/**********************************************************************
* FILENAME : ListProcedureColumns.c
*
* DESCRIPTION :
* Example uses SQLProcedures to return the list of input and
* output parameters, as well as the column details for the
* procedures visible to a datasource.
*
* ODBC USAGE :
* selectDSN() to get data source name
* SQLProcedures() - with statement handle and NULL for SchemaName,
* ProcName and ColumnName. This retrieves all procedure
* names.
* SQLProcedureColumns - for each procedure name returned by
* SQLProcedures, retrieved column names, types and data types.
*/
#include <stdio.h>
#include <stdlib.h>
#include <sql.h>
#include <sqlext.h>
#include <string.h>
#include "util.c"
// Arbitary sizes for char buffs
#define BUFF_SIZE 255
// Arbitary sizes for number of procedures and
#define MAX_PROCS 100
// Declare buffers for result set data
SQLCHAR strProcedureName[BUFF_SIZE];
struct DataBinding {
SQLSMALLINT TargetType;
SQLPOINTER TargetValuePtr;
SQLINTEGER BufferLength;
SQLLEN StrLen_or_Ind;
};
//
// Fill an array of procedure names associated with a datasource
//
SQLRETURN getProcedures (SQLCHAR *procNames[], // procedure names array
SQLLEN *noProcNames, // no of procedures found
SQLCHAR *dsn) { // data source handle
SQLHENV henv = SQL_NULL_HENV; // Environment
SQLHDBC hdbc = SQL_NULL_HDBC; // Connection handle
SQLHSTMT hstmt = SQL_NULL_HSTMT; // Statement handle
SQLRETURN retcode;
SQLCHAR strProcName[BUFF_SIZE];
SQLLEN lenProcName;
char confirm='N';
char reply=' ';
int i;
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,
(SQLPOINTER*)SQL_OV_ODBC3, 0);
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)5, 0);
CHECK_ERROR(retcode, "SQLSetConnectAttr (SQL_LOGIN_TIMEOUT)",
hdbc, SQL_HANDLE_DBC);
retcode = SQLConnect(hdbc, (SQLCHAR*) dsn, SQL_NTS,
(SQLCHAR*) NULL, 0, NULL, 0);
CHECK_ERROR(retcode, "SQLConnect (SQL_HANDLE_DBC)",
hdbc, SQL_HANDLE_DBC);
retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
CHECK_ERROR(retcode, "SQLAllocHandle (SQL_HANDLE_STMT)",
hstmt, SQL_HANDLE_STMT);
// Request all procedures
retcode = SQLProcedures (hstmt,
NULL, 0,
NULL, 0,
NULL, 0);
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
// Bind Procedure Name column of result set to buffers
retcode = SQLBindCol(hstmt, 3, SQL_C_CHAR, strProcName,
sizeof(strProcName), &lenProcName);
while (SQL_SUCCESS == retcode && i < MAX_PROCS) {
retcode = SQLFetch(hstmt);
CHECK_ERROR(retcode, "SQLFetch (Procedures)",
hstmt, SQL_HANDLE_STMT);
procNames[i]=(char *)malloc((strlen(strProcName)+1)*sizeof(char));
strcpy (procNames[i++], strProcName);
}
*noProcNames=i;
}
exit:
if (hstmt != SQL_NULL_HSTMT)
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
if (hdbc != SQL_NULL_HDBC) {
SQLDisconnect(hdbc);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
}
if (henv != SQL_NULL_HENV)
SQLFreeHandle(SQL_HANDLE_ENV, henv);
return retcode;
}
int main () {
SQLHENV henv = SQL_NULL_HENV; // Environment
SQLHDBC hdbc = SQL_NULL_HDBC; // Connection handle
SQLHSTMT hstmt = SQL_NULL_HSTMT; // Statement handle
SQLRETURN retcode;
SQLCHAR *procNames[MAX_PROCS];
SQLLEN noProcNames;
SQLCHAR colName[BUFF_SIZE];
// vars for full result set
SQLCHAR strProcedureCat[BUFF_SIZE];
SQLCHAR strProcedureSchem[BUFF_SIZE];
SQLCHAR strProcedureName[BUFF_SIZE];
SQLCHAR strColumnName[BUFF_SIZE];
SQLSMALLINT ColumnType;
SQLSMALLINT DataType;
SQLCHAR strTypeName[BUFF_SIZE];
SQLLEN ColumnSize;
SQLLEN BufferLength;
SQLLEN DecimalDigits;
SQLSMALLINT NumPrevRadix;
SQLSMALLINT Nullable;
SQLCHAR strRemarks[BUFF_SIZE];
SQLCHAR strColumnDef[BUFF_SIZE];
SQLSMALLINT SQLDataType;
SQLSMALLINT SQLDateTimeSub;
SQLLEN CharOctetLength;
SQLLEN OrdinalPosition;
SQLCHAR strIsNullable[BUFF_SIZE];
// ind/len vars for full result set
SQLLEN lenProcedureCat;
SQLLEN lenProcedureSchem;
SQLLEN lenProcedureName;
SQLLEN lenColumnName;
SQLLEN lenColumnType;
SQLLEN lenDataType;
SQLLEN lenTypeName;
SQLLEN lenColumnSize;
SQLLEN lenBufferLength;
SQLLEN lenDecimalDigits;
SQLLEN lenNumPrevRadix;
SQLLEN lenNullable;
SQLLEN lenRemarks;
SQLLEN lenColumnDef;
SQLLEN lenSQLDataType;
SQLLEN lenSQLDateTimeSub;
SQLLEN lenCharOctetLength;
SQLLEN lenOrdinalPosition;
SQLLEN lenIsNullable;
char procName[BUFF_SIZE];
char dsn[BUFF_SIZE];
char confirm='N';
char reply=' ';
int i, header;
// Allcoate environment handle
retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
CHECK_ERROR(retcode, "SQLAllocHandle (SQL_HANDLE_ENV)",
henv, SQL_HANDLE_ENV);
// Set ODBC Version
retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
(SQLPOINTER*)SQL_OV_ODBC3, 0);
CHECK_ERROR(retcode, "SQLSetEnvAttr (SQL_HANDLE_ENV)",
henv, SQL_HANDLE_ENV);
// Prompt for DSN
retcode = selectDSN (henv, dsn, "Select DSN : ");
if (retcode != SQL_SUCCESS) {
printf ("DSN not selected, exiting.");
goto exit;
}
// Allocate connection handle
retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
CHECK_ERROR(retcode, "SQLAllocHandle (SQL_HANDLE_DBC)",
hdbc, SQL_HANDLE_DBC);
// Set connection timeout
retcode = SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)5, 0);
CHECK_ERROR(retcode, "SQLSetConnectAttr (SQL_LOGIN_TIMEOUT)",
hdbc, SQL_HANDLE_DBC);
// Connect to DSN
retcode = SQLConnect(hdbc, (SQLCHAR*) dsn, SQL_NTS,
(SQLCHAR*) NULL, 0, NULL, 0);
CHECK_ERROR(retcode, "SQLConnect (dsn)", hdbc, SQL_HANDLE_DBC);
// Allocate statement handle
retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
CHECK_ERROR(retcode, "SQLAllocHandle (SQL_HANDLE_STMT)",
hstmt, SQL_HANDLE_STMT);
// Get list of procedure names
retcode = getProcedures (procNames, &noProcNames, dsn);
if (retcode==SQL_SUCCESS) {
for (i=0;i<noProcNames;i++) {
// Get columns associated with each pocedure name
header=0;
retcode = SQLProcedureColumns (hstmt,
NULL, 0,
NULL, 0,
procNames[i], SQL_NTS,
NULL, 0);
CHECK_ERROR(retcode, "SQLProcedureColumns ()",
hstmt, SQL_HANDLE_STMT);
// Bind all 19 columns
SQLBindCol (hstmt, 1, SQL_C_CHAR, strProcedureCat,
sizeof(strProcedureCat), &lenProcedureCat);
SQLBindCol (hstmt, 2, SQL_C_CHAR, strProcedureSchem,
sizeof(strProcedureSchem),&lenProcedureSchem);
SQLBindCol (hstmt, 3, SQL_C_CHAR, strProcedureName,
sizeof(strProcedureName), &lenProcedureName);
SQLBindCol (hstmt, 4, SQL_C_CHAR, strColumnName,
sizeof(strColumnName), &lenColumnName);
SQLBindCol (hstmt, 5, SQL_C_SHORT, &ColumnType,
sizeof(ColumnType), &lenColumnType);
SQLBindCol (hstmt, 6, SQL_C_SHORT, &DataType,
sizeof(DataType), &lenDataType);
SQLBindCol (hstmt, 7, SQL_C_CHAR, strTypeName,
sizeof(strTypeName), &lenTypeName);
SQLBindCol (hstmt, 8, SQL_C_LONG, &ColumnSize,
sizeof(ColumnSize), &lenColumnSize);
SQLBindCol (hstmt, 9, SQL_C_LONG, &BufferLength,
sizeof(BufferLength), &lenBufferLength);
SQLBindCol (hstmt, 10, SQL_C_SHORT, &DecimalDigits,
sizeof(DecimalDigits), &lenDecimalDigits);
SQLBindCol (hstmt, 11, SQL_C_SHORT, &NumPrevRadix,
sizeof(NumPrevRadix), &lenNumPrevRadix);
SQLBindCol (hstmt, 12, SQL_C_SHORT, &Nullable,
sizeof(Nullable), &lenNullable);
SQLBindCol (hstmt, 13, SQL_C_CHAR, strRemarks,
sizeof(strRemarks), &lenRemarks);
SQLBindCol (hstmt, 14, SQL_C_CHAR, strColumnDef,
sizeof(strColumnDef), &lenColumnDef);
SQLBindCol (hstmt, 15, SQL_C_SHORT, &SQLDataType,
sizeof(SQLDataType), &lenSQLDataType);
SQLBindCol (hstmt, 16, SQL_C_SHORT, &SQLDateTimeSub,
sizeof(SQLDateTimeSub), &lenSQLDateTimeSub);
SQLBindCol (hstmt, 17, SQL_C_LONG, &CharOctetLength,
sizeof(CharOctetLength), &lenCharOctetLength);
SQLBindCol (hstmt, 18, SQL_C_LONG, &OrdinalPosition,
sizeof(OrdinalPosition), &lenOrdinalPosition);
SQLBindCol (hstmt, 19, SQL_C_CHAR, strIsNullable,
sizeof(strIsNullable), &lenIsNullable);
// Get column data.
while (retcode == SQL_SUCCESS) {
retcode = SQLFetch(hstmt);
CHECK_ERROR(retcode, "SQLFetch (ProcedureColumns)",
hstmt, SQL_HANDLE_STMT);
if (header++==0) {
printf ("\nProcedure Name : %s\n",strProcedureName);
}
printf (" Column Name : %s\n", strColumnName);
printf (" Column Type : %i\n", ColumnType);
printf (" Data Type : %i\n", DataType);
if (retcode==SQL_NO_DATA && header==1) {
printf ("(NO DATA)\n");
}
}
}
}
printf ("\nThe End.\n");
exit:
if (hstmt != SQL_NULL_HSTMT)
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
if (hdbc != SQL_NULL_HDBC) {
SQLDisconnect(hdbc);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
}
if (henv != SQL_NULL_HENV)
SQLFreeHandle(SQL_HANDLE_ENV, henv);
}
Further information
