Getting list of available ODBC data sources in my

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.

Answer 1

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.