I’m working on a project converting a client’s VFP 9 data to SQL Server. The VFP app uses local views (via data objects) that use native VFP data and local views. Swapping the native VFP data for SQL Server data isn’t as simple as redirecting a view from a DBF to a remote view. I should have realized this since, as I well know, VFP doesn’t automatically requery underlying views.
Say I have a view on a People table that selects the last name–call it lv_PeopleByLastName. It’s trivial to redirect the view to query remote data instead of local data. But if I then use lv_PeopleByLastName with NODATA, the remote view is also opened NODATA. That makes sense. The gotcha is that requering lv_PeopleByLastName does not also requery the remote view. So, even after Requery() the local view will still be empty since the remote view is empty. One must requery the remote view, then the local view.
IOW, once open every data source operates independently. VFP doesn’t automagically maintain dependency info about an open datasource (except through the database container, of course).
This should not have surprised me. It’s not fatal, just a complication I should have foreseen. So, I can’t simply redirect a view from local data to a remote view without changing either the views or the way dataobjects manage requerying.
Here are my options as best I can tell:
- Change the views so they don’t use other views. To be frank, SQL syntax and I are not BFFs. I’d rather stick a fork through my hand than work as a database manager. SQL feels like one is writing entire programs in a single line of code–the antithesis of maintainability. So, I like SQL streamlined as much as possible. Luckily most queries are fairly straightforward.
- I could rework the data objects and the views so that data objects cascade other data objects representing underlying views that would be requeried whenever the parent object is requeried. Okay, I confess. I just threw that in there because options should always come in threes.
- The data objects could make note of what underlying views are involved and requery them when the object is requeried. That works. It’s not elegant, but it is effective, and considering the bottle neck is generally fetching remote data, doesn’t add significant processing time. However, if more than view uses the same underlying data, it could make for some unnecessary requerying.
It looks like option 1, with option 3 held in reserve. Which I could have had done by now instead of posting. But I’ll call this organizing my thoughts rather than procrastinating on the inevitable.