[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 412
  • Last Modified:

Foxpro Database to Foxpro Database - Data Exchange

I have several application with Foxpro database backends that need to exchange data between one another when they're open.  Currently I have program that copies needed data from a database to temporary tables on a thumb drive.  I have a program associated with the other database that imports data from the thumb drive.  Kind of a pain.  Do I just creat a connnection?  If yes, how do you work with the connection to copy data from one database to the other?  Thanks in advance for your help/suggestions.
1 Solution
FoxPro isn't a pure backend. FoxPro is a file oriented database and it can handle both data and user interface. Thus if you can see DBF file from any location you may open it, read it, or even update it from elsewhere. All the work is done "locally" not on the machine where the file is located. It means all data must be copied over the network when you want to query or update them. But you probably know these details.

You can make a connection via ODBC or OLE DB driver to FoxPro tables but again, this driver does not work on the backend but it reads all DBF data over the network.

If you can map disk drives from one server on the other one then you may read data directly from FoxPro application and you don't need any ODBC connection or the thumb drive.

OTOH, the FoxPro application is sensitive to a network stability and reliability. Recovery from an error is almost impossible if the connection is broken during the read operation. And how would you like to ensure copied data integrity? The thimb drive can represent a certain kind of a safety element.
NIDDK1550Author Commented:
I'm just trying append a record(s) from a table in one database to a table in another database while both databases are open.  For example, I want to take the lab results for a patient from the labs database and copy those results to the patient letters database.  Yes, I'm aware that I'm duplicating data and no I wouldn't do it this way if I had the time to rewrite the applications involved.  At some point I'll rewrite the front end with .NET and the backend would be SQL.  Right now I just have to work the best I can with these legacy applications.  Again, in a nutshell I just need to copy data from one database to another database (both foxpro) while both databases are open.  What's the best way to do that?  
NIDDK1550Author Commented:
Also should have mentioned that these databases exist on the network and therefore they should be able to access one another.
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

To append records from one table into another one simply issue following INSERT command (these two tables can be in different databases):

USE d:\path\OneTable SHARED
USE x:\path\AnotherTable SHARED

INSERT INTO OneTable (ColumnA1, ColumnA2, ColumnA3, ...) ;
   SELECT ColumnA1, ColumnA2, ColumnA3, ... FROM AnotherTable WHERE SomeCondition

If you need to be sure nobody will change records in the "AnotherTable" during the query then you have to lock it. The table "OneTable" will be locked automatically (or an eror appears when the lock will be unsuccessful).

Several other commands exist which could do this work. The "best way" depends on your data and also on indexes created on these data.

You should also properly handle primary keys generation or autoincremental columns if any. Difference is also in processing record updates vs. new data insertion. Databases could contain additional validations which will avoid updates made by 3rd party programs etc.

If you are not sure how exactly are data handled by your legacy applications then you have to analyse it carefully or ask somebody who is able to do this work for you.
The advice you have been given above is very good.

If you already know this stuff, please don't take offense, but, based on the nature of your question, you also might want to look over the various VFP tutorial videos regarding the use of data tables, etc. at:      

Good Luck
Olaf DoschkeSoftware DeveloperCommented:
Based on your current workaround and the recommendations you got, why don't you do the same stuff like now, but with a folder in the LAN instead of a thumb drive?

There is no really different and better way, as pcelba initially said, foxpro is not a database server, there is no replication or connections between databases, you always go through some vfp process querying data into cursors, then appending them to other data or whatever.

If you have data used by both applications, why not simply use the same dbf in both apps instead of synching?

You could also think of creating a data access layer usable by all applications. In the simples case a vcx with database queries to common data, compiled into each application needing that data.

Remember: Redundancy is the worst in databases, it's destined to get out of synch.

Bye, Olaf.
NIDDK1550Author Commented:
Thanks to everyone else for their comments.

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now