Sync vfp data to sql server

[naeempk]

Hi,

I have a vfp7 multi user app which uses vfp free table. These tables have no timestamp like field. I need to update Sql server database from these vfp tables ( in realtime if possible ). How I get "Change data capture" from vfp tables and update my Sql server database?

TIA
Naeem

 

[Olaf Doschke]

No way.
VFP freee tables don't cause any triggers and even don't take part in transactions. Besides VFP has no transaction log.
So you have to introduce a timestamp, if not in the free tables (because you can't touch any exisitng code), then in a 1:1 related table. But then, how do inserts, updates, and deletes of any source get in there?
No way.

With free tables you're out of luck.

Bye, Olaf.

 

[JRB-Bldr]

There are a lot of previous postings on this forum for how to Update SQL Server data tables.
Do a forum search for: update sql server

In general, there are 2 different methods which can be used.
1. SQL Pass-thru commands

If you use SQL Pass-thru you would use a Connection String into the SQL Server database and then use SQLEXEC() to issue T-SQL SQL Commands to update the tables.​

2. An UPDATEABLE VFP Remote View into the SQL Server data table(s)

If you use UPDATEABLE VFP Remote View(s) you would handle them like a VFP data table.​

Generally it would not be TRUE 'RealTime' updates, but sequential - Update one table, then update the other.
But the time differential would be negligible.

How I get "Change data capture" from vfp tables and update my Sql server database?
I am not sure what you mean by this.

Good Luck,
JRB-Bldr

 

[Mike Lewis]

Hello Naeem, and welcome to the forum.

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?'. What exactly do you want to achieve?

Do you want to record the date that the SQL Server table is updated? 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).

But I don't want to give any more details, in case I have misunderstood what you want to do.

It would also help if you can tell us how much you already know about using VFP to update SQL Server.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[naeempk]

Hi,

Thanks all. I want to update my sql server database from my vfp free tables which are updated by my app users. Initially, I will use sql server database for my app reports and gradually I port my vfp data to sql server.

"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.

I have vfp app code but no data access classes or framework is used. I can use vfp sql commands/functions.

Thanks
Naeem

 

[Olaf Doschke]

>I have vfp app code but no data access classes or framework is used. I can use vfp sql commands/functions

That's good ground.

I thought you were having an inherited legacy application with free tables and no source code and try to sync changes to SQL Server.

If you are the owner of this the best thing surely is to add a timestamp field to all tables and make it a filter for all records needing to sync. Instead of a timestamp you could even keep it simpler and have a logical field "changed". So

1. New records will have this set .T. when inserting,
2. Existing records will get this set .T. when updating
3. Deleted records will get this set .T. before deleting them.

Since you won't pack (if you do, you'd move that operation into syncing) you can also read this field from deleted records and repeat any changes done on dbfs to the SQL Server.

I don't see any good way in not adding any field to decide what needs syncing.

The syncing code then will scan every dbf for changed records, and then compare these with SQL tables, of course. Again there are the three major cases:

1. 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 .F. in the DBF.
2. If the primary key does exist in MSSQL, it is an updated record, so move it to MSSQL as update, set changed .F. in the DBF.
3. If the DBF record is changed=.T. and DELETED()=.T., then delete the record in MSSQL, set changed .F. in the DBF.

That's all, isn't it?

With VFP9 you could use binary indexes even on Free tables and therefore will find all changed records fast. You obviously have to make changes in correct order to maintain referential integrity, insert operations need to be replayed in the opposite order of delete operations, so you'll have multi passes, one for inserts/updates and one for deletes.

Bye, Olaf.

 

[Olaf Doschke]

One last pass for packing (if you need to do this at all): After you deleted MSSQL records and changed them in DBFs to changed=.F. make double sure there are no newly deleted records with changed=.T. not synced. If you check this while having exclusive DBF access you can be sure there also will be no records deleted during that check. Then you can PACK.

Indeed PACK can also be delayed to after working hours.

All this syncing can be done in an extra process, it isn't needed right away, it can run each minute, hour, day. The more often you sync the less records will be marked, there will be a best trade between the base time needed to open scan and close all tables and the time needed for many accumulated changes. Once you know a typical sweep time you can sync more frequent, which should then even lower the sweep time, as there is less to do when sync intervals get shorter.

Bye, Olaf.

 

[naeempk]

Hi,

Thanks Olaf for detailed reply. I will try this.

I once used single vfp table via spt (2 to 3k records) to oracle database and it took a long time because it processed the table record by record.

1) Can I update records in a bulk/batch? Should I use CA / remote views ?

2) Should I use multi threading to speed it up or use multiple connections to sql server for different set of tables ?

3) If I have limited concurrent connections limit (RDBMS other than sql server), how can I use connections efficiently ?

Thanks
Naeem

 

[Olaf Doschke]

Even if your database is large, you only process changed records of the last day, hour, minute, whatever interval. For short intervals that really may be a few records only, no need for bulk operations.

One thing you could do is select all records where changed=.f. and upload them into one or two staging tables per operative table in oracle and do further processing there. a Staging table is starting empty for each sync sweep, you insert all changed records, then process them and end with an empty staging table. The staging table for simplicity has the same structure and no indexes but on ID. 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.

To update a bulk of records, you can store them as CSV and bulk insert (I assume Oracle can do bulk inserts as T-SQL) or you create a updatable spt cursor for the staging table, Append from some.DBF for changed=.t. AND !DELETED() and a secondary staging table FOR DELETED() and finally Tableupdate(), that's also kind of a bulk insert.

With the normal staging table inside oracle you can do an INSERT INTO Table SELECT * From StagingTable WHERE StagingTable.ID NOT IN (SELECT ID FROM Table) for inserts and UPDATE Table FROM StagingTable1 where Table.id=StagingTable1.id for updates. Then DELETE FROM Table WHERE Table.ID IN (SELECT ID FROM StagingTable2). StagingTable2 in contrast to StagingTable1 only needs and has the IDs of records to be deleted. Then you don't need to get forth and back for each record between DBF and Oracle.

Bye, Olaf.

 

[naeempk]

Hi,

Thanks Olaf.

 

[Olaf Doschke]

Just one thing, that would make it a little faster: Do the UPDATE before the INSERT. Otherwise you'll update just inserted records with the same data they already have. Doing an UPDATE with the staging table having new records not found by ID=ID where clause doesn't hurt, on the other side.

And remember the thing about the hierarchical order of insert/updates vs deletes. That's another reason two staging tables per operative table make sense.

Bye, Olaf.

 

[Olaf Doschke]

>how can I use connections efficiently ?
In SQLStringConnect you specify whether a connection is sharable or not.

Bye, Olaf.

 

[Olaf Doschke]

...and then use the same connection with SQLCONNECT(handle), where handle is the handle you got from SQLStringConnect(constring,.T.) )SQLStringConnect itself always makes a new connection). I don't know if this only works in the same process or also in other processes.

Bye, Olaf.

 

[naeempk]

Hi,

Thanks Olaf.

1) I have logical field rec_Status in vfp table and rec_status number(1) column in oracle table. For spt, I convert (map) logical to numeric and vice versa in my DML query. Can this be automated through CA "ConversionFunc" property or through Remote view ?

Thanks
Naeem

 

[Olaf Doschke]

Why would you have such a field in the Server at all? It only exists to mark the records you want to sync. And as far as I understood DBFs are master data and the Server is the slave needing to be updated.

Even if you had such a field in the server, as you only stage records with rec_Status=.t., all records in staging tables will have 1 in that field. That's no info you need. All records in the staging table1 are either new or updated and all IDs in staging table2 need to be deleted.

Bye, Olaf.

 

[naeempk]

=================
> 1) I have logical field rec_Status in vfp table and rec_status number(1) column in oracle table. For spt, I convert (map) logical to numeric and vice versa in my DML query. Can this be automated through CA "ConversionFunc" property or through Remote view ?
=================

Should I post this as a new thread. As I just want to know how other vfp programmers handle different data type mappings (like logical fields, dates, varchar etc) between source and target databases.

Thanks
Naeem

 

[Olaf Doschke]

Field type mappings are really a separate topic. Yet rather small. See the table in

http://msdn.microsoft.com/en-us/library/czskh2h3(VS.80).aspx

It's just a bit outdated, as you wouldn't use image field type of MSSQL and some other additional MSSQL und VFP types exist.

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.

Bye, Olaf.

 

[Olaf Doschke]

I thought a bit about the staging and whether a simple flag about changed records really is a sufficient idea. Such a flag is like the archive attribute of files. For a backup utility doing an incremental backup this attribute is sufficient to decide this file has changed and therefore needs to be backed up. 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) during the backup (or sync). So you could lock the file (record). 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). 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. I copy this record with increment=3 into the staging area and can later reset it to 0, then further updates can increment it again. So it's like 0 is no change and any number >0 means the count of changes. 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. 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. It allows to let a snapshot take a while and not lock anything, so syncing can be done in normal, shared mode without risking to skip and forget any update.

Bye, Olaf.

 

[naeempk]

Hi Olaf,

I thought to add two fields timestamp and flag in my tables but now I will try this increment field idea.

Thanks
Naeem

 

[Olaf Doschke]

A timestamp isn't bad in itself. Eg in a VFP app writing to DBFs via Tableupdate Especially since you can define Tableupdate to detect conflicts with WhereType 4 (DB_KEYANDTIMESTAMP). Instead of finding out with key and all modified or updatable fields, if a change happend while the current change was done, VFP then only checks timestamp, if it's higher than the one loaded. Also a timestamp will at least give the time of the last update, which in itself can sometimes be very useful. This stays useful, once you combine VFP frontend and server backend and it even stays useful, when working on server data with .NET Entity framework.

I thought about a version field never being decremented, but then you'd need to compare the version of all records with the sync target database, no good idea, you need increments since last sync.

Bye, Olaf.