Page 1 of 1
MARIADB ROWSET BATCH DML OPERATION EXAMPLE
Posted: Sun Sep 10, 2023 4:21 am
by 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
Re: MARIADB ROWSET BATCH DML OPERATION EXAMPLE
Posted: Mon Sep 11, 2023 5:00 am
by nageswaragunupudi
Will you please try this?
Code: Select all | Expand
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.
Re: MARIADB ROWSET BATCH DML OPERATION EXAMPLE
Posted: Mon Sep 11, 2023 12:26 pm
by shrifw
Dear Rao Sir ,
Will try and update you. Many thanks...!!
Thanks
Shridhar
Re: MARIADB ROWSET BATCH DML OPERATION EXAMPLE
Posted: Mon Sep 11, 2023 8:32 pm
by vilian
Mr Rao,
What is the differenc between BATCH and TRANSACTION ? Could you explain it to me ?
Re: MARIADB ROWSET BATCH DML OPERATION EXAMPLE
Posted: Tue Sep 12, 2023 1:28 am
by 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
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.
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
oCn:SaveBatch() // write all changes
oCn:CancelBatch() // discard all changes
This is mostly useful in Master/Child edits like Invoices, Quotations, Vouchers, etc.
Re: MARIADB ROWSET BATCH DML OPERATION EXAMPLE
Posted: Tue Sep 12, 2023 10:26 am
by vilian
Thank you
Re: MARIADB ROWSET BATCH DML OPERATION EXAMPLE
Posted: Wed Oct 11, 2023 4:43 am
by shrifw
Dear Rao Sir ,
As requested , could you please provide code to use extended the feature of XBROWSE , DATAROW & ROWSET.
Thanks
Shridhar
Re: MARIADB ROWSET BATCH DML OPERATION EXAMPLE
Posted: Thu Oct 19, 2023 2:06 pm
by vilian
Do you know if is possible to use Transaction with BEGIN SEQUENC/RECOVER? Something like this:
Code: Select all | Expand
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().