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

Question

We have a Microsoft Sql 2014 database on a remote windows server. I am trying to develop a QT GUI app which connects to this database. The app is in Linux/c++ environment. I tried using QtSql APIs to connect to that database.

bool MainWindow::connect()
{
    QSqlDatabase db=QSqlDatabase::addDatabase( "QODBC" );//I am not sure if I am calling this correctly
    db.setHostName("III");

    db.setDatabaseName("YYY");
    db.setUserName("YYY");
    db.setPassword("XX");
    bool ok = db.open();
    if(ok==true)
    {
        QSqlQuery query;
        query.exec("SELECT * FROM Subjects");
        while (query.next()) 
        {
            int phy = query.value(0).toInt();
            int chem = query.value(1).toInt();
            ui->lineEdit_2->setText(QString::number(phy));
            ui->lineEdit_3->setText(QString::number(chem));
        }
    }
    else
    {
        qDebug()<<db.lastError();
    }
    return ok;
}

Its giving me error as :

QSqlDatabase: QODBC driver not loaded
QSqlDatabase: available drivers: QSQLITE QMYSQL QMYSQL3 QPSQL QPSQL7
QSqlError("", "Driver not loaded", "Driver not loaded")

I tried exporting the path of sql drivers to my Qt project.Still it didn't work.Then I read some articles and found that I need mssql-tools as well as ODBC Driver on my Linux box. I tried installing this based on this website:

https://blogs.msdn.microsoft.com/sqlnativeclient/2016/10/20/odbc-driver-13-0-for-linux-released/

and found that this won't work on my linux box (14.04 ubuntu) but only on 15.10 & 16.04.

The following packages have unmet dependencies:
mssql-tools : 
           Depends: libc6 (>= 2.21) but 2.19-0ubuntu6.9 is to be installed
           Depends: libstdc++6 (>= 5.2) but 4.8.4-2ubuntu1~14.04.3 is to be installed
           Depends: msodbcsql (>= 13.1.0.0) but it is not going to be installed
           Depends: msodbcsql (< 13.2.0.0) but it is not going to be installed
E: Unable to correct problems, you have held broken packages.

Then I used docker from here: https://hub.docker.com/r/taylorbarrick/mssql-server-linux-tools/

I could install it successfully but when I am running

$docker run -t  taylorbarrick/mssql-server-linux-tools sqlcmd -d <dbname> -H <host> -U <username> -P <password>

I am getting errors again.

Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login timeout  expired.
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : TCP Provider:    Error code 0x2749.
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

However,when using visual interface I can connect to sql database.Please help.

S.V · Accepted Answer · 2023-10-20 20:00:42Z

2

+50

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 work 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.

edited Oct 20, 2023 at 20:00

S.V

2,8552 gold badges27 silver badges52 bronze badges

answered Feb 15, 2017 at 8:22

J. Doe

4292 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

J. Doe Over a year ago

Also make sure that QODBC driver (the .so file) stored somewhere in $QTDIR/5.6/gcc_64/plugins/sqldrivers/ and unixodbc-dev package must be installed to build the driver!

Minion Over a year ago

Thanks a lot for providing a solution and explaining that so well. However, I solved it using concept of Foreign Data Wrapper ,foreign tables and thus enabling my postgres database to access a "view" on SQL database. I did not have to install anything on postgres client side but only on the server side. And because of this my QT app needs no change now. For my app, its a regular call to the postgress db.This worked perfectly. But I am accepting your answer.

Mike · Accepted Answer · 2017-02-10 21:48:06Z

0

To use MSSql server, you may have to install Microsoft's version of ODBC (msodbc) rather than unixodbc because... Microsoft. You can download it at https://www.microsoft.com/en-us/sql-server/sql-server-vnext-including-Linux.

Once you have that, you can access MSSql databases via ODBC. It is buggy and Whenever it updates, it may fail so you have to remove and reinstall both the library and the dev package.

answered Feb 10, 2017 at 21:48

Mike

2,7711 gold badge18 silver badges21 bronze badges

2 Comments

Minion Over a year ago

I tried this.I keep getting stuck in the end. I think its mainly because I can't install some things in Ubuntu 14.04 but only 16.04.

Mike Over a year ago

Ah, you're using the LTS. Don't. It's fine for the end user but they are useless for a dev machine. MSSql for Linux only came out last year so its requirements aren't going to be met with the LTS release. Upgrade and try again - I had this time and timg again when I tried to stick with 14.04.

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related