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 CNumberCommandA ccessor

{

public:

LONG m_ID;

DATE m_Num0;

BEGIN_PARAM_MAP (CNumberCommand Accessor)

COLUMN_ENTRY_TY PE(1, DBTYPE_DATE, m_Num0)

COLUMN_ENTRY(2, m_ID)

END_PARAM_MAP()

DEFINE_COMMAND_ EX(CNumberComma ndAccessor, L"\

UPDATE Table2 \

SET Table2.Num0 = (?) \

WHERE Table2.ID =(?)")

};

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

{
CoInitialize(0) ;

{

CComPtr<IDataCo nvert> pCvt = 0;

HRESULT hr = CoCreateInstanc e(CLSID_OLEDB_C ONVERSIONLIBRAR Y, 0,
CLSCTX_INPROC_S ERVER | CLSCTX_LOCAL_SE RVER, IID_IDataConver t, (void
**)&pCvt);

if(FAILED(hr))

return 0;

CSession session;

CDataSource db;

CDBPropSet dbinit[1];

dbinit[0].SetGUID(DBPROP SET_DBINIT);

dbinit[0].AddProperty(DB PROP_AUTH_USERI D, OLESTR("Admin") );

dbinit[0].AddProperty(DB PROP_INIT_DATAS OURCE, sDBFile);

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

dbinit[0].AddProperty(DB PROP_INIT_PROMP T, (short)DBPROMPT _NOPROMPT);
if(Open(session , db, dbinit, 1))

{

//CTable<CAccesso r<CDateAccessor > > d;

CCommand<CAcces sor<CNumberComm andAccessor> > d;

CDBPropSet propset(DBPROPS ET_ROWSET);

propset.AddProp erty(DBPROP_IRo wsetChange, true);

propset.AddProp erty(DBPROP_IRo wsetUpdate, true);

propset.AddProp erty(DBPROP_UPD ATABILITY, DBPROPVAL_UP_CH ANGE |
DBPROPVAL_UP_IN SERT | DBPROPVAL_UP_DE LETE);

/*

We have a record #6, yes ...

*/

d.m_ID = 6;

d.m_Num0 = SOME_NEW_INTEGE R_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 CNumberCommandA ccessor

{

public:

LONG m_ID;

DATE m_Num0;

BEGIN_PARAM_MA P(CNumberComman dAccessor)

COLUMN_ENTRY_T YPE(1, DBTYPE_DATE, m_Num0)

COLUMN_ENTRY(2 , m_ID)

END_PARAM_MAP( )

DEFINE_COMMAND _EX(CNumberComm andAccessor, L"\

UPDATE Table2 \

SET Table2.Num0 = (?) \

WHERE Table2.ID =(?)")

};

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

{
CoInitialize(0 );

{

CComPtr<IDataC onvert> pCvt = 0;

HRESULT hr = CoCreateInstanc e(CLSID_OLEDB_C ONVERSIONLIBRAR Y, 0,
CLSCTX_INPROC_ SERVER | CLSCTX_LOCAL_SE RVER, IID_IDataConver t, (void
**)&pCvt);

if(FAILED(hr ))

return 0;

CSession session;

CDataSource db;

CDBPropSet dbinit[1];

dbinit[0].SetGUID(DBPROP SET_DBINIT);

dbinit[0].AddProperty(DB PROP_AUTH_USERI D, OLESTR("Admin") );

dbinit[0].AddProperty(DB PROP_INIT_DATAS OURCE, sDBFile);

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

dbinit[0].AddProperty(DB PROP_INIT_PROMP T, (short)DBPROMPT _NOPROMPT);
if(Open(sessio n, db, dbinit, 1))

{

//CTable<CAccesso r<CDateAccessor > > d;

CCommand<CAcce ssor<CNumberCom mandAccessor> > d;

CDBPropSet propset(DBPROPS ET_ROWSET);

propset.AddPro perty(DBPROP_IR owsetChange, true);

propset.AddPro perty(DBPROP_IR owsetUpdate, true);

propset.AddPro perty(DBPROP_UP DATABILITY, DBPROPVAL_UP_CH ANGE |
DBPROPVAL_UP_I NSERT | DBPROPVAL_UP_DE LETE);

/*

We have a record #6, yes ...

*/

d.m_ID = 6;

d.m_Num0 = SOME_NEW_INTEGE R_VALUE;

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

if(FAILED(hr ))

{

Close(sessio n, db);

return 0;

}
d.Close();

session.Commit ();
Close(sessio n, 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******** ************@se anet.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 CNumberCommandA ccessor

{

public:

LONG m_ID;

DATE m_Num0;

BEGIN_PARAM_MAP (CNumberCommand Accessor)

COLUMN_ENTRY_TY PE(1, DBTYPE_DATE, m_Num0)

COLUMN_ENTRY(2, m_ID)

END_PARAM_MAP()

DEFINE_COMMAND_ EX(CNumberComma ndAccessor, L"\

UPDATE Table2 \

SET Table2.Num0 = (?) \

WHERE Table2.ID =(?)")

};

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

{
CoInitialize(0) ;

{

CComPtr<IDataCo nvert> pCvt = 0;

HRESULT hr = CoCreateInstanc e(CLSID_OLEDB_C ONVERSIONLIBRAR Y, 0,
CLSCTX_INPROC_S ERVER | CLSCTX_LOCAL_SE RVER, IID_IDataConver t, (void
**)&pCvt);

if(FAILED(hr))

return 0;

CSession session;

CDataSource db;

CDBPropSet dbinit[1];

dbinit[0].SetGUID(DBPROP SET_DBINIT);

dbinit[0].AddProperty(DB PROP_AUTH_USERI D, OLESTR("Admin") );

dbinit[0].AddProperty(DB PROP_INIT_DATAS OURCE, sDBFile);

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

dbinit[0].AddProperty(DB PROP_INIT_PROMP T, (short)DBPROMPT _NOPROMPT);
if(Open(session , db, dbinit, 1))

{

//CTable<CAccesso r<CDateAccessor > > d;

CCommand<CAcces sor<CNumberComm andAccessor> > d;

CDBPropSet propset(DBPROPS ET_ROWSET);

propset.AddProp erty(DBPROP_IRo wsetChange, true);

propset.AddProp erty(DBPROP_IRo wsetUpdate, true);

propset.AddProp erty(DBPROP_UPD ATABILITY, DBPROPVAL_UP_CH ANGE |
DBPROPVAL_UP_IN SERT | DBPROPVAL_UP_DE LETE);

/*

We have a record #6, yes ...

*/

d.m_ID = 6;

d.m_Num0 = SOME_NEW_INTEGE R_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.m icrosoft.com> wrote in message
news:O9******** ******@TK2MSFTN GP09.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******** ************@se anet.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 CNumberCommandA ccessor

{

public:

LONG m_ID;

DATE m_Num0;

BEGIN_PARAM_MAP (CNumberCommand Accessor)

COLUMN_ENTRY_TY PE(1, DBTYPE_DATE, m_Num0)

COLUMN_ENTRY(2, m_ID)

END_PARAM_MAP()

DEFINE_COMMAND_ EX(CNumberComma ndAccessor, L"\

UPDATE Table2 \

SET Table2.Num0 = (?) \

WHERE Table2.ID =(?)")

};

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

{
CoInitialize(0) ;

{

CComPtr<IDataCo nvert> pCvt = 0;

HRESULT hr = CoCreateInstanc e(CLSID_OLEDB_C ONVERSIONLIBRAR Y, 0,
CLSCTX_INPROC_S ERVER | CLSCTX_LOCAL_SE RVER, IID_IDataConver t, (void
**)&pCvt);

if(FAILED(hr))

return 0;

CSession session;

CDataSource db;

CDBPropSet dbinit[1];

dbinit[0].SetGUID(DBPROP SET_DBINIT);

dbinit[0].AddProperty(DB PROP_AUTH_USERI D, OLESTR("Admin") );

dbinit[0].AddProperty(DB PROP_INIT_DATAS OURCE, sDBFile);

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

dbinit[0].AddProperty(DB PROP_INIT_PROMP T, (short)DBPROMPT _NOPROMPT);
if(Open(session , db, dbinit, 1))

{

//CTable<CAccesso r<CDateAccessor > > d;

CCommand<CAcces sor<CNumberComm andAccessor> > d;

CDBPropSet propset(DBPROPS ET_ROWSET);

propset.AddProp erty(DBPROP_IRo wsetChange, true);

propset.AddProp erty(DBPROP_IRo wsetUpdate, true);

propset.AddProp erty(DBPROP_UPD ATABILITY, DBPROPVAL_UP_CH ANGE |
DBPROPVAL_UP_IN SERT | DBPROPVAL_UP_DE LETE);

/*

We have a record #6, yes ...

*/

d.m_ID = 6;

d.m_Num0 = SOME_NEW_INTEGE R_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;

}