February 10, 2009 at 6:23 pm
I have over 300 tables in VFP version 9 and/or 10.
There is an interface that is not quite reliable for synchronization and that also has been written in VFP.
Q: is there a tool / third party for one synchronization between vfp to sql.. one way??
Q: is there any way to use CRC 32 or 16 to capture what's changed and write it to sql??
I have to find a way for this before I lose my mind...
:w00t:
thx
Cheers,
John Esraelo
February 10, 2009 at 10:11 pm
How often do you need to synchronize? Is it a 2 way sync?
The issue is that VFP uses flat files, the application actually has to use DLLs to read and write to the files. This means that there isn't a "server" that can react and sync things.
SSIS can grab information from VFP files and write back to them, but you are talking about a complicated set of replication. Is there any way the VFP app can be repointed to SQL Server instead?
February 10, 2009 at 10:29 pm
John, check your private message box.
There's a couple of other ways than using DLL's to read from/write to VFP tables, depending on their structure, the relationships between the tables, and whether any other items (BLOBs, database containers, general fields) exist in the tables.
BTW, there is no VFP 10 -- the current version is VFP 9 SP2.
February 10, 2009 at 10:33 pm
It is a one way from VFP to SQL.
I do know and understand that there should be some measure to detect the last change and so on so forth.
For instance, having VFP to have a double write processes, one, writing to the VFP tables from the front-end stand point and, two, at the same time having the application write a record to a "transaction" log. Of course we can have certain additional columns built in the "transaction" log that would denote the action, date-stamp, etc. At that point, perhaps we can use the transaction log to update our sql server tables. Sure, this can be done. However, the memo fields may be issues during the process and it may raise questions. Such as, whether we should use the bulk-insert or an odbc for that matter to connect and process the records..
It is a bit an interesting concept.
Let us look at this situation from another angle and see if we can remove the synchronization idea out of this equation. Then another question would .. would it not be rather difficult having all the VFP table now in SQL and the application pointing to those tables without any modification on the application code side? I know for fact the answer would be a big NO. Since, the VFP application by design does not quite work like in Microsoft Access that can easily have those tables "linked".
Perhaps I need to rethink and come up with better questions instead of burst of question initially and confusing the readers here. 🙂
Cheers,
John Esraelo
February 10, 2009 at 10:35 pm
Thank you for correction on the version part. thx
Cheers,
John Esraelo
February 10, 2009 at 10:36 pm
flextech,
Would the containers work like in Microsoft Access where the tables are linked to an SQL DB?
Cheers,
John Esraelo
February 10, 2009 at 10:42 pm
John,
In VFP, a "database container" is really just another table with a DBC extension. Each table that is part of the container has its header modified to hold the *full path and location* of the container table (yeah, I know...not a smart move, but that's the way they did it).
So no, it doesn't work like Access. That's both a good and bad thing.
February 10, 2009 at 10:42 pm
I haven't worked with VFP in some time, but the code for getting to VFP files and going to SQL Server wasn't much, if at all, different. You could have the application merely write to the ODBC data source and "repoint that". There might be things like reindexing or opening files that would fail, but you could possibly just avoid running them.
If you want the data in SQL Server as well as VFP, and you can tolerate some delay, SSIS might be the best solution.
February 10, 2009 at 10:44 pm
Also, you do NOT want to use ODBC with VFP -- the driver hasn't been updated since version 6 of VFP, and it's a pain to use. Far better to use the OLE DB driver or bulk insert.
February 10, 2009 at 10:49 pm
Steve, you're correct -- the code's not much at all. In fact, if you've implemented any kind of a data access layer (as I have in almost all my VFP code for the last several years), it's a breeze to change back end providers. I have one system right now that has to generate a series of reports from a combination of VFP, SQL and Oracle data -- no problems at all.
From what John seems to be presenting, this doesn't sound like a large effort to me.
February 10, 2009 at 10:49 pm
flextech,
Yes, you are right, perhaps the container is not what I need to look into.
Cheers,
John Esraelo
February 10, 2009 at 10:52 pm
Steve,
I believe the option of having the "transaction" log in the middle, between sql and vfp, can be the solution. In addition, the idea of having an SSIS should not be thrown out. In fact, having the SSIS running every few seconds, reading the VFP table containing the transactions would be a better route, instead of trying to write an SSIS trying to connect to a massive number of tables.. Yes, that would be a bit slowwwww.
Cheers,
John Esraelo
February 10, 2009 at 10:54 pm
flextech,
wow, since version six.. ouch..
yes, you are right the odbc is not a good idea and if we keep the idea of having a "transaction" log in the middle while using a bulk-insert or an ssis would be a better choice.
Cheers,
John Esraelo
February 10, 2009 at 11:02 pm
John, agreed. It sounds like a "pseudo transaction" table might be your best short-term solution. Depending on your schemas on both sides, you could possibly get away with something as simple as:
VFP table name
VFP row primary key
VFP d/t last updated
SQL table name
SQL row primary key
SQL d/t last updated
Build it initially from the VFP side, then each time you replicate to SQL you can UPDATE the necessary fields. The table could exist either on the VFP side or the SQL side.
Let me know if you need assistance on the VFP stuff...I sent you my contact info.
February 10, 2009 at 11:09 pm
flextech,
absolutely right. The structure of both realms are identical at this point. All objects are names the same. Therefore, things are a bit easier to work with. 🙂
Thus, the main focus should be the structure of the VFP "transaction" table in the middle and watch out for the blobs.
Also, on the SSIS side, I need to think of methods and codes to conditionally pick and write to the sql side objects.
I think this should do it, and would like to thank all of you for listening to my wining. 🙂
Please send me your contact information, that would be great.
Cheers,
John Esraelo
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply