When connecting to a database server (perhaps using the ADO connection object or SQL Passthrough (SPT)), you need to keep in mind that some familiar VFP SQL syntax won’t work against a database that doesn’t support it. For a simple example, in SPT, you cannot do the following on a SQL Server 2005 database (for more info on connecting to SQL Server, see the article ‘How to Connect to SQL Server from Visual FoxPro’):

cConn = ;
    "driver=SQL Server;server=SERVERNAME;database=DATABASE;uid=;pwd="
nHnd = SQLSTRINGCONNECT(cConn,.f.)
IF nHnd <= 0
  AERROR(aErrs)
  =MESSAGEBOX('Connection failed. ' + aErrs[1,2], 16, 'SQL Error')
ELSE
  *-- VFP allows abbreviation of most keywords to 4 letters,
  *-- SQL Server does not. This code generates an error:
  cSQL = "SELE TOP 50 * FROM store_transactions"
  SQLEXEC(nHnd ,cSQL, "crTemp")
  =SQLDISCONNECT(nHnd)
  SELECT crTemp
  BROWSE NORMAL
ENDIF

While this may be annoying for those of us who are trying to save a few miles in our carpel tunnels, it should be a reminder that the SQL we use is native to the database we’ve connected to. This is extremely important to understand because there may be some behavioral differences between VFP and other languages, which may not trigger a syntax error! I have yet to find a good resource that details these differences from a Visual FoxPro point of view (if anyone has a link, please pass it along!). Here is a site, prepared by Troels Arvin called ‘Comparison of different SQL implementations’ that compares Standard ANSI SQL to PostgreSQL, DB2, MS SQL, MySQL, and Oracle. Troels has licensed this under Creative Commons, which means it might be worth adding the document to the fox wiki with a VFP category.

On the flip side, it opens several doors into the proprietary SQL language of the connected database. For example, SQL Server 2005 supports different types of table operators (like APPLY, PIVOT, and UNPIVOT) and some new SET operations (EXCEPT and INTERSECT). The OVER clause is also very handy (for analytical rankings such as row_number, rank, dense_rank, and ntitle). While I’m no T-SQL guru, consider the following syntax as a healthy example of what I’m talking about. It uses the PIVOT operation (and a CASE statement for good measure) to create a silly little pivot table that lists how many registers are used just a few times, more than a few, and a lot of times.

cConn = ;
    "driver=SQL Server;server=SERVERNAME;database=DATABASE;uid=;pwd="
nHnd = SQLSTRINGCONNECT(cConn,.f.)
IF nHnd <= 0
  AERROR(aErrs)
  =MESSAGEBOX('Connection failed. ' + aErrs[1,2], 16, 'SQL Error')
ELSE
  cSQL = "SELECT store_id, afew, more, alot " + ;
      "FROM ( " + ;
      "  SELECT store_id, store_register, " + ;
      "    CASE WHEN COUNT(tran_id) < 10 THEN 'afew' " + ;
      "      WHEN COUNT(tran_id) < 50 THEN 'more' " + ;
      "      WHEN COUNT(tran_id) >= 50 THEN 'alot' " + ;
      "    END AS totals " + ;
      "  FROM store_transactions " + ;
      "  GROUP BY store_id, store_register " + ;
      ") as tmp " + ;
      "PIVOT( COUNT(store_register) " + ;
      "  FOR totals IN(afew,more,alot)  ) AS tmp2"  
 
  SQLEXEC(nHnd ,cSQL, "crTemp")
  =SQLDISCONNECT(nHnd)
  SELECT crTemp
  BROWSE NORMAL
ENDIF

Also note that using SQLEXEC, you can create stored procedures in the target database, among other things.

With VFP9, there is greater support for subqueries and other SQL elements common to other languages. Because of this, it is easy to write VFP-style SQL code that can be interpreted by a database server such as SQL Server without modification. But be aware of the nuances between the various SQL implementations and certainly take advantage of the propriety features (like PIVOT)!

Tags: SQL, SQL Server, VFP