GetRows and GetRowsEx Methods

The dbDAO classes support the GetRows method for fetching multiple rows of data in one call. Like the Microsoft Visual Basic version, GetRows returns the data as variants.

The GetRowsEx method is a performance improvement by dbDAO and Microsoft Foundation Classes (MFC) DAO to avoid the necessity to retrieve all data as variants and then convert to native types. GetRowsEx does not exist in Visual Basic DAO.

Important The underlying ICDAORecordset::GetRows method might not be supported in future versions of DAO; however, the MFC and dbDAO classes that use this method will continue to be supported.

Using the dbDAO GetRows Method

The GetRows method takes one parameter, the number of rows to fetch, and returns the row data as variants. Unlike in Visual Basic, you cannot simply use subscripts to indicate the array field in a variant. Rather, the data is returned as an OLE SafeArray. The parray member of the variant points to an array desciptor. Individual fields are accessed by calling SafeArrayGetElement. For more information on using the SafeArray datatype, see the OLE documentation.

The following code from the GetRows sample shows how to use GetRows. Note that the table schema is known a priori, allowing the programmer to hard-code the display functions.

void CGetRowsDlg::DoGetRows() 
{
   COleVariant      cRows;
   COleVariant      varField;
   CString         strLBRow;
   TCHAR            szId[16];
   LONG            lNumRecords;
   HRESULT         hResult;
   LONG            lIndex[2];
   CListBox         *pListBox = (CListBox *)GetDlgItem(IDD_GETROWSLIST);

   // Perform GetRows on Employee table. Retrieve MAX_EMP_REC rows and
   // store results as a variant in cRows.
   cRows = m_cEmpRecordSet.GetRows(MAX_EMP_REC);

   // Find out how many records were actually retrieved.
   // Note that lNumRecords is 1 based
   SafeArrayGetUBound(cRows.parray, 2, &lNumRecords);

   // Clear the listbox for displaying the row values.
   pListBox->ResetContent();
   
   //Step through the returned rows.
   for (lIndex[1] = 0; lIndex[1] <= lNumRecords; lIndex[1]++)
      {
      // Clear the string containing the row values.
      strLBRow.Empty();

      // Set array index to EmpID field.
      lIndex[0] = EMP_EMPLOYEE_ID;
         
      // Use OLE safe array function to access fields.
      SafeArrayGetElement(cRows.parray, &lIndex[0], &varField);
      
      // EmpID is known to be a long integer value. Store the value in
      // a string. The type check is used because the array may
      // contain empty rows.
      if(varField.vt == VT_I4)
         {
         wsprintf(szId, "%d,  ", varField.iVal);
         }
      else      // empty row
         {
         strcpy(szId, _T("Unexpected data type"));
         }

      strLBRow += (LPCTSTR)szId;

      // Set array index to LastName field.
      lIndex[0] = EMP_LAST_NAME;
         
      // Use OLE safe array function to access fields.
      SafeArrayGetElement(cRows.parray, &lIndex[0], &varField);

      // Add LastName to the row string.
      strLBRow += (LPCTSTR)varField.bstrVal;

      // Display string.
      pListBox->AddString(strLBRow);
      }
}

Using the dbDAO GetRowsEx Method

The GetRowsEx method retrieves multiple rows and allows you to specify the data types of the fields stored in your application variables. It has the following parameters:

Parameter Data Type Description
pvBuffer LPVOID Pointer to buffer in which to store returned rows
cbRow LONG Length of row in bytes
prb LPDAORSETBINDING Pointer to binding structure (see below)
cBinding LONG Number of bindings
pvVarBuffer LPVOID Pointer to buffer in which to store variable-length data
cbVarBuffer LONG Length in bytes of pvVarBuffer
lRows LONG Number of rows requested

The DAORSETBINDING structure specifies how data is to be copied from the rows to the memory buffer. A separate binding structure must be filled in for each field retrieved. DAORSETBINDING is defined as follows:

typedef struct
   {
   DWORD dwBindIndexType;
   union
      {
      LONG  i;
      LPCTSTR pstr;
      };
   DWORD dwType;
   DWORD dwOffset;
   DWORD cb;
   } DAORSETBINDING, *LPDAORSETBINDING;
Member Description
dwBindIndexType Specifies whether field is indicated by an index number or by name (use one of the values specified in Settings)
dwType Specifies the data type (use one of the values specified in Settings)
dwOffset Offset in bytes in the row buffer where data is copied
cb Field length in bytes

Settings

You can use one of the following values for BindIndexType.

Constant Description
dbBindIndexINT index
dbBindIndexSTR name

You can use one of the following values for Type.

Constant Datatype
dbBindI2 short
dbBindI4 long
dbBindR4 float
dbBindR8 double
dbBindCY currency
dbBindDATE DATE
dbBindBOOL VARIANT_BOOL
dbBindUI1 unsigned char
dbBindVARIANT VARIANT
dbBindWCHAR wchar_t[]
dbBindSTRING (same as dbBindWCHAR for Unicode, dbBindUI1for ANSI)
dbBindLPSTR char_pointer
dbBindLPTSTR wchar_pointer
dbBindLPSTRING (dbBindLPTSTR for Unicode, dbBindLPSTR for ANSI)
dbBindBookmark CdbBookmark
dbBindBlob unsigned char pointer

The following code from the GetRows sample shows how to use GetRowsEx. Note that the table schema is known a priori, allowing you to predefine a structure for the data.

Two different binding schemes for retrieving string data are shown. If the size of the strings is fixed, then you can retrieve them directly into the structure. If you are using variable length strings, they will be placed sequentially in a single large buffer and a pointer into that buffer will be stored in the struct.

// User-defined structure for storing data returned by GetRowsEx.
typedef struct
// Structure for GetRowsEx
typedef struct
   {         // employee id as a long
   LONG   lEmpId;            
            // pointer into pVarBuffer to LastName
   TCHAR   *strLastName;
            // the first name (\0 terminated)
   TCHAR   strFirstName[20];   
   } EMP, *LPEMP ;

/* Employee table binding. This structure can be kept    in a header file and modified as needed (for    example, when you update the table definition).    Filling in the structure statically allows you to    define bindings once, rather than each time you    read data.
*/
DAORSETBINDING   Bindings[] = 
{
//Index Type      Column      Type         Offset            Size
{dbBindIndexINT,   EMP_ID,      dbBindI4,      offsetof(EMP,lEmpId),      sizeof(LONG)},
{dbBindIndexINT,   EMP_LNAME,   dbBindLPSTRING,   offsetof(EMP,strLastName),   sizeof(TCHAR *)},
{dbBindIndexINT,   EMP_FNAME,   dbBindSTRING,   offsetof(EMP,strFirstName),   sizeof(TCHAR) * 20}
};

// Perform dbDAO GetRowsEx against the Employee table
void CGetRowsDlg::DoGetRowsEx()
{
   LPEMP         pEmpRows = new EMP[MAX_EMP_REC];
   CListBox      *pListBox = (CListBox *)GetDlgItem(IDD_GETROWSLISTEX);
   CString         strLBRow;
   TCHAR         szId[16];
   LONG         lNumRecords;
   LONG         lCount;
   TCHAR         pBuf[MAX_EMP_REC * 15];   // allow average of 15 chars/name

   // Call GetRowsEx to fetch rows.
   lNumRecords = m_cEmpRecordSet.GetRowsEx(
         pEmpRows,         // Pointer to data buffer.
         sizeof(EMPS),      // Length of row in bytes.
         &Bindings[0],      // Pointer to binding structures.
         sizeof(Bindings) / sizeof(DAORSETBINDING),   // Number of bindings.
          pBuf,            // Storage for variable-length data (lastname).
          sizeof(pBuf),   // Bytes available for variable-length data.
         MAX_EMP_REC);      // Get MAX_EMP_REC rows.
   
   //Step through the returned rows and display results.
   for (lCount = 0; lCount < lNumRecords; lCount++)
      {
      strLBRow.Empty();      // Clear the string containing the row values.
      wsprintf(szId, _T("%d,  "), pEmpRows[lCount].lEmpId);
      strLBRow += szId;
      strLBRow += pEmpRows[lCount].strLastName;
      strLBRow += _T(", ");
      strLBRow += (LPCTSTR) pEmpRows[lCount].strFirstName;
      pListBox->AddString(strLBRow);
      }

   delete [] pEmpRows;         // Free memory.
}