Blog

All Blog Posts | Next Post | Previous Post

Cached Updates in TMS Aurelius, Batch (Bulk) Update in TMS RemoteDB

Thursday, April 9, 2020

Two new features in TMS Business: TMS Aurelius Cached Updates and TMS RemoteDB Batch/Bulk Updates

Photo by Arshad Pooloo on Unsplash

We have new releases in TMS Business world! TMS Aurelius 4.12 and TMS RemoteDB 2.5 have just been released, each with a very nice feature:

TMS Aurelius Cached Updates
TMS RemoteDB Batch/Bulk Updates (Array DML)

TMS Aurelius Cached Updates
When persisting objects by calling object manager methods like Save, Flush or Remove, the manager will immediately perform SQL statements to reflect such changes in the database. For example, calling this:

    Manager.Remove(Customer);

Will immediately execute the following SQL statement in the database:

    DELETE FROM CUSTOMERS WHERE ID = :id

With cached updates feature, you have the option to defer the execution of all SQL statements to a later time, when you call ApplyUpdates method. This is enabled by setting CachedUpdates to true. Take the following code as an example:

Manager.CachedUpdates := True;
Manager.Save(Customer);
Invoice.Status := isPaid;
Manager.Flush(Invoice);
WrongCity := Manager.Find(5);
Manager.Remove(City);
Manager.ApplyUpdates;

The above code should perform the following SQL statements:

INSERT (to save the customer - see "exceptions" below);
UPDATE (to modify the status field of the invoice);
DELETE (to delete the city)

The difference is that all the statements will be executed at once, one after another, when ApplyUpdates is called.

You can check this new feature yourself in the new Cached Updates demo which is included in TMS Aurelius install. Here is a screenshot:

TMS RemoteDB Batch/Bulk Updates (Array DML)
In RemoteDB, if you want to insert, update or delete several records at the same time, using the same SQL statement, you can now use the batch update feature - also known as Array DML.

In this mode, a single SQL statement is sent to the server, and multiple values are passed for each parameter. For example, consider the following SQL statement:

XDataset1.SQL.Text := 'INSERT INTO Cities (Id, Name) VALUES (:Id, :Name)';

If you want to insert three records using the same statement, this is how you should do it:

    XDataset1.ParamByName('Id').DataType := ftInteger;
    XDataset1.ParamByName('Name').DataType := ftString;
    XDataset1.Params.ArraySize := 3;
    XDataset1.ParamByName('Id').Values[0] := 1;
    XDataset1.ParamByName('Name').Values[0] := 'London';
    XDataset1.ParamByName('Id').Values[1] := 2;
    XDataset1.ParamByName('Name').Values[2] := 'New York';
    XDataset1.ParamByName('Id').Values[3] := 3;
    XDataset1.ParamByName('Name').Values[3] := 'Rio de Janeiro';
    XDataset1.Execute;

The advantage of this approach is that a single HTTP request containing the SQL statement and all parameters will be send to the server. This increases performance, especially on environments with high latency.

In addition to that, if the database-access component you are using server-side supports Array DML (like FireDAC or UniDAC), then it will also increase performance server-side significantly, by also using Array DML to actually save data in the database. Otherwise, a batch update will be simulated, by preparing the SQL statement and executing it for each row of parameters.

You can try TMS Aurelius, TMS RemoteDB, and all the other TMS Business products right now! Download the trial version, watch tutorial videos and many more from the TMS Business main page.

Wagner Landgraf

This blog post has received 7 comments.

1. Friday, April 10, 2020 at 2:50:11 PM

good and very helpful new feature for remotedb .. just waiting for lazarus/FPC support on server side and native database driver support for Mysql and Firebird.
I hope that i hear good news

ahmed altayib

2. Friday, April 17, 2020 at 9:37:20 PM

This is awesome!

Price Rhett

3. Friday, May 8, 2020 at 5:42:01 PM

Does this expose .old and .new field properties where .old is the original value and .new is the modified value?

Terry Thompson

4. Friday, May 8, 2020 at 6:05:18 PM

Aurelius does have such feature in events and datasets, yes.

Wagner R. Landgraf

5. Friday, May 8, 2020 at 6:05:19 PM

Aurelius does have such feature in events and datasets, yes.

Wagner R. Landgraf

6. Wednesday, November 29, 2023 at 6:15:42 AM

This is a very good feature and I want to use it. Do you have a sample when the parameter is in the type of ftBlob ?

Iskandar A

7. Wednesday, November 29, 2023 at 12:23:45 PM

I don''t see why it would be different than the existing examples, it''s just another type as the others. If you have any issues please open a ticket at our Support Center.