Integration between Qt application running on Linux and Microsoft SQL Server

I love computers, technology and software that’s for sure. I fell in love with them at a very early age and I still got the passion

To me, when I’m able to make one system talk or communicate and interact with another it’s always fun and challenging, but more importantly it’s rewarding as.

I have always been a Microsoft dude, but at the same time I have always been supporter of FOSS and everything it’s got to offer. On a separate note but in the same context (kind of) I am passionate about standards, patterns and languages.

Talking of languages… People always come to me asking stuff about C++ and their misconceptions on the language. C++ allows me to do what any other language can’t… that simple. I am afraid that they way it’s be taught at universities differs big time from modern C++ features and best practices, but I’m not the best person to make that judgment and that’s not the intent of this post anyways.

From now onwards, I will start publishing articles on Qt, FOSS and how to integrate them into the Microsoft Eco-System. Today, I will start describing how to query a SQL Server Database from Ubuntu Linux via a Qt application.

The beauty of standards is that no matter who the consumer is, the expected outcome will always be the same (e.g.: Surfing the web from any browser regardless of the operating system, for instance).

Qt natively provides support for data aware applications, therefore developers can greatly benefit from this by implementing MVC based applications. MVC support was introduced in Qt 4. In this post, however I will not discuss any MVC related topic but a simple application running on Ubuntu that connects and pulls data from SQL Server. In order to do so, some artifacts and configuration steps are required in Linux.

In linux we will require to have UnixODBC driver, FreeTDS libraries and for debugging purposes Wireshark to check whether our requests are being sent over the network. We must configure wireshark in order to enable listening on network cards.

angel@ubuntu:~$ sudo apt-get install wireshark

angel@ubuntu:~$ sudo groupadd wireshark

angel@ubuntu:~$ sudo usermod -a -G wireshark $USER

angel@ubuntu:~$ sudo chgrp wireshark /usr/bin/dumpcap

angel@ubuntu:~$ sudo setcap cap_net_raw,cap_net_admin=eip /usr/bin/dumpcap

angel@ubuntu:~$ sudo apt-get -y install freetds-bin tdsodbc unixodbc

Once we have all the pre-requisites installed, we have to make changes to odbc.ini, odbcinst.ini and freetds.conf. These changes are shown below

Freetds.conf

odbc.ini

odbcinst.ini

Qt Creator uses the odbc.ini and odbcinst.ini files that are not the ones we have made changes to, so we either create a symbolic link to them or just copy them to the expected folder.

angel@ubuntu:~$ sudo cp /etc/odbcinst.ini /usr/local/etc/odbcinst.ini

angel@ubuntu:~$ sudo cp /etc/odbc.ini /usr/local/etc/odbc.ini

In order to test that our Linux environment has been properly configured and we can effectively connect and query SQL Server, we can use tsql utility that’s part of FreeTDS. It’s important to specify UNICODE (UTF-8) as the client charset in freetds.conf otherwise the client won’t be able to understand some of the data returned by SQL Server.

The issue with UNICODE not being properly configured comes up in Wireshark as Unknown Packet Type.

Back to my introduction on standards, TDS is an application layer protocol that was initially designed and developed by Sybase, but later on it’s been enhanced and maintained by Microsoft for their SQL Server. The protocol definition can be found here

We have described the prerequisite and configuration steps, now let’s move on to the sample application. As mentioned earlier, we will not make this a MVC app where we can bind the model directly to the view (native Qt widget) but we’ll pull data based on a query and then we populate a DTO that’s a vector (collection) of EmployeeDto class.

std::vector<EmployeeDto> EmployeeDal::GetPeople() {

    std::vector<EmployeeDto> retval;

    auto db = Database_get();

    if (db.open()) {

        QString queryStr("Select  distinct top 100  'ID'=A.BusinessEntityID, 'FirstName'=A.FirstName, 'LastName'=A.LastName ");

        queryStr.append("From  [AdventureWorks2012].[Person].[Person] A ");

        queryStr.append("Order by LastName");

        QSqlQueryModel query;

        query.setQuery(queryStr, db);

        auto count = query.rowCount();

        for (auto r = 0; r < (count > 0 ? count : RowCount); r++) {

            auto record = query.record(r);

            if (!record.isEmpty()) {

                EmployeeDto newRecord;

                newRecord.Id_set(record.field("ID").value().toInt());

                newRecord.FirstName_set(QString(record.field("FirstName").value().toString()));

                newRecord.LastName_set(QString(record.field("LastName").value().toString()));

                retval.push_back(newRecord);

            }

        }

        db.close();

    }

    return retval;

The way Qt handles events might resemble to .NET, but they’re quite different. Qt has the concept of Signals and Slots which makes it easy for developers to raise events to notify consumers that something has occurred and needs to be handled. The code snippet below calls our DAL which in turn pulls data from SQL Server. Qt uses any C++ toolchain available, and when running in Windows it uses MSVC. In my Windows development machine, I have Visual Studio, Qt Creator and CLION but I only have Qt Creator and CLION in my Linux environment, and once again the beauty of standards is that I can compile a project in Windows and the same project can also be compiled in a different environment. In Linux I mainly use the C++ compiler in GCC thus I can have C++ code with new features (e.g.: Lambdas) and benefit from whichever environment I’m using.

void MainWindow::on_btnRunQuery_clicked() {

    auto index = 0;

    EmployeeDal dalObj;

    auto results = dalObj.GetPeople();

    auto lstView = findChild<QTableWidget*>("lstQueryResult");

    if (results.size() > 0 && lstView != nullptr) {

        lstView->clearContents();

        lstView->setRowCount(results.size());

        std::for_each(results.begin(), results.end(), [&](EmployeeDto& employee) {

            lstView->setItem(index, 0, new QTableWidgetItem(QString::number(employee.Id_get())));

            lstView->setItem(index, 1, new QTableWidgetItem(employee.FirstName_get()));

            lstView->setItem(index, 2, new QTableWidgetItem(employee.LastName_get()));

            index++;

        });

    }

}

void MainWindow::on_btnClose_clicked() {

    auto code =  [&]() {close();};

    Messenger("Are you sure you want to quit?", reinterpret_cast<const QMainWindow*>(this), code);

}

Lambda expressions in C++ allow us to pass a functor as a parameter or we can also use  the function class template which is a general-purpose polymorphic function wrapper (This would be the equivalent of Action or Func in C#)

void MainWindow::SomeFunction(std::function<bool(void)>& ptr) {

    if (ptr != nullptr)

        ptr();

}

void MainWindow::on_Something_triggered() {

    std::function<bool(void)> ptr = std::bind(&MainWindow::DoSomething, this);

    SomeFunction(ptr);

}

In our example I have a template function called “Messenger” which displays a Messagebox that executes a lambda passed as parameter.

#include <QMessageBox>

template<class T>

void Messenger(const QString& text, const QMainWindow* window, T&& functor) {

    QMessageBox msgBox(window->parentWidget());

    msgBox.setText(text);

    msgBox.setStandardButtons(QMessageBox::Yes | QMessageBox::No);

    msgBox.setDefaultButton(QMessageBox::Yes);

    if (msgBox.exec() == QMessageBox::Yes)

        functor();

}

The images depicted below correspond to the application running and the MessageBox displayed by invoking our template function.

If we start a trace in SQL Server Profiler we can see the request that has been received from the application

Sample demo source code here

Regards,

Angel