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 CFileUpdateByNameAccessor

{

public:

void ClearRecord()

{

memset(&this->m_DriveName, 0, offsetof(CFileUpdateByNameAccessor,
m_TextType) + sizeof(m_TextType));

}

wchar_t m_DriveName[DRIVEBUFSIZE];

wchar_t m_DirName[DIRECTORYBUFSIZE];

wchar_t m_FileName[FILEBUFSIZE];

LONG m_Checksum;

LONG m_SizeLo32;

DATE m_CreationDateTime;

DATE m_LastAccessDateTime;

DATE m_ModificationDateTime;

DATE m_SSLastUpdate;

LONG m_TextType;

BEGIN_PARAM_MAP(CFileUpdateByNameAccessor)

COLUMN_ENTRY_TYPE(1, DBTYPE_DATE, m_CreationDateTime)

COLUMN_ENTRY_TYPE(2, DBTYPE_DATE, m_ModificationDateTime)

COLUMN_ENTRY_TYPE(3, DBTYPE_DATE, m_LastAccessDateTime)

COLUMN_ENTRY_TYPE(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(CFileUpdateByNameAccessor, L"\

UPDATE Files \

SET \

CreationDateTime = (?), \

ModificationDateTime = (?), \

LastAccessDateTime = (?), \

SSLastUpdate = (?), \

Checksum = (?), \

SizeLo32 = (?), \

TextType = (?) \

WHERE \

Files.FileID in \

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

where drives.name = (?) and \

directories.name = (?) and \

files.name = (?) and \

drives.driveid = directories.driveid and \

files.directoryid = directories.directoryid)")

};

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

bool CMyDB::FileTimeToDBDate(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

&nBytesConverted, //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_DEFAULT //default conversion

);

if(FAILED(hr))

{

LogDBError(__WFILE__, __LINE__, hr);

return false;

}

assert(nBytesConverted);

assert(SUCCEEDED(dbStatus));

return true;

}

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

wcsncpy(fun.m_DriveName, szFN, (pDir - szFN));

wcsncpy(fun.m_DirName, pDir, (pFile - pDir));

wcsncpy(fun.m_FileName, pFile, wcslen(pFile));

//here we get the file dates and times as FILETIME structs
if(!GetFileAttributesExW(szFN, GetFileExInfoStandard, &gfi))

return E_INVALIDARG;
FILETIME ft = {0,};

SYSTEMTIME st = {0,};

GetSystemTime(&st);

SystemTimeToFileTime(&st, &ft);

//convert the FILETIMEs to db DATE per fxn above

FileTimeToDBDate(&ft, &fun.m_SSLastUpdate);

FileTimeToDBDate(&gfi.ftCreationTime, &fun.m_CreationDateTime);

FileTimeToDBDate(&gfi.ftLastAccessTime, &fun.m_LastAccessDateTime);

FileTimeToDBDate(&gfi.ftLastWriteTime, &fun.m_ModificationDateTime);

fun.m_Checksum = CalcChecksum(szFN);

fun.m_SizeLo32 = gfi.nFileSizeLow;
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********************@seanet.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 CFileUpdateByNameAccessor

{

public:

void ClearRecord()

{

memset(&this->m_DriveName, 0, offsetof(CFileUpdateByNameAccessor,
m_TextType) + sizeof(m_TextType));

}

wchar_t m_DriveName[DRIVEBUFSIZE];

wchar_t m_DirName[DIRECTORYBUFSIZE];

wchar_t m_FileName[FILEBUFSIZE];

LONG m_Checksum;

LONG m_SizeLo32;

DATE m_CreationDateTime;

DATE m_LastAccessDateTime;

DATE m_ModificationDateTime;

DATE m_SSLastUpdate;

LONG m_TextType;

BEGIN_PARAM_MAP(CFileUpdateByNameAccessor)

COLUMN_ENTRY_TYPE(1, DBTYPE_DATE, m_CreationDateTime)

COLUMN_ENTRY_TYPE(2, DBTYPE_DATE, m_ModificationDateTime)

COLUMN_ENTRY_TYPE(3, DBTYPE_DATE, m_LastAccessDateTime)

COLUMN_ENTRY_TYPE(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(CFileUpdateByNameAccessor, L"\

UPDATE Files \

SET \

CreationDateTime = (?), \

ModificationDateTime = (?), \

LastAccessDateTime = (?), \

SSLastUpdate = (?), \

Checksum = (?), \

SizeLo32 = (?), \

TextType = (?) \

WHERE \

Files.FileID in \

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

where drives.name = (?) and \

directories.name = (?) and \

files.name = (?) and \

drives.driveid = directories.driveid and \

files.directoryid = directories.directoryid)")

};

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

bool CMyDB::FileTimeToDBDate(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

&nBytesConverted, //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_DEFAULT //default conversion

);

if(FAILED(hr))

{

LogDBError(__WFILE__, __LINE__, hr);

return false;

}

assert(nBytesConverted);

assert(SUCCEEDED(dbStatus));

return true;

}

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

wcsncpy(fun.m_DriveName, szFN, (pDir - szFN));

wcsncpy(fun.m_DirName, pDir, (pFile - pDir));

wcsncpy(fun.m_FileName, pFile, wcslen(pFile));

//here we get the file dates and times as FILETIME structs
if(!GetFileAttributesExW(szFN, GetFileExInfoStandard, &gfi))

return E_INVALIDARG;
FILETIME ft = {0,};

SYSTEMTIME st = {0,};

GetSystemTime(&st);

SystemTimeToFileTime(&st, &ft);

//convert the FILETIMEs to db DATE per fxn above

FileTimeToDBDate(&ft, &fun.m_SSLastUpdate);

FileTimeToDBDate(&gfi.ftCreationTime, &fun.m_CreationDateTime);

FileTimeToDBDate(&gfi.ftLastAccessTime, &fun.m_LastAccessDateTime);

FileTimeToDBDate(&gfi.ftLastWriteTime, &fun.m_ModificationDateTime);

fun.m_Checksum = CalcChecksum(szFN);

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

fun.Close();