Today I used a new VFP 9 feature for the first time in a real world scenario - the new ability to query data from a buffered cursor using the new WITH (BUFFERING = .T.) clause/syntax.
I know there are VFP developers and database administrators who will say using this syntax is absolutely wrong because the data still has a chance to be reverted and thus the query result is not reproducible. The information could be used incorrectly to make business decisions and this is not smart, and is not a professional approach. These individuals are absolutely correct when you need to have checks and balances in the database, integrity of the data in the database, and integrity in reporting and analysis.
I on the other hand needed to use this new functionality to check the validity of the data before it was committed to the database. Here is my scenario:
- I have data to import from an outside source.
- I have data to import from an internal source, but is exported from a different application.
- There is a good chance there is duplication from the two data sources.
- All data is imported to tabled buffered cursors.
- I have to commit the import of the data across three different tables all at once, or not at all. Two of the tables are related.
I begin the transaction and import the data from the internal source, then import the data from the external source checking for the duplicates. I want to review it before I committed the transaction. In the review process I query the various tables to make sure there are no duplicate people, no duplicate vendors, and need to verify all the registrations get set for the appropriate year.
Here is my query to count the number of people with multiple registrations:
SELECT cLastName, cFirstName, COUNT(*) as nCount ;
FROM people WITH (BUFFERING = .T.) ;
JOIN registration WITH (BUFFERING = .T.) ;
ON people.cPeople_PK = registration.cPeople_FK ;
GROUP BY cLastName, cFirstName ;
HAVING nCount > 1 ;
INTO CURSOR curMultiYearRegistrations
I counted the test data records in advance of the import to make my comparisons. This slicing and dicing of the buffered data saved me a lot of aggravation of removing the imported data when I found errors. I could have blown the database away and restored a backup, but that would have taken more time. I could have imported to a holding area, but that would have made twice the work. I was able to suspend the import process and do some ad hoc queries to verify all is well during each step of the import.
I have another idea where this new syntax will help in HackCX Professional. I anticipate adding a report or summary screen in the next version so you can see all the buffered changes you have made in the class library you are hacking. Sure I can do this without the SQL Select syntax, but I think writing a simple SQL Select is faster and less code than scanning through the whole table to determine what records have changed.
Have you used this new syntax? If so, what for?
Labels: VFP
