UPDATE query not happy with date fields

[Riley DeWiley]

I have an UPDATE query that is always setting 0 records. When I cut and
paste the SQL into Access and use it, it fails in the same way unless I
coerce the date fields to be '=now()', in which case it works. I never get
errors, and if I don't try to update the date fields, I always update the
expected number of rows (1). When I do date conversion using the same
conversion function, then call CRowset::Add() to add a record to the same
table, it works as expected.

I am using OLEDB/C++ against Jet 4.0.

Here are some code fragments for reading enjoyment.

Any insights appreciated.

RDeW

---------------------------------
//accessor
class CFileUpdateByNa meAccessor

{

public:

void ClearRecord()

{

memset(&this->m_DriveName, 0, offsetof(CFileU pdateByNameAcce ssor,
m_TextType) + sizeof(m_TextTy pe));

}

wchar_t m_DriveName[DRIVEBUFSIZE];

wchar_t m_DirName[DIRECTORYBUFSIZ E];

wchar_t m_FileName[FILEBUFSIZE];

LONG m_Checksum;

LONG m_SizeLo32;

DATE m_CreationDateT ime;

DATE m_LastAccessDat eTime;

DATE m_ModificationD ateTime;

DATE m_SSLastUpdate;

LONG m_TextType;

BEGIN_PARAM_MAP (CFileUpdateByN ameAccessor)

COLUMN_ENTRY_TY PE(1, DBTYPE_DATE, m_CreationDateT ime)

COLUMN_ENTRY_TY PE(2, DBTYPE_DATE, m_ModificationD ateTime)

COLUMN_ENTRY_TY PE(3, DBTYPE_DATE, m_LastAccessDat eTime)

COLUMN_ENTRY_TY PE(4, DBTYPE_DATE, m_SSLastUpdate)

COLUMN_ENTRY(5, m_Checksum)

COLUMN_ENTRY(6, m_SizeLo32)

COLUMN_ENTRY(7, m_TextType)

COLUMN_ENTRY(8, m_DriveName)

COLUMN_ENTRY(9, m_DirName)

COLUMN_ENTRY(10 , m_FileName)
END_PARAM_MAP()

DEFINE_COMMAND_ EX(CFileUpdateB yNameAccessor, L"\

UPDATE Files \

SET \

CreationDateTim e = (?), \

ModificationDat eTime = (?), \

LastAccessDateT ime = (?), \

SSLastUpdate = (?), \

Checksum = (?), \

SizeLo32 = (?), \

TextType = (?) \

WHERE \

Files.FileID in \

(SELECT Files.FileID from files, directories, drives \

where drives.name = (?) and \

directories.nam e = (?) and \

files.name = (?) and \

drives.driveid = directories.dri veid and \

files.directory id = directories.dir ectoryid)")

};

///date conversion function, uses IDataConvert, returns S_OK and
reasonable-looking values

bool CMyDB::FileTime ToDBDate(const FILETIME *pFT, DATE *pD)

{

unsigned long nBytesConverted = 0;

DBSTATUS dbStatus = {0,};

assert(pFT);

assert(pD);

assert(_Module. pCvt);

/*

HRESULT DataConvert (

DBTYPE wSrcType,

DBTYPE wDstType,

ULONG cbSrcLength,

ULONG * pcbDstLength,

void * pSrc,

void * pDst,

ULONG cbDstMaxLength,

DBSTATUS dbsSrcStatus,

DBSTATUS * pdbsStatus,

BYTE bPrecision,

BYTE bScale,

DBDATACONVERT dwFlags);

*/

HRESULT hr =

_Module.pCvt->DataConvert(

DBTYPE_FILETIME , //file time goes in

DBTYPE_DATE, //date comes out

sizeof(*pFT), //size of input

&nBytesConverte d, //how many bytes did we get?

(void *)pFT, //data in

(void *)pD, //data out

sizeof(*pD), //size of data out

DBSTATUS_S_OK, //magic per Status page in OLEDB docs

&dbStatus, //address to store status out

0, //don't care about precision ...

0, //...or about scale

DBDATACONVERT_D EFAULT //default conversion

);

if(FAILED(hr))

{

LogDBError(__WF ILE__, __LINE__, hr);

return false;

}

assert(nBytesCo nverted);

assert(SUCCEEDE D(dbStatus));

return true;

}

//------------------------UPDATE code, szFN has a "nice" file name here ...

wcsncpy(fun.m_D riveName, szFN, (pDir - szFN));

wcsncpy(fun.m_D irName, pDir, (pFile - pDir));

wcsncpy(fun.m_F ileName, pFile, wcslen(pFile));

//here we get the file dates and times as FILETIME structs
if(!GetFileAttr ibutesExW(szFN, GetFileExInfoSt andard, &gfi))

return E_INVALIDARG;
FILETIME ft = {0,};

SYSTEMTIME st = {0,};

GetSystemTime(& st);

SystemTimeToFil eTime(&st, &ft);

//convert the FILETIMEs to db DATE per fxn above

FileTimeToDBDat e(&ft, &fun.m_SSLastUp date);

FileTimeToDBDat e(&gfi.ftCreati onTime, &fun.m_Creation DateTime);

FileTimeToDBDat e(&gfi.ftLastAc cessTime, &fun.m_LastAcce ssDateTime);

FileTimeToDBDat e(&gfi.ftLastWr iteTime, &fun.m_Modifica tionDateTime);

fun.m_Checksum = CalcChecksum(sz FN);

fun.m_SizeLo32 = gfi.nFileSizeLo w;
hr = fun.Open();
//...error trap omitted, always get S_OK, but nothing updated, stepping into
code shows 0 records updated.

fun.Close();

[Riley DeWiley]

Sorry, that is CRowset::Insert (), not Add()

RDeW

"Riley DeWiley" <ri***********@ gmail.com> wrote in message
news:Wf******** ************@se anet.com...

I have an UPDATE query that is always setting 0 records. When I cut and
paste the SQL into Access and use it, it fails in the same way unless I
coerce the date fields to be '=now()', in which case it works. I never get
errors, and if I don't try to update the date fields, I always update the
expected number of rows (1). When I do date conversion using the same
conversion function, then call CRowset::Add() to add a record to the same
table, it works as expected.

I am using OLEDB/C++ against Jet 4.0.

Here are some code fragments for reading enjoyment.

Any insights appreciated.

RDeW

---------------------------------
//accessor
class CFileUpdateByNa meAccessor

{

public:

void ClearRecord()

{

memset(&this->m_DriveName, 0, offsetof(CFileU pdateByNameAcce ssor,
m_TextType) + sizeof(m_TextTy pe));

}

wchar_t m_DriveName[DRIVEBUFSIZE];

wchar_t m_DirName[DIRECTORYBUFSIZ E];

wchar_t m_FileName[FILEBUFSIZE];

LONG m_Checksum;

LONG m_SizeLo32;

DATE m_CreationDateT ime;

DATE m_LastAccessDat eTime;

DATE m_ModificationD ateTime;

DATE m_SSLastUpdate;

LONG m_TextType;

BEGIN_PARAM_MAP (CFileUpdateByN ameAccessor)

COLUMN_ENTRY_TY PE(1, DBTYPE_DATE, m_CreationDateT ime)

COLUMN_ENTRY_TY PE(2, DBTYPE_DATE, m_ModificationD ateTime)

COLUMN_ENTRY_TY PE(3, DBTYPE_DATE, m_LastAccessDat eTime)

COLUMN_ENTRY_TY PE(4, DBTYPE_DATE, m_SSLastUpdate)

COLUMN_ENTRY(5, m_Checksum)

COLUMN_ENTRY(6, m_SizeLo32)

COLUMN_ENTRY(7, m_TextType)

COLUMN_ENTRY(8, m_DriveName)

COLUMN_ENTRY(9, m_DirName)

COLUMN_ENTRY(10 , m_FileName)
END_PARAM_MAP()

DEFINE_COMMAND_ EX(CFileUpdateB yNameAccessor, L"\

UPDATE Files \

SET \

CreationDateTim e = (?), \

ModificationDat eTime = (?), \

LastAccessDateT ime = (?), \

SSLastUpdate = (?), \

Checksum = (?), \

SizeLo32 = (?), \

TextType = (?) \

WHERE \

Files.FileID in \

(SELECT Files.FileID from files, directories, drives \

where drives.name = (?) and \

directories.nam e = (?) and \

files.name = (?) and \

drives.driveid = directories.dri veid and \

files.directory id = directories.dir ectoryid)")

};

///date conversion function, uses IDataConvert, returns S_OK and
reasonable-looking values

bool CMyDB::FileTime ToDBDate(const FILETIME *pFT, DATE *pD)

{

unsigned long nBytesConverted = 0;

DBSTATUS dbStatus = {0,};

assert(pFT);

assert(pD);

assert(_Module. pCvt);

/*

HRESULT DataConvert (

DBTYPE wSrcType,

DBTYPE wDstType,

ULONG cbSrcLength,

ULONG * pcbDstLength,

void * pSrc,

void * pDst,

ULONG cbDstMaxLength,

DBSTATUS dbsSrcStatus,

DBSTATUS * pdbsStatus,

BYTE bPrecision,

BYTE bScale,

DBDATACONVERT dwFlags);

*/

HRESULT hr =

_Module.pCvt->DataConvert(

DBTYPE_FILETIME , //file time goes in

DBTYPE_DATE, //date comes out

sizeof(*pFT), //size of input

&nBytesConverte d, //how many bytes did we get?

(void *)pFT, //data in

(void *)pD, //data out

sizeof(*pD), //size of data out

DBSTATUS_S_OK, //magic per Status page in OLEDB docs

&dbStatus, //address to store status out

0, //don't care about precision ...

0, //...or about scale

DBDATACONVERT_D EFAULT //default conversion

);

if(FAILED(hr))

{

LogDBError(__WF ILE__, __LINE__, hr);

return false;

}

assert(nBytesCo nverted);

assert(SUCCEEDE D(dbStatus));

return true;

}

//------------------------UPDATE code, szFN has a "nice" file name here
...

wcsncpy(fun.m_D riveName, szFN, (pDir - szFN));

wcsncpy(fun.m_D irName, pDir, (pFile - pDir));

wcsncpy(fun.m_F ileName, pFile, wcslen(pFile));

//here we get the file dates and times as FILETIME structs
if(!GetFileAttr ibutesExW(szFN, GetFileExInfoSt andard, &gfi))

return E_INVALIDARG;
FILETIME ft = {0,};

SYSTEMTIME st = {0,};

GetSystemTime(& st);

SystemTimeToFil eTime(&st, &ft);

//convert the FILETIMEs to db DATE per fxn above

FileTimeToDBDat e(&ft, &fun.m_SSLastUp date);

FileTimeToDBDat e(&gfi.ftCreati onTime, &fun.m_Creation DateTime);

FileTimeToDBDat e(&gfi.ftLastAc cessTime, &fun.m_LastAcce ssDateTime);

FileTimeToDBDat e(&gfi.ftLastWr iteTime, &fun.m_Modifica tionDateTime);

fun.m_Checksum = CalcChecksum(sz FN);

fun.m_SizeLo32 = gfi.nFileSizeLo w;
hr = fun.Open();
//...error trap omitted, always get S_OK, but nothing updated, stepping
into code shows 0 records updated.

fun.Close();