I am working on a project to enhance an existing application, and in the process I am updating it from VFP 5 to VFP 9 (more stable, less filling). I thought I was going to take advantage of the multiple detail band features of the new Report Designer, but decided on another approach. The update from VFP 5 to VFP 9 has been smooth with the help of three lines of code:
SET ENGINEBEHAVIOR 70
SET REPORTBEHAVIOR 80
_screen.Themes = .F.
That is until yesterday…
The users acceptance testing revealed two bugs that are related. The application has some calculations on a report, and the calculations were evaluating incorrectly. The users pointed me toward a number not being included in the final results, but the formula definitely included the table column in question. It turns out that data was not being correctly pulled from the SQL Server database.
My debugging session showed no records in a certain scenario. I initially thought it had to do with the data in the base tables in SQL Server not having the conditions. and told the users they needed to set up data that met the criteria. They did and it still did not pull any data. Things that make you go hmmmmm.
What I found out in closer inspection is the original developer made a local view of two remote views. The code USEs the one local view with NODATA and later does a REQUERY(). Years ago I observed strange behavior with views based on other views and have stayed away from them. This situation is even “stranger” since it is a local view based on two remote views. Looking a the code, I never gave it a thought this was the case. I assumed (incorrectly of course) that this was a view pulling data directly from SQL Server.
In VFP 9, when you open a view NODATA, and the view is based on other views that are not yet USEd, the underlying views are also opened with NODATA.
In VFP 8 and earlier (I tested every version back to VFP 5), when you open a view NODATA and the other views are not yet USEd, only that view is opened with NODATA and the underlying views are opened with data.
Back to VFP 9, issuing the REQUERY() on the view requeried no records because the other views were already empty. Ugh. It was not the SQL Server data not meeting the criteria after all, it was the local cursors being empty.
The fix is easy: open the underlying views first, then open up the original view and REQUERY(). Fortunately the IT manager who is my customer understands how things like this can happen. He has been waiting to hear the “VFP 9 update excuse” during this testing period. He is a .NET developer so we had some fun jabbing each other’s favorite developer tool.
I also tested this same issue with a local view of two other local views. Same behavior. It is not remote view specific. Unfortunately I cannot find any documentation in the Help file with respect to this behavior change, so I thought I would post it here and possibly save someone else some aggravation down the road. I never ran into this because I do not do view-on-view coding, but I know others do and this is something you should be aware of when you use this technique.
Hi Rick,
There was heated discussion about this change in behavior in VFP 9.0 on UT in thread #1101756.
I quote Aleksey Tsingauz’s (VFP Team) explanation:
NODATA clause is documented as the fastest method for determining the structure of a SQL view. It is also mentioned that, VFP achieves this goal by not retrieving data from the source. Therefore, avoiding waiting for the source to build a potentially large cursors.
The old behavior, when VFP was still fetching data for referenced views, was contradicting documentation and the purpose of the NODATA clause. Besides the fetching issue, there was an inconsistent behavior in regard to parameterized views. When NODATA clause was used, one wasn’t prompted for parameters for the view itself, but was prompted for parameters for referenced views (the side-effect of an attempt to fetch data). Therefore, the behavior was considered to be a bug and we’ve decided to make it consistent (i.e. do not prompt for parameters regardless whether they are used in the view itself or in referenced views) and aligned with documentation and the purpose of the NODATA clause.
Thanks for passing along this key information Sergey. I see the Fox Team perspective and understand the reasoning for change. I see definite advantage in the case when developers use the view-on-view technique.
I just wish it had been documented. Not that I can recall every detail of the documentation, but at least I would have read this once.
This behavior is what I would expect, and it is correct, IMO, given the problem of underlying, parameterized queries. The problem is extant with requerying local views that are based on local views. One has to loop through each underlying view and requerying in 8.0, also.
I found this post while looking for a better, smarter solution to the NODATA, but it sounds like it is just as awkward as I'm finding. Or, rather, my data layer isn't designed entirely correctly. :-\