C++ Access to SQL Server from Linux

Here are the links I bookmarked concerning that topic, hope it can help you:

ODBC Tutorial
FreeTDS
Connection strings
How to configure ODBC - This one was really useful.

It was some time ago, but basically what I remember is:

You have to create an entry for the particular MSSQL driver you have in a file named /etc/odbcinst.ini. Then, for each MSSQL server, you have to create an entry (or DSN), either globaly in /etc/odbc.ini, or user-local, in $HOME/.odbc.ini.

Some names I used might differ (and I don't have acces to my Linux box right now to check) but you got the general idea.

Once you did that, isql -d should connect succesfully to the database. If so, then using the C/Linux API for ODBC should be a piece of cake. Tutorials provided in the given links.

How to access SQL server from linux c++ application?

Doing what you want is definitely possible but slightly tricky. Here is my short guide for that (personally, I solved this quest with help of unixodbc and freetds. They works fine, although, msodbc could be used too, the general idea will be same).

Firstly, the errors you got (QSqlDatabase: QODBC driver not loaded) means that you don't have Qt's ODBC driver. So you have to build it from Qt sources. Run MaintenanceTool to make sure that sources for your version of Qt are installed within Qt's directory. Then read carefully documentation about building SQL drivers and details of usage ODBC. The batch you need is following:

cd $QTDIR/qtbase/src/plugins/sqldrivers/odbc
qmake "INCLUDEPATH+=/usr/local/unixODBC/include" "LIBS+=-L/usr/local/unixODBC/lib -lodbc"
make

Secondly, in GNU/Linux environment you need unixodbc, tdsodbc, freetds-bin packages (the names for Debian Jessie, they may be different in your particular distribution). I also recommend you to read MANs from these packages. Freetds driver must be "installed" into unixodbc. Following command will do the job (again, in Debian Jessie):

odbcinst -i -d -f /usr/share/tdsodbc/odbcinst.ini

Next, you should supply correct connection string to QSqlDatabase instance (via the QSqlDatabase::setDatabaseName call). Also, make sure that the type of database is QODBC (you code is correct at this point). You can't pass usernames, password and etc via regular calls of QSqlDatabase (i.e. QSqlDatabase::setDatabaseName, QSqlDatabase::setPassword and etc won't work). They must be included into connection string which should looks something like that:

DRIVER={freetds};SERVER=192.168.55.55;PORT=1433;DATABASE=YYY;UID=YYY;PWD=XX

Obviously, you should put correct IP or host name and other parameters. Also, freetds must replaced with correct driver name (it stored within the unixodbc configuration file). There is a handy site which generates connection string. Also there is a reference for connection string format at MSDN.

Finally, MS SQL Server and instance of you DB must be properly configured. Make sure that MS SQL Server accepts TCP connections and bound to correct ports (and addresses!). Also, you should set correct "Authentication Mode" for both SQL Server and instance of DB: it is "Windows Authentication only" by default but to connect via freetds it must allow "SQL Server Authentication".

Probably something else need to be tweaked. Read docs and logs carefully.

Added: I've just read about driver supplied by MS. The overall process is same, but instead freetds you should use this MS driver (i.e. "install" it into unxiodbc via odbcinst or manually into config and put correct driver name and other parameters into connection string). Anyway, Qt driver for ODBC wraps around unixodbc, so, it cannot be avoided.

What are some ways of accessing Microsoft SQL Server from Linux?

FreeTDS + unixODBC or iODBC

Install first FreeTDS, then configure one of the two ODBC engines to use FreeTDS as its ODBC driver. Then use the commandline interface of the ODBC engine.

unixODBC has isql, iODBC has iodbctest

You can also use your favorite programming language (I've successfully used Perl, C, Python and Ruby to connect to MSSQL)

I'm personally using FreeTDS + iODBC:

$more /etc/freetds/freetds.conf
[10.0.1.251]
    host = 10.0.1.251
    port = 1433
    tds version = 8.0

$ more /etc/odbc.ini
[ACCT]
Driver = /usr/local/freetds/lib/libtdsodbc.so
Description = ODBC to SQLServer via FreeTDS
Trace = No
Servername = 10.0.1.251
Database = accounts_ver8

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

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

how to access remote windows SQL Server Express from Ubuntu VS C# Code

You can use a client tool provided by Microsoft called mssql-cli (it is also available for Windows and can be used the same way). To install it, follow this quick guide:

https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-setup-tools?view=sql-server-ver15#ubuntu

After installation you are able to use mssql-cli. This page explains the usage in detail:

https://www.mssqltips.com/sqlservertip/5298/new-interactive-command-line-tool-mssqlcli-for-sql-server/

Connect to MS SQL Server Database Using isql on Ubuntu

Microsoft's "ODBC Driver 17 for SQL Server" is the preferred driver for connecting to SQL Server. However, if you need to pass Windows credentials from a Linux box to a SQL Server instance then you do in fact need to use FreeTDS, at least for now.

We get the required components via

sudo apt install tdsodbc unixodbc

Then we use sudo nano -w /etc/odbcinst.ini to create an entry like this:

[FreeTDS]
DESCRIPTION=FreeTDS ODBC driver
DRIVER=/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so

Next we use sudo nano -w /etc/odbc.ini to create an entry like this:

[SQLServer01]
DRIVER=FreeTDS
SERVER=192.168.0.179
PORT=49242
DATABASE=myDb
TDS_Version=7.2

Finally, we can connect via isql (note the double backslash):

isql SQLServer01 mydomain\\myusername mypassword

How to access SQL DB in C++?

Accessing MS SQL server from windows should not be a problem in general. For accessing it from Linux, this web page may help you to find a way

http://www.sommarskog.se/mssqlperl/unix.html

Finding a solution that works on both Windows and Linux is a task that may not be solved easily, depends on the exact requirements and your current architecture. For example, when you just need C++ program calling a Perl script which does the DB work for you, this path may provide you with a free cross-platform solution. Perl is free and cross-platform, and the DBI modules too.

On the other hand, when your cross-platform C++ program uses a cross-platform framework like Qt, and you need a direct connection in your C++ program to the SQL server, ODBC may be the best option:

http://doc.qt.nokia.com/4.7/sql-driver.html

Unfortunately, according to the information behind my first link, there are no free ODBC drivers for Linux, so you might bite the bullet and buy one.

C++ Access to SQL Server from Linux