PRB: Output Param Not Returned from Procedure Without Resultset

• The Microsoft Foundation Classes (MFC) included with:

      - Microsoft Visual C++, 32-bit Editions, version 5.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.

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.

Keywords          : MfcDatabase
Version           : WINNT:5.0
Platform          : winnt
Issue type        : kbprb