/**********************************************************************
* FILENAME :        DescribeColumn.c
*
* DESCRIPTION :
*       Example illustrates use of SQLDescribeCol and SQLColAttribute to
*       obtain column information from a prepared statement and then use
*       that information to bind parameters in the prepared statement
*       before executing it.
*
* ODBC USAGE :
*       SQLPrepare      - prepare basic 'SELECT * FROM' statement
*       SQLNumResultCols- get number of columns
*       SQLDescribeCol  - obtain column information (name, type, etc) and
*                         save
*       SQLColAttribute - obtain column max size information
*       SQLBindCol      - binds the column data saved by SQLDescribeCol
*                         to the statement
*       SQLExecute      - execute the 'SELECT * FROM'
*       SQLFetch        - return the records until SQL_NO_DATA returned
*
*/
#include <stdio.h>
#include <stdlib.h>
#include <sql.h>
#include <sqlext.h>
#include <string.h>

#include "util.c"

#define PERSONID_LEN  2
#define LASTNAME_LEN  255
#define FIRSTNAME_LEN 255
#define ADDRESS_LEN 255
#define CITY_LEN  255
#define MAXCOLS 10
#define TRUE 1

SQLHENV  henv   = SQL_NULL_HENV;        // Environment
SQLHDBC  hdbc   = SQL_NULL_HDBC;        // Connection handle
SQLHSTMT hstmt = SQL_NULL_HSTMT;        // Statement handle
SQLRETURN retcode;

SQLRETURN display_results(SQLHSTMT hstmt)
{
    SQLCHAR         colname[32];        // column name
    SQLSMALLINT     coltype;            // column type
    SQLSMALLINT     colnamelen;         // length of column name
    SQLSMALLINT     nullable;           // whether column can have NULL value
    SQLLEN          collen[MAXCOLS];    // array of column lengths
    SQLSMALLINT     decimaldigits;      // no of digits if column is numeric
    SQLLEN          outlen[MAXCOLS];    // lengths of column values returned
    SQLCHAR *       data[MAXCOLS];      // buffers for retrieving column values
    SQLRETURN       retcode;            // general return code
    SQLLEN          displaysize;        // drivers column display size
    SQLINTEGER      i,j;
    SQLSMALLINT columns;

    // Initialise data array
    for (i=0;i<MAXCOLS;i++) {
        data[i]=NULL;
    }

    // Get number of columns from prepared statement
    retcode=SQLNumResultCols(hstmt, &columns);
    CHECK_ERROR(retcode, "SQLNumResultCols()",
                hstmt, SQL_HANDLE_STMT);

    printf ("Number of columns is %i\n", (int) columns);
    printf ("Use SQLDescribeCol to obtain column details\n");

    for (i = 0; i < columns; i++)
    {
        // for each column from the prepared statement in hstmt, get the
        // column name, type, column size, decimal digits, and nullability
        retcode = SQLDescribeCol (hstmt,
                                  (SQLUSMALLINT)i+1,
                                  colname,
                                  sizeof (colname),
                                  &colnamelen,
                                  &coltype,
                                  &collen[i],
                                  &decimaldigits,
                                  &nullable);

        CHECK_ERROR(retcode, "SQLDescribeCol()",
                    hstmt, SQL_HANDLE_STMT);

        displaysize=0;
        // get Maximum number of characters required to display data
        // from the column.
        retcode = SQLColAttribute (hstmt,
                                   (SQLUSMALLINT)i+1,
                                   SQL_COLUMN_DISPLAY_SIZE,
                                   NULL, 0, NULL, &displaysize);
        CHECK_ERROR(retcode, "SQLColAttribute()",
                    hstmt, SQL_HANDLE_STMT);

        /* set column length to max of display length, and column name
           length. Plus one byte for null terminator       */
        if (collen[i] > displaysize) {
            collen[i]=displaysize+1;
        } else {
            collen[i]=collen[i]+1;
        }
        // allocate memory to bind column
        data[i] = (SQLCHAR *) malloc (collen[i]);
    }

    //Bind columns to data array
    printf ("Bind columns to statement\n");
    for (i = 0; i < columns; i++) {
        outlen[i]=0;
        retcode = SQLBindCol (hstmt,
                              (SQLUSMALLINT)i+1,
                              SQL_C_CHAR,
                              (SQLPOINTER) data[i],
                              collen[i],
                              &outlen[i]);

        CHECK_ERROR(retcode, "SQLBindCol()", hstmt, SQL_HANDLE_STMT);
    }

    /* execute the now fully prepared statement */
    printf ("Execute Statement\n");
    retcode=SQLExecute(hstmt);
    CHECK_ERROR(retcode, "SQLExecute()", hstmt, SQL_HANDLE_STMT);

    printf ("Fetch Results\n");
    /* display the result rows  */
    for (i=0; ; i++) {
        retcode = SQLFetch(hstmt);
        if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
            printf("Record %i : ", i+1);
            for (j=0;j<columns;j++) {
                printf ("%s", rtrim(data[j], ' '));
                if (j!=columns-1) {printf (", ");}
            }
            printf ("\n");
        } else {
            if (retcode != SQL_NO_DATA) {
                CHECK_ERROR(retcode, "SQLFetch()",
                            hstmt, SQL_HANDLE_STMT);
            }
            break;
        }
    }

exit:

    /* free data buffers  */
    for (i = 0; i < columns; i++)
    {
        if (data[i]!=NULL) free (data[i]);
    }

    return;
}

int main () {

    SQLSMALLINT sCustID;

	char sqlstr[] = "SELECT * from TestTBL1Copy order by PersonID";

    // Allocate the ODBC environment and save handle.
    retcode = SQLAllocHandle (SQL_HANDLE_ENV, NULL, &henv);
    CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_ENV)",
                henv, SQL_HANDLE_ENV);

    // Notify ODBC that this is an ODBC 3.0 app.
    retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
                            (SQLPOINTER) SQL_OV_ODBC3, 0);
    CHECK_ERROR(retcode, "SQLSetEnvAttr(SQL_ATTR_ODBC_VERSION)",
                henv, SQL_HANDLE_ENV);

    // Allocate ODBC connection handle and connect.
    retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
    CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_DBC)",
                hdbc, SQL_HANDLE_DBC);

    // Connect to the DSN
    retcode=SQLDriverConnect(hdbc, NULL, "DSN=DATASOURCE;", SQL_NTS,
                             NULL, 0, NULL, SQL_DRIVER_COMPLETE);
    CHECK_ERROR(retcode, "SQLDriverConnect(DATASOURCE 0)",
                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);

    // Prepare the statement
    retcode = SQLPrepare(hstmt, (SQLCHAR*) sqlstr, SQL_NTS);
    CHECK_ERROR(retcode, "SQLPrepare(SQL_HANDLE_STMT)",
                hstmt, SQL_HANDLE_STMT);

    retcode = display_results(hstmt);

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


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