在我的 Qt 应用程序中获取可用 ODBC 数据源的列表 [英] Getting list of available ODBC data sources in my Qt application

问题描述

在我的 Windows 机器上，我可以列出用户和系统 ODBC dsns.例如:

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");

我想获取数据源名称列表，包括用户和系统，过滤它们以仅包含适合我的应用程序的名称，在此示例中，它们将以M10-Server"开头，然后将列表提供给用户，以便用户可以选择要连接的数据库.

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.

如何以编程方式获取 DSN 列表?

How do I get the list of DSNs programmatically?

注意:该应用可以在 Windows 或 Linux 上运行，因此欢迎针对其中之一或两者的解决方案.

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

推荐答案

在 Windows 上，您可以使用函数 SQLDataSources 列出系统上配置的可用系统和/或用户 DSN.有关详细信息，请参阅此处:https://msdn.microsoft.com/en-us/library/ms711004%28v=vs.85%29.aspx

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

unixodbc 也有这个功能，不过我没用过 unixodbc.

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

一些可在 Windows 上执行此操作的可编译和工作示例代码:

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;
}

将 SQL_FETCH_FIRST 替换为 SQL_FETCH_FIRST_USER 或 SQL_FETCH_FIRST_SYSTEM 以仅获取用户或系统 dsn 条目.

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

请注意，在构建应用程序时，您需要链接 odbc32.lib.

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

这篇关于在我的 Qt 应用程序中获取可用 ODBC 数据源的列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！