Introduction to basic ODBC programming

Sample program to write basic odbc program to connect/ query/ disconnect with unicode data.

//--- BEGIN PROGRAM SOURCE

#include <stdio.h>
#include <stdlib.h>
#include <windows.h>
#include <sqlext.h>

int DBP_SQLSERVER = 1;
int DBP_DB2   = 0;
int DBP_POSTGRES = 0;

#define ENV      1
#define DBC      2
#define STMT     3
#define SETCODE  1

#define SQLNOTFOUND 100

void OpenConnecton(void);
void CloseConnection(void);
void SelectSQL(void);
void odbc_checkerr(wchar_t *, int, int);
long set_native_sql(wchar_t *, int);
void myexit(int);
void ChangeSession(wchar_t *);

SQLWCHAR out_connect_str[1024] = {0};
SQLWCHAR in_connect_str[1026] = {0};
SQLSMALLINT in_connect_str_len = 1024;
SQLSMALLINT out_connect_str_len = 1024;
SQLSMALLINT stringlen = 0;

HENV henv;
HDBC hdbc;
HSTMT hstmt;
long odbc_rc;
long Native_sql_code;
#define ATEND (Native_sql_code == 100)
wchar_t Msg[SQL_MAX_MESSAGE_LENGTH];

wchar_t strSQL[513] = {0};
long lngCBInd = 0;

void wmain(int argc, wchar_t **argv)
{
 OpenConnecton();
 SelectSQL();
 CloseConnection();

 myexit(0);
}
//********************************************************************************************
void OpenConnecton()
{
 // CREATE THE ENVIRONMENT HANDLE
 odbc_rc = SQLAllocEnv(&henv);
 odbc_checkerr(L"OpenConnecton: SQLAllocEnv", ENV, SETCODE);

 // CREATE THE CONNECTION HANDLE
 odbc_rc = SQLAllocConnect(henv, &hdbc);
 odbc_checkerr(L"OpenConnecton: SQLAllocConnect", DBC, SETCODE);

 // BUILD CONNECTION STRING
 if (DBP_SQLSERVER) {
  swprintf((wchar_t *)in_connect_str,
   L"Driver={SQL Server};"
   L"SERVER=MYSEQUELSERVER;"
   L"DATABASE=mydatabase;"
   L"UID=me;"
   L"PWD=me;"
   L"UseProcForPrepare=0");
 }
 else if (DBP_DB2) {
  swprintf((wchar_t *)in_connect_str,
      L"DRIVER={IBM DB2 ODBC Driver};"
   L"UID=me;"
   L"PWD=me;"
   L"GRAPHIC=3;"
   L"DBALIAS=MYALIAS;");
 }
 else { // PostgreSQL
  swprintf((wchar_t *)in_connect_str,
      L"DRIVER={PostgreSQL};"
   L"UID=me;"
   L"PWD=me;"
   L"SERVER=MYPOSTSERVER;"
   L"DATABASE=mydatabase;");
 }

 // CONNECT TO SERVER
wprintf(L"CONNECTION STRING <%s>\n", in_connect_str);
 odbc_rc = SQLDriverConnect(hdbc,
  (SQLHWND)0,
  (SQLWCHAR *)in_connect_str,
  (SQLSMALLINT)in_connect_str_len,
  (SQLWCHAR *)out_connect_str,
  (SQLSMALLINT)out_connect_str_len,
  &stringlen,
  SQL_DRIVER_NOPROMPT
  );
 odbc_checkerr(L"OpenConnecton: SQLDriverConnect", DBC, SETCODE);

 if (DBP_DB2) ChangeSession(L"efacdb");
}
//********************************************************************************************
void CloseConnection(void)
{
wprintf(L"CLOSING CONNECTION\n");

 odbc_rc = SQLDisconnect(hdbc);
 odbc_checkerr(L"CloseConnection: SQLDisconnect", DBC, SETCODE);

 odbc_rc = SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
 odbc_checkerr(L"CloseConnection: SQLFreeHandle", DBC, SETCODE);

 odbc_rc = SQLFreeHandle(SQL_HANDLE_ENV, henv);
 odbc_checkerr(L"CloseConnection: SQLFreeHandle", ENV, SETCODE);
}
//********************************************************************************************
void SelectSQL(void)
{
 long lngValue = 0;
 long rows = 0;
 wchar_t strBindInUni[21] = {0};
 wchar_t strBindOut[21] = {0};
 char strBindInAsc[21] = {0};

//******************************
// SELECT 1 (bind using INTEGER)
//******************************
 odbc_rc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
 odbc_checkerr(L"SELECT 1: SQLAllocHandle", STMT, SETCODE);

 lngValue = 1;

 odbc_rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, &lngValue, 0, NULL);
 odbc_checkerr(L"SELECT 1: SQLBindParameter", STMT, SETCODE);

 odbc_rc = SQLExecDirect(hstmt, (SQLWCHAR *)L"select col2 from testtable where col4 = ?", SQL_NTS);
 odbc_checkerr(L"SELECT 1: SQLExecDirect", STMT, SETCODE);

 odbc_rc = SQLBindCol(hstmt, 1, SQL_C_WCHAR, strBindOut, sizeof(strBindOut), &lngCBInd);
 odbc_checkerr(L"SELECT 1: SQLBindCol", STMT, SETCODE);

 odbc_rc = SQLFetch(hstmt);
 set_native_sql(L"SELECT 1: SQLFetch", STMT);

 if (ATEND) {
 wprintf(L"SELECT 1: SQLFetch = ATEND\n");

  SQLFreeStmt(hstmt, SQL_DROP);
  CloseConnection();
  myexit(0);
 }
 odbc_checkerr(L"SELECT 1: SQLFetch", STMT, 0);

 wprintf(L"SELECT 1: DATA FETCHED: strBindOut = <%s>\n", strBindOut);

 SQLFreeStmt(hstmt, SQL_DROP);

//***********************************
// SELECT 2 (bind using ASCII STRING)
//***********************************
 odbc_rc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
 odbc_checkerr(L"SELECT 2: SQLAllocHandle", STMT, SETCODE);

 strcpy(strBindInAsc, "record two data b");

 odbc_rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, sizeof(strBindInAsc), 0, strBindInAsc, 0, NULL);
 odbc_checkerr(L"SELECT 2: SQLBindParameter", STMT, SETCODE);

 odbc_rc = SQLExecDirect(hstmt, (SQLWCHAR *)L"select col2 from testtable where col3 = ?", SQL_NTS);
 odbc_checkerr(L"SELECT 2: SQLExecDirect", STMT, SETCODE);

 odbc_rc = SQLBindCol(hstmt, 1, SQL_C_WCHAR, strBindOut, sizeof(strBindOut),&lngCBInd);
 odbc_checkerr(L"SELECT 2: SQLBindCol", STMT, SETCODE);

 odbc_rc = SQLFetch(hstmt);
 set_native_sql(L"SELECT 2: SQLFetch", STMT);

 if (ATEND) {
 wprintf(L"SELECT 2: SQLFetch = ATEND\n");

  SQLFreeStmt(hstmt, SQL_DROP);
  CloseConnection();
  myexit(0);
 }
 odbc_checkerr(L"SELECT 2: SQLFetch", STMT, 0);

wprintf(L"SELECT 2: DATA FETCHED: strBindOut = <%s>\n", strBindOut);

 SQLFreeStmt(hstmt, SQL_DROP);

//*************************************
// SELECT 3 (bind using UNICODE STRING)
//*************************************
 odbc_rc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
 odbc_checkerr(L"SELECT 3: SQLAllocHandle", STMT, SETCODE);

 wcscpy(strBindInUni, L"record three");

 odbc_rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_WCHAR, SQL_WVARCHAR, sizeof(strBindInUni), 0,
strBindInUni, 0, NULL);
 odbc_checkerr(L"SELECT 3: SQLBindParameter", STMT, SETCODE);

 odbc_rc = SQLExecDirect(hstmt, (SQLWCHAR *)L"select col2 from testtable where col1 = ?", SQL_NTS);
 odbc_checkerr(L"SELECT 3: SQLExecDirect", STMT, SETCODE);

 odbc_rc = SQLBindCol(hstmt, 1, SQL_C_WCHAR, strBindOut, sizeof(strBindOut),&lngCBInd);
 odbc_checkerr(L"SELECT 3: SQLBindCol", STMT, SETCODE);

 odbc_rc = SQLFetch(hstmt);
 set_native_sql(L"SELECT 3: SQLFetch", STMT);

 if (ATEND) {
wprintf(L"SELECT 3: SQLFetch = ATEND\n");

  SQLFreeStmt(hstmt, SQL_DROP);
  CloseConnection();
  myexit(0);
 }
 odbc_checkerr(L"SELECT 3: SQLFetch", STMT, 0);

wprintf(L"SELECT 3: DATA FETCHED: strBindOut = <%s>\n", strBindOut);

 SQLFreeStmt(hstmt, SQL_DROP);
}
//********************************************************************************************
void ChangeSession(wchar_t *session)
{
 wchar_t strSQL[256] = {0};

 swprintf(strSQL, L"SET SCHEMA = %s", session);

wprintf(L"ChangeSession: session <%s>\n", session);

 odbc_rc = SQLAllocStmt(hdbc, &hstmt);
 odbc_checkerr(L"ChangeSession: SQLAllocStmt", STMT, SETCODE);

 odbc_rc = SQLExecDirect(hstmt, (SQLWCHAR *)strSQL, SQL_NTS);
 odbc_checkerr(L"ChangeSession: SQLExecDirect", STMT, SETCODE);

 SQLFreeStmt(hstmt, SQL_DROP);
}
//********************************************************************************************
void myexit(int num)
{
 wchar_t s[2] = {0};
 _getws(s);

 exit(num);
}
//********************************************************************************************
long set_native_sql(wchar_t *str, int handle_type)
{
    wchar_t SqlState[6];
    SWORD MsgLen;

//wprintf(L"set_native_sql: IN: odbc_rc = %ld, Native_sql_code = %ld, Msg <%s>\n", odbc_rc, Native_sql_code, Msg);
 if (odbc_rc == SQL_SUCCESS || (DBP_SQLSERVER && odbc_rc == SQL_SUCCESS_WITH_INFO))
  return Native_sql_code = SQL_SUCCESS;

 if (handle_type == STMT) {

  if (odbc_rc == SQLNOTFOUND)
   return Native_sql_code = SQLNOTFOUND;
  else {
   if (SQLGetDiagRec(
       SQL_HANDLE_STMT,
       hstmt,
       1,
       SqlState,
       &Native_sql_code,
       Msg,
       SQL_MAX_MESSAGE_LENGTH - 1,
       &MsgLen) != SQL_SUCCESS) {
    // Should never occur...?
    wprintf(L"STMT: (%s): ODBC produced an error but no error code could be found (%s)\n", str, Msg);
    myexit(0);
   }
  }
 }
 else if (handle_type == DBC) {
  if (SQLGetDiagRec(
      SQL_HANDLE_DBC,
      hdbc,
      1,
      SqlState,
      &Native_sql_code,
      Msg,
      SQL_MAX_MESSAGE_LENGTH - 1,
      &MsgLen) != SQL_SUCCESS) {
   // Should never occur...?
   wprintf(L"DBC: (%s): ODBC produced an error but no error code could be found.", str);
   myexit(0);
  }
 }
 else {
  if (SQLGetDiagRec(
      SQL_HANDLE_ENV,
      henv,
      1,
      SqlState,
      &Native_sql_code,
      Msg,
      SQL_MAX_MESSAGE_LENGTH - 1,
      &MsgLen) != SQL_SUCCESS) {
   // Should never occur...?
   wprintf(L"ENV: (%s): ODBC produced an error but no error code could be found.", str);
   myexit(0);
  }
 }

 if (Native_sql_code == 0) {
  // We have an error but their is no
  // native sql code, so set to 1000.
  Native_sql_code = 1000;
 }

 Native_sql_code = -Native_sql_code;

 return Native_sql_code;
}
//********************************************************************************************
void odbc_checkerr(wchar_t *str, int stattype, int checktype)
{
//wprintf(L"odbc_checkerr: odbc_rc = %ld\n", odbc_rc);

 if (odbc_rc == SQL_SUCCESS || ((DBP_SQLSERVER || DBP_DB2) && odbc_rc == SQL_SUCCESS_WITH_INFO)) {
  Native_sql_code = SQL_SUCCESS;
  return;
 }

 if (checktype == SETCODE)
  set_native_sql(str, stattype);

 if (Native_sql_code == SQL_SUCCESS) return;

 wprintf(L"ODBC ERROR:(%s) %ld (%s).", str, Native_sql_code, Msg);

 //CloseConnection();

 myexit(0);
}

//--- ENDPROGRAM SOURCE

For more help we can refer to MSDN doc link and get more sample code as well.