Converting to ADO/ ADO newbie

[George Wynne]

Hi all,

I'm considering a conversion from BDE to ADO in D7. I've gleaned from help
and other posts that I'll need to do something like the following:

- replace TDatabase with TADOConnection
- search and replace TQuery with TADOQuery in the dfms
- change ParamData with Parameters in the dfms
- change Params to Parameters, and ApplyUpdates to UpdateBatch(arCurrent) in
the .pas files
- make sure LockType = ltBatchOptimistic where previously CachedUpdates =
true
- somehow replace my TUpdateSQL components with some other updating
mechanism (client dataset?) - I'm pretty fuzzy on this one

So, I need to know what is the easiest way to convert all those TUpdateSQL
components to something else. And let me know if there is anything I'm
missing or misunderstanding in my basic list of things to do.

Thanks,
George

[George Wynne]

Post by George Wynne
So, I need to know what is the easiest way to convert all those TUpdateSQL
components to something else.

You probably don't need the TupdateSQL with ADO.
At least with SQL server and Access most Joins will be directly

updateable.
I'm not so concerned about joins, but I guess I just like to know and to
control what the update statement will look like. I have many queries on
one table that return 10 fields, where I only update 2 or 3 fields and I
only have 1 or 2 fields in the where clause. If I just make a global change
so that all fields are updated and are in the where clause, then I could
have problems. Is there any way that I can keep these update statements?

Thanks,
George

[George Wynne]

Forget about joins, OK?

Let's say that my TQuery.SQL =
select field1, field2, field3, field4, field5, field6, field7
from mytable
where field1 > 10

Let's say that I only let the user update field1 in this process. Field2 is
a unique identifier, and the others are just visible to help the user decide
how to change field1.

So, the update statement in my good old BDE TUpdateSQL is
update mytable
set field1 = :field1
where field2 = :field2

I like this, because:
- I can be sure that my code is not trying to update fields2-7.
- It's easier to debug.
- I don't run into any problems updating field2 if field2 is an identity
column (SQL Server).
- I don't fire any update triggers that may be contingent on updating
fields3-7.
- I can let another process update field3 of the same record roughly
simultaneously, and that will not break this process, because I'm
restricting the where clause to the unique identifier, field2.

So, if it is possible, I would like to keep using these update statements in
ADO.

Thanks,
George

[Brian Bushay TeamB]

Post by George Wynne
So, if it is possible, I would like to keep using these update statements in
ADO.

Ado does not provide for an update component. You could write your own updates
and fire them off from the beforepost method then about the post and resync the
record but ADO isn't going to change the value of any fields the user doesn't
change so my advice is just make them readonly.

--
Brian Bushay (TeamB)
***@NMPLS.com

[George Wynne]

OK. Well, I guess I'm still curious as to how ADO constructs update
statements when I do a normal post.

Let's say I have:
select f1, f2, f3, f4
from atable

I tie this to a grid. f1 is my unique ID, and I've set f1.readonly = true
and f2.readonly = true.

At run-time, say it returns two rows. A user edits f3 on the first row,
edits f3 and f4 on the second row, and then calls UpdateBatch.

So what happens?

Maybe ADO calls...
update atable
set f1 = :newf1, f2 = newf2, f3 = :newf3, f4 = :newf4
where f1 = :oldf1 and f2 = :oldf2 and f3 = :oldf3 and f4 = :oldf4
...for both rows.

Or maybe ADO calls...
update atable
set f3 = :newf3, f4 = :newf4
where f1 = :oldf1 and f2 = :oldf2 and f3 = :oldf3 and f4 = :oldf4
...for both rows.

Or maybe ADO calls...
update atable
set f3 = :newf3
where f1 = :oldf1 and f2 = :oldf2 and f3 = :oldf3 and f4 = :oldf4
...for the first row and ...
update atable
set f3 = :newf3, f4 = :newf4
where f1 = :oldf1 and f2 = :oldf2 and f3 = :oldf3 and f4 = :oldf4
...for the second.

Or maybe something else happens.

I just want to know how it works before I dive in.

Thanks,
George

[George Wynne]

Never mind - I see all this is in TField.ProviderFlags. Well, so much for a
pain-free conversion to ADO.

Thanks,
George

[Brian Bushay TeamB]

Post by George Wynne
Never mind - I see all this is in TField.ProviderFlags. Well, so much for a
pain-free conversion to ADO.

ADO components don't use the provider flags so this isn't going to help you.
The Update code is just sets values for fields that have changed.
The default for finding records in the database is to use the Old value from all
the selected fields to build a where clause
--
Brian Bushay (TeamB)
***@NMPLS.com

[George Wynne]

Post by Brian Bushay TeamB
ADO components don't use the provider flags so this isn't going to help you.
The Update code is just sets values for fields that have changed.
The default for finding records in the database is to use the Old value from all
the selected fields to build a where clause

I meant to say that I can use TField.ProviderFlags if I do updates through a
TClientDataset and TDatasetProvider.

Thanks,
George

[George Wynne]

Post by Brian Bushay TeamB
The default for finding records in the database is to use the Old value from all
the selected fields to build a where clause

OK, now I've tested this with SQL Server 7.0 and SQL Server Profiler.

I've found that if there is a unique index on the table that is being
updated, then the where clause consists of the fields in that index and the
fields that are being updated.

Otherwise, it puts all selected fields in the where clause.

Thanks,
George

[Brian Bushay TeamB]

Post by George Wynne
I've found that if there is a unique index on the table that is being
updated, then the where clause consists of the fields in that index and the
fields that are being updated.
Otherwise, it puts all selected fields in the where clause.

This behavior is configurable with the
Recordset dynamic property "Update Criteria"
--
Brian Bushay (TeamB)
***@NMPLS.com