SQL Server driver missing (Pi4, Qt 5.11.3)

washburn_it

Hi,

I'm new with Qt (but not new with developing) and, coming from Lazarus (porting of Delphi on Linux), I would like to start developing with Qt.
Most of the application that I develop for Raspberry Pi must support connection to SQL Server (Express or Full) and, at the moment, I can do it in Lazarus with FreeTDS libraries.
So now I'm trying to connect to a SQL database (2014) from Qt but when I connect to the server I get the error "Driver not loaded".
The only installed driver is "QSQLITE", how to install also the proper driver for SQL Server?
Thank you, regards.

Roberto

JonB

@washburn_it
For SQL Server you will want to use the QODBC driver, described in https://doc.qt.io/qt-5/sql-driver.html.

I have to say, you are using a strange match of RPi/Linux and SQL Server. A more "natural" choice would have been MySQL, or even SQLite.

washburn_it

Thanks JonB,

I know it sounds strange but...I usually use SQLite for a local DB (local in the Raspberry) but I have to interface with Database Servers where SQL runs (and sometimes also Oracle).
So...I've read the document at the link that you published but...still doesn't work, I get the same error.
Searching on the forum I found a post where it was suggested to install " libqt5sql5-odbc".
I installed it and now I have some other drivers available, included "QODBC", "QODBC3".
The piece of code that I'm using to try to connect to the DB is the following:

QString cnStr = QStringLiteral("Driver={SQL Server};Server=192.168.1.205\\CASA;Database=DBHome;Trusted_Connection=yes;user=sa; password=12345;");
    QSqlDatabase db = QSqlDatabase::addDatabase("QODBC3");
    db.setDatabaseName(cnStr);

Now the error that i get is:
[unixODBC][Driver Manager]Can't open lib "SQL Server"
:file not found QODBC3:Unable to connect

Maybe the connection string is not correct?

Roberto

JonB

@washburn_it
I won't guess. There are fair few Google hits for [unixODBC][Driver Manager]Can't open lib "SQL Server", I suggest you read through them.

BTW, although it's probably unrelated to your problem

Trusted_Connection=yes;user=sa; password=12345;

Are you aware that you are asking for two different ways to authenticate here? I imagine the Trusted_Connection will override the user/password method. When you have sorted out whatever to connect, you should know which of the two you are wishing to use.

washburn_it

I did some searches and I was missing some configuration steps that I never had the need to do when using Lazarus.
Ok...so I've installed everything related to "freetds" and "unixodbc".
Configured "/etc/freetds.conf" as follows:

[SQLSRV]
host=192.168.1.205
port=1433
tds version=7.4
instance=CASA

then checked the connection from the commandline with:

tsql -S SQLSRV -U sa

it asked the password, typed the password but the connection failed telling:

Error 20012 (severity 2):
	Server name not found in configuration files.
locale is "it_IT.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
Error 20013 (severity 2):
	Unknown host machine name.
Error 20009 (severity 9):
	Unable to connect: Adaptive Server is unavailable or does not exist
There was a problem connecting to the server

I found a post where an user suggested to "cp /etc/freetds.conf ~/.freetds.conf" (<-- this last operation is needed only to make "tsql ....." connecting without errors, it's not needed for Qt to work as I discovered later)

Ok...I tried to go on and configured "/etc/odbcinst.ini" as follows:

[FreeTDS]
Description=FreeTDS unixODBC Driver
Driver = /usr/lib/arm-linux-gnueabihf/odbc/libtdsodbc.so

and "/etc/odbc.ini":

[P860]
Description= SQL Server
Driver= /usr/lib/arm-linux-gnueabihf/odbc/libtdsodbc.so
Trace= No
Server= 192.168.1.205\CASA
Port= 1433
TDS_Version= 7.4

then I checked the connection with:

isql P860 sa 12345

and this time it connected and I was able to execute queries.
I went back to the code and modified this way:

QString cnStr = QStringLiteral("P860");
QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
db.setDatabaseName(cnStr);

bool ok = db.open("sa", "12345");

...and now it can connect to SQL Server !
Thanks for the support !

Roberto