How to connect and execute a simple query to SQL Server from C++ on Linux

Question

I guess, I have all prerequisites for setting connection and querying SQL Server database, since I can do that from my Python code. And I do this like so:

#1. Set connection using pyodbc library

db = pyodbc('DRIVER=FreeTDS;SERVER='+host+';PORT='+port+';DATABASE='+ \
            db_name+ ';UID='+ user+ ';PWD='+ pwd+ ';TDS_Version=7.0;ClientCharset=UTF8;') 

#2. List all table names in a particular database

cursor = db.cursor()
cursor.execute('SELECT TABLE_NAME FROM ' + db_name + '.INFORMATION_SCHEMA.Tables WHERE ' + \
'TABLE_TYPE=\'BASE TABLE\'')
res = cursor.fetchall()

And I'm done. Now I want to implement the same thing, using C++. I've seen some sample codes like this, but they look awfully terrible with five ifs nested one into another. But I need something really simple, taking into consideration that probably all prerequisites are met (if may be not, please, instruct what else should be installed).

The last thing I want to know is indeed how to compile this program (I normally do this using g++). And, please, do not post sole references to FreeTDS and ODBC - I've already seen them. What I want at this stage is a tiny minimized example of executing the simplest in the world query (like I did it above, using Python).

Answer 1

This is not tested with freetds driver of unixodbc. This is how i setup unixodbc with postgres or mysql. https://k3ut0i.github.io/work/2015-08-09-odbc-setup-linux.html

The global setup of drivers and data sources for ODBC are in /etc/odbc.ini and /etc/odbcinst.ini. some operating systems may have these files empty or differenet names. Here are my config files.

Drivers, libraries in /etc/odbcinst.ini file.

    [MySQL]
    Description     = ODBC driver for mariaDB
    Driver          = /usr/lib/libmyodbc.so
    Setup           = /usr/lib/libmyodbc5S.so
    FileUsage       = 1
    

the pyodbc function in the python file is equivalent a data source in /etc/odbc.ini file.

    [mariadb-connector]
    Description     = connection to test database for mariadb
    Driver          = MySQL
    Database        = test
    Server          = 127.0.0.1
    UserName        = keutoi
    Trace           = No
    Port            = 3306
    

Part of the code that connects and query. I just started from your link and cleaned it up.

        /**
         * Connect to data source named in /etc/odbc.ini file
         */
        retcode = SQLDriverConnect(hdbc, NULL, (SQLCHAR*)"DSN=mariadb-connector;", SQL_NTS, outstr, sizeof(outstr), &outstrlen, SQL_DRIVER_COMPLETE);
        check_error(retcode, "connect to the data source");

        //Allocate statement handle
        retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
        check_error(retcode, "allocate a statement handle");

        /**
         * statement to be executed.
         */
        retcode = SQLExecDirect (hstmt, (SQLCHAR *) "select * from mytable", SQL_NTS);
        check_error(retcode, "execute the statement");

        /**
         * Bind a column to a variable
         */
        retcode = SQLBindCol(hstmt, 1, SQL_C_CHAR, szName, NAME_LEN, &cbName);
        check_error(retcode, "bind 1 column to the statement");
        retcode = SQLBindCol(hstmt, 2, SQL_C_CHAR, szID, ID_LEN, &cbID);
        check_error(retcode, "bind 2 column to the statement");

        /**
         * fetch sql hstmt untill there is no data and print
         */
        for (int i=0 ; ; i++)
        {
            retcode = SQLFetch(hstmt);
            if(retcode == SQL_NO_DATA)break;
            else printf( "%d: %s %s %s\n", i + 1, szID, szName);
        }

Complete example is here. Compile with g++ file.cc -lodbc