PRB: Output Param Not Returned from Procedure Without Resultset

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(?)}"));
				

However, if the stored procedure does return a resultset, use CRecordset's FlushResultSet function as follows:

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