/**********************************************************************
* FILENAME :        BindColumn.c
*
* DESCRIPTION :
*       Example shows use of SQLBindCol to retrieve records from a table
*
* ODBC USAGE :
*       SQLExecDirect - to execute SELECT statement
*       SQLBindCol    - to bind data areas to columns
*       SQLFetch      - to fetch records until end
*
*/
#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  256
#define FIRSTNAME_LEN 256
#define ADDRESS_LEN 256
#define CITY_LEN  256

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 strFirstName[LASTNAME_LEN], strLastName[FIRSTNAME_LEN];
    SQLCHAR strAddress[ADDRESS_LEN], strCity[CITY_LEN];
    SQLLEN lenFirstName=0, lenLastName=0, lenAddress=0, lenCity=0;
    SQLLEN cPersonId=0, lenPersonId=0;

    int i=0;
    // Allocate environment handle
    retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
    CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_ENV)",
                henv, SQL_HANDLE_ENV);

    // Set the ODBC version environment attribute
    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 connection handle
    retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
    CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_DBC)",
                hdbc, SQL_HANDLE_DBC);

    // Set login timeout to 5 seconds
    SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)5, 0);
    CHECK_ERROR(retcode, "SQLSetConnectAttr(SQL_LOGIN_TIMEOUT)",
                hdbc, SQL_HANDLE_DBC);

    // Connect to data source
    retcode = SQLConnect(hdbc, (SQLCHAR*) "DATASOURCE", SQL_NTS,
                         (SQLCHAR*) NULL, 0, NULL, 0);
    CHECK_ERROR(retcode, "SQLConnect(DATASOURCE)",
                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);

    retcode = SQLExecDirect(hstmt, (SQLCHAR*) "SELECT PersonID,"
                            "FirstName, LastName, Address, City FROM TestTBL1"
                            "ORDER BY 1, 3, 2, 4, 5", SQL_NTS);

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

    // Bind columns 1, 2, 3, 4 and 5
    retcode = SQLBindCol(hstmt, 1, SQL_C_USHORT, &cPersonId, 2,
                         (SQLPOINTER) &lenPersonId);
    retcode = SQLBindCol(hstmt, 2, SQL_C_CHAR, &strFirstName,
                         FIRSTNAME_LEN, &lenFirstName);
    retcode = SQLBindCol(hstmt, 3, SQL_C_CHAR, &strLastName,
                         LASTNAME_LEN, &lenLastName);
    retcode = SQLBindCol(hstmt, 4, SQL_C_CHAR, &strAddress,
                         ADDRESS_LEN, &lenAddress);
    retcode = SQLBindCol(hstmt, 5, SQL_C_CHAR, &strCity,
                         CITY_LEN, &lenCity);

    // Fetch and print each row of data until SQL_NO_DATA returned.
    // On an error, display a message and exit.
    for (i=0; ; i++) {
        retcode = SQLFetch(hstmt);
        if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
            printf("Record %d: %i %s, %s, %s, %s\n", i+1,
                (int) cPersonId,rtrim(strFirstName,' '),rtrim(strLastName,' '),
                rtrim(strAddress,' '),rtrim(strCity,' '));
        } else {
            if (retcode != SQL_NO_DATA) {
                CHECK_ERROR(retcode, "SQLFetch()",
                            hstmt, SQL_HANDLE_STMT);
            } else {
                break;
            }
        }
    }

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.