Code Samples

The following code snippets show the usage of the ODBC API for some of the common programming tasks.

SQLGetData

Use SQLGetData to get values while iterating through a cursor:

  SQLHENV henv;
  SQLHDBC hdbc;
  SQLHSTMT hstmt;
  char sInternalId[255] = {0};
  SQLLEN cbId = 0;
  if (SQLAllocHandle(SQL_HANDLE_ENV, 0 ,&henv) == SQL_SUCCESS) {
    SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0); 
    if (SQLAllocHandle(SQL_HANDLE_DBC, henv ,&hdbc) == SQL_SUCCESS) {
      if (SQLConnect(hdbc, _T("RSSBus NetSuite Source"), SQL_NTS, 0, 0, 0, 0) == SQL_SUCCESS) {
        if (SQLAllocHandle(SQL_HANDLE_STMT, hdbc ,&hstmt) == SQL_SUCCESS) {
          if (SQLExecDirect(hstmt, _T("SELECT InternalId FROM Account"), SQL_NTS) == SQL_SUCCESS) {
            while(SQLFetch(hstmt) == SQL_SUCCESS) {
              if (SQLGetData(hstmt, 1, SQL_C_CHAR, (SQLPOINTER)sInternalId, 255, &cbId) == SQL_SUCCESS) {
                cout << "InternalId: " << sInternalId << endl;
              }
            }
          }
          SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
        }
        SQLDisconnect(hdbc);
      }
      SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
    }
    SQLFreeHandle(SQL_HANDLE_ENV, henv);
  }

SQLBindColumn

Use SQLBindColumn to get values while iterating through a cursor:

  SQLHENV henv;
  SQLHDBC hdbc;
  SQLHSTMT hstmt;
  char sInternalId[255] = {0};
  SQLLEN cbId = 0;
  if (SQLAllocHandle(SQL_HANDLE_ENV, 0 ,&henv) == SQL_SUCCESS) {
    SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0); 
    if (SQLAllocHandle(SQL_HANDLE_DBC, henv ,&hdbc) == SQL_SUCCESS) {
      if (SQLConnect(hdbc, _T("RSSBus NetSuite Source"), SQL_NTS, 0, 0, 0, 0) == SQL_SUCCESS) {
        if (SQLAllocHandle(SQL_HANDLE_STMT, hdbc ,&hstmt) == SQL_SUCCESS) {
          if (SQLExecDirect(hstmt, _T("SELECT InternalId FROM Account"), SQL_NTS) == SQL_SUCCESS) {
            if (SQLBindCol(hstmt, 1, SQL_C_CHAR, (SQLPOINTER)sInternalId, 255, &cbId) == SQL_SUCCESS) {
              while(SQLFetch(hstmt) == SQL_SUCCESS) {
                cout << "InternalId: " << sInternalId << endl;
              }
            }
          }
          SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
        }
        SQLDisconnect(hdbc);
      }
      SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
    }
    SQLFreeHandle(SQL_HANDLE_ENV, henv);
  }

Row-Wise Binding

Bind an entire structure that models a row:

  SQLHENV henv;
  SQLHDBC hdbc;
  SQLHSTMT hstmt;
  typedef struct Account {
    SQLCHAR sInternalId[255];
    SQLINDICATOR cbsInternalId;
    SQLCHAR sAcctName[255];
    SQLINDICATOR cbsAcctName;
  } Account;
  Account Accounts[10];
  SQLUINTEGER NumRowsFetched;
  if (SQLAllocHandle(SQL_HANDLE_ENV, 0 ,&henv) == SQL_SUCCESS) {
    SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0); 
    if (SQLAllocHandle(SQL_HANDLE_DBC, henv ,&hdbc) == SQL_SUCCESS) {
      if (SQLConnect(hdbc, _T("RSSBus NetSuite Source"), SQL_NTS, 0, 0, 0, 0) == SQL_SUCCESS) {
        if (SQLAllocHandle(SQL_HANDLE_STMT, hdbc ,&hstmt) == SQL_SUCCESS) {
          SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_BIND_TYPE, (SQLPOINTER) sizeof(Account), 0);
          SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_ARRAY_SIZE, (SQLPOINTER)10, 0);
          SQLSetStmtAttr(hstmt, SQL_ATTR_ROWS_FETCHED_PTR, (SQLPOINTER)&NumRowsFetched, 0);
          if (SQLExecDirect(hstmt, _T("SELECT InternalId, AcctName FROM Account"), SQL_NTS) == SQL_SUCCESS) {
            SQLBindCol(hstmt, 1, SQL_C_CHAR, (SQLPOINTER)Accounts[0].sInternalId, 255, &Accounts[0].cbsInternalId);
            SQLBindCol(hstmt, 2, SQL_C_CHAR, (SQLPOINTER)Accounts[0].sAcctName, 255, &Accounts[0].cbsAcctName);
            while(SQLFetch(hstmt) == SQL_SUCCESS) {
              for(int row = 0; row < NumRowsFetched; row++) {
                cout << "InternalId: " << Accounts[row].sInternalId << ", AcctName: " << Accounts[row].sAcctName << endl;
              }
            }
          }
          SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
        }
        SQLDisconnect(hdbc);
      }
      SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
    }
    SQLFreeHandle(SQL_HANDLE_ENV, henv);
  }

Column-Wise Binding

Bind an entire structure that models a column:

  SQLHENV henv;
  SQLHDBC hdbc;
  SQLHSTMT hstmt;
  SQLCHAR sInternalId[10][50];
  SQLLEN cbsInternalId[10];
  SQLUINTEGER NumRowsFetched;
  if (SQLAllocHandle(SQL_HANDLE_ENV, 0 ,&henv) == SQL_SUCCESS) {
    SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0); 
    if (SQLAllocHandle(SQL_HANDLE_DBC, henv ,&hdbc) == SQL_SUCCESS) {
      if (SQLConnect(hdbc, _T("RSSBus NetSuite Source"), SQL_NTS, 0, 0, 0, 0) == SQL_SUCCESS) {
        if (SQLAllocHandle(SQL_HANDLE_STMT, hdbc ,&hstmt) == SQL_SUCCESS) {
          if (SQLExecDirect(hstmt, _T("SELECT * FROM Account"), SQL_NTS) == SQL_SUCCESS) {
            SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_BIND_TYPE, (SQLPOINTER)SQL_BIND_BY_COLUMN, 0);
            SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_ARRAY_SIZE, (SQLPOINTER)10, 0);
            SQLSetStmtAttr(hstmt, SQL_ATTR_ROWS_FETCHED_PTR, (SQLPOINTER)&NumRowsFetched, 0);
            if (SQLBindCol(hstmt, 1, SQL_C_CHAR, (SQLPOINTER)&sInternalId[0], 50, &cbsInternalId[0]) == SQL_SUCCESS) {
              while(SQLFetch(hstmt) == SQL_SUCCESS) {
                for(int row = 0; row < NumRowsFetched; row++) {
                  cout << "InternalId: " << sInternalId[row] << endl;
                }
              }
            }
          }
          SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
        }
        SQLDisconnect(hdbc);
      }
      SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
    }
    SQLFreeHandle(SQL_HANDLE_ENV, henv);
  }

Calling Stored Procedures

Use SQLFetch to call a stored procedure and iterate through its results:

  SQLHENV henv;
  SQLHDBC hdbc;
  SQLLEN cbObjectName = SQL_NTS;
  if (SQLAllocHandle(SQL_HANDLE_ENV, 0 ,&henv) == SQL_SUCCESS) {
    SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0); 
    if (SQLAllocHandle(SQL_HANDLE_DBC, henv ,&hdbc) == SQL_SUCCESS) {
      if (SQLConnect(hdbc, _T("RSSBus NetSuite Source"), SQL_NTS, 0, 0, 0, 0) == SQL_SUCCESS) {
        SQLHSTMT hstmt;
        SQLAllocHandle(SQL_HANDLE_STMT, hdbc ,&hstmt); 
        if (SQLExecDirect(hstmt, _T("{?=call GetRoles('Checking')}"), SQL_NTS) == SQL_SUCCESS) {
          char sRole_InternalId[255] = {0};
          SQLLEN cbsRole_InternalId = 0;
          while(SQLFetch(hstmt) == SQL_SUCCESS) {
            SQLGetData(hstmt, 4, SQL_C_CHAR, (SQLPOINTER)sRole_InternalId, 255, &cbsRole_InternalId);
            cout << "Role_InternalId: " << sRole_InternalId << endl;
          }
        }
        SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
        SQLDisconnect(hdbc);
      }
      SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
    }
    SQLFreeHandle(SQL_HANDLE_ENV, henv);
  }

Using Parameterized Statements

Bind parameters in a parameterized statement:

  SQLHENV henv;
  SQLHDBC hdbc;
  SQLHSTMT hstmt;
  char sInternalId[30] = {0};
  SQLLEN cbsInternalId = 0;
  char param[30] = {0};
  strcpy(param, "Savings");
  SQLLEN cbParam = SQL_NTS;
  if (SQLAllocHandle(SQL_HANDLE_ENV, 0 ,&henv) == SQL_SUCCESS) {
    SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0); 
    if (SQLAllocHandle(SQL_HANDLE_DBC, henv ,&hdbc) == SQL_SUCCESS) {
      if (SQLConnect(hdbc, _T("RSSBus NetSuite Source"), SQL_NTS, 0, 0, 0, 0) == SQL_SUCCESS) {
        if (SQLAllocHandle(SQL_HANDLE_STMT, hdbc ,&hstmt) == SQL_SUCCESS) {
          SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 100, 0, (SQLPOINTER)param, 30, &cbParam);
          if (SQLExecDirect(hstmt, _T("SELECT InternalId FROM Account WHERE InternalId=?"), SQL_NTS) == SQL_SUCCESS) {
            while(SQLFetch(hstmt) == SQL_SUCCESS) {
              if (SQLGetData(hstmt, 1, SQL_C_CHAR, (SQLPOINTER)sInternalId, 255, &cbsInternalId) == SQL_SUCCESS) {
                cout << "InternalId: " << sInternalId << endl;
              }
            }
          }
          SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
        }
        SQLDisconnect(hdbc);
      }
      SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
    }
    SQLFreeHandle(SQL_HANDLE_ENV, henv);
  }