Cached Update to ADO Migration Question

[Quailin]

I'm migrating several applications using BDE that use CachedUpdates to
update several datasets within a single transaction. This is done to not
only to protect the updates within a dataset, but to insure several datasets
updates are made correctly (TDataSet CachedUpdates, rather than
TQuery/TStoredProc).

An example would be to get customers/orders/items/commissions/inventory/etc
all updated in a transaction. Master/detail and asynchronous detail all
commit or fail together.

I understand how to replace CachedUpdates very effectively with
TClientDataset. However, since we will be using ADO (MS-SQL Server 7) I was
wondering if I could find a replacement there. My thinking is that first
I'll migrate to ADO; then, if our environment would benefit by 3-tier over
the current 2-tier approach, move it into Midas.

Fedrov and Elmanova, Advanced Delphi Developer's Guide to ADO, have one
brief comment in Appendix A "To use cached updates, it is possible to use
TADODataSet component with the LockType property equal to ltBachtOptimistic,
and use the UpdateBatch method to update multiple records in a single
operation."

Not yet deep enough into ADO to understand how this could be used with
several datasets to form a transaction. Nor do I see any "Ah-ha, this is
it!" in that book or the D5 Developer's Guide.

Does anyone know of other literature, examples, code samples, whatever that
would help me get started in the right direction.

Thanks in Advance,
Quailin

BTW: Fedorov and Elmoanova looks to be an _excellent_ guide to using ADO.
I've just got it and starting, but am confident it will be a reference as
well as tutor. Also, the D5 Demos\ADO\ADOTest is an amazing compilation of
a whole bunch of ADO ideas as well as a lot of interesting ideas for data
access and presentation.

[alzaimar]

I've used a book by Andreas Kosch, which is in german ('ADO und Delphi'). It explaines very well how to use the ltBatchOptimistic Mode. Of course it works.

However, our preferred method is to completely ignore these features and build our own SQL statements in order to batch update or whatever. One big mistake in ADO is that it is not able to produce 100% correct updates which only take a key field as a filter condition:

Suppose you have a table 'Foo' with 2 fields 'fID' (your primary key) and 'fBar' (a string or whatever).
If you execute a post against that ADO-table, ADO will generate something like this:

SQL-Code:

Update Foo set fBar='NewValue' where fID = 1 and fBar='OldValue'

The last 'and fBar...' is completely stupid and might even cause the whole statement to crash. We just never found a proper way to do accurate and fast updates that always worked under all conditions. To get even worse, we use 'updatable views' in our SQL-server, which is a very cool feature, but not fully supported by ADO.

Now, we simply generate a script containing all the updates, inserts, linking etc. and execute this using MyAdoConnection.Execute within a transaction. Nothing is faster than that. This gives us the ultimate control on how and what to change, update and delete.

BDE is a pain in the a***, but it's TUpdateSQL component was really something.