OLE DB transactions

The person who learns the data generally knows the importance of the transaction, which is a method of grouping or batching a series of updates to a data source, either committing the update at the same time when all the updates are successful, or when any one of the updates fails to roll back the data in the database to the one before all operations in the batch are executed. Using transactions guarantees the integrity of the data. This is not a matter of details, just about OLE DB's support for the transaction.

ITransactionLocal interface

The interface that supports transactions in OLE DB is the ITransactionLocal interface, which is an optional interface, and OLE DB does not force all databases to support the interface, so you need to decide whether to support it before using it, but it is now supported by several common databases.

1. The interface belongs to the reply object, so to get the interface only need to call QueryInterface according to a reply object.
2. Invokes the StartTransaction method of the interface to start a transaction
   
   The function is prototyped as follows

HRESULT StartTransaction (   ISOLEVEL                isoLevel,   ULONG                   isoFlags,   ITransactionOptions    *pOtherOptions,   ULONG                  *pulTransactionLevel);

The first parameter is the level of isolation of the transaction concurrency, typically the most commonly used is isolationlevel_cursorstability, which means that only the final commit can query the data of the corresponding database table.
The second parameter is a flag, and it must now have a value of 0
The 3rd parameter is a pointer, which can be empty, or a pointer returned by calling the Itransactionlocal::getoptionsobject function
The 4th parameter is the concurrency isolation level of the transaction after the function is called to create a transaction

The isolation level is for different threads or processes, such as when there are multiple clients operating the database at the same time, if we are set to isolationlevel_cursorstability, then only if one of the clients commits the transaction update in the same transaction. Another client can do the normal operation of the query, such as it is simple to treat this identity as it is locked in the database, only if it completes the transaction after the other client can use the database normally

1. Perform related database operations normally after starting a transaction
2. Call the ITransaction::Commit method to submit all modifications made by the transaction when all steps are completed properly
3. Or, call the ITransaction::Abort method to rollback all operations when one or more steps fail.

Demo Example

//Note that data from both tables can be read after using Isolationlevel_cursorstability to represent the final comminthr = Pitransaction->starttransaction (isolationlevel_cursorstability,0, null,null);//Gets the maximum value of the primary table primary keyPretdata = Runsqlgetvalue (piopenrowset,_t ("Select Max (PID) as PMax from T_primary"));if(NULL = = Pretdata) {GotoCLEAR_UP; } IPID = * (int*) ((byte*) Pretdata +sizeof(Dbstatus) +sizeof(ULONG));The //maximum is always 1, so even if a null value is obtained, the starting value is normal 1++ipid;    Tableid.ekind = Dbkind_name;    TableID.uName.pwszName = (LPOLESTR) pszprimarytable; hr = Piopenrowset->openrowset (Null,&tableid, Null,iid_irowsetchange,1, Propset, (iunknown**) &pirowsetchange); Com_com_check (hr,_t ("Open Table Object"%s' failure, error code: 0x%08x\n"), PSZPRIMARYTABLE,HR); Ulchangeoffset = CreateAccessor (Pirowsetchange,piaccessor,hchangeaccessor,pchangebindings,ulrealcols);if(0= = Ulchangeoffset | | NULL = = Hchangeaccessor | | NULL = = Piaccessor | | NULL = = Pchangebindings | |0= = Ulrealcols) {GotoCLEAR_UP; }//Assign a new row data after setting data to insertPbnewdata = (byte*) com_calloc (Ulchangeoffset);//Set the first field K_pid* (Dblength *) ((BYTE *) Pbnewdata + pchangebindings[0].oblength) =sizeof(int); *(int*) (Pbnewdata + pchangebindings[0].obvalue) = IPID;//Set a second field F_mvalue* (Dblength *) ((BYTE *) Pbnewdata + pchangebindings[1].oblength) =8; StringCchCopy ((wchar*) (Pbnewdata + pchangebindings[1].obvalue), pchangebindings[1].cbmaxlen/sizeof(WCHAR), _t ("Master table data"));//Insert new datahr = Pirowsetchange->insertrow (null,hchangeaccessor,pbnewdata,null); Com_com_check (hr,_t ("Calling InsertRow to insert a new row failed with error code: 0x%08x\n"), HR); hr = Pirowsetchange->queryinterface (Iid_irowsetupdate, (void* *) &pirowsetupdate); Com_com_check (hr,_t ("Get IRowsetUpdate interface failed with error code: 0x%08x\n"), HR); hr = Pirowsetupdate->update (NULL,0, null,null,null,null); Com_com_check (hr,_t ("The call to update failed to commit updates, error code: 0x%08x\n"), HR);    Com_safefree (pchangebindings);    Com_safefree (Pretdata); Com_safefree (Pbnewdata);if(Null! = Hchangeaccessor && NULL! = piaccessor)        {piaccessor->releaseaccessor (hchangeaccessor,null);    Hchangeaccessor = NULL;    } com_saferelease (Piaccessor);    Com_saferelease (Pirowsetchange); Com_saferelease (pirowsetupdate);//Insert a second data from the tableTableid.ekind = Dbkind_name;    TableID.uName.pwszName = (LPOLESTR) pszminortable; hr = Piopenrowset->openrowset (Null,&tableid, Null,iid_irowsetchange,1, Propset, (iunknown**) &pirowsetchange); Com_com_check (hr,_t ("Open Table Object"%s' failure, error code: 0x%08x\n"), PSZMINORTABLE,HR); Ulchangeoffset = CreateAccessor (Pirowsetchange,piaccessor,hchangeaccessor,pchangebindings,ulrealcols);if(0= = Ulchangeoffset | | NULL = = Hchangeaccessor | | NULL = = Piaccessor | | NULL = = Pchangebindings | |0= = Ulrealcols) {GotoCLEAR_UP; }//Assign a new row data after setting data to insertPbnewdata = (byte*) com_calloc (Ulchangeoffset);//Set the first field K_mid* (Dblength *) ((BYTE *) Pbnewdata + pchangebindings[0].oblength) =sizeof(int);//Set a second field K_pid* (Dblength *) ((BYTE *) Pbnewdata + pchangebindings[1].oblength) =sizeof(int); *(int*) (Pbnewdata + pchangebindings[1].obvalue) = IPID;//Set a second field* (Dblength *) ((BYTE *) Pbnewdata + pchangebindings[2].oblength) =8; StringCchCopy ((wchar*) (Pbnewdata + pchangebindings[2].obvalue), pchangebindings[2].cbmaxlen/sizeof(WCHAR), _t ("from table data")); for(inti = IMiDs; I <= Imidmax; i++) {//Loop Insert new data        //Set the first field K_mid*(int*) (Pbnewdata + pchangebindings[0].obvalue) = i;        hr = Pirowsetchange->insertrow (null,hchangeaccessor,pbnewdata,null); Com_com_check (hr,_t ("Calling InsertRow to insert a new row failed with error code: 0x%08x\n"), HR); } hr = Pirowsetchange->queryinterface (Iid_irowsetupdate, (void* *) &pirowsetupdate); Com_com_check (hr,_t ("Get IRowsetUpdate interface failed with error code: 0x%08x\n"), HR); hr = Pirowsetupdate->update (NULL,0, null,null,null,null); Com_com_check (hr,_t ("The call to update failed to commit updates, error code: 0x%08x\n"), HR);//All operations are successful, commit TRANSACTION release resourceshr = Pitransaction->commit (FALSE, Xacttc_sync,0); Com_com_check (hr,_t ("Transaction commit failed with error code: 0x%08x\n"), HR); CLEAR_UP://operation failed, ROLLBACK TRANSACTION First, then release resourcehr = Pitransaction->abort (NULL, False, false);

In the above code, first create a transaction object, and then in the related database operation, here is mainly to update and insert new data, when all the successful operation after the invocation of the commit function, when there is an error in which will jump to the CLEAR_UP tag, call abort to rollback

Complete code for the last instance:
Trancation

OLE DB transactions