What is the difference between Post, ApplyUpdates and Commit for databases?

Question

I am trying to figure out which commands I want to use after modifying the database. I am learning SQLite3 and db controls, and here is my understanding ...

When a user enters something into a control that supports db (or otherwise puts the data set into memory in the edit state), POST save the change in memory. Controls will often do this automatically or implicitly for you.

Although you must post messages before any changes are recognized anywhere, the changes were not sent to the actual database file on disk. They are only in memory. APPLYUPDATES is required to send changes to the disk.

Even after sending to a file on disk via APPLYUDATES they can still be changed or canceled. It's like a Undo kick. They are not saved to disk until COMMIT is called.

Does this sound right? I would really like to know what I'm doing, so I'm not just copying and pasting the code. But please feel free to copy, paste and edit my attempt in response.

Answer 1

The answer to your question: Post, ApplyUpdates and Commit do completely different things and are usually found in different places (processes) and contexts in the database application.

Post and ApplyUpdates are truly client-side operations, while Commit is an SQL operation that may (or may not) have to be explicitly called on the server side to complete the transaction.

The easiest way to understand the differences is if you are considering a three-tier server. SQLite is a little strange because it is not a true Sql server of the type that is designed to respond to calls from different processes on different computers (although it can do this as a background server of a 3-tier system.

In the simplest traditional three-tier layout, there is a mid-level Delphi server located between the Sql server, for example, MS Sql Server, and the client level, usually this is your Delphi program running on the client machine. The traditional Borland / EMBA technology for its implementation is DataSnap .

Typically, the client tier contains a TClientDataSet (or third-party equivalent) that retrieves data from the internal SQL Server through a descendant of TDataSet descendants on a mid-tier server. Although getting data from the Sql server to the middle tier is usually associated with a transaction on the Sql server, once the data is uploaded to the CDS at the client level, there are no transactions on SQL Server (unless you go out of your way to keep the transaction open on the server, which is not friendly for other users of the server and consumes blocking resources on the server, which are finite).

When editing data in CDS (or any TDataset descendant, in fact), which puts the data set in dsEdit state (see the online help for TDataSetState). The changes made are preliminary, which means that they can be canceled in CDS until you call .Post, which saves them in CDS data (in the case of TClientDataSet, changes to client data can be undone after calling. Post, until was called .ApplyUpdates). Remember that there are no transactions on the Sql server (or at least there shouldn’t be any) when .Post is invoked on CDS at the client level.

Calling .Post does not cause changes to be sent back to the intermediate mid-tier dataset. To initiate this, you invoke ApplyUpdates on the client-level CDS, which ripples to the TDataSetProvider in the middle tier, which interacts with the CDS with a dataset related to the server with the middle tier. This is a DataSetProvider (or rather, the associated TSqlResolver) that generates SQL, which is actually sent to the SQL server to apply the changes to the SQL database. So, in the standard 3-level DataSnap setup, you have no direct control over the commit call or not.

Commit is an SQL operation performed by the Sql server as one of two possible ways to complete a transaction (the other is Rollback ). With MS Sql, fi server, the connection to the server can be configured to automatically wrap the received UPDATE , INSERT and DELETE in implicit transactions.

The extent to which you need to control the transaction depends on the server server used and the requirements for your application in terms of concurrency with other uses of the server data. If you are interested in processing SLite transactions, refer to the documents for the database components used or their source code.

Some Delphi component libraries for working with true SQL servers support exposures for managing server-side transactions, for example. IBX for Interbase.

Btw, in Delphi terms, CachedUpdates is a hang from the long-obsolete BDE, which was Borland's first attempt at a common database access structure for various server servers. It is preserved in some implementations of TDataSet descendants and (unfortunately imo) did something like a return to FireDAC, the latest cross-byte release of EMBA.

Answer 2

when you use ApplyUpdates, you must set the CachedUpdates property to True. then you can use the message, delete (and the rest) to change your data, these changes will be cached first. When you call ApplyUpdates, all changes you make are saved to the database. With CancelUpdates you can undo all changes you have made. When you set the CachedUpdates property to false, then all changes you make will be directly stored in the database. The ApplyUpdate and CancelUpdates commands may not be used.