Home » Uncategorized » SQL Select – Bufferred Queries
Jul
03

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:

  1. I have data to import from an outside source.
  2. I have data to import from an internal source, but is exported from a different application.
  3. There is a good chance there is duplication from the two data sources.
  4. All data is imported to tabled buffered cursors.
  5. 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?

4 Responses to “SQL Select – Bufferred Queries”

  1. July 4th, 2007 at 03:37 | #1

    Absolutely, I use this method as well, and I find this feature incredibly useful, I also use this to check if the user has selected some records before saving and then if they wish to select other records the “already selected records” get excluded. If we didn’t have this feature we would have to use the getnextmodified to build a cursor manually, I previously had to do this using the getnextmodified and it was a pain.

  2. July 5th, 2007 at 17:42 | #2

    I find it extremely useful as well. One use that comes to mind is being able to display subtotals while the user is still editing the data which affects the totals, such as when creating or updating a budget in our accounting software. This allows a user to play with the numbers and view the results before actually committing them to the table.

  3. July 6th, 2007 at 17:08 | #3

    We tried to use it, but found it extremelly slow in a long and complex procedure, so we had to resort to dbase commands (locate/seek) instead for our purpose. I’ve documented this in one of my posts in UT.

  4. Anonymous
    July 8th, 2007 at 00:44 | #4

    Here is the discussion about Buffering = .t. on UT I was talking about http://www.universalthread.com/wconnect/wc.dll?2,15,1136588

Add reply