PRB: Output Param Not Returned from Procedure Without Resultset

• The Microsoft Foundation Classes (MFC), included with:
  • Microsoft Visual C++, 32-bit Editions, versions 5.0, 6.0

SYMPTOMS

When using stored procedures with the Microsoft Foundation Class Library's CRecordset class, no values are returned for output parameters unless the stored procedure returns a resultset.

CAUSE

CRecordset is not designed to handle output parameters from a stored procedure if no resultset is returned.

RESOLUTION

If the stored procedure doesn't return a resultset, derive from CDatabase and override the BindParameters function to provide your own binding. Then use CDatabase::ExecuteSQL directly without using CRecordset.

NOTE: It is possible to use a CRecordset derived class to retrieve the results of a stored procedure that doesn't return a result set by overriding the CRecordset::Move() function and returning from the method without doing anything. This allows you to use the CRecordset and RFX functions for binding in and out parameters.

Sample of BindParameters

   void CExecDatabase::BindParameters(HSTMT hstmt)
   {
      // This sample shows how to return RETURN code and OUTPUT params.
      RETCODE nRetCode;
      m_lDbOutParam = 0;   // First, the RETURN parameter.
      m_lBufLength = sizeof(m_lDbOutParam);
      // This is binding the RETURN value.
      nRetCode = ::SQLBindParameter(hstmt, 1, SQL_PARAM_OUTPUT,
                                    SQL_C_SLONG, SQL_INTEGER,
                                    11, 0, (void*)&m_lDbOutParam,
                                    0, &m_lBufLength);

      m_lDbOutParam2 = 1;  // Second, the OUTPUT parameter.
      m_lBufLength2 = sizeof(m_lDbOutParam2);
      // This is binding the OUTPUT value.
      nRetCode = ::SQLBindParameter(hstmt, 2, SQL_PARAM_OUTPUT,
                                    SQL_C_SLONG, SQL_INTEGER,
                                    11, 0, (void*)&m_lDbOutParam2,
                                    0, &m_lBufLength2);
   }

   // In your MFC code, call your stored procedure directly.
   // CRecordset is not involved.
   db.ExecuteSQL(_T("{?=CALL TestQuery(?)}")); 

   while( rs.FlushResultSet())
      {
         while (!rs.IsEOF())
         {
            rs.MoveNext();
         }
      }
   // Now retrieve your bound parameters, as you normally would.
   m_strName = rs.m_noutparam; 

STATUS

This is by design.

Additional query words: kbvc500 kbvc600

Keywords : kbDatabase kbMFC kbODBC kbVC
Version : WINNT:5.0
Platform : winnt
Issue type : kbprb