RFX_Date using COleDateTime

Posted by Dan Querciagrossa on August 6th, 1998
The following code is a modified version of the MFC source for RFX_Date, instead of the crippled CTime, I use COleDateTime. This avoids the annoying 1970 - 2038 year limits (COleDateTime can handle years 100-9999) of the CTime object and a COleDateTime is a lot nicer to deal with than a TIMESTAMP_STRUCT. All I do is use ClassLizard to create a recordset, then replace the CTime variables with COleDateTime and the RFX_Date calls with RFX_OleDateTime. It's a good idea to move those changes outside the class wizard blocks so they don't cause problems. 
void AFXAPI RFX_OleDateTime(CFieldExchange* pFX, LPCTSTR szName,
                            COleDateTime& value)
{
 ASSERT(AfxIsValidAddress(pFX, sizeof(CFieldExchange)));
 ASSERT(AfxIsValidString(szName));

 RETCODE nRetCode;
 UINT nField;
 if (!pFX->IsFieldType(&nField))
  return;

 LONG* plLength = pFX->m_prs->GetFieldLengthBuffer(nField - 1, pFX->m_nFieldType);
 switch (pFX->m_nOperation)
 {
 default:
LDefault:
  pFX->Default(szName, &value, plLength, SQL_C_TIMESTAMP,
   sizeof(value), TIMESTAMP_PRECISION);
  return;

 case CFieldExchange::BindParam:
  {
   TIMESTAMP_STRUCT* pts;
   pFX->m_prs->m_bRebindParams = TRUE;

   if (pFX->m_prs->IsParamStatusNull(nField - 1))
   {
    pts = NULL;
    *plLength = SQL_NULL_DATA;
   }
   else
   {
    // Allocate proxy array if necessary
    if (pFX->m_prs->m_pvParamProxy == NULL)
    {
     pFX->m_prs->m_pvParamProxy = new void*[pFX->m_prs->m_nParams];
     memset(pFX->m_prs->m_pvParamProxy, 0, pFX->m_prs->m_nParams*sizeof(void*));
     pFX->m_prs->m_nProxyParams = pFX->m_prs->m_nParams;
    }

    // Allocate TIMESTAMP_STRUCT if necessary for SQLBindParameter
    if (pFX->m_prs->m_pvParamProxy[nField-1] == NULL)
    {
     pts = new TIMESTAMP_STRUCT;
     pFX->m_prs->m_pvParamProxy[nField-1] = pts;
    }
    else
     pts = (TIMESTAMP_STRUCT *)pFX->m_prs->m_pvParamProxy[nField-1];

    pts->year = (SWORD)value.GetYear();
    pts->month = (UWORD)value.GetMonth();
    pts->day = (UWORD)value.GetDay();
    pts->hour = (UWORD)value.GetHour();
    pts->minute = (UWORD)value.GetMinute();
    pts->second = (UWORD)value.GetSecond();
    pts->fraction = 0;
    *plLength = sizeof(TIMESTAMP_STRUCT);
   }

   AFX_SQL_SYNC(::SQLBindParameter(pFX->m_hstmt, (UWORD)nField,
    (SWORD)pFX->m_nFieldType, SQL_C_TIMESTAMP, SQL_C_TIMESTAMP,
    TIMESTAMP_PRECISION, 0, pts, 0, plLength));
   if (nRetCode != SQL_SUCCESS)
    pFX->m_prs->ThrowDBException(nRetCode, pFX->m_hstmt);

   // Add the member address to the param map
   pFX->m_prs->m_mapParamIndex.SetAt(&value, (void*)nField);
  }
  return;

 case CFieldExchange::RebindParam:
  {
   *plLength = pFX->m_prs->IsParamStatusNull(nField - 1) ?
    SQL_NULL_DATA : sizeof(TIMESTAMP_STRUCT);
   if (pFX->m_prs->m_nProxyParams != 0)
   {
    // Fill buffer (expected by SQLBindParameter) with new param data
    TIMESTAMP_STRUCT* pts;
    pts = (TIMESTAMP_STRUCT *)pFX->m_prs->m_pvParamProxy[nField-1];
    pts->year = (SWORD)value.GetYear();
    pts->month = (UWORD)value.GetMonth();
    pts->day = (UWORD)value.GetDay();
    pts->hour = (UWORD)value.GetHour();
    pts->minute = (UWORD)value.GetMinute();
    pts->second = (UWORD)value.GetSecond();
    pts->fraction = 0;
   }
  }
  return;

 case CFieldExchange::BindFieldToColumn:
  {
#ifdef _DEBUG
   // Assumes all bound fields BEFORE unbound fields
   CODBCFieldInfo* pODBCInfo =
    &pFX->m_prs->m_rgODBCFieldInfos[nField - 1];

   if (pODBCInfo->m_nSQLType != SQL_DATE &&
    pODBCInfo->m_nSQLType != SQL_TIME &&
    pODBCInfo->m_nSQLType != SQL_TIMESTAMP)
   {
    // Warn of possible field schema mismatch
    if (afxTraceFlags & traceDatabase)
     TRACE1("Warning: CTime converted from SQL type %ld.\n",
      pODBCInfo->m_nSQLType);
   }
#endif // _DEBUG

   // Allocate proxy array if necessary
   if (pFX->m_prs->m_pvFieldProxy == NULL)
   {
    pFX->m_prs->m_pvFieldProxy = new void*[pFX->m_prs->m_nFields];
    memset(pFX->m_prs->m_pvFieldProxy, 0,
     pFX->m_prs->m_nFields*sizeof(void*));
    pFX->m_prs->m_nProxyFields = pFX->m_prs->m_nFields;
   }

   // Allocate TIMESTAMP_STRUCT for SQLBindCol (not necessary on Requery)
   if (pFX->m_prs->m_pvFieldProxy[nField-1] == NULL)
    pFX->m_prs->m_pvFieldProxy[nField-1] = new TIMESTAMP_STRUCT;

   AFX_SQL_SYNC(::SQLBindCol(pFX->m_prs->m_hstmt, (UWORD)nField,
    SQL_C_TIMESTAMP, pFX->m_prs->m_pvFieldProxy[nField-1],
    sizeof(TIMESTAMP_STRUCT), plLength));
   if (!pFX->m_prs->Check(nRetCode))
    pFX->m_prs->ThrowDBException(nRetCode);

   // Add the member address to the field map
   pFX->m_prs->m_mapFieldIndex.SetAt(&value, (void*)nField);
  }
  return;

 case CFieldExchange::BindFieldForUpdate:
  if (pFX->m_prs->m_nProxyFields != 0)
  {
   // Fill buffer (expected by SQLSetPos) with new field data
   TIMESTAMP_STRUCT* pts;
   pts = (TIMESTAMP_STRUCT *)pFX->m_prs->m_pvFieldProxy[nField-1];
   pts->year = (SWORD)value.GetYear();
   pts->month = (UWORD)value.GetMonth();
   pts->day = (UWORD)value.GetDay();
   pts->hour = (UWORD)value.GetHour();
   pts->minute = (UWORD)value.GetMinute();
   pts->second = (UWORD)value.GetSecond();
   pts->fraction = 0;

   pFX->Default(szName, (void *)pts, plLength, SQL_C_TIMESTAMP,
    sizeof(TIMESTAMP_STRUCT), TIMESTAMP_PRECISION);
  }
  return;

 case CFieldExchange::Fixup:
  if (*plLength == SQL_NULL_DATA)
  {
   pFX->m_prs->SetNullFieldStatus(nField - 1);
   value.SetStatus(COleDateTime::null);//    = AFX_RFX_DATE_PSEUDO_NULL;
  }
  else
  {
   TIMESTAMP_STRUCT* pts =
    (TIMESTAMP_STRUCT*)pFX->m_prs->m_pvFieldProxy[nField-1];
   if (pts->year < 100 || pts->year > 9999)
   {
    // Time value out of range, return NULL
#ifdef _DEBUG
    if (afxTraceFlags & traceDatabase)
     TRACE0("Warning: date value out of range, returning NULL value.\n");
#endif
    pFX->m_prs->SetNullFieldStatus(nField - 1);
    value.SetStatus(COleDateTime::null);//value = AFX_RFX_DATE_PSEUDO_NULL;
   }
   else
   {
#ifdef _DEBUG
    if ((afxTraceFlags & traceDatabase) && pts->fraction != 0)
     TRACE0("Warning: ignoring milliseconds.\n");
#endif
    value = COleDateTime(pts->year, pts->month, pts->day,
     pts->hour, pts->minute, pts->second);
   }
  }
  return;

 case CFieldExchange::NameValue:
  if (pFX->m_prs->IsFieldStatusDirty(nField - 1))
  {
   *pFX->m_pstr += szName;
   *pFX->m_pstr += '=';
  }
  // Fall through

 case CFieldExchange::Value:
  if (pFX->m_prs->IsFieldStatusDirty(nField - 1))
  {
   TIMESTAMP_STRUCT* pts =
    (TIMESTAMP_STRUCT*)pFX->m_prs->m_pvFieldProxy[nField-1];
   if (pFX->m_prs->IsFieldStatusNull(nField - 1))
   {
    *plLength = SQL_NULL_DATA;
   }
   else
   {
    pts->year = (SWORD)value.GetYear();
    pts->month = (UWORD)value.GetMonth();
    pts->day = (UWORD)value.GetDay();
    pts->hour = (UWORD)value.GetHour();
    pts->minute = (UWORD)value.GetMinute();
    pts->second = (UWORD)value.GetSecond();
    pts->fraction = 0;
    *plLength = sizeof(TIMESTAMP_STRUCT);
   }

   // If optimizing for bulk add, only need lengths & proxy set correctly
   if(!(pFX->m_prs->m_dwOptions & CRecordset::optimizeBulkAdd))
   {
    *pFX->m_pstr += '?';
    *pFX->m_pstr += pFX->m_lpszSeparator;
    pFX->m_nParamFields++;

    // Assumes all bound fields BEFORE unbound fields
    CODBCFieldInfo* pODBCInfo =
     &pFX->m_prs->m_rgODBCFieldInfos[nField - 1];

    AFX_SQL_SYNC(::SQLBindParameter(pFX->m_hstmt,
     (UWORD)pFX->m_nParamFields, SQL_PARAM_INPUT,
     SQL_C_TIMESTAMP, pODBCInfo->m_nSQLType,
     TIMESTAMP_PRECISION, 0, pts, 0, plLength));
   }
  }
  return;

 case CFieldExchange::SetFieldNull:
  if ((pFX->m_pvField == NULL &&
   pFX->m_nFieldType == CFieldExchange::outputColumn) ||
   pFX->m_pvField == &value)
  {
   if (pFX->m_bField)
   {
    // Mark fields null
    pFX->m_prs->SetNullFieldStatus(nField - 1);
    value.SetStatus(COleDateTime::null);//value = AFX_RFX_DATE_PSEUDO_NULL;
    *plLength = SQL_NULL_DATA;
   }
   else
   {
    pFX->m_prs->ClearNullFieldStatus(nField - 1);
    *plLength = sizeof(TIMESTAMP_STRUCT);
   }
#ifdef _DEBUG
   pFX->m_nFieldFound = nField;
#endif
  }
  return;

 case CFieldExchange::MarkForAddNew:
  {
   // can force writing of psuedo-null value (as a non-null) by setting
field dirty
   COleDateTime timeNull;// = AFX_RFX_DATE_PSEUDO_NULL;
   if (value != timeNull)
   {
    pFX->m_prs->SetDirtyFieldStatus(nField - 1);
    pFX->m_prs->ClearNullFieldStatus(nField - 1);
   }
  }
  return;

 case CFieldExchange::MarkForUpdate:
  {
   COleDateTime timeNull;// = AFX_RFX_DATE_PSEUDO_NULL;
   if (value != timeNull)
    pFX->m_prs->ClearNullFieldStatus(nField - 1);
  }
  goto LDefault;

 case CFieldExchange::LoadField:
  {
   // Get the field data
   CFieldInfo* pInfo = &pFX->m_prs->m_rgFieldInfos[nField - 1];

   // Restore the status
   pFX->m_prs->SetFieldStatus(nField - 1, pInfo->m_bStatus);

   // If not NULL, restore the value, length and proxy
   if (!pFX->m_prs->IsFieldStatusNull(nField - 1))
   {
    AfxCopyValueByRef(pInfo->m_pvDataCache, &value,
     plLength, pInfo->m_nDataType);

    // Restore proxy for correct WHERE CURRENT OF operations
    TIMESTAMP_STRUCT* pts =
     (TIMESTAMP_STRUCT*)pFX->m_prs->m_pvFieldProxy[nField-1];

    pts->year = (SWORD)value.GetYear();
    pts->month = (UWORD)value.GetMonth();
    pts->day = (UWORD)value.GetDay();
    pts->hour = (UWORD)value.GetHour();
    pts->minute = (UWORD)value.GetMinute();
    pts->second = (UWORD)value.GetSecond();
    pts->fraction = 0;
   }
   else
    *plLength = SQL_NULL_DATA;

#ifdef _DEBUG
   // Buffer address must not change - ODBC's SQLBindCol depends upon this
   if (pInfo->m_pvBindAddress != pFX->m_prs->m_pvFieldProxy[nField-1])
   {
    TRACE1("Error: CString buffer (column %u) address has changed!\n",
     nField);
    ASSERT(FALSE);
   }
#endif // _DEBUG
  }
  return;

 case CFieldExchange::AllocCache:
  {
   CFieldInfo* pInfo = &pFX->m_prs->m_rgFieldInfos[nField - 1];
   pInfo->m_pvDataCache = new COleDateTime;
   pInfo->m_nDataType = AFX_RFX_DATE;
  }
  return;

#ifdef _DEBUG
 case CFieldExchange::DumpField:
  *pFX->m_pdcDump << "\n" << szName << " = " << value;
		return;
#endif // _DEBUG

	}
}

Last updated: 16 April 1998

IT Offers

Comments

  • how define time field in DAO and how write date of system on that field

    Posted by Legacy on 03/02/2003 12:00am

    Originally posted by: ASHKAN

    Dear sir,
    I create a *.mdb file with DAO but I can not write date of system in one field of database record. In other word I dont know how define time field in DAO and how write date of system on that field.Please help me with a source file
    thanks in advance,
    sincerely yours,

    Reply

  • VSsp5 Breaks CDateTimeCtrl/COleDateTime

    Posted by Legacy on 08/02/2001 12:00am

    Originally posted by: Mubeen M. Deen

    Hi everyone,

    I noticed that the Visual Studio Service Pack 5 for VS6 has actually broken my Visual Studio! Specifically, the CDateTimeCtrl (MFC's wrapper for the DateTimePicker control) and the COleDateTime.

    Prior to the service pack 5, I had written an app that uses a MySQL ODBC Datasource, and the DateTimePicker (it looks like a combobox, but when you click the arrow down button, you see a calender with todays date circled in red), and a few property pages. On one of the property pages was a medical record that used a DateTimePicker control, everything worked fine before and after I added the service pack.

    HERE COMES THE CATCH:

    The app still worked fine prior to service pack 5. Afterwards, I added another page to the program, that also had a DateTimePicker, and I compiled this with the patched VS C++6 sp5. Guess what? the DateTimePicker didn't even establish a connection with the DB via RFX macro. The code compiled fine, but the control just seemed to sit there. It could not establish any communication with the data source. I spent one whole day looking at the code, and scratching my head. I even compared both the medical page, and the new page. I had no idea; the code was correct. The old worked, and new one didn't, go figure! I did so many things; I even tried to see if MySQL was messed up, by dumping the raw data from an ODBC operation to see if the DBServer was transmitting. But it was fine. So on whim, I asked myself "hmmm, let's see what would happen if we re-compiled the old medical page; would it suddenly stop working and show the same symptoms that the new page is showing?" So I did this (compiled the medical page), and guess what, it broke! The control on the medical page that once worked, now seized to function correctly; and was exactly dysfunctional in the same way as the new page.

    This morning, I completely uninstalled VS and MSDN. And reinstalled both fresh from the CDs. I did a recompile of all code, and now everything works perfectly!

    BOTTOM LINE:

    Do NOT install service packs unless you know for sure that it will solve a specific problem that you are experiencing.

    Thanks,
    Mubeen

    -PS: I tried all of the above on two totally different systems. Both with VS6 + SP5 connection to a remote DBMS:

    System 1:
    128 MB - 200 MHz Pentium /w. MMX
    Innoculate Antivirus
    Windows NT Workstation 4 with Service Pack 6

    System 2:
    128 MB - 750 MHz Pentium III
    Innoculate Antivirus
    Windows 2000 Workstation with Service Pack 2

    -PSS: I don't think Innoculate has anything to do with this; maybe someone might want to check it out.

    Reply

  • Confirmed Visual C++ 6 macro does work...

    Posted by Legacy on 06/11/2001 12:00am

    Originally posted by: Mubeen M. Deen

    Confirmed Visual C++ 6 macro does work as stated by Dan Querciagrossa. (This macro is located in DoFieldExchange method of your CRecordset derived class)

    I had discovered that limitation problem using CTime(1970 to 2034 limitation) that many people have reported. Since I'm writing a program that deals with medical records; dropping CTime because of its inadequacies is definately a no brainer (there are people who were born before 1970!). I don't know what MS was thinking when they made this class.

    Any how, COleDateTime can effective replace CTime. All you need to do is just do a EDIT | REPLACE; effectively removing all typograpical entries of "CTime" with "COleDateTime". That's it! You don't need to fiddle with the macro that is found in DoFieldExchange, because it will automatically recognize the difference.

    eg. RFX_Date(pFX, _T("[RXDATE]"), m_RXDATE);
    where:
    pFX is arg for DoFieldExchange,
    _T("[RXDATE]") is a database field
    m_RXDATE, is a COleDateTime object (use to be CTime object, changed after doing a EDIT | REPLACE)

    It does work for:
    1) CRecordset objects
    2) AddNew
    3) Update

    Good luck!

    Mubeen M. Deen

    Reply

  • Update Recordset

    Posted by Legacy on 08/31/1999 12:00am

    Originally posted by: peter

    In these cases you have to change your source RFX_OleDateTime, then it works correct when updating database.
    If COleDateTime has not status valid, the field in the table changes to NULL.
    I have tested this with Sybase SqlAnywhere via ODBC-Driver.

    case CFieldExchange::MarkForAddNew:

    if (value.GetStatus() == COleDateTime::valid)
    {
    pFX->m_prs->SetDirtyFieldStatus(nField - 1);
    pFX->m_prs->ClearNullFieldStatus(nField - 1);
    }

    return;

    case CFieldExchange::MarkForUpdate:

    if (value.GetStatus() == COleDateTime::valid)
    {
    pFX->m_prs->SetDirtyFieldStatus(nField - 1);
    pFX->m_prs->ClearNullFieldStatus(nField - 1);
    }
    else
    {
    pFX->m_prs->SetDirtyFieldStatus(nField - 1);
    pFX->m_prs->SetNullFieldStatus(nField - 1);
    *plLength = SQL_NULL_DATA;//!
    }
    }
    goto LDefault; //orginal Microsoft!

    case CFieldExchange::BindFieldForUpdate:

    if (pFX->m_prs->m_nProxyFields != 0)
    {

    if (value.GetStatus() == COleDateTime::valid) //new!
    {
    // Fill buffer (expected by SQLSetPos) with new field data
    TIMESTAMP_STRUCT* pts;
    pts = (TIMESTAMP_STRUCT *)pFX->m_prs->m_pvFieldProxy[nField-1];
    pts->year = (SWORD)value.GetYear();
    pts->month = (UWORD)value.GetMonth();
    pts->day = (UWORD)value.GetDay();
    pts->hour = (UWORD)value.GetHour();
    pts->minute = (UWORD)value.GetMinute();
    pts->second = (UWORD)value.GetSecond();
    pts->fraction = 0;
    pFX->Default(szName, (void *)pts, plLength, SQL_C_TIMESTAMP, sizeof(TIMESTAMP_STRUCT), TIMESTAMP_PRECISION);
    }
    }
    return;

    Reply

  • doesn't update if date is already set ?

    Posted by Legacy on 05/17/1999 12:00am

    Originally posted by: Marco

    Using this method on a recordset i found out that it sets the data if no data is set before but it doesn't change the date if it's already stored in the database (working with snapshot on a SQL Server)

    Workaround:
    you have to use "CRecordset::SetFieldDirty( void * )" by hand ...

    But the method is'nt capable of resetting the DateTime Field to an empty value so far ...

    has someone a newer version of this code ? I'm not willing to pay 1500,- DM (about 700$) for an update to VC++6, 'cause this is the only new feature I really need so far ...


    Reply

  • COleDateTime as an outputParam in stored procedure

    Posted by Legacy on 02/03/1999 12:00am

    Originally posted by: Sam Chipman

    Can a COleDateTime be passed from stored procedure as an output param using RFX_Date in VC++ 6.0?

    I am receiving other values from my sp but not the COleDateTime?

    Reply

  • Two small changes

    Posted by Legacy on 10/27/1998 12:00am

    Originally posted by: Andrew Lazarus 

    I looked over this code (and copied it), but there are two places I am a little dubious.


    1. In the two places where value is compared to a local timeNull, I

    used calls to 


    if (value.GetStatus()==value.valid) // etc


    2. The call to AfxCopyByRef looks very dangerous because the first two arguments are treated as
TIMESTAMP_STRUCT* not COleDateTime*.

    I think it doesn't bomb only because the implicit assignment is usually a no-op.


    I replaced this with an assignment


    //	AfxCopyValueByRef(pInfo->m_pvDataCache, &value,

     //    plLength, pInfo->m_nDataType);

    	   *pInfo->m_pvDataCache= value;

    	   *plLength= sizeof(TIMESTAMP_STRUCT);

    Reply
Leave a Comment
  • Your email address will not be published. All fields are required.

Whitepapers and More

Most Popular Programming Stories

  • There have been no articles posted today.

More for Developers

Latest Developer Headlines

RSS Feeds