Connection string for ODBC / Microsoft SQL Express

Mark81

I need to connect to a Microsoft SQL Express database through ODBC.
I have got this information:

ip address of the server
name of the database
username
password

Here my code:

QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
db.setConnectOptions();

QString connectionString = QString("DRIVER={SQL Server};Server=192.168.2.22;Database=NAME;Uid=USER;Pwd=PASSWORD;");
db.setDatabaseName(connectionString);
qDebug() << db.open();
qDebug() << db.lastError();

The output is:

false
QSqlError("0", "QODBC: Unable to connect", "[unixODBC][Driver Manager]Can't open lib 'SQL Server' : file not found")

It seems anything I put inside { } is the library it tries to open.
So I tried to pass the full path of the driver - even I've never seen such a connection string:

QString connectionString = QString("DRIVER={/home/user/bin/plugins/sqldrivers/libqsqlodbc.so};[...]");

Now the error is different:

QSqlError("0", "QODBC: Unable to connect", "[unixODBC][Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_HENV failed")

Before begin to dig this error, I'd like to be sure my approach is correct...

artwaw

@Mark81 According to the documentation you form the string wrongly.

Mark81

@artwaw I read that page but I thought that example was specific for HANA db!
Anyway I think I have to select the proper library, in my case from:

$ ldd libqsqlodbc.so 
	linux-vdso.so.1 (0x00007ffeb81d5000)
	libodbc.so.2 => /lib/x86_64-linux-gnu/libodbc.so.2 (0x00007fbfe2cb1000)
	libQt6Sql.so.6 => not found
	libQt6Core.so.6 => not found
	libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0 (0x00007fbfe2c8e000)
	libstdc++.so.6 => /lib/x86_64-linux-gnu/libstdc++.so.6 (0x00007fbfe2aac000)
	libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x00007fbfe295d000)
	libgcc_s.so.1 => /lib/x86_64-linux-gnu/libgcc_s.so.1 (0x00007fbfe2940000)
	libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007fbfe274e000)
	libltdl.so.7 => /lib/x86_64-linux-gnu/libltdl.so.7 (0x00007fbfe2743000)
	/lib64/ld-linux-x86-64.so.2 (0x00007fbfe2d58000)
	libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007fbfe273d000)

I guess the correct one is /lib/x86_64-linux-gnu/libodbc.so.2 :

QString connectionString = QStringLiteral(
    "DRIVER=/lib/x86_64-linux-gnu/libodbc.so.2;"
    "SERVERNODE=192.168.2.22:1433;"
    "UID=USER;"
    "PWD=PASSWORD;");

but the output is the same:

QSqlError("0", "QODBC: Unable to connect", "[unixODBC][Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_HENV failed")

piervalli

@Mark81 Usually I use this link to check the connection string. https://www.connectionstrings.com/
On Windows it works.

artwaw

@Mark81 said in Connection string for ODBC / Microsoft SQL Express:

Driver's SQLAllocHandle on SQL_HANDLE_HENV failed

That's completely different error now and sadly, I suggest googling for it in the context of ODBC in general. First of the results offers different potential solutions https://stackoverflow.com/questions/55474713/drivers-sqlallochandle-on-sql-handle-henv-failed-0-sqldriverconnect-when-co

Mark81

@artwaw I didn't find this information in the Qt docs pages - at least not in the pages I read.
So it's beyond my knowledge right now. The solutions proposed are very specific (they talks about Python, docker and Windows). I'll try to guess what I missed..

Mark81

@piervalli from that site I grabbed the first connection string I reported in the initial question. But it was wrong... so I'm not sure if it applies to the Qt implementation too.

artwaw

@Mark81 my understanding of your current situation is that it goes beyond Qt (as in Qt part is done right). Seems to me the problem now lies in ODBC configuration in the system.

mchinand

Can you connect to it via another ODBC client (i.e., one not based on Qt)?