UPDATE query results not written to database

[Riley DeWiley]

Using OLEDB, Jet 4.0, C++, Windows.

I have been baffled by UPDATE queries that return S_OK but do not change the
data in the database. It seems that UPDATEs do not update until the
CDataSource object goes out of scope, in some cases, they seem not to update
at all. Apparently the data is being cached and transmitted through some
kind of flushing mechanism.

I would like to "force" writes of data after executing an UPDATE query
through a CCommand<> derived object.

How do I do this?

Some code is posted below.

class CNumberCommandAccessor

{

public:

LONG m_ID;

DATE m_Num0;

BEGIN_PARAM_MAP(CNumberCommandAccessor)

COLUMN_ENTRY_TYPE(1, DBTYPE_DATE, m_Num0)

COLUMN_ENTRY(2, m_ID)

END_PARAM_MAP()

DEFINE_COMMAND_EX(CNumberCommandAccessor, L"\

UPDATE Table2 \

SET Table2.Num0 = (?) \

WHERE Table2.ID =(?)")

};

int wmain(int argc, wchar_t * argv[])

{
CoInitialize(0);

{

CComPtr<IDataConvert> pCvt = 0;

HRESULT hr = CoCreateInstance(CLSID_OLEDB_CONVERSIONLIBRARY, 0,
CLSCTX_INPROC_SERVER | CLSCTX_LOCAL_SERVER, IID_IDataConvert, (void
**)&pCvt);

if(FAILED(hr))

return 0;

CSession session;

CDataSource db;

CDBPropSet dbinit[1];

dbinit[0].SetGUID(DBPROPSET_DBINIT);

dbinit[0].AddProperty(DBPROP_AUTH_USERID, OLESTR("Admin"));

dbinit[0].AddProperty(DBPROP_INIT_DATASOURCE, sDBFile);

dbinit[0].AddProperty(DBPROP_INIT_MODE, (long)16);

dbinit[0].AddProperty(DBPROP_INIT_PROMPT, (short)DBPROMPT_NOPROMPT);
if(Open(session, db, dbinit, 1))

{

//CTable<CAccessor<CDateAccessor> > d;

CCommand<CAccessor<CNumberCommandAccessor> > d;

CDBPropSet propset(DBPROPSET_ROWSET);

propset.AddProperty(DBPROP_IRowsetChange, true);

propset.AddProperty(DBPROP_IRowsetUpdate, true);

propset.AddProperty(DBPROP_UPDATABILITY, DBPROPVAL_UP_CHANGE |
DBPROPVAL_UP_INSERT | DBPROPVAL_UP_DELETE);

/*

We have a record #6, yes ...

*/

d.m_ID = 6;

d.m_Num0 = SOME_NEW_INTEGER_VALUE;

HRESULT hr = d.Open(session); //I EXPECT to see the changes right after this
executes but don't

if(FAILED(hr))

{

Close(session, db);

return 0;

}
d.Close();

session.Commit();
Close(session, db);

//STILL NO CHANGES

}

//NOW THEY APPEAR - WHY NOW?
}

CoUninitialize();

return 0;

}

[Tom van Stiphout]

On Sat, 20 Aug 2005 15:55:39 -0700, "Riley DeWiley"
<ri***********@gmail.com> wrote:

I have no specific knowledge about CCommand, but isn't there an
Execute method? Open seems more for recordset-returning commands
(SELECT statements), whereas Execute is typically used for action
queries like UPDATE, DELETE etc.

Your code reminds me why I don't program in C++ anymore. I've lost
appetite for accomplishing simple tasks with many lines of code.

-Tom.

Using OLEDB, Jet 4.0, C++, Windows.

I have been baffled by UPDATE queries that return S_OK but do not change the
data in the database. It seems that UPDATEs do not update until the
CDataSource object goes out of scope, in some cases, they seem not to update
at all. Apparently the data is being cached and transmitted through some
kind of flushing mechanism.

I would like to "force" writes of data after executing an UPDATE query
through a CCommand<> derived object.

How do I do this?

Some code is posted below.

class CNumberCommandAccessor

{

public:

LONG m_ID;

DATE m_Num0;

BEGIN_PARAM_MAP(CNumberCommandAccessor)

COLUMN_ENTRY_TYPE(1, DBTYPE_DATE, m_Num0)

COLUMN_ENTRY(2, m_ID)

END_PARAM_MAP()

DEFINE_COMMAND_EX(CNumberCommandAccessor, L"\

UPDATE Table2 \

SET Table2.Num0 = (?) \

WHERE Table2.ID =(?)")

};

int wmain(int argc, wchar_t * argv[])

{
CoInitialize(0);

{

CComPtr<IDataConvert> pCvt = 0;

HRESULT hr = CoCreateInstance(CLSID_OLEDB_CONVERSIONLIBRARY, 0,
CLSCTX_INPROC_SERVER | CLSCTX_LOCAL_SERVER, IID_IDataConvert, (void
**)&pCvt);

if(FAILED(hr))

return 0;

CSession session;

CDataSource db;

CDBPropSet dbinit[1];

dbinit[0].SetGUID(DBPROPSET_DBINIT);

dbinit[0].AddProperty(DBPROP_AUTH_USERID, OLESTR("Admin"));

dbinit[0].AddProperty(DBPROP_INIT_DATASOURCE, sDBFile);

dbinit[0].AddProperty(DBPROP_INIT_MODE, (long)16);

dbinit[0].AddProperty(DBPROP_INIT_PROMPT, (short)DBPROMPT_NOPROMPT);
if(Open(session, db, dbinit, 1))

{

//CTable<CAccessor<CDateAccessor> > d;

CCommand<CAccessor<CNumberCommandAccessor> > d;

CDBPropSet propset(DBPROPSET_ROWSET);

propset.AddProperty(DBPROP_IRowsetChange, true);

propset.AddProperty(DBPROP_IRowsetUpdate, true);

propset.AddProperty(DBPROP_UPDATABILITY, DBPROPVAL_UP_CHANGE |
DBPROPVAL_UP_INSERT | DBPROPVAL_UP_DELETE);

/*

We have a record #6, yes ...

*/

d.m_ID = 6;

d.m_Num0 = SOME_NEW_INTEGER_VALUE;

HRESULT hr = d.Open(session); //I EXPECT to see the changes right after this
executes but don't

if(FAILED(hr))

{

Close(session, db);

return 0;

}
d.Close();

session.Commit();
Close(session, db);

//STILL NO CHANGES

}

//NOW THEY APPEAR - WHY NOW?
}

CoUninitialize();

return 0;

}

[Matt Neerincx [MS]]

Yes, this is how the Jet driver works. Data modifications are
asynchronously flushed to the database file.
You have to do some special things to bypass this asynch behavior, I'm
digging around but I can't find anything to help you here. I wrote a KB
article about 5-6 years ago about this but it looks like they deleted it.

Matt Neerincx [MSFT]

This posting is provided "AS IS", with no warranties, and confers no rights.

Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

"Riley DeWiley" <ri***********@gmail.com> wrote in message
news:CN********************@seanet.com...

Using OLEDB, Jet 4.0, C++, Windows.

I have been baffled by UPDATE queries that return S_OK but do not change
the data in the database. It seems that UPDATEs do not update until the
CDataSource object goes out of scope, in some cases, they seem not to
update at all. Apparently the data is being cached and transmitted through
some kind of flushing mechanism.

I would like to "force" writes of data after executing an UPDATE query
through a CCommand<> derived object.

How do I do this?

Some code is posted below.

class CNumberCommandAccessor

{

public:

LONG m_ID;

DATE m_Num0;

BEGIN_PARAM_MAP(CNumberCommandAccessor)

COLUMN_ENTRY_TYPE(1, DBTYPE_DATE, m_Num0)

COLUMN_ENTRY(2, m_ID)

END_PARAM_MAP()

DEFINE_COMMAND_EX(CNumberCommandAccessor, L"\

UPDATE Table2 \

SET Table2.Num0 = (?) \

WHERE Table2.ID =(?)")

};

int wmain(int argc, wchar_t * argv[])

{
CoInitialize(0);

{

CComPtr<IDataConvert> pCvt = 0;

HRESULT hr = CoCreateInstance(CLSID_OLEDB_CONVERSIONLIBRARY, 0,
CLSCTX_INPROC_SERVER | CLSCTX_LOCAL_SERVER, IID_IDataConvert, (void
**)&pCvt);

if(FAILED(hr))

return 0;

CSession session;

CDataSource db;

CDBPropSet dbinit[1];

dbinit[0].SetGUID(DBPROPSET_DBINIT);

dbinit[0].AddProperty(DBPROP_AUTH_USERID, OLESTR("Admin"));

dbinit[0].AddProperty(DBPROP_INIT_DATASOURCE, sDBFile);

dbinit[0].AddProperty(DBPROP_INIT_MODE, (long)16);

dbinit[0].AddProperty(DBPROP_INIT_PROMPT, (short)DBPROMPT_NOPROMPT);
if(Open(session, db, dbinit, 1))

{

//CTable<CAccessor<CDateAccessor> > d;

CCommand<CAccessor<CNumberCommandAccessor> > d;

CDBPropSet propset(DBPROPSET_ROWSET);

propset.AddProperty(DBPROP_IRowsetChange, true);

propset.AddProperty(DBPROP_IRowsetUpdate, true);

propset.AddProperty(DBPROP_UPDATABILITY, DBPROPVAL_UP_CHANGE |
DBPROPVAL_UP_INSERT | DBPROPVAL_UP_DELETE);

/*

We have a record #6, yes ...

*/

d.m_ID = 6;

d.m_Num0 = SOME_NEW_INTEGER_VALUE;

HRESULT hr = d.Open(session); //I EXPECT to see the changes right after
this executes but don't

if(FAILED(hr))

{

Close(session, db);

return 0;

}
d.Close();

session.Commit();
Close(session, db);

//STILL NO CHANGES

}

//NOW THEY APPEAR - WHY NOW?
}

CoUninitialize();

return 0;

}

[Matt Neerincx [MS]]

Ok, found it ->

I wrote this KB eons ago, but it still applies.

"How To Synchronizing Reads and Writes Between Two DAO Processes"
http://support.microsoft.com/kb/180223/EN-US/

Looks like the title was translated to some other language and back and
suffered in the process but the KB has all the info you need.
Matt Neerincx [MSFT]
This posting is provided "AS IS", with no warranties, and confers no rights.

Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

"Matt Neerincx [MS]" <ma***@online.microsoft.com> wrote in message
news:O9**************@TK2MSFTNGP09.phx.gbl...

Yes, this is how the Jet driver works. Data modifications are
asynchronously flushed to the database file.
You have to do some special things to bypass this asynch behavior, I'm
digging around but I can't find anything to help you here. I wrote a KB
article about 5-6 years ago about this but it looks like they deleted it.

Matt Neerincx [MSFT]

This posting is provided "AS IS", with no warranties, and confers no
rights.

Please do not send email directly to this alias. This alias is for
newsgroup purposes only.

"Riley DeWiley" <ri***********@gmail.com> wrote in message
news:CN********************@seanet.com...

Using OLEDB, Jet 4.0, C++, Windows.

I have been baffled by UPDATE queries that return S_OK but do not change
the data in the database. It seems that UPDATEs do not update until the
CDataSource object goes out of scope, in some cases, they seem not to
update at all. Apparently the data is being cached and transmitted
through some kind of flushing mechanism.

I would like to "force" writes of data after executing an UPDATE query
through a CCommand<> derived object.

How do I do this?

Some code is posted below.

class CNumberCommandAccessor

{

public:

LONG m_ID;

DATE m_Num0;

BEGIN_PARAM_MAP(CNumberCommandAccessor)

COLUMN_ENTRY_TYPE(1, DBTYPE_DATE, m_Num0)

COLUMN_ENTRY(2, m_ID)

END_PARAM_MAP()

DEFINE_COMMAND_EX(CNumberCommandAccessor, L"\

UPDATE Table2 \

SET Table2.Num0 = (?) \

WHERE Table2.ID =(?)")

};

int wmain(int argc, wchar_t * argv[])

{
CoInitialize(0);

{

CComPtr<IDataConvert> pCvt = 0;

HRESULT hr = CoCreateInstance(CLSID_OLEDB_CONVERSIONLIBRARY, 0,
CLSCTX_INPROC_SERVER | CLSCTX_LOCAL_SERVER, IID_IDataConvert, (void
**)&pCvt);

if(FAILED(hr))

return 0;

CSession session;

CDataSource db;

CDBPropSet dbinit[1];

dbinit[0].SetGUID(DBPROPSET_DBINIT);

dbinit[0].AddProperty(DBPROP_AUTH_USERID, OLESTR("Admin"));

dbinit[0].AddProperty(DBPROP_INIT_DATASOURCE, sDBFile);

dbinit[0].AddProperty(DBPROP_INIT_MODE, (long)16);

dbinit[0].AddProperty(DBPROP_INIT_PROMPT, (short)DBPROMPT_NOPROMPT);
if(Open(session, db, dbinit, 1))

{

//CTable<CAccessor<CDateAccessor> > d;

CCommand<CAccessor<CNumberCommandAccessor> > d;

CDBPropSet propset(DBPROPSET_ROWSET);

propset.AddProperty(DBPROP_IRowsetChange, true);

propset.AddProperty(DBPROP_IRowsetUpdate, true);

propset.AddProperty(DBPROP_UPDATABILITY, DBPROPVAL_UP_CHANGE |
DBPROPVAL_UP_INSERT | DBPROPVAL_UP_DELETE);

/*

We have a record #6, yes ...

*/

d.m_ID = 6;

d.m_Num0 = SOME_NEW_INTEGER_VALUE;

HRESULT hr = d.Open(session); //I EXPECT to see the changes right after
this executes but don't

if(FAILED(hr))

{

Close(session, db);

return 0;

}
d.Close();

session.Commit();
Close(session, db);

//STILL NO CHANGES

}

//NOW THEY APPEAR - WHY NOW?
}

CoUninitialize();

return 0;

}