MARIADB ROWSET BATCH DML OPERATION EXAMPLE

[shrifw]

Dear Rao Sir,

May I request to share the example of Maria DB Rowset Batch Operation in which record add / Modify / Delete and finally save the data in batch mode. Thanks in advance..!


Thanks
Shridhar

[nageswaragunupudi]

Will you please try this?

Code: Select all  Expand view  RUN

oRs := oCn:RowSet( cSql )
oRs:SetBatchMode( .t. )
XBROWSER oRs FASTEDIT
if oRs:IsBatchEdited()
   if MsgYesNo( "Save Changes?" )
      oRs:SaveBatch()
   else
      oRs:CancelBatch()
   endif
endif
XBROWSER oRs
 

It is very important that the Primary Key(s) should be included in the rowset.

[shrifw]

Dear Rao Sir ,

Will try and update you. Many thanks...!!

Thanks
Shridhar

[vilian]

Mr Rao,

What is the differenc between BATCH and TRANSACTION ? Could you explain it to me ?

[nageswaragunupudi]

TRANSACTION:
We use this feature when we need to update/insert more than one table and want that either all changes to be written or none.
In other words, we do not want a situation where some tables are updated and some or not due to whatever reasons.

For example we want all these updates to be written

Code: Select all  Expand view  RUN

aSql := { "UPDATE table1 ... WHERE ... ", ;
          "UPDATE table2 ... WHERE ... ", ;
          ..more.., ;
        }
if WriteAllOrNone( oCn, aSql )
   ? "Written"
else
   ? "fail"
endif
//-------------------------------
function WriteAllOrNone( oCn, aSql )

local cUpdateSql

oCn:BeginTransaction()
for each cUpdateSql IN aSql
   oCn:Execute( cSql )
   if oCn:nError != 0
      oCn:RollBack()
      return .f.
   endif
next
oCn:CommmitTransaction()   

return .t.
 

BATCH:
Same way as in ADO, we can also use RowSet in BatchMode.

Code: Select all  Expand view  RUN

oRs := oCn:RowSet( cSql )
oRs:SetBatchMode( .t. )
 

In this case all changes (add/modify/delete) we make to the RowSet (or RecordSet in ADO) are all made to the RowSet/RecordSet in memory, but not written to the database.
After making all required changes, the we can decide to write all changes to the physical database or discard all changes.

Code: Select all  Expand view  RUN

oCn:SaveBatch()  // write all changes
oCn:CancelBatch() // discard all changes
 

This is mostly useful in Master/Child edits like Invoices, Quotations, Vouchers, etc.

[vilian]

Thank you

[shrifw]

Dear Rao Sir ,

As requested , could you please provide code to use extended the feature of XBROWSE , DATAROW & ROWSET.

Thanks
Shridhar

[vilian]

Do you know if is possible to use Transaction with BEGIN SEQUENC/RECOVER? Something like this:

Code: Select all  Expand view  RUN

BEGIN SEQUENC
      oCn:BeginTransaction()
         oCn:Insert(...)
         oCn:Insert(...)
         oCn:Insert(...)
     oCn:CommitTransaction()

RECOVER USING oError
      oCn:RollBack()
END SEQUENC
 

oCn:CommitTransaction() only will be executed it there is no any fail during oCn:Insert().