FIX: Requery Not Affected by Changing a CTime Parameter

The information in this article applies to:

• The Microsoft Foundation Classes (MFC), included with:

      - Microsoft Visual C++ for Windows, version 1.5
  

SYMPTOMS

Requery() fails to present the correct results if a CTime object is used in a parameterized filter for a CRecordset.

Consider the following scenario: An application uses a parameterized filter where a CTime object is one of the parameters. The CTime object is initialized to some value before the CRecordset is opened; the records that are returned when the recordset is opened are based on this value. Later, the CTime object's value may be changed with the intent of requerying for a different result set. However, after the value of the CTime parameter is modified and the Requery() function is called, the result set does not reflect the newly changed CTime parameter. In fact, if the CTime object was the only changed parameter, the result set does not change at all from the initial query performed during the opening of the CRecordset.

CAUSE

The Microsoft Foundation Classes code fails to store the value of the CTime object into the proxy buffer used to exchange data between the application and the ODBC driver. The CRecordset code allocates a proxy, which is a storage location for holding the parameter data to be used for the query. A proxy is needed because ODBC drivers know nothing about converting DATE/TIME/TIMESTAMP data to CTime objects. Instead, the ODBC drivers convert the data to a TIMESTAMP_STRUCT and store this in the parameter buffer (the proxy).

The RFX_Date() function typically handles the fixup for converting TIMESTAMP_STRUCTs (the data in the proxy) to their corresponding CTime variables. However, the RFX_Date() routine does not provide an RFX operation for storing the CTime data into the proxy before doing a Requery(). Therefore, the initial CTime variable is continually reused for each Requery() call.

STATUS

Microsoft has confirmed this to be a bug in the Microsoft products listed at the beginning of this article. This bug was corrected in Visual C++ for Windows, version 1.51.

RESOLUTION

Below are two methods to work around this problem:

Method #1

The CRecordset class contains a function called BindParams() that binds memory where the parameter data is stored and retrieved by the ODBC driver. It creates the proxy area and stores the value of data variables (in our case, the CTime object) into the proxy area. It is called in CRecordset::Open(). This is why the first query is successful. You can override Requery() and call BindParams() before calling CRecordset::Requery(). For example:

BOOL CYourRecordset::Requery() {

    // code for cleaning up old proxies for parameters - otherwise
    // memory leak occurs
    if (m_pvParamProxy != NULL)
    {
        for (UINT nParam = 0; nParam != m_nParams; nParam++)
            delete m_pvParamProxy[nParam];
    }

    // Rebind parameters and store values in from variables to Proxies.
    BindParams(m_hstmt);
    return CRecordset::Requery();

NOTE: Code was added to correct a memory leak that would have occurred by calling BindParams(). The variable m_pvParamProxy is a member variable of the CRecordset class, which stores a pointer to all the proxies for the parameters used by the CRecordset.

Method #2

Method #1 is inefficient because it recalls an ODBC function to rebind parameters. Also, the old proxies have to be deleted. This second method is more efficient.

Create a new function in your class derived from CRecordset that stores the value of the CTime object into the TIMESTAMP_STRUCT proxy. It should look like the following:

    void CYourRecordset::SetCTimeParam(CTime * pv, int nParam)
    {
        ASSERT (nParam>0 && nParam<=m_nParams);

        TIMESTAMP_STRUCT * pts=
                     (TIMESTAMP_STRUCT *)m_pvParamProxy[nParam-1];

        pts->year = (short int)pv->GetYear();
        pts->month = (unsigned short int)pv->GetMonth();
        pts->day = (unsigned short int)pv->GetDay();
        pts->hour = (unsigned short int)pv->GetHour();
        pts->minute = (unsigned short int)pv->GetMinute();
        pts->second = (unsigned short int)pv->GetSecond();
        pts->fraction = 0;

    }

Call this function before calling Requery() or override Requery() in your CRecordset-derived class and then call the function within the override. For example:

    BOOL CYourRecordset::Requery()
    {
        SetCTimeParam(&m_QueryDate,1);
        return CRecordset::Requery();
    }

    where m_QueryDate is a CTime variable and is the first parameter.