I want to update my sql server database from my vfp free tables which are updated by my app users. The syncing code then will scan every dbf for changed records, and then compare these with SQL tables, of course. If the primary key doesn't exist in MSSQL, it is a new record, it doesn't matter how many updates were already following the insert, you can insert it into sql server as is and at that moment set the changed field . Otherwise you'll update just inserted records with the same data they already have.
Initially, I will use sql server database for my app reports and gradually I port my vfp data to sql server. Doing an UPDATE with the staging table having new records not found by ID=ID where clause doesn't hurt, on the other side.
"Change data capture" (may be not a correct term in this context), I want to update data changes to sql server from my last update process. And remember the thing about the hierarchical order of insert/updates vs deletes.
I have vfp app code but no data access classes or framework is used. Thanks Naeem I have vfp app code but no data access classes or framework is used. That's another reason two staging tables per operative table make sense. ..then use the same connection with SQLCONNECT(handle), where handle is the handle you got from SQLString Connect(constring,.
How about using a numeric field called "increment", all operations (insert, update, delete) will increment this field.
Say since last sync a record was inserted and updated twice, it will then have an increment value of 3.
So it's like 0 is no change and any number 0 means the count of changes.The backup software has the same problem deciding when to reset that flag, it doesn't matter much, if you reset this flag before the backup (or sync) or afterwards - both moments have their pros and cons - in both cases you will not want any modifications of the file (or record) the backup (or sync). But that may block any software working with the file (or record), which is bad.I rather not want to interrupt any software working on the files or data, so I thought about how it could be acceptable software continues to work while I do a snapshot (in both backup or sync case you could call the fetching of changed data a snapshot).Actually it's not of much interest how many changes there were, we just take the current state anyway and only do one update in the sync target, but now comes the benefit: If we don't simply reset the increment to 0 but decrement the field by the increment we copied to the staging data, most probably the result will be 0, but if there was any further change of the data during syncing, the increment will be decremented to some value 0, which simply is the number of changes after the snapshot, so that change will go into the next sync and will not be forgotten.So this is better than both a time stamp and a flag.But then, how do inserts, updates, and deletes of any source get in there? I think you need to explain what you mean by ' How I get "Change data capture" from vfp tables and update my Sql server database? If so, you can add a timestamp (a datetime field) to the SQL Server table, and then either add the current datetime to your INSERT command (or the remote view, if applicable), or create a trigger in the SQL Server database that updates that field on an INSERT (and/or an UPDATE if appropriate). The staging table for simplicity has the same structure and no indexes but on ID.But I don't want to give any more details, in case I have misunderstood what you want to do. Since you have no flag for deleted records at least two staging tables are needed, one separate for the IDs to delete, which only needs the IDs of records due to be purged. DELETED() and a secondary staging table FOR DELETED() and finally Tableupdate(), that's also kind of a bulk insert.A time stamp would also allow you to see which updates where after the last snapshot, but you'd need to maintain a snapshot time stamp to compare with and the increment number already is indicating how many versions it's ahead of the sync target database.I hope you haven't begun to change all your application code to set a flag only, you could now increment an integer field instead to have this benefit. Eg in a VFP app writing to DBFs via Tableupdate Especially since you can define Tableupdate to detect conflicts with Where Type 4 (DB_KEYANDTIMESTAMP).Thanks Naeem Field type mappings are really a separate topic. You may go about mapping this way: Create a test table in the server with all types.Query SELECT * FROM test via SQLEXEC and see how VFP maps them to VFP types. I thought a bit about the staging and whether a simple flag about changed records really is a sufficient idea.