Hi Michael!

I thought Lesters advice seems pretty nice, and Helen's paper on
replication may be exactly what you want, but here's another suggestion for
you as well:

For the web server (DB2), use four separate DSQLs and add one field called
UpdatedByMichael or something:

The four DSQL should be:
1) Your UPDATE SQL but in addition setting UpdatedByMichael to 1
2) Your INSERT SQL but in addition setting UpdatedByMichael to 1
3) One Delete SQL: DELETE FROM <MichaelsTable> where UpdatedByMichael = 0
4) One UPDATE SQL: UPDATE <MichaelsTable> SET UpdatedByMichael = 0

Use a TIB_Cursor for your local server, step through all records calling
your update sql, and if it fails due to the record already existing run
your insert sql. Once finished run your dsql 3 and 4 and Commit.

Of course, make sure to prepare all statements before your loop, keep
everything within one transaction and use BeginBusy/EndBusy as usual.

Set

At 10:54 11.02.2001 -0500, you wrote:

>Thanks all,
>
>Here are the Details of what I am trying to do:
>
>1. DB1 is on the local server and is the production DB.
>2. DB2 is on a Web server and the data in it doesn't get
> edited but does have to be there.
>3. Once a day we want to make the web data match the local data.
>4. Since the records are just copied the PK of each should match.
>5. Since the web data is in use I cannot issue a "delete from file",
> Well maybe I could since under transaction control it wouldn't
> show up until I finished my update and committed (interesting).
>6. About 20 files with between 3 & 2000 records each so for the
> longest it takes about 5 min to transmit across the internet
> to the web server.
>
>What I was looking at with the select and the InsertSQL, EditSQL,
>DeleteSQL properties was that I could have all my sql in one
>control without needing 4 separate controls, but maybe the
>TIB_Cursor is not setup to use those properties separately, it
>may be designed to only use them in an interactive environment
>where special handling is needed.
>
>What I was thinking of doing:
>
>1. Select PKs from the web, Select all from the Local.
>2. If PKs match then update the web record
>3. If Local.PK < Web.PK then insert record
>4. If Local.PK > Web.PK then delete the missing web record
>5. Handle the end of file stuff.
>6. Done.
>
>But after some more thought, and considering Transactions,
>maybe I should do this.
>
>1. StartTransaction
>2. Delete from file
>3. Insert all records
>4. Commit.
>
>NO NO NO NO NO that won't work after all. Some of the files
>in the web DB that are not updated have "foreign keys" based
>on the data in the files being updated. So I cannot just
>delete all the records, I will look into dropping those
>foreign keys, maybe they are not necessary. But that takes
>it back to something like the first option.
>
>So, I can use separate DSQLs I just thought I could use the
>Cursor to stick it all together.
>
>Comments, Thoughts, Questions, Ideals WELCOME!
>
>I would still like more details on how to use the InsertSQL,
>EditSQL, DeleteSQL?
>
>Thanks
>Michael L. Horne
>
>
>
>
>
>
>
>