Tod means Fox

Michael Desmond alludes to in his article, “Working in the Phone Booth“, that Microsoft’s denial of future Fox releases is part of a bigger plan (that also includes the dropping of Visual Basic 6 and J#). He asks his readers to respond:

Retiring operating systems and applications is tough. IT shops face a huge task as they migrate to software with an assured roadmap. But retiring a programming language? The mechanics of switching to a new dev platform and managing the migration of bits between languages is hard enough. The human factor is harder still. What’s your take? How should the ranks of dedicated FoxPro and VB6 developers respond to these retirements? And as a development manager, how important is it to cross-train coders and proactively move off of sunsetting platforms?
E-mail me at mdesmond@reddevnews. We’d love to hear your opinions!

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)!

I decided to create a Windows Live Spaces account the other day in order to post a comment on a blog there. I was a little puzzled at what I could actually put in my new ’space’. So I decided to use it as a blog for posting interesting links that relate to VFP. The purpose is so that I can blog here on various topics and not clutter things with links to this-or-that. This may prove to be a dumb idea, but I’m going to give a try!

Tod means Fox (b)

Update

I will no longer be using Windows Live Spaces. It’s a crappy system, causes bloating, and gives me a headache.

Although this article seems to be a re-publication of another recent article, I felt like posting to show that the story isn’t really dead yet. This one is in ComputerWorld (http://www.computerworld.com): FoxPro Supporters Lobby to Keep DB Tool Alive.If you’ve ever used FoxPro, and would like to see Microsoft continue its development, please take a moment to support the folks over at MasFoxPro (http://www.masfoxpro.com). Sign the petition!

Sorry, but I’m just a little annoyed at the moment. In order to post a comment on a blog on this system I had to create an account. Normally, that’s OK with me. With spam and abuse issues at perhaps their greatest height, logging in offers a sense of protection. What I don’t like is the fact that I get a spiffy new hotmail email address. What I don’t like is all the user interface oddities and jumpiness. What I don’t like is that this site fails W3C’s validation with 107 errors. Also, page loads seem painfully slow (and I’m running on a pretty nice system using a fast network).

OK, I’m done complaining! Carry on.

If you haven’t already noticed, I have an article in the April/May issue of Fox Advisor magazine entitled “Warehouse Data with Microsoft Visual FoxPro”. This is my first published article, although I’ve been writing functional and technical documents for quite a while. I’d like to thank Andy Kramek for his help during the early stages (his initial edit was as valuable to me as it will be for you!) and anyone else involved with getting this to print. Heather Petrig did an amazing job channeling me in all the right directions.

In the end, I hope you like what I had to say. Please post any comments, questions, and/or suggestions you may have either here or in the Advisor forums (I’ll check them occasionally).

PS: I’d like to mention that a follow-up article is currently in ‘edit mode’ (as I like to call it) and might be out in June/July. This follow-up article discusses VFP as an ETL tool; I’ll have a spiffy new website, foxetl.com to go along with it.