在单独的函数中调用SQLBindParameter()失败

Question

我希望通过将SQLPrepare()和SQLBindParameter()移动到单独的函数中，将代码分解成更小的部分。(在实际代码中，SQL查询长达200行。)

1. 我将替换参数标记的Caller char数组传递给函数。这是行不通的。
2. 我使用了一个可以在Caller中设置的实例变量。那也没用。
3. 我在单独的函数中注释掉了SQLBindParameter()命令，然后将其复制并粘贴到Caller中，并向它提供Caller的char数组。效果很好。
4. 我在Caller的SQLBindParameter()命令中使用了实例变量，这也很好。

有人知道为什么--除了我的非正统编码风格--在一个单独的函数中调用SQLBindParameter()不能工作吗？

MyClass.h
class MyClass
{   char                                        CHRSZ_NameKey[11];
    std::unique_ptr<std::map<int, std::string>> UPTR__NameKeysMap;
    SQLHANDLE                                   HNDL__SqlStatement;

    bool        BuildPreparedStatement( char* );
    bool        ExecutePreparedStatement();
    std::string RetrieveName();
} 


MyClass.cpp
MyClass::MyClass()
:   CHRSZ_NameKey(      "EMPTY"          ),
    HNDL__SqlStatement(  SQL_NULL_HANDLE )
{   UPTR__NameKeysMap = std::unique_ptr<std::map<int, std::string>>( new std::map<int, std::string>() );
}


bool MyClass::BuildPreparedStatement( char* chrsz_NameKey )
{   SQLINTEGER  sqint_NameKeyLengthOrIndicator  = SQL_NTS;
    std::string strng_SqlQuery;

    strng_SqlQuery  = "Select ";
    strng_sqlQuery += "    Name ";
    strng_SqlQuery += "from  Names ";
    strng_SqlQuery += "where NameKey = ?";

    if( SQL_SUCCESS != SQLPrepare( HNDL__SqlStatement, (SQLCHAR*) strng_SqlQuery.c_str(), SQL_NTS ) )
    {   ShowSqlError( SQL_HANDLE_STMT, HNDL__SqlStatement );
        return false;
    }

    std::cout << "MyClass::BuildPreparedStatement()   : chrsz_NameKey            = " <<         chrsz_NameKey << std::endl;
    std::cout << "MyClass::BuildPreparedStatement()   : Address of chrsz_NameKey = " << (void*) chrsz_NameKey << std::endl;
//  std::cout << "MyClass::BuildPreparedStatement()   : CHRSZ_NameKey            = " <<         CHRSZ_NameKey << std::endl;
//  std::cout << "MyClass::BuildPreparedStatement()   : Address of CHRSZ_NameKey = " << (void*) CHRSZ_NameKey << std::endl;

    if( SQL_SUCCESS != SQLBindParameter( HNDL__SqlStatement, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 11, 0, chrsz_NameKey, 0, &sqint_NameKeyLengthOrIndicator ) )
//  if( SQL_SUCCESS != SQLBindParameter( HNDL__SqlStatement, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 11, 0, CHRSZ_NameKey, 0, &sqint_NameKeyLengthOrIndicator ) )
    {   ShowSqlError( SQL_HANDLE_STMT, HNDL__SqlStatement );
        return false;
    }

    return true;
}


bool MyClass::ExecutePreparedStatement()
{   std::string                             strng_Name;
    char                                    chrsz_NameKey[11]               = "empty";
    std::string                             strng_NameKey;
    SQLINTEGER                              sqint_NameKeyLengthOrIndicator  = SQL_NTS;
    std::map<int, std::string>::iterator    iter__NameKeysMap;

    std::cout << "MyClass::ExecutePreparedStatement() : chrsz_NameKey            = " <<         chrsz_NameKey << "    (prior to reading NameKeysMap)" << std::endl;
    std::cout << "MyClass::ExecutePreparedStatement() : Address of chrsz_NameKey = " << (void*) chrsz_NameKey <<    " (prior to reading NameKeysMap)" << std::endl;
//  std::cout << "MyClass::ExecutePreparedStatement() : CHRSZ_NameKey            = " <<         CHRSZ_NameKey << "    (prior to reading NameKeysMap)" << std::endl;
//  std::cout << "MyClass::ExecutePreparedStatement() : Address of CHRSZ_NameKey = " << (void*) CHRSZ_NameKey <<    " (prior to reading NameKeysMap)" << std::endl;

//  <Snip> Connect to SQL Server and allocate HNDL__SqlStatement.

    BuildPreparedStatment( chrsz_NameKey );
//  if( SQL_SUCCESS != SQLBindParameter( HNDL__SqlStatement, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 11, 0, chrsz_NameKey, 0, &sqint_NameKeyLengthOrIndicator ) )
//  if( SQL_SUCCESS != SQLBindParameter( HNDL__SqlStatement, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 11, 0, CHRSZ_NameKey, 0, &sqint_NameKeyLengthOrIndicator ) )
//  {   ShowSqlError( SQL_HANDLE_STMT, HNDL__SqlStatement );
//      return false;
//  }

//  <Snip> Read NameKeys into UPTR__NameKeysMap.    

    iter__NameKeysMap = UPTR__NameKeysMap->begin();
    while( iter__NameKeysMap != UPTR__NameKeysMap->end() )
    {   strng_NameKey = iter__NameKeysMap->second;
        strcpy( chrsz_NameKey, strng_NameKey.c_str() );
//      strcpy( CHRSZ_NameKey, strng_NameKey.c_str() );

        std::cout << "MyClass::ExecutePreparedStatement() : chrsz_NameKey            = " <<         chrsz_NameKey << "    (after    reading NameKeysMap)" << std::endl;
        std::cout << "MyClass::ExecutePreparedStatement() : Address of chrsz_NameKey = " << (void*) chrsz_NameKey <<    " (after    reading NameKeysMap)" << std::endl;
//      std::cout << "MyClass::ExecutePreparedStatement() : CHRSZ_NameKey            = " <<         CHRSZ_NameKey << "    (after    reading NameKeysMap)" << std::endl;
//      std::cout << "MyClass::ExecutePreparedStatement() : Address of CHRSZ_NameKey = " << (void*) CHRSZ_NameKey <<    " (after    reading NameKeysMap)" << std::endl;

        if( SQL_SUCCESS != SQLExecute( HNDL__SqlStatement ) )
        {   ShowSqlError( SQL_HANDLE_STMT, HNDL__SqlStatement );
            return false;
        }

        strng_Name  = RetrieveName();

        iter__NameKeysMap++;
    }
}


std::string MyClass::RetrieveName()
{   char        chrsz_Name[257];
    SQLINTEGER  sqint_NameLengthOrIndicator = SQL_NTS;

    if( SQL_SUCCESS == SQLFetch( HNDL__SqlStatement ) )
    {   SQLGetData( HNDL__SqlStatement, 1, SQL_C_CHAR, chrsz_Name, 256, &sqint_NameLengthOrIndicator );
    }
    std::cout << "MyClass::RetrieveName()             : chrsz_Name               = '" << chrsz_Name << "'" << std::endl; 

    return std::string( chrsz_Name );
}

将chrsz_NameKey传递给BuildPreparedStatement()的输出：

MyClass::ExecutePreparedStatement() : chrsz_NameKey            = empty    (prior to reading NameKeysMap)
MyClass::ExecutePreparedStatement() : Address of chrsz_NameKey = 0x22fd09 (prior to reading NameKeysMap)
MyClass::BuildPreparedStatement()   : chrsz_NameKey            = empty
MyClass::BuildPreparedStatement()   : Address of chrsz_NameKey = 0x22fd09
MyClass::ExecutePreparedStatement() : chrsz_NameKey            = BB-62    (after    reading NameKeysMap)
MyClass::ExecutePreparedStatement() : Address of chrsz_NameKey = 0x22fd09 (after    reading NameKeysMap)
MyClass::RetrieveName()             : chrsz_Name               = ''

不知道为什么会失败。

注释掉chrsz_NameKey和取消注释实例变量CHRSZ_NameKey的输出：

MyClass::ExecutePreparedStatement() : CHRSZ_NameKey            = EMPTY    (prior to reading NameKeysMap)
MyClass::ExecutePreparedStatement() : Address of CHRSZ_NameKey = 0x818a18 (prior to reading NameKeysMap)
MyClass::BuildPreparedStatement()   : CHRSZ_NameKey            = EMPTY
MyClass::BuildPreparedStatement()   : Address of CHRSZ_NameKey = 0x818a18
MyClass::ExecutePreparedStatement() : CHRSZ_NameKey            = BB-62    (after    reading NameKeysMap)
MyClass::ExecutePreparedStatement() : Address of CHRSZ_NameKey = 0x818a18 (after    reading NameKeysMap)
MyClass::RetrieveName()             : chrsz_Name               = ''

真的不知道为什么会失败。

在BuildPreparedStatement()中注释掉BuildPreparedStatement()并使用chrsz_NameKey在ExecutePreparedStatement()中运行SQLBindParameter()的输出：

MyClass::ExecutePreparedStatement() : chrsz_NameKey            = empty    (prior to reading NameKeysMap)
MyClass::ExecutePreparedStatement() : Address of chrsz_NameKey = 0x22fd09 (prior to reading NameKeysMap)
MyClass::ExecutePreparedStatement() : chrsz_NameKey            = BB-62    (after    reading NameKeysMap)
MyClass::ExecutePreparedStatement() : Address of chrsz_NameKey = 0x22fd09 (after    reading NameKeysMap)
MyClass::RetrieveName()             : chrsz_Name               = 'USS New Jersey'

成功！

在BuildPreparedStatement()中注释掉BuildPreparedStatement()并使用CHRSZ_NameKey在ExecutePreparedStatement()中运行SQLBindParameter()的输出：

MyClass::ExecutePreparedStatement() : CHRSZ_NameKey            = EMPTY    (prior to reading NameKeysMap)
MyClass::ExecutePreparedStatement() : Address of CHRSZ_NameKey = 0x818a18 (prior to reading NameKeysMap)
MyClass::ExecutePreparedStatement() : CHRSZ_NameKey            = BB-62    (after    reading NameKeysMap)
MyClass::ExecutePreparedStatement() : Address of CHRSZ_NameKey = 0x818a18 (after    reading NameKeysMap)
MyClass::RetrieveName()             : chrsz_Name               = 'USS New Jersey'

又成功了。

另外，我将函数签名更改为：

bool        BuildPreparedStatement( char (&chrsz_NameKey)[11] );

并重新尝试了我最初的传递chrsz_NameKey的设置。我也有同样的失败。

我如何误用SQLBindParameter()？

Answer 1

读者，你是对的:这是一个超出范围的变量。

在功能上

bool MyClass::BuildPreparedStatement( char* chrsz_NameKey )
{   SQLINTEGER  sqint_NameKeyLengthOrIndicator  = SQL_NTS;
    ...
    if( SQL_SUCCESS != SQLBindParameter( HNDL__SqlStatement, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 11, 0, chrsz_NameKey, 0, &sqint_NameKeyLengthOrIndicator ) )
    ...

局部变量sqint_NameKeyLengthOrIndicator是罪魁祸首。我之所以这样想，是因为它只是告诉SQLBindParameter()，chrsz_NameKey是一个以空结尾的字符串，SQLBindParameter()拥有执行所需的一切。显然不是。

当执行SQLExecute()时，该变量必须在作用域中。因此，将MyClass::BuildPreparedStatement改为接收变量

bool MyClass::BuildPreparedStatement(char* chrsz_NameKey, SQLINTEGER& sqint_NameKeyLengthOrIndicator )
{ //SQLINTEGER  sqint_NameKeyLengthOrIndicator  = SQL_NTS;
    ...
    if( SQL_SUCCESS != SQLBindParameter( HNDL__SqlStatement, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 11, 0, chrsz_NameKey, 0, &sqint_NameKeyLengthOrIndicator ) )
    ...

并更改调用方以传递变量。

bool MyClass::ExecutePreparedStatement()
{   SQLINTEGER  sqint_NameKeyLengthOrIndicator  = SQL_NTS;
    ...
    BuildPreparedStatment( chrsz_NameKey, sqint_NameKeyLengthOrIndicator );
    ...
    if( SQL_SUCCESS != SQLExecute( HNDL__SqlStatement ) )
    ...

允许SQLExecute()正常工作。

在我为@Phil发布的链接中，微软似乎真的是这么说的：

StrLen_or_IndPtr参数指向一个缓冲区，当SQLExecute或SQLExecDirect被称为时，该缓冲区包含以下内容之一。

• SQL_NTS。参数值是以空结尾的字符串。