Hi William,

I agree with Michael.  With SF you can work with a VFP or SQL table at the same time, this is perfect for smooth transition out of VFP front end and take advantage of the power of SF Business Objects, the end user will not notice or care if it is talking to VFP or SQL table.

Plan your design carefully, if you will be porting the application to SQL tables, then you can build a custom import process with BOs for VFP tables which will allow you to manipulate the data easily. 

I also came from a VFP background and at the beginning had many question on how to approach the VFP cursor feature in SF BO, but trust me you have by far much more power and control of the data with a SF BO than you would ever had with VFP cursor.   Take a look couple of times to the BO methods available (ex: CopyDataFrom, CurrentDatatable, AcceptChanges, etc) they will be your best friend and of course the BO.GetEnumerable() it is invaluable to loop your current data, the BO.Filter and BO.Sort are also extremely powerful and should be used with extra care, so if you set a filter or sort, you code should be aware of it to clear them up when adding new records, etc.

Again, a lot of plan and quick testing ahead of your current business rule, start playing with it, search the forums for plenty of samples and solutions, then post question here when ready to rock and roll!

Go for it

Edhy Rijo

Hi Michael,

Thanks for the reply. I have attached a diagram of an idea I'm contemplating and I would be very happy to have comments.

Our current VFP app is "LAN bound" because of the data access design of the legacy VFP application, which means we have a separate database in each office. This is a big drawback for our users and we are trying to come up with a way to solve this limitation.

Our intention is to ultimately migrate to SQL/.NET, but we haven't been able to make a compelling argument to freeze development of the legacy app while we re-write from scratch in SQL/.NET. This being the case, we're being pressured to re-design and re-work the existing VFP app to work with data in SQL. I know this is going to be a huge undertaking and I'm trying to find an alternate path that will let us migrate incrementally while continuing to use the legacy app and VFP datastore.

I would like to prototype a linked server scenario as shown in the attached Visio document. If our StrataFrame BO's could be designed to work with data that exists on both sides of the WAN, essentially displaying a unified view of data from all offices, this would be a huge argument in favor of developing a .NET app that can work with our current VFP data. Then, as major components of the app are completed, we can move the data from the VFP tables into SQL server.

I realize the OLE DB Provider for VFP is probably the weakest link, but a huge part of the application is presentation (UI stuff) only with little actual updates needed. If we could build a beautiful presentation layer, I think we would have more clout to move parts of the VFP data into SQL and require users to use the new .NET app for parts of the app that have been "converted".

So I guess the first question is whether or not this linked server scenario is a viable design in the first place...?

If this linked server approach is viable, then I'm hoping the SF community can help me come up with BO's that are well designed not only to work with the SF framework, but also to provide a unified view of data from multiple databases via linked servers.

Thanks.

Hi Williams,

First of all, I think that today a linked server is not a viable design for other purpose than read only.
We are working with my friend Francis Faure (French VFP MVP) on a project we'll present at the next meeting of VFP french community (Rencontres AtoutFox 2011), this project is named "VFPinSQL" and is a full functional read-write linked server. In our project, we manage a dbc with triggers and RI for all tables, and pull from VFP to SQL the concurrency mode defined from within VFP. All SQL code (views and their triggers) is generated by VFP. But it is not ready today (NULLs management is not as we want it), and we are still uncertain about the necessity of a rewriting of all VFPOLEDB.

So, I'ld keep far from VFP linked server today in a production environment.

You need to use VFP tables, from within legacy VFP app and StrataFrame App. It's no problem here, the way is simply to map your BOs on VFP tables. the only difference between each LAN segment will be on the connection string.
In the same Strata app, you need to use SQL tables; these tables may be new tables, or tables from Informix linked server. You do not need 2 SQL servers, only one! This SQL Server may be on one of the LANs, or anywhere else. You'll reach it on the WAN using StrataFrame Enterprise server, which is designed for that entire purpose.

In summary...
All the time data are in VFP, map the BOs directly on VFP tables and views. When you migrate a table (or a set of tables) from VFP to SQL Server, map the BO on SQL Server. You'll have a few code to modify by yourself, but the main part will be done by the BO Mapper. In your client StrataFrame application, you may consume several connections on different data source type. You need only one SQL server, which can be reached in a secured way using StrataFrame Enterprise Server.

Michel,

Thanks again for your input. I have just a few items in response.

We do not have the budget to purchase the SF Enterprise Server. We see a great benefit in the product, but the cost is too prohibitive. We package/deploy/support our applications as if they were commercial apps, but our customers pay nothing to use them, so we cannot use a product without a royalty free runtime.

Thanks for your insight regarding VFP and linked server. I'm very interested in your VFPinSQL project and would like to read anything you might post to the community.

The reason for a SQL server on both sides of the WAN is that I need to access both VFP databases in the SF app on both sides of the WAN (without an SF Enterprise Server). The idea would be to set up the VFP database to the local SQL server as a linked server, then both SQL servers would be linked together as linked servers. If one of the SQL servers is not local to the VFP data, then the VFP data would have to be pulled across the WAN, which is not an option. Does that help to explain why there are 2 SQL servers in the design?

William,

I understand the 2 SQL servers on both sides, and also the fact you have not the budget for ES.

VFP data are local in each side of the WAN, are they? and will stay local, during all the smooth migrating process? If yes, then go with BO directly mapped on your VFP tables. And keep the SQL Servers as linked servers if you need it.

About VFPinSQL: we'll present it on march 23, at Montpellier (France). Maybe we shall put the code on CodePlex, but we don't know when. And now, we need to find a solution for the NULLs , and also for a problem with grants on automation .