Use Existing MSDN C++ ODBC Samples for Microsoft Linux ODBC Driver

By Gregory Suarez | Sr. Escalation Engineer | SQL Server

The CTP release of the Microsoft SQL Server ODBC Driver for Linux (https://www.microsoft.com/download/en/details.aspx?id=28160 ) opens many opportunities for Red Hat Enterprise Linux (RHEL 5.x) customers who want to access the power of Microsoft SQL Server.

Well known utilities such as BCP (bulk copy) and SQLCMD are provided with the driver and their use is fairly straight forward; however; some customers have mentioned the absence of an SDK and C/C++ samples. These features are slated to be available shortly after the product RTM's at the end of March 2012 but in the meantime, you can use the existing MSDN ODBC C/C++ samples to get you started.

The Microsoft ODBC Linux driver shares a common code base with its Windows ODBC counterpart and has been regression tested using many of the same test suites. With that said, many of the existing MSDN ODBC C/C++ samples that are console based should compile on Linux with only minor modifications.

To get you started, I'll use SQLBindCol sample found here: https://msdn.microsoft.com/en-us/library/windows/desktop/ms711010%28v=vs.85%29.aspx

A vast majority of Linux users will retrieve the data as SQLCHAR and will use printf. This is different from typical Windows usage which is why I decided to use single byte below. Note that the driver will return the SQLCHAR data as UTF8.

Here is a list of changes made

1. Remove unneeded header files. In particular <windows.h> header.
2. Replaced SQLWCHAR with SQLCHAR
3. Replaced wprintf with printf

Here is the final source ready to be compiled on RHEL:

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

#define NAME_LEN 50
#define PHONE_LEN 20

void show_error() {
printf("errorn");
}

int main() {
SQLHENV henv;
SQLHDBC hdbc;
SQLHSTMT hstmt = 0;
SQLRETURN retcode;
SQLCHAR szName[NAME_LEN], szPhone[PHONE_LEN], sCustID[NAME_LEN];
SQLLEN cbName = 0, cbCustID = 0, cbPhone = 0;

// Allocate environment handle
retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);

// Set the ODBC version environment attribute
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER*)SQL_OV_ODBC3, 0);

// Allocate connection handle
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);

// Set login timeout to 5 seconds
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)5, 0);

// Connect to data source
retcode = SQLConnect(hdbc, (SQLCHAR*) "SQLCMD", SQL_NTS, (SQLCHAR*) "Test1", 5, (SQLCHAR*) "Password1", 9);

// Allocate statement handle
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);

retcode = SQLExecDirect (hstmt, (SQLCHAR *) "SELECT CustomerID, ContactName, Phone FROM CUSTOMERS ORDER BY 2, 1, 3", SQL_NTS);
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {

// Bind columns 1, 2, and 3
retcode = SQLBindCol(hstmt, 1, SQL_C_CHAR, sCustID, 100, &cbCustID);
retcode = SQLBindCol(hstmt, 2, SQL_C_CHAR, szName, NAME_LEN, &cbName);
retcode = SQLBindCol(hstmt, 3, SQL_C_CHAR, szPhone, PHONE_LEN, &cbPhone);

// Fetch and print each row of data. On an error, display a message and exit.
for (int i=0 ; ; i++) {
retcode = SQLFetch(hstmt);
if (retcode == SQL_ERROR || retcode == SQL_SUCCESS_WITH_INFO)
show_error();
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
printf( "%d: %s %s %sn", i + 1, sCustID, szName, szPhone);
else
break;
}
}

// Process data
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
SQLCancel(hstmt);
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
}

SQLDisconnect(hdbc);
}

SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
}
}
SQLFreeHandle(SQL_HANDLE_ENV, henv);
}
}

Public ODBC headers are installed with unixODBC 2.3.0 and are typically included in the /usr/include/odbc directory. 

Be sure your IDE is configured correctly to find the headers and also be sure to link to the odbc library object using the –lodbc switch.

I was able to compile the program above using the following command line options:

cc -m64 -g -I/usr/include -L/usr/lib -lodbc -o SQLBindColtest SQLBindColtest.c

Good luck and I hope this helps you get started with writing C/C++ application that use the Microsoft Linux ODBC driver.

Comments

• Anonymous
  January 31, 2014
  Any hints on getting this to compile on Ubuntu 12.04?

• Anonymous
  November 06, 2014
  libodbc.so.2: cannot open shared object file: No such file or directory libodbc.so.2 already present in usr/local/lib but it get above statement cc  -g -I/usr/local/include -L/usr/local/lib -lodbc -o odbcTest odbcTest.c