将SQL Provider从SQLOLEDB.1更改为SQLNCLI.1会导致应用程序在通过存储过程访问数据时失败

I'm supporting a legacy app written in MFC/C++. The database for the app is in SQL Server 2000. We bolted on some new functionality recently and found that when we change the SQL Provider from SQLOLEDB.1 to SQLNCLI.1 some code that is trying to retrieve data from a table via a stored procedure fails.

我支持用MFC / C ++编写的遗留应用程序。该应用程序的数据库位于SQL Server 2000中。我们最近使用了一些新功能,发现当我们将SQL Provider从SQLOLEDB.1更改为SQLNCLI.1时,一些代码试图通过存储过程从表中检索数据失败。

The table in question is pretty straightforward and was created via the following script:

有问题的表非常简单,并通过以下脚本创建:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UAllergenText](
    [TableKey] [int] IDENTITY(1,1) NOT NULL,
    [GroupKey] [int] NOT NULL,
    [Description] [nvarchar](150) NOT NULL,
    [LanguageEnum] [int] NOT NULL,
CONSTRAINT [PK_UAllergenText] PRIMARY KEY CLUSTERED
(
    [TableKey] ASC) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
    IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[UAllergenText]  WITH CHECK ADD  CONSTRAINT 
FK_UAllergenText_UBaseFoodGroupInfo] FOREIGN KEY([GroupKey])
REFERENCES [dbo].[UBaseFoodGroupInfo] ([GroupKey])
GO
ALTER TABLE [dbo].[UAllergenText] CHECK CONSTRAINT 
FK_UAllergenText_UBaseFoodGroupInfo]

Bascially four columns, with TableKey being an identity column and everything else is populated via the following script:

基本上是四列,其中TableKey是一个标识列,其他所有内容都通过以下脚本填充:

INSERT INTO UAllergenText (GroupKey, Description, LanguageEnum)
VALUES (401, 'Egg', 1)

with a long list of other INSERT INTO's that follow the one above. Some of the rows inserted have special characters (like accent marks above letters) in their descriptions. I had originally thought that the inclusion of the special characters was part of the problem but if I completely clear out the table and then repopulate it with just the single INSERT INTO from above that has no special characters, it still fails.

以及上面列出的其他INSERT INTO列表。插入的某些行在其描述中具有特殊字符(如字母上方的重​​音符号)。我原本以为包含特殊字符是问题的一部分,但是如果我完全清除表格,然后只用上面没有特殊字符的单个INSERT INTO重新填充它,它仍然会失败。

So I moved on...

所以我继续......

The data in this table is then accessed via the following code:

然后通过以下代码访问此表中的数据:

std::wstring wSPName = SP_GET_ALLERGEN_DESC;
_variant_t  vtEmpty1 (DISP_E_PARAMNOTFOUND, VT_ERROR);
_variant_t  vtEmpty2(DISP_E_PARAMNOTFOUND, VT_ERROR);

_CommandPtr pCmd = daxLayer::CDataAccess::GetSPCommand(pConn, wSPName); 
pCmd->Parameters->Append(pCmd->CreateParameter("@intGroupKey", adInteger, adParamInput, 0, _variant_t((long)nGroupKey)));
pCmd->Parameters->Append(pCmd->CreateParameter("@intLangaugeEnum", adInteger, adParamInput, 0, _variant_t((int)language)));

_RecordsetPtr pRS = pCmd->Execute(&vtEmpty1, &vtEmpty2, adCmdStoredProc);            

//std::wstring wSQL = L"select Description from UAllergenText WHERE GroupKey = 401 AND LanguageEnum = 1";
//_RecordsetPtr pRS = daxLayer::CRecordsetAccess::GetRecordsetPtr(pConn,wSQL);

if (pRS->GetRecordCount() > 0)
{
    std::wstring wDescField = L"Description";
    daxLayer::CRecordsetAccess::GetField(pRS, wDescField, nameString);
}   
else
{
    nameString = "";
}

The daxLayer is a third party data access library the application is using, though we have the source to it (some of which will be seen below.) SP__GET_ALLERGEN_DESC is the stored proc used to get the data out of the table and it was created via this script:

daxLayer是应用程序正在使用的第三方数据访问库,虽然我们有它的源代码(其中一些将在下面看到。)SP__GET_ALLERGEN_DESC是用于从表中获取数据的存储过程,它是通过这个脚本:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[spRET_AllergenDescription] 
-- Add the parameters for the stored procedure here
    @intGroupKey int, 
    @intLanguageEnum int
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT Description FROM UAllergenText WHERE GroupKey = @intGroupKey AND LanguageEnum = @intLanguageEnum
END

When the SQL Provider is set to SQLNCLI.1, the app blows up at:

当SQL提供程序设置为SQLNCLI.1时,应用程序会爆炸:

daxLayer::CRecordsetAccess::GetField(pRS, wDescField, nameString);

from the above code snippet. So I stepped into GetField, which looks like the following:

从上面的代码片段。所以我进入GetField,看起来如下:

void daxLayer::CRecordsetAccess::GetField(_RecordsetPtr pRS,
const std::wstring wstrFieldName, std::string& sValue, std::string  sNullValue)
{
    if (pRS == NULL)
    {
        assert(false);
        THROW_API_EXCEPTION(GetExceptionMessageFieldAccess(L"GetField", 
        wstrFieldName, L"std::string", L"Missing recordset pointer."))
    }
    else
    {
        try
        {
            tagVARIANT tv = pRS->Fields->GetItem(_variant_t(wstrFieldName.c_str()))->Value;

            if ((tv.vt == VT_EMPTY) || (tv.vt == VT_NULL))
            {
                sValue = sNullValue;
            }
            else if (tv.vt != VT_BSTR)
            {
                // The type in the database is wrong.
                assert(false);
                THROW_API_EXCEPTION(GetExceptionMessageFieldAccess(L"GetField", 
                wstrFieldName, L"std::string", L"Field type is not string"))
            }
            else
            {
                 _bstr_t bStr = tv ;//static_cast<_bstr_t>(pRS->Fields->GetItem(_variant_t(wstrFieldName.c_str()))->Value);                     
                 sValue = bStr;
            }
        }
        catch( _com_error &e )
        {
            RETHROW_API_EXCEPTION(GetExceptionMessageFieldAccess(L"GetField", 
            wstrFieldName, L"std::string"), e.Description())
        }
        catch(...)
        {        
            THROW_API_EXCEPTION(GetExceptionMessageFieldAccess(L"GetField",
            wstrFieldName, L"std::string", L"Unknown error"))
        }
    }
}

The culprit here is:

罪魁祸首是:

tagVARIANT tv = pRS->Fields->GetItem(_variant_t(wstrFieldName.c_str()))->Value;

Stepping into Fields->GetItem brings us to:

踏入Fields-> GetItem将我们带到:

GetItem

inline FieldPtr Fields15::GetItem ( const _variant_t & Index ) {
    struct Field * _result = 0;
    HRESULT _hr = get_Item(Index, &_result);
    if (FAILED(_hr)) _com_issue_errorex(_hr, this, __uuidof(this));
    return FieldPtr(_result, false);
}

Which then takes us to:

然后我们将:

GetValue

inline _variant_t Field20::GetValue ( ) {
    VARIANT _result;
    VariantInit(&_result);
    HRESULT _hr = get_Value(&_result);
    if (FAILED(_hr)) _com_issue_errorex(_hr, this, __uuidof(this));
    return _variant_t(_result, false);
}

If you look at _result while stepping through this at runtime, _result's BSTR value is correct, its value is "Egg" from the "Description" field of the table. Continuing to step through traces back through all the COM release calls, etc. When I finally get back to:

如果你在运行时单步查看_result,_result的BSTR值是正确的,它的值是表格“Description”字段中的“Egg”。继续逐步浏览所有COM释放调用等。当我最终回到:

tagVARIANT tv = pRS->Fields->GetItem(_variant_t(wstrFieldName.c_str()))->Value;

And step past it to the next line, the contents of tv, which should be BSTR="Egg" are now:

然后跳过它到下一行,tv的内容,应该是BSTR =“Egg”现在:

tv BSTR = 0x077b0e1c "ᎀݸﻮﻮﻮﻮﻮﻮﻮﻮﻮﻮﻮﻮ㨼㺛帛᠄"

When the GetField function tries to set its return value to the value in tv.BSTR

当GetField函数尝试将其返回值设置为tv.BSTR中的值时

_bstr_t bStr = tv;
sValue = bStr;

it unsurprisingly chokes and dies.

毫不奇怪的是窒息而死。

So what happened to the value of BSTR and why does it only happen when the provider is set to SQLNCLI.1?

那么BSTR的价值究竟发生了什么?为什么只有当提供者设置为SQLNCLI.1时才会发生?

For the heck of it, I commented out using the stored procedure in the topmost code and just hard coded the same SQL SELECT statement that the stored procedure uses and found that it works just fine and the value returned is correct.

为此,我注释掉了使用最顶层代码中的存储过程,只是对存储过程使用的相同SQL SELECT语句进行了硬编码,发现它工作得很好并且返回的值是正确的。

Also, it's possible for users to add rows to the table through the application. If the application creates a new row in that table and retrieves that row via stored procedure, it also works correctly unless you include a special character in the description in which case it correctly saves the row but blows up again in the exact same way as above upon retrieval of that row.

此外,用户可以通过应用程序向表中添加行。如果应用程序在该表中创建一个新行并通过存储过程检索该行,它也可以正常工作,除非您在描述中包含一个特殊字符,在这种情况下它正确地保存行但是以与上面完全相同的方式再次爆炸检索那一行。

So to summarize, if I can, rows put into the table via the INSERT script ALWAYS blow up the app when they are accessed by stored procedure (regardless of whether they contain any special characters). Rows put into the table from within the application by the user at runtime are retrieved correctly via stored procedure UNLESS they contain a special character in the Description, at which point they blow up the app. If you access any of the rows in the table by using SQL from the code at runtime instead of the stored procedure it works whether there is a special character in the Description or not.

总而言之,如果可以的话,通过INSERT脚本放入表中的行总是在存储过程访问应用程序时将应用程序炸毁(无论它们是否包含任何特殊字符)。用户在运行时从应用程序内放入表中的行将通过存储过程正确检索,除非它们在描述中包含特殊字符,此时它们会炸毁应用程序。如果通过在运行时使用SQL而不是存储过程访问表中的任何行,则无论描述中是否存在特殊字符,它都可以工作。

Any light that can be shed on this will be greatly appreciated, and I thank you in advance.

任何可以在这上面散发的光都将不胜感激,我提前感谢你。

1 个解决方案

#1

This line might be problematic:

这条线可能有问题:

tagVARIANT tv = pRS->Fields->GetItem(_variant_t(wstrFieldName.c_str()))->Value;

If I read it right, ->Value returns a _variant_t, which is a smart pointer. The smart pointer will release its variant when it goes out of scope, right after this line. However, tagVARIANT is not a smart pointer, so it won't increase the reference count when it is assigned to. So after this line, tv might point to a variant which has effectively been released.

如果我读得正确, - > Value返回_variant_t,这是一个智能指针。智能指针将在超出范围时释放其变体,就在此行之后。但是,tagVARIANT不是智能指针,因此在分配时不会增加引用计数。所以在这一行之后,tv可能会指向一个已经有效释放的变体。

What happens if you write the code like this?

如果您编写这样的代码会发生什么?

_variant_t tv = pRS->Fields->GetItem(_variant_t(wstrFieldName.c_str()))->Value;

Or alternatively, tell the smart pointer not to release its payload:

或者,告诉智能指针不释放其有效负载:

_tagVARIANT tv = pRS->Fields->GetItem(
    _variant_t(wstrFieldName.c_str()))->Value.Detach();

It's been a long time since I coded in C++, and reading this post, I don't regret moving away!

自从我用C ++编写代码已经很长时间了,阅读这篇文章,我不后悔搬走了!