通过 ODBC 从 C# 读取 Netezza 数据库表在 Windows 7 中不起作用 [英] Reading Netezza database table from C# via ODBC not working in Windows 7

问题描述

为什么 .NET 无法通过安装的 {NetezzaSQL} 驱动程序连接到我的 Netezza 盒子?64 位应用程序也无法通过此 ODBC 连接进行连接.为什么会这样?我已经在控制面板中建立了用户和系统 Netezza ODBC 连接，当我单击测试连接"时，它们都可以正常工作吗?我看到注册表中的值，但是当我遍历注册表驱动程序时，.NET 没有看到NetezzaSQL".据 Netezza 称，他们没有 64 位 ODBC 驱动程序.他们提供的驱动程序应该适用于 32 位和 64 位应用程序.这可能是 Windows 7 的权限问题吗?

Why can't .NET connect to my Netezza box via the installed {NetezzaSQL} driver? 64bit applications also cannot connect via this ODBC connection. Why would that be? I've built both user and system Netezza ODBC connections in Control Panel, and both work fine when I click "Test Connection"? I see the value in the registry, but when I traverse the registry drivers, .NET does not see "NetezzaSQL". According to Netezza, they don't have a 64 bit ODBC driver. The driver they provide should work for 32 and 64 bit applications. Could this be a permissions issue perhaps with Windows 7?

static void CreateNetezzaTableObjectFolders()
{
    //string activeDir = @"C:SourceEDWdw-objects	runk";

    OdbcConnection conn = new OdbcConnection();
    conn.ConnectionString = "Driver={NetezzaSQL};servername=10.1.170.18;port=5480;database=DEV_SANDBOX; username=mac;password=secret;";

    OdbcDataReader rdr = null;

    try
    {
        conn.Open();
        System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand("SELECT OBJECT_NAME FROM QA_ETL..STG_OBJECTS", conn);
        rdr = cmd.ExecuteReader();
        while (rdr.Read())
        {
            Console.WriteLine(rdr[0]);
            // Create Folder
            //string objectName = rdr[0].ToString();
            //string newPath = System.IO.Path.Combine(activeDir, objectName);
            //System.IO.Directory.CreateDirectory(newPath);
        }
    }
    finally
    {
        // close the reader
        if (rdr != null)
        {
            rdr.Close();
        }

        // close the connection
        if (conn != null)
        {
            conn.Close();
        }
    }
}

在新选项卡中打开以查看 ODBC 的注册表设置并放大:

==============================

============================

显然，驱动程序名称位于大括号内.当我以编程方式获取驱动程序列表时，我看不到驱动程序.如何在 Windows 用户界面中添加到此列表?但是，我会在控制面板">管理工具">ODBC 驱动程序"下看到已安装的 Netezza 驱动程序.

Apparently, the driver name goes inside of the curly brackets. When I get a list of drivers programmatically, I don't see the driver. How do I add to this list in the Windows user interface? I do however, see the installed Netezza driver under the Control Panel > Administrative Tools > ODBC Drivers.

以下是一些验证我所解释内容的代码:

Here is some code that verifies what I am explaining:

public static void GetSystemDriverList()
{
    //List<string> names = new List<string>();
    // get system dsn's
    Microsoft.Win32.RegistryKey reg = (Microsoft.Win32.Registry.LocalMachine).OpenSubKey("Software");
    if (reg != null)
    {
        reg = reg.OpenSubKey("ODBC");
        if (reg != null)
        {
            reg = reg.OpenSubKey("ODBCINST.INI");
            if (reg != null)
            {

                reg = reg.OpenSubKey("ODBC Drivers");
                if (reg != null)
                {
                    // Get all DSN entries defined in DSN_LOC_IN_REGISTRY.
                    foreach (string sName in reg.GetValueNames())
                    {
                        Console.WriteLine(sName);
                        //names.Add(sName);
                    }
                }
                try
                {
                    reg.Close();
                }
                catch { /* ignore this exception if we couldn't close */ }
            }
        }
    }

    Console.ReadLine();
}    

这是我在使用用户 ODBC 连接时从 Windows 7 框中的 Microsoft Access 2007 获得的连接错误.系统一不可见.

Here is the connection error I get from Microsoft Access 2007 from the Windows 7 box when I use the user ODBC connection. The system one was not visible.

错误:ODBC--调用失败.[Microsoft][ODBC 驱动程序管理器] 指定的 DSN 包含驱动程序和应用程序之间的架构不匹配 (#0)"

Error: "ODBC--call failed. [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application (#0)"

推荐答案

Microsoft 从未重命名他们的system32"文件夹，因此 system32 确实拥有所有 64 位驱动程序.SysWow64 文件夹包含所有 32 位驱动程序.默认情况下，在 Visual Studio 2010 中创建的 .NET 项目将具有 x86 的目标平台".我只是将其更改为 x64，并且我的连接使我的 64 位 NetezzaSQL 驱动程序适用于 .NET.

Microsoft never renamed their "system32" folder, so system32 really has all of the 64 bit drivers. And SysWow64 folder has all of the 32 bit drivers. By default, a .NET project created in Visual Studio 2010 will have a "Target Platform" of x86. I just changed it to x64 and my connection worked to get my 64 bit NetezzaSQL driver working for .NET.

刚刚意识到您可以通过运行 C:Windowssystem32 文件夹中的odbcad32.exe"文件(这是 64 位版本的用于构建 64 位驱动程序的对话框)，或在 C:WindowsSysWow64 文件夹中(这是用于构建 32 位驱动程序的对话框的 32 位/x86 版本).是的，文件夹被颠倒了，因为微软在那种方式上很奇怪.

Just realize that you can launch the ODBC (Data Sources) dialog under Control Panel > Administrative Tools by running the "odbcad32.exe" file in either the C:Windowssystem32 folder (this is the 64 bit version of the dialog to build 64 bit drivers), or in the C:WindowsSysWow64 folder (this is the 32 bit/x86 version of the dialog to build 32 bit drivers). Yes, the folders are reversed because Microsoft is weird that way.

此外，注册表中有两个不同的文件夹可以构建.原来上面的列表(在我的黑色屏幕截图中)来自 ComputerHKEY_LOCAL_MACHINESOFTWAREWow6432NodeODBC 列表，因为它们是 32 位驱动程序.我安装的 dll，nzodbcsetup.exe"，显然是作为 64 位驱动程序安装的.我不确定如何在 64 位 Windows 上将其安装为 32 位驱动程序，所以这就是我将 .NET 项目中的目标平台"从默认 x86 更改为 x64 的原因.必须有办法将它安装为 32 位驱动程序，因为当我将它安装在 32 位 Windows XP 机器上时，驱动程序运行良好.

Furthermore, there are two different folders in the Registry that get built. Turns out that the list above (in my black screen shot) comes from the ComputerHKEY_LOCAL_MACHINESOFTWAREWow6432NodeODBC list because those are 32 bit drivers. The dll I installed, "nzodbcsetup.exe", apparently installed as a 64 bit driver. I am not exactly sure how to install this as a 32 bit driver on 64 bit windows, so that's why I changed the "Target Platform" in my .NET project from the default x86 to x64. There has to be a way to install it as a 32 bit driver as well because when I installed this on a 32 bit Windows XP box, the driver worked fine.

奇怪的是 Netezza 的 OLE 驱动程序是分开的.他们有一个 32 位和一个 64 位的 dll exe 文件(适用于 6.0.3 版).nzoledbsetup.exe"(~82MB x86)和nzoledbsetup64.exe"(~102MB x64).但是 ODBC 只有一个，显然两者兼而有之.我尝试在 SysWOW6432Node 中手动构建 32 位驱动程序(在运行 > regedit 下)并指向驱动程序文件和安装文件(在 system32 文件夹中，但出现错误.请评论在 Windows 中是否有一种方法可以自动安装为32 位，不是 64.

What's weird is that Netezza's OLE drivers are separated. They have a 32 bit and a 64 bit dll exe file (for version 6.0.3). "nzoledbsetup.exe" (~82MB x86) and "nzoledbsetup64.exe" (~102MB x64). But the ODBC just has one, which is apparently both. I tried manually building a 32 bit driver in the SysWOW6432Node (under run > regedit) and pointed to the Driver file and Setup file (in the system32 folder, but I got an error. Please comment is there a way in Windows to automatically install as 32 bit, not 64.

这篇关于通过 ODBC 从 C# 读取 Netezza 数据库表在 Windows 7 中不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！