通过

VFP 9 SQL and Industry Standards (by John Koziol)

  • 项目

Perhaps overshadowed by massive enhancements to other product areas are the changes made to the Visual FoxPro data engine. Major changes have been made to that area to eliminate or greatly ameliorate product limitations; eg, subquery limitations. Also, some behavior has been tweaked to ensure compliance with SQL Server or ANSI SQL standards. Big changes that'll benefit our pals, but the magnitude of the changes kinda take our breath away; the potential for ripple (see earlier post here) is large.

Allow me an aside - two folks get a lot of credit for pushing this:  Aleksey Tsingauz and David Anderson. Aleksey - a Fox Team developer - has spent a lot of time studying and implementing standards, such as Joe Celko's SQL for Smarties, and ensuring that the VFP engine lived up to those standards. David, a contract tester well-known to early Fox aficianados, has been testing the heck out of the changes and has implemented some great TPC benchmark tests.

However, as I've said before, we're a handful of earnest guys and you're a gang of thousands. I'd love to see those of you with your hands on the beta to pound on the thing with heavy SQL queries. This would really help out. And, indirectly, help you out.

Ken has asked for a call to action in his latest column.  Here's my call to action.  Thanks.

Comments

  • Anonymous
    June 28, 2004
    Check out the comment from Frank Camp on my blog, John.

    The biggest issue that will come out of removing these limitations is that the max length of a FoxPro command is 8192 chars. With a lot of fields and join conditions, this limit will be reached immediately.
  • Anonymous
    June 29, 2004
    I am going to do some further tests (but not now, 4.57am here) to see what I can find with my apps that either use VFP or SQL2000 as the backend.

    The 8192 limit has already been mentioned.
    But another this that seems to be missing is:
    (From the SQL 2000 BOL)
    SELECT [ ALL | DISTINCT ]
    [ TOP n [ PERCENT ] [ WITH TIES ] ]
    < select_list >

    the WITH TIES is not implemented which is a shame because now we can't use that feature when we reference the VFP database instead of the SQL 2000 database.

    For instance:
    This works:
    SQLEXEC(nConn,'select top 1 with ties * from northwind..orders where freight=0.56 order by freight')

    but this doesn't:
    SELECT TOP 1 WITH ties * FROM (HOME()+'samplesnorthwindorders') WHERE freight = 0.56 ORDER BY freight

  • Anonymous
    June 29, 2004
    I'll direct the "WITH TIES" comment to Aleksey and others. Perhaps it's something that can be looked at.