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

Question

I am struggling to figure out the commands I want to use after changing a database. I'm learning via SQLite3 and db-aware controls, and here's my understanding ...

When a user types something in a db-aware control (or otherwise puts an in memory dataset into Edit state), POST will store the change in memory. Controls will often automatically, or implicitly, do this for you.

Although you have to post before any changes are recognized anywhere, the changes have not been sent to the actual database file on disk. They're only in memory. Sending the changes to the disk requires APPLYUPDATES.

Even after being sent to the file on disk via APPLYUDATES they can still be changed, or rolled back. It's like hitting Undo. They are not permanently saved to disk until COMMIT has been invoked.

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

Answer 1

The answer to your question is that Post, ApplyUpdates and Commit do entirely different things and typically occur in different places (processes) and contexts in a database app.

Post and ApplyUpdates are both really client-side operations, whereas Commit is a SQL operation that may (or not) need to be explicitly called on the server side to complete a transaction.

It's easiest to understand the differences if you consider a three-tier server. SQLite is a bit of an oddball, because it's not a true Sql Server of the sort that's designed to respond to calls from different processes on different machines (though it can do that as the back-end of a 3-tier system_.

About the simplest traditional 3-tier arrangement has a middle-tier Delphi server that sits between the Sql Server, say an MS Sql Server, and the client-tier, typically your Delphi program running on the client machine. Borland/EMBA's traditional technology to implement this is DataSnap.

The client tier usually contains a TClientDataSet (or 3rd-party equivalent) that receives data from a back-end SQL Server via a server-specific TDataSet descendant in the middle tier. Although getting the data from the Sql Server to the middle tier usually involves a transaction on the Sql Server, once the data is all loaded into the CDS in the client tier, there is no transaction pending on the SQL Server (unless you go out of your way to keep a transaction open on the server, which is not friendly to other users of the server and consumes lock resources on the server, which are finite).

When you edit data in the CDS (or any TDataset descendant, actually), that puts the dataset into dsEdit state (see online help for TDataSetState). The changes made are provisional, meaning they can be undone in the CDS until you call .Post, which saves them to the CDS's Data (in the case of a TClientDataSet, changes to the client-side data can be rolled back event after calling .Post, as long as .ApplyUpdates hasn't been called). Remember that there is no transaction pending on the Sql Server (or at least, there shouldn't be) when .Post is called on a CDS in the client tier.

Calling .Post does not cause the changes to be propagated back to the counterpart middle-tier dataset. To initiate that, you call ApplyUpdates on the client-tier CDS, which ripples through to a TDataSetProvider in the middle tier which interfaces the CDS with the middle-tier's server-facing dataset. It's the DataSetProvider (or, more accurately a TSqlResolver associated with it) which generates the SQL which is actually sent to the SQL server to apply the changes to the SQL database. So, in a standard DataSnap 3-tier set-up, you don't have any direct control over whether Commit is called or not.

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

The extent to which you need to concern yourself with transaction control depends on the back-end server you're using and your app's requirements in terms of concurrency with other use of the server data. If you're interested in SLite's handling of transactions, consult the docs for the DBcomponents you're using or their source cide.

Some Delphi component libraries for working with true SQL Servers do support expose facilities for controlling server-side transactions, e.g. the IBX ones for Interbase.

Btw, In Delphi terms, CachedUpdates is a hang-over from the long-obsolete BDE, which was Borland's first attempt at common DB-access framework for a variety of back-end servers. It persists in some TDataSet-descendant implementations and (regrettably, imo) has made something of a comeback in FireDAC, EMBA's latest cross-database offering.