/**********************************************************************
* 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);
}

See Also


Oracle is a registered trademark of Oracle Corporation and/or its affiliates.