DataRow can save multiple records in Batch Mode?

[shri_fwh]

Dear All ,

I'm currently using the DataRow class to save single records in MariaDB, and it's working well.

Now, I want to integrate XBrowse with DataRow to save multiple records into MariaDB in batch mode. However, I'm unsure whether DataRow supports batch operations.

I'd appreciate any guidance or suggestions on how to achieve this.

Thanks
Shridhar

[Antonio Linares]

Dear Shridhar,

Please add these methods to Class TXBrowse and let us know if they work as expected:

Code: Select all | Expand

/*----------------------------------------------------------------------------//
* METHOD UpdateBatch() CLASS TXBrowse
*
* Saves all pending changes in a batch update
* Requires the ADO recordset to be opened with LockType = adLockBatchOptimistic (4)
* and CursorLocation = adUseClient (3).
*
* RETURN: .t. if successful, .f. otherwise
*/
METHOD UpdateBatch() CLASS TXBrowse
   local lSaved := .f.
   if ::nDataType == DATATYPE_ADO .and. ::oRs != nil
      if ::oRs:LockType == 4 // adLockBatchOptimistic
         TRY
            ::oRs:UpdateBatch( 2 ) // adAffectAll
            lSaved := .t.
         CATCH
            ::oRs:CancelBatch( 2 ) // adAffectAll
            FW_ShowAdoError( ::oRs:ActiveConnection )
         END
      else
         MsgInfo( "UpdateBatch() requires LockType adLockBatchOptimistic" )
      endif
   endif
return lSaved
And here is the code for the CancelBatch() method:

/*----------------------------------------------------------------------------//
* METHOD CancelBatch() CLASS TXBrowse
*
* Cancels all pending changes in a batch update
* Requires the ADO recordset to be opened with LockType = adLockBatchOptimistic (4)
*
* RETURN: nil
*/
METHOD CancelBatch() CLASS TXBrowse
   if ::nDataType == DATATYPE_ADO .and. ::oRs != nil
      if ::oRs:LockType == 4 // adLockBatchOptimistic
         TRY
            ::oRs:CancelBatch( 2 ) // adAffectAll
         CATCH
            FW_ShowAdoError( ::oRs:ActiveConnection )
         END
      else
         MsgInfo( "CancelBatch() requires LockType adLockBatchOptimistic" )
      endif
   endif
return nil

[shri_fwh]

Dear Antonio ,

Basically I am looking Methods in DataRow as its has own array data. As currently I am using below method to save single records.

Code: Select all | Expand

  oAcctRs3  := oApp:oDBConn:RowSet( T_VACCT3_FIND_BY_ID(1) )   
  
  oAcctRow := TDataRow():New( oAcctRs3 ) 

  // CHANGE SOME VALUES BY USING GET 
  oAcctRow:bank_name  := 'ABC BANK' 

 // WRITE DATA TO MARIADB 
  oAcctRow:save()  

The similar way I am looking.

Thanks
Shridhar

[shri_fwh]

Dear Antonio ,

Changing the Subject Line as It should be with Only DataRow.

Thanks
Shridhar

[Antonio Linares]

I would still strongly recommend placing this logic in the TXBrowse class for a few key reasons:

Direct Access to the Recordset: The TXBrowse class holds the direct reference to the ADO recordset object (oRs). The UpdateBatch() and CancelBatch() functions are native methods of this ADO recordset. My proposed implementation in TXBrowse is simply a clean, direct wrapper that exposes this existing ADO capability.

Class Responsibility: The TXBrowse class is responsible for managing the entire collection of rows displayed in the grid. A batch operation, which acts on this entire collection of modified rows, fits naturally within the scope of TXBrowse. Conversely, the TDataRow class is designed to represent and handle a single row of data, and its Save() method is built for single-record transactions.

Implementation Complexity: To implement this in TDataRow, we would need to create a new mechanism to gather all the modified TDataRow rows, which would be complex and less efficient. The current approach leverages the fact that XBrowse (when used with a client-side cursor) already tracks all the changes in its local ADO cache. My proposed UpdateBatch() method simply tells ADO to send all those cached changes to the database at once.

Given these points, the most robust, efficient, and maintainable solution is to add the UpdateBatch() and CancelBatch() methods to TXBrowse.

[shri_fwh]

Dear Antonio ,

Sorry for the delay in the response.

I am using MariaDB RowSet (not ADO).

TXBrowse to edit invoice detail records, while the master invoice record is handled using a TDataRow. However, using both in combination might cause issues.

My goal is to use a TDataRow array to manage multiple invoice detail records and save them directly to MariaDB, just like a single TDataRow is saved.

I believe this would be a powerful feature—similar to how Hibernate ORM handles object-relational mapping in Java.

Thanks
Shridhar

[vilian]

Hi Shridhar,

I think you can easily do this using TARRAYDATA.

[Antonio Linares]

You're asking about using TArrayData to manage multiple invoice detail records for MariaDB, similar to how TDataRow handles single records. This would create an ORM-like batch editing capability where you can edit multiple detail records in memory and save them all at once to MariaDB.

Yes, TArrayData is exactly the right class for this use case. tarrdata.prg:42 The class is designed to handle arrays of data with full CRUD operations and can work directly with MariaDB connections for batch operations.

How TArrayData Works with MariaDB
TArrayData supports MariaDB through its constructor that accepts a MariaDB connection: tarrdata.prg:265-266

The class can be instantiated in several ways for MariaDB:

New( oCn, cTable, [cWhere] ) - Load records from a MariaDB table tarrdata.prg:247
New( oCn, cSql, [aParams] ) - Load from a complex SQL query tarrdata.prg:248

Key Features for Your Use Case
Batch Editing and Saving: TArrayData provides SaveData() method that saves all modifications, additions, and deletions in a single transaction: tarrdata.prg:223

MariaDB Transaction Support: The SaveToMYSQL() method handles all changes within a transaction, ensuring data consistency: tarrdata.prg:1719-1733

TDataRow Integration: You can create TDataRow objects from TArrayData for individual record editing: tarrdata.prg:214-217

Example Implementation
Here's how you would implement this for invoice details:

Code: Select all | Expand

// Load invoice detail records  
oInvDetails := TArrayData():New( oCn, "invoice_details", "invoice_id = " + Str( nInvoiceId ) )  
  
// Enable auto-append for easy adding of new detail lines  
oInvDetails:lAutoAppend := .t.  
  
// Set up XBrowse for editing  
oBrw := TXBrowse():New( oDlg )  
oInvDetails:SetXBrowse( oBrw )  
  
// Edit individual records using TDataRow  
oDetailRec := oInvDetails:Record( "item_code,description,qty,price" )  
oDetailRec:Edit()  
  
// Save all changes (edits, additions, deletions) in one transaction  
if oInvDetails:SaveData()  
   MsgInfo( "All changes saved successfully" )  
else  
   MsgAlert( "Error saving changes" )  
endif

Integration with Master Record
You can use this alongside your master invoice TDataRow:

Code: Select all | Expand

// Master invoice record  
oInvoice := TDataRow():New( oCn:RowSet( "invoices", "id = " + Str( nInvoiceId ) ) )  
  
// Detail records array  
oInvDetails := TArrayData():New( oCn, "invoice_details", "invoice_id = " + Str( nInvoiceId ) )  
  
// Edit master  
oInvoice:Edit()  
  
// Edit details in browse  
// ... user edits in XBrowse ...  
  
// Save both  
if oInvoice:Save() .and. oInvDetails:SaveData()  
   MsgInfo( "Invoice saved completely" )  
endif

Advanced Features
Primary Key Management: TArrayData automatically handles primary keys for MariaDB tables: tarrdata.prg:1725-1728

Field Defaults: You can set default values for new records: tarrdata.prg:52

Change Tracking: The class tracks all modifications and provides lModified property: tarrdata.prg:81-83

Notes
This approach provides exactly the ORM-like functionality you're looking for. The TArrayData class was specifically enhanced in recent versions to support this kind of batch editing with MariaDB, as documented in the changelog: whatsnew.txt:3660-3670 The class handles transactions automatically, ensuring data integrity when saving multiple related records.

[Antonio Linares]

graph TB %% Clusters subgraph subGraph4["Array Data Source"] TArrayData["TArrayData"] ArrayMethods["SetData()<br>Sort()<br>Filter()<br>Append()"] end subgraph subGraph3["ADO Data Source"] TRecSet["TRecSet"] ADOMethods["Open()<br>Find()<br>Filter()<br>Sort()"] end subgraph subGraph2["MySQL Data Source"] FWMariaConnection["FWMariaConnection"] FRecSet["FRecSet"] MySQLMethods["Execute()<br>RowSet()<br>CreateTable()"] end subgraph subGraph1["DBF Data Source"] TDatabase["TDatabase"] DBFMethods["Use()<br>SetFilter()<br>SetOrder()<br>Lock()/UnLock()"] end subgraph subGraph0["Common Interface Methods"] Common["RecCount()<br>RecNo()<br>FieldGet()<br>FieldPut()<br>GoTo()<br>Skip()<br>Bof()/Eof()"] end %% Edges reflecting the SVG connections TArrayData --> ArrayMethods TArrayData --> Common TRecSet --> ADOMethods TRecSet --> Common FRecSet --> Common FWMariaConnection --> FRecSet FWMariaConnection --> MySQLMethods FWMariaConnection --> Common TDatabase --> DBFMethods TDatabase --> Common

[shri_fwh]

Dear Antonio,

Many Many Thanks for your detailed reply. I will try this and update you.

Thanks
Shridhar

[shri_fwh]

Dear Antonio ,

I tried below code and its working. I believe this is extremely powerful, as the modified data is populated only in base table ( FROM vtran ).

Code: Select all | Expand

LOCAL cSQL := "SELECT ac.ACCT_NAME , t.* " + ; 
                 " FROM vtran t " + ;
                 " INNER JOIN vacct ac ON ac.ACCT_ID = t.ACCT_ID " + ;
                 " WHERE tran_id = 20 "

   aTxnDRows := TArrayData():New( oApp:oDBConn, cSQL) // "SELECT * FROM vt03_2022 WHERE tran_id = 20" ) 
   
   XBROWSER aTxnDRows FASTEDIT
   
   aTxnDRows:SaveData()

Thanks
Shridhar