HOWTO: Retrieving Multiple Resultsets with Varying Columns

MORE INFORMATION

   void MyFunction()
   {
      // First resultset has only one column;
      // the second resultset has two columns.

      CString strTemp;
      CDatabase dbase;
      BOOL bRet = dbase.Open("SQLServer", FALSE,FALSE,"ODBC;",FALSE);
      CRecordset recSet(&dbase);
      bRet = recSet.Open(CRecordset::forwardOnly,
                         "{call sp_myProc}",CRecordset::readOnly);

      //Check the number of columns in the first resultset.
      short nCols = recSet.GetODBCFieldCount();

      recSet.GetFieldValue((short)0,strTemp);
      AfxMessageBox(strTemp);

      recSet.FlushResultSet();

      //Rebuild the cache info to retrieve new column info.
      AllocAndCacheFieldInfo(recSet);

      recSet.MoveNext();

      //Check the number of columns in the second resultset.
      nCols = recSet.GetODBCFieldCount();

      recSet.GetFieldValue((short)0,strTemp);
      AfxMessageBox(strTemp);

      recSet.MoveNext();
      recSet.GetFieldValue(1,strTemp);
      AfxMessageBox(strTemp);
   }


   void CCurLib2View::AllocAndCacheFieldInfo(CRecordset &pSet)
   {
      pSet.m_nResultCols = -1;
      ASSERT(pSet.GetODBCFieldCount() < 0);

      if(pSet.m_rgODBCFieldInfos != NULL)
         delete [] pSet.m_rgODBCFieldInfos;


      RETCODE nRetCode;
      SWORD nActualLen;

      // Cache the number of result columns.
      AFX_ODBC_CALL(::SQLNumResultCols(pSet.m_hstmt, &pSet.m_nResultCols));
      if (!pSet.Check(nRetCode))
      {
         TRACE0("Error: Can't get field info.\n");
         AfxThrowDBException(nRetCode, pSet.m_pDatabase,pSet.m_hstmt);
      }

      // If there are no fields, quit now.
      if (pSet.m_nResultCols == 0)
         return;

      // Allocate buffer and get the ODBC meta data.
      pSet.m_rgODBCFieldInfos = new CODBCFieldInfo[pSet.m_nResultCols];
      LPSTR lpszFieldName;

   #ifdef _UNICODE
      // Need proxy to temporarily store non-UNICODE name.
      lpszFieldName = new char[MAX_FNAME_LEN + 1];
   #endif

      // Get the field info for each field.
      for (WORD n = 1; n <= pSet.GetODBCFieldCount(); n++)
      {
   #ifndef _UNICODE
     // Reset the buffer to point to next element.
     lpszFieldName =
     pSet.m_rgODBCFieldInfos[n - 1].m_strName.GetBuffer(MAX_FNAME_LEN + 1);
   #endif

         AFX_ODBC_CALL(::SQLDescribeCol(pSet.m_hstmt, n,
            (UCHAR*)lpszFieldName, MAX_FNAME_LEN, &nActualLen,
            &pSet.m_rgODBCFieldInfos[n - 1].m_nSQLType,
            &pSet.m_rgODBCFieldInfos[n - 1].m_nPrecision,
            &pSet.m_rgODBCFieldInfos[n - 1].m_nScale,
            &pSet.m_rgODBCFieldInfos[n - 1].m_nNullability));

   #ifndef _UNICODE
        pSet.m_rgODBCFieldInfos[n - 1].m_strName.ReleaseBuffer(nActualLen);
   #else
         // Copy the proxy data to correct element.
         pSet.m_rgODBCFieldInfos[n - 1].m_strName = lpszFieldName;
   #endif

      }

   #ifdef _UNICODE
      delete[] lpszFieldName;
   #endif
   }