ADO BeginTrans inside a try catch block
-
I have a couple of questions about using BeginTrans() in a try catch block. Below is an example of how I use ADO to modify a record in my Application. This application was originally made to use JET and I am tyring to migrate to MSDE. When I was using JET to maintain consistancy between the records of one connection and the records of another connection in the same app using a different thread I had to flush the cache in Jet ( using a call to pJetEngine->RefreshCache( m_pConnection)) and then call BeginTrans, update the data and call CommitTrans. In JET each ConnectionPtr has a cache on the database that is independent of other open connections. Using this procedure the thread will see and modify the current data in the db and not what was cached for this connection. Do we have to do someting like this with MSDE? Is there a way to flush the cache? Also is it possible that BeginTrans / CommitTrans will throw an exception? If so how do I handle BeginTrans inside a try / catch block? Do I call RollbackTrans in each of my catch routines? Could RollbackTrans() throw an exception? _RecordsetPtr pRst = NULL; try { TESTHR(pRst.CreateInstance(__uuidof(Recordset))); pRst->Open(_bstr_t(strQuery), m_pConnection, adOpenStatic , adLockOptimistic, adCmdText); m_pConnection->BeginTrans(); int count = pRst->GetRecordCount(); if ( count == 1 ) { // Modify record using PutCollect } pRst->Update(); pRst->Close(); pRst=NULL; m_pConnection->CommitTrans(); } catch (_com_error &e) { GenerateError(e.Error(), e.ErrorMessage(), e.Description(),__FILE__,__LINE__); } catch( CException* e) { GenerateError(e,__FILE__,__LINE__); } catch (...) { GenerateError(__FILE__,__LINE__); }
-
I have a couple of questions about using BeginTrans() in a try catch block. Below is an example of how I use ADO to modify a record in my Application. This application was originally made to use JET and I am tyring to migrate to MSDE. When I was using JET to maintain consistancy between the records of one connection and the records of another connection in the same app using a different thread I had to flush the cache in Jet ( using a call to pJetEngine->RefreshCache( m_pConnection)) and then call BeginTrans, update the data and call CommitTrans. In JET each ConnectionPtr has a cache on the database that is independent of other open connections. Using this procedure the thread will see and modify the current data in the db and not what was cached for this connection. Do we have to do someting like this with MSDE? Is there a way to flush the cache? Also is it possible that BeginTrans / CommitTrans will throw an exception? If so how do I handle BeginTrans inside a try / catch block? Do I call RollbackTrans in each of my catch routines? Could RollbackTrans() throw an exception? _RecordsetPtr pRst = NULL; try { TESTHR(pRst.CreateInstance(__uuidof(Recordset))); pRst->Open(_bstr_t(strQuery), m_pConnection, adOpenStatic , adLockOptimistic, adCmdText); m_pConnection->BeginTrans(); int count = pRst->GetRecordCount(); if ( count == 1 ) { // Modify record using PutCollect } pRst->Update(); pRst->Close(); pRst=NULL; m_pConnection->CommitTrans(); } catch (_com_error &e) { GenerateError(e.Error(), e.ErrorMessage(), e.Description(),__FILE__,__LINE__); } catch( CException* e) { GenerateError(e,__FILE__,__LINE__); } catch (...) { GenerateError(__FILE__,__LINE__); }
Hi Do I call RollbackTrans in each of my catch routines? Looking at your sample, the answer is yes. But I have one question: why your try/catch block and begintrans/committrans enclose all your recordset operation? Regards, Wanderley
-
Hi Do I call RollbackTrans in each of my catch routines? Looking at your sample, the answer is yes. But I have one question: why your try/catch block and begintrans/committrans enclose all your recordset operation? Regards, Wanderley
There are several cases in my database where I need to guarantee that the data read from one ConnectionPtr is the latest data written to the database. This generally happens when I need to generate an ID for something and don't use an AutoNumber. One example of this is when I need to generate a Patient ID when the user has not entered a valid one and the case has been forcibly closed because of inactivity. As I said in my original description, because of caching that occurs in the connection at least when using JET to gaurantee you have the latest data from one ConnectionPtr to another you have to flush then cache (on reads) and do begintrans/committrans on all writes that need to be the latest. There is a MS KB article Q200300 that explains the problem as it refers to JET, it is a couple of years old and I have no idea if it applies to MSDE also. Below is taken from the KB article: In certain situations you may need to use two separate ADO connections when writing and reading data from an Access database using the Jet OLEDB Provider. For example, if you have two separate processes that are writing and reading to the same Access database, there is no way to share a single connection. In this situation, you can synchronize writes and reads with separate ADO connections if you follow these guidelines: The writer must start a transaction, using ADO's Connection.BeginTrans, prior to writing the data. The writer must make the database updates and then commit the transaction (using ADO's Connection.CommitTrans). The reader must call JRO.JetEngine.RefreshCache passing in it's connection prior to attempting to read the data.
-
There are several cases in my database where I need to guarantee that the data read from one ConnectionPtr is the latest data written to the database. This generally happens when I need to generate an ID for something and don't use an AutoNumber. One example of this is when I need to generate a Patient ID when the user has not entered a valid one and the case has been forcibly closed because of inactivity. As I said in my original description, because of caching that occurs in the connection at least when using JET to gaurantee you have the latest data from one ConnectionPtr to another you have to flush then cache (on reads) and do begintrans/committrans on all writes that need to be the latest. There is a MS KB article Q200300 that explains the problem as it refers to JET, it is a couple of years old and I have no idea if it applies to MSDE also. Below is taken from the KB article: In certain situations you may need to use two separate ADO connections when writing and reading data from an Access database using the Jet OLEDB Provider. For example, if you have two separate processes that are writing and reading to the same Access database, there is no way to share a single connection. In this situation, you can synchronize writes and reads with separate ADO connections if you follow these guidelines: The writer must start a transaction, using ADO's Connection.BeginTrans, prior to writing the data. The writer must make the database updates and then commit the transaction (using ADO's Connection.CommitTrans). The reader must call JRO.JetEngine.RefreshCache passing in it's connection prior to attempting to read the data.
Hi I asked that because I use a different approach for try/catch and begin/rollback/committrans and even for custom generated PKIDs (I create it at stored procedure level). Sorry, I couldn't help this time. :) Regards, Wanderley
-
Hi I asked that because I use a different approach for try/catch and begin/rollback/committrans and even for custom generated PKIDs (I create it at stored procedure level). Sorry, I couldn't help this time. :) Regards, Wanderley
Thanks. Actually you have been very helpful. I have not used any stored procedures (except the system ones) yet.. I've only started to migrate from JET to MSDE last friday and T-SQL is new to me.
-
Thanks. Actually you have been very helpful. I have not used any stored procedures (except the system ones) yet.. I've only started to migrate from JET to MSDE last friday and T-SQL is new to me.
Hi One more thing: do you need to be sure that you have the latest data to create unique IDs? Do you keep a table of IDs and increment it whenever you need? I said I have a different approach because I just use try/catch blocks where it's more likely (is that right? english is my second language :)) to have trouble. In your sample, I would use it only when opening the recordset. And I use begin/commit/rollback when I'm actually writing data (before addnew and after update, for example). Regards, Wanderley