By Gregory Suarez | Sr. Escalation Engineer | SQL Server

 

The CTP release of the Microsoft SQL Server ODBC Driver for Linux (http://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: http://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("error\n");
}

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 %s\n", 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.