Getting list of available ODBC data sources in my Qt application

Question

On my Windows box I can list user and system ODBC dsns. For example:

In my code I can connect to a database by using the name of a data source. For example:

QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
db.setDatabaseName("M10-Server-Production");

I would like to obtain the list of data source names, both user and system, filter them to include only the ones suitable for my application, in this example they will start with "M10-Server", then offer the list to the user so the user can select which database to connect to.

How do I get the list of DSNs programmatically?

NB: the app may be run on Windows or Linux so solutions for either or both are welcomed.

http://doc.qt.io/qt-5/qsqldatabase.html#drivers ? - It's a list of drivers, supported by your application. — Dmitry Sazonov, Oct 08 '15 at 16:39
@SaZ yes - on my windows box it returns the following list: QSQLITE, QMYSQL, QMYSQL3, QODBC, QODBC3, QPSQL, QPSQL7. On my Linux box it returns something similar. That is to say, it does not return the data sources, just installed drivers. Thanks for your suggestion, though. — Michael Vincent, Oct 09 '15 at 08:21

erg · Accepted Answer · 2015-10-14T09:28:19.833

On windows, you can use the function SQLDataSources to list available System and/or user DSN that are configured on the system. See here for more information: https://msdn.microsoft.com/en-us/library/ms711004%28v=vs.85%29.aspx

This function is also available in unixodbc, but I never worked with unixodbc.

Some compilable and working sample code to do this on windows:

#include <windows.h>
#include <tchar.h>
#include <iostream>
#include <sql.h>
#include <sqlext.h>
#include <sqlucode.h>

#define BUFF_LENGTH 1024

int _tmain(int argc, _TCHAR* argv[])
{
    // Get an Environment handle
    SQLHENV hEnv = SQL_NULL_HENV;
    // SQLAllocHandle() is for ODBC 3.x
    SQLRETURN ret = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv);
    // If you do not have ODBC 3.x try with the old version:
    // SQLRETURN ret = SQLAllocEnv(&hEnv);
    if(!SQL_SUCCEEDED(ret))
    {
        std::wcerr << L"No handle" << std::endl;
        return 1;
    }
    // set odbc version (this is required, if not set we get 'function sequence error')
    ret = SQLSetEnvAttr(hEnv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3_80, NULL);
    if(!SQL_SUCCEEDED(ret))
    {
        std::wcerr << L"Failed to set version" << std::endl;
        SQLFreeHandle(SQL_HANDLE_ENV, hEnv);
        return 1;
    }

    // Query sources
    SQLWCHAR nameBuffer[BUFF_LENGTH];
    SQLWCHAR descBuffer[BUFF_LENGTH];
    SQLSMALLINT nameBufferLength = 0;
    SQLSMALLINT descBufferLength = 0;
    ret = SQLDataSources(hEnv, SQL_FETCH_FIRST, nameBuffer, BUFF_LENGTH, &nameBufferLength, descBuffer, BUFF_LENGTH, &descBufferLength);
    if(ret == SQL_NO_DATA)
    {
        // no entries found
        std::wcout << L"No DSN found" << std::endl;
    }
    else if(SQL_SUCCEEDED(ret))
    {
        do
        {
            // do something with the name available in nameBuffer now..
            std::wcerr << L"Name: " << nameBuffer << std::endl;
            // then fetch the next record
            ret = SQLDataSources(hEnv, SQL_FETCH_NEXT, nameBuffer, BUFF_LENGTH, &nameBufferLength, descBuffer, BUFF_LENGTH, &descBufferLength);
        } while(ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO);
    }
    else
    {
        SQLSMALLINT recNr = 1;
        SQLRETURN ret = SQL_SUCCESS;
        while(ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)
        {
            SQLWCHAR errMsg[SQL_MAX_MESSAGE_LENGTH + 1];
            SQLWCHAR sqlState[5 + 1];
            errMsg[0] = 0;
            SQLINTEGER nativeError;
            SQLSMALLINT cb = 0;
            ret = SQLGetDiagRec(SQL_HANDLE_ENV, hEnv, recNr, sqlState, &nativeError, errMsg, SQL_MAX_MESSAGE_LENGTH + 1, &cb);
            if(ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)
            {
                std::wcerr << L"ERROR; native: " << nativeError << L"; state: " << sqlState << L"; msg: " << errMsg << std::endl;
            }
            ++recNr;
        }
        std::wcerr << L"Failed";

        SQLFreeHandle(SQL_HANDLE_ENV, hEnv);
        return 1;
    }


    SQLFreeHandle(SQL_HANDLE_ENV, hEnv);
    return 0;
}

Replace the SQL_FETCH_FIRST with SQL_FETCH_FIRST_USER or SQL_FETCH_FIRST_SYSTEM to get only user or system dsn entries.

Note that you need to link against odbc32.lib when building the app.

Thanks for your answer. This is exactly the sort of thing I was looking for. I'm having trouble getting SQLAllocHandle() through the compiler, though. I get an "unresolved external symbol" error reported. Any ideas how to sort this? Thanks again. — Michael Vincent, Oct 12 '15 at 13:50
@Michael: On Windows? I tested the above code with visual studio 2013, you should be able to just create a new win32 console app and copy the above content into the main.cpp (replacing all the rest). Do you have all the includes? SQLAllocHandle should be declared in sql.h if ODBCVER >= 0x0300 . — erg, Oct 12 '15 at 15:24
Thanks for that. I'm using Qt Creator rather than VS. I copied the list of includes and the compiler didn't object to any of them. I need to check the ODBC version. I suspect that QTCreator is picking up a version older than 3. Thank you. — Michael Vincent, Oct 12 '15 at 15:44
This is somehow strange, if you include the same headers you should get the same result, not? For completeness, I checked, I do not have any additional include directories set and have the following defined for the pre-processer - maybe one of those is missing in your case: WIN32;_DEBUG;_CONSOLE;_LIB; Altough I dont see how they could have any influence on the odbc version. And: Try to replace SQLAllocHandle with SQLAllocEnv() - this is the deprecated odbc 2.x function that was replaced by SQLAllocHandle - I added the old version as commented code in the sample code above. — erg, Oct 13 '15 at 06:56
It is odd. I'm going to accept your answer and open another question if I can't sort it out. That'll work better than trying to work it out in the comments. Thanks again for your help. It is much appreciated. — Michael Vincent, Oct 13 '15 at 09:43
Thanks for this answer. None of the documentation or examples I found mentioned having to call `SQLSetEnvAttr` and I was getting exactly the "function sequence error" code. — caps, Feb 07 '20 at 21:56

Getting list of available ODBC data sources in my Qt application

1 Answers1

Linked