DBCs to SQL using Views (to save from rewriting the code) 3

[Rajesh Karunakaran]

Dear Friends,

We have a VFP software. We want to shift the database from DBC to MSSQL. What I believe is that (possibly) if we create separate VFP VIEWS for each DBF table (which are being used) and use them for Data Entry Modules, we can use the existing code for updating the VIEWS as if they were DBF tables. If I am able to define, at design time, that the targeted database of the views as MSSQL DATABASE and its TABLES, I believe that the existing code itself should update the SQL automatically.

By the way, we use Data Environment and Data Buffering in most of the modules.

Does these things make sense to you? Is this a practical and possible way?
If yes, can you describe the procedure/process for getting this work?

Thank in advance,
Rajesh Karunakaran

 

[Mike Lewis]

The answer depends partly on why you want to migrate to SQL Server, and partly on how much effort you are willing to put into it. Basically, you have two options:

1. Do as you suggest. It will give you a quick-and-dirty way of moving your data to the back-end database. It's perfectly feasible to do that, although there will still be issues you will have to face.

2. Re-design the data handling in your program to make it a true client-server application. This will obviously be much harder, but will give you a much better result.

The point is that Option 1 will simply mimic your existing system, without necessarily getting the benefits of client-server. If your main reason for migrating is for better data security, or larger data volumes (exceeding VFP's 2GB limit), that's fine. It will be a good approach.

But most developers who go to client-server do so because they want greater efficiency. A true client-server system is designed to minimise the amount of data that travels over the network. If you simply create remote views that mimic your existing DBFs, you won't achieve that. You need to re-think the overall approach to data handling in your application.

To give a simple example, suppose you have a table of 100,000 customers. You want to do a report of all customers that meet some condition. You have migrated the table to the server, and created a customer view. You then continue to run your existing VFP code to create the report, except that instead of opening the original DBF, you open the view.

That's the simple approach, and it will work. But what will happen is that every customer record will have to come across the network, to be read by the client, which will then decide whether it qualifies for inclusion in the report. So a report containing perhaps only half a dozen records will still require the entire table to be read.

Now, compare that to the client-server approach. Here it is the server that selects the records that qualify for the report. The server only delivers those relatively few records to the client. I'm sure you can see how much more efficient that will.

It's true that you can achieve that result (the server selecting the records) by using remote views. But it's not a simple one-to-one substitution - creating a view to mimic each of the DBFs - and you won't be able to use your original VFP code without change.

I suggest you think about that choice before getting into the details of how remote views work and how best to use them. And come back when you need more specific information.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Rajesh Karunakaran]

Dear Mike,

As usual, that was a spontaneous and point-to-point explanation! Great! Thank you.

We plan to shift some of the modules/reports of this software to Web (say, for example, a few reports and a few modules which just updates the status of some existing transaction records), some modules/reports to DotNet that they will be then run from another existing DotNet software. The rest will continue to remain in the VFP application. So, I thought it would be better if I can port the entire VFP database to SQL well in advance so that reading/writing from the WEB or DotNet to VFP can be avoided. So, for time being, it is more about convenience, data volume & better security than whatever we expect to achieve from the Client-Server approach.

By the way, will you be able to guide me how to define the target database as SQL in VFP VIEWS and to update the SQL from them.
It would be great if you can paste some samples.

Thank you very much once again.

Rajesh Karunakaran.

 

[Mike Lewis]

Rajesh, given your objectives, it sounds like you are going in broadly the right direction.

You asked:

By the way, will you be able to guide me how to define the target database as SQL in VFP VIEWS and to update the SQL from them.
It would be great if you can paste some samples.

I'm glad to help, but I suggest you start by reading the VFP Help topics on remote data access. The topic entitled "Client/Server Solution Development" would be a good place to start. There are also a couple of books available specifically on client-server with VFP. Have a look at the Hentzenwerke site for some examples.

I mentioned that, even with a straight one-to-one migration based on remote views, there are several issues that you will have to deal with. One of them is data types. You need to think carefully about how to map your DBF data types to SQL Server equivalents. Another is how to handle NULLs. There are also issues that seem trivial, but which can really catch you out. A good example is the fact that SQL Server does not support the concept of an empty date, which is something of a VFP/dBASE quirk. That's just one example.

Rather than ask people to paste samples, you first need to be sure you understand the concepts. Do your research first, after which you will be able to ask more specific questions, and you will understand better the samples that people offer.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[JRB-Bldr]

One of my current clients has a Legacy VFP application with VFP data tables and they too needed to migrate the application (part of all) to the Web.

They left the data in VFP data tables and, for the Web part (almost a full functional duplication of the older application), they used VB.ASP and merely access the VFP Data as needed using Connection strings.

In that way, they did not need to change the Legacy VFP application at all for those users who refused to migrate to the Web, while still providing full functionality (via the VB.ASP 'front end') to those using the Web interface.

Changing the data 'back end' may or may not be a good idea, but do it for the right reasons.

Good Luck,
JRB-Bldr

 

[Rajesh Karunakaran]

Dear Mike,

That's indeed the right guidance to the right direction.
I will check the links you mentioned and will be here if I am in need of some help.

Thank you so much.

Rajesh Karunakaran

 

[Rajesh Karunakaran]

Dear JRB,

I got your point! Thanks.

By the way, users refusing to upgrade or update on something is usual in software field. Fortunately, we don't expect any 'refusal' issues here. Too, me and my colleagues don't feel comfortable (or rather we're not able to convince ourselves justifying it) using VFP tables for our Web and DotNet applications. The client you referred has an obvious reason, I understand.

However, as you said, I will study all aspects well, prior to start working it!

Thanks
Rajesh Karunakaran

 

[Olaf Doschke]

I agree with Mike here, but have to add, even when going route 1 with Views "emulating" DBFs, it's not necessarily meaning you fetch all data, it's not hindering you to parameterize views to only fetch necessary data and it's also not impossible to add additional views with more complex queries. Especially for readonly access to data, like for extraction and aggregation of data into reports.

And even the simple DBF-like Views could mean joins already, if the SQL Server model structure differs from the DBFs. Modernizing the old frontend then isn't necessary, you can do that with a new frontend making use of the new structure. Often enough that means a compromise for the new database model, but often an acceptable due to faster transition. For example it might be a budget limitation to design the new database in a way you're able to recycle your client side logic to a great extent.

In a good migration of data to a new SQL Server Backend you'd want to redesign the Frontend, too, to make best use of it, but that may be a step after the data is migrated.

Bye, Olaf.

 

[Olaf Doschke]

By the way: A nice tool for an easy 1:1 transition to MSSQL tables is the SQL Upsizing Wizard, but don't take the native version of that coming with VFP, not even from VFP9 SP2, there is a separate newest version within the VFPX Sedna projects - see

http://vfpx.codeplex.com/wikipage?title=Sedna&referringTitle=Home

more precise

http://vfpx.codeplex.com/wikipage?title=Sedna&referringTitle=Home#SQLServerUpsizing

This also once was a Code Magazine topic.

Bye, Olaf.

 

[Mike Lewis]

Just another point while I think of it ....

I have been involved with half a dozen or so project over the years that involved migrating a VFP system to SQL Server. In every case, the first thing we did was to redesign the database. That's partly because, in many cases, the design in the old systems just weren't very good. We took the view that it was worth taking advantage of the migration to get a better design. And partly because SQL Server (and many other back ends) offer many features that weren't present in old FoxPro (or Foxbase or dBASE) systems, that we wanted to take advantage of. These included things like IDENTITY (auto-incrementing) fields, long field names (more than the earlier 10-character limit), variable-width data types, and quite a lot more.

I'm not saying that re-designing the database is a pre-requisite for the migration. But, if your experience is anything like mine, it is something you might want to consider doing anyway, and therefore something you should budget for.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Rajesh Karunakaran]

Dear Mike and Olaf,

You both (and many other of course) are not only answering the queries but try your best to guide our friends in the right direction, Great! I do respect this forum and the members!

As you said, I will go through the appropriate solutions, their advantages and benefits before deciding on how
should I deal with this situation. I believe, I should check with people like you once again when I get hold
of a solution positively.

Thank you very much
Rajesh Karunakaran,