SQL SELECT in VFP and T-SQL
Watch out for these language differences when moving between Visual FoxPro and SQL Server.
Over the last few years, the FoxPro team at Microsoft has done an excellent job of bringing VFP’s SQL sub-language closer to accepted industry standards. In particular, they have made the SELECT statement more closely conform to ANSI-92 standards, which is good news for developers who need to move between VFP and back-end databases like SQL Server and Oracle.
Yet VFP’s version of SELECT still has enough syntax and other differences to trap the unwary programmer. Some of these differences reflect the peculiarities of the FoxPro language, while others offer genuinely useful – albeit non-standard – services that developers would be reluctant to do without – the ability to send query results direct to an array, for example.
In this article, we highlight the differences between the VFP and SQL Server (T-SQL) versions of SELECT. We originally compiled the list with SQL Server 2000 in mind, but all of the points apply equally to SQL Server 2005 (as well as to version 7). If you know of any differences not covered here, we’d welcome your feedback.
Order of clauses
VFP has always been relaxed about the order of the clauses within a statement, and that applies equally to SELECT. Put your ORDER BY before the FROM clause, or your TOP N right at the end of the command, and VFP won’t so much as blink. T-SQL is much stricter. In general, T-SQL demands that the clauses appear in the order in which they are listed in the syntax diagram in the Help file.
Empty column names
An oddity of T-SQL that sometimes surprises VFP folk is its ability to generate result sets with empty column names. This happens by default with calculated or aggregate columns. For example, in the results of this query:
SELECT EmployeeID, UPPER(LEFT(LastName,4)) FROM Employees
the second column will have no name. In VFP, the column name would default to something like Exp_1.
In both versions, you can use the AS clause to assign a name to a column:
SELECT EmployeeID, UPPER(LEFT(LastName,4)) AS ShortName FROM Employees
but T-SQL gives you an alternative syntax that’s not supported by VFP:
SELECT EmployeeID, ShortName = UPPER(LEFT(LastName,4)) FROM Employees
TOP N
In VFP, SELECT statements with a TOP N clause must also have an ORDER BY clause. Not so in T-SQL. You may omit the ORDER BY, in which case the ordering will be arbitrary.
WITH TIES
In T-SQL, you can add WITH TIES to a TOP N clause to indicate that the result set should include any tied values in the Nth row. So, if you specify TOP 10 WITH TIES, and the 10th and 11th rows have the same value, you’ll get 11 rows in the result set.
VFP doesn’t support this clause. However, VFP 8.0 and below always behave as if WITH TIES was present. So, the following query will produce 11 rows if the 10th and 11th rows have the same order amount:
SELECT TOP 10 Order_Amt FROM Orders ; ORDER BY Order_amt
If that’s not what you want, add a GROUP BY clause, like so:
SELECT TOP 10 Order_Amt FROM Orders ; ORDER BY Order_amt GROUP BY Order_Amt
In VFP 9.0, the default behaviour is not to return tied values. So a query will always return exactly the number of rows specified in the TOP N clause. You can make VFP 9.0 behave like earlier versions by setting ENGINEBEHAVIOR to 70 or 80, but this will be at the expense of performance. (Our thanks to Stella Levin for pointing out this behaviour to us and for suggesting the GROUP BY workaround shown here.)
Optional FROM clause
Now here’s another one that surprises a lot of people. In T-SQL, it’s legal to omit the FROM clause from a SELECT command – but only if the expressions in the expression list don’t reference any columns in a table. Doing so will always generate a result set with exactly one row. The following command, for instance, would produce a result set containing just the current datetime:
SELECT GETDATE() AS Current
VFP would unhesitatingly reject a command like that.
LIKE operator
VFP and T-SQL both let you use wildcard characters (‘_’ and ‘%’) with the LIKE operator. These represent a single character and an arbitrary number of characters respectively. So this query:
SELECT * FROM Products WHERE Product_Name LIKE '%chocolate%'
will find all products whose names includes ‘chocolate’.
T-SQL goes one better by letting you also use square brackets to denote ranges and sets. For example, the following statement would find products whose codes start with A, B or C, followed by two digits:
SELECT * FROM Products WHERE Product_Code LIKE '[A-C][0-9][0-9]'
Unfortunately, this handy feature is not available in VFP.
GROUP BY and aggregate functions
This one has trapped many VFP developers, especially since VFP 8.0 first appeared. In earlier versions, the following syntax was legal:
SELECT Country, City, AVG(Inv_Total) ; FROM Invoices GROUP BY Country
However, although legal, that syntax produced potentially incorrect results. In the above example, VFP would select an arbitrary city to match each country, even though a given country could have many cities.
ASNI-92 SQL imposes a simple rule: if a query has a GROUP BY clause, every item in the expression list (the list immediately following the keyword SELECT) must either be an aggregate function or one of the columns listed in the GROUP BY clause. T-SQL strictly enforces that rule, as does VFP 8.0 and above, but earlier versions of FoxPro don’t (in VFP 8.0 and above, you can override the enforcement by setting ENGINEBEHAVIOR to 70).
Grouping on column numbers
Still on the subject of GROUP BY, the following syntax is legal in all versions of VFP:
SELECT Country, AVG(Inv_Total) ; FROM Invoices GROUP BY 1
T-SQL would choke on that because column numbers are not permitted in the GROUP BY clause. You would have to use the following form (which would also be legal in VFP):
SELECT Country, AVG(Inv_Total) FROM Invoices GROUP BY Country
However, both versions allow the use of column numbers with ORDER BY.
HAVING clause
In VFP, you can use a HAVING clause even if there is no GROUP BY clause. In that case, HAVING behaves like WHERE. In T-SQL, HAVING is not permitted without GROUP BY.
Outer joins
In T-SQL, you can’t specify a join simply as OUTER. Unlike in VFP, you must include the appropriate keyword: LEFT, RIGHT or FULL.
Projections and derived tables
VFP and T-SQL both support subqueries in the WHERE clause. In VFP 9.0, as in T-SQL, subqueries can also appear in the expression list and in the FROM and JOIN clauses. Subqueries in the expression list are called projections, while those in the FROM or JOIN clauses are called derived tables. The Help files for VFP 9.0 and SQL Server both contain examples of how these can be used.
VFP 8.0 and below do not support projections or derived tables.
Query destinations
VFP allows you to send query results to a variety of destinations. By default, they go to a behinds-the-scenes cursor which VFP immediately displays in a Browse window. You can use the PREFERENCE clause to specify options for that window. Alternatively, you can use the TO clause to send the results to the screen, a printer or a text file. Or you can use INTO to place the results in a cursor, table or array.
In T-SQL, query results are simply returned to the client process in the form of a result set. The only option for re-directing them is to send them to a new table, which you do like so:
SELECT * INTO Choc_Products FROM Products WHERE Product_Name LIKE '%chocolate%'
Unlike in VFP, the position of the INTO clause is significant – it must appear between the expression list and the FROM clause – and it shouldn’t include the keyword TABLE.
If you want to send the query results to a temporary table – similar to a cursor in VFP – use the same syntax as above, but start the table name with #. Tables whose names begin with # are stored in SQL Server’s Tempdb database, and are destroyed when the connections which created them are closed.
Memo and general fields in DISTINCT clause
VFP 7.0 and below allow you to specify memo and general fields in the expression list of a query that includes the keyword DISTINCT, even though such queries produce incorrect results. For VFP 8.0, the FoxPro team tightened up the syntax. Now, memo and general fields are disallowed in DISTINCT queries, unless ENGINEBEHAVIOR is set to 70.
The SQL Server equivalents of memo and general fields are text and image columns. T-SQL does not allow these column types in the result sets of queries that have a DISTINCT clause.
Number of tables
VFP 8.0 and below imposes a limit of 30 tables (to be precise, 30 aliases) in a SELECT statement. The equivalent figure in T-SQL is 256. VFP 9.0 has no theoretical limit.
Nesting of subqueries
In VFP 8.0 and below, you can only have a single level of subquery nesting. In VFP 9.0, there is no theoretical limit. In T-SQL, the limit is 32 levels.
Null values
If you want to exclude null values from a result set (or exclude everything except null values), you can (and should) use IS [NOT] NULL in the WHERE clause. That applies in both VFP and T-SQL.
But be careful about the seemingly similar ISNULL() function. In VFP, this function does what its name suggests: it tells you if a given expression evaluates to null. In T-SQL, ISNULL() is more like VFP’s NVL() function. It takes two arguments; if the first argument is null, the function returns the second argument, otherwise it returns the first.
Visual Foxpro Autocomplete – Tips and Tricks
Last night I spoke at the Atlanta Foxpro Users Group meeting on Autocomplete – Tips and Tricks.
Here is the issue/problem: The client wants a lookup against existing manufacturers and constrain them (for the most part) against the existing list, but would also like to be able to add a manufacturer on the fly with as little fuss as possible. My initial thought is the combo box, but you are constrained by the contents of the combo box on what can be chosen. So then I think of the new Autocomplete feature of the textbox.
Another issue is that the database for the system is in MySQL and can be large. Autocomplete only works against a VFP free table. So, how do you get autocomplete to work with a MySQL backend, large dataset, but constrained to a VFP free table?
I poked around the Internet in all of the usual places and I just couldn’t find any information on how to make Autocomplete work, especially not in the environment that I was running in. There were a couple of blog entries by Calvin Hsia and Jorge Mota that helped out a lot. Along with some additional testing, I was able to come up with something that worked out.
If Autocomplete would work against a cursor, this article would have been done right now, but Autocomplete works against a table sitting on the HD. I was really getting worried now. What if I wanted Autcomplete features against a table that is over 2G?
While testing, I was also trying to determine when the table was opened. Was it during the init of the form? Load of the form? Did it leave it open? I couldn’t really tell, because as far as I was able to determine, the Autocomplete table is opened in a private data session.
I was thinking I was going to fail in my endeavors so I went to the xSource directory to see if the source code for Autocomplete was in there. I was hoping to get some of my questions answered, and if I hit a wall, I could see what technology the VFP Team used in Autocomplete and roll my own to where it was acceptable. Unfortunately, the source for Autocomplete isn’t in the xSource code. Another potential roadblock.
All this worry was for naught however. Autocomplete was built in such a way as I was able to get everything working in an acceptable manner.
Enough already. Stop talking about the roadblocks and talk about the solution! {You didn’t know that I had multiple personalities, did you!?!}
To get autocomplete working, you must have three properties set in the textbox control: AutoComplete, AutoCompTable, and AutoCompSource. These are reasonably well documented but I’ll make some brief comments. AutoComplete stores a numeric between 0 (Off) and 4 that indicates what type of lookup you would like. AutoCompTable holds which VFP table to open for the AutoComplete lookup. Yes, this can be a full or partial path such as c:\temp\MyAutoCompTable or .\AutoCompData\MyAutoCompTable. This is good news in that you could specify a different table for each control if you would like. The VFP Team left it up to you on whether to have a single AutoComplete table or multiple. The AutoCompleteSource holds the value that is also used, in combination of the value of the textbox, in the lookup of the value in the AutoComplete table.
The first good news is that the Autocomplete table doesn’t appear to be opened until a value is typed into the text box. This means that on the Init of the textbox itself that I can manipulate the table and ZAP it if necessary. Why would you need to do this? Well, if you want to use Autocomplete against a MSSQL/MySQL/Oracle/PostgreSQL back end with a billion records in it, you may want to start out the day with an empty Autocomplete table.
The second good news is that the AutoComplete table isn’t opened exclusively. We would have guessed this as the VFP Team would want AutoComplete tables shared among users, and even within the same application using multiple instances of a form, and across multiple instances of the textbox even on different forms. This also means that we can open the AutoComplete table in our current datasession and insert records on the fly.
The third piece of good news is that the refresh of the AutoComplete display occurs after the InteractiveChange event. This means that we can do a lookup against our backend database and conditionally insert records into the AutoComplete table and have those values immediately reflected in the display to the user! We’re cooking with gas now!
We’re up to the fourth piece of good news. That’s a lot of good news considering how much concern we started with. Notice that with the late opening of the AutoComplete table after the init, and that the display of the AutoComplete values happens after the InteractiveChange event, means that all of this lends itself to building in custom methods into a textbox class. OOP as it should be. It would be simple to build some logic into the INIT of the textbox control to state “IF THIS.AutoComplete > 0 THEN THIS.AutoCompleteInit()†where AutoCompleteInit is a custom method that takes care of setting up the environment for AutoComplete and the needs of this control. Likewise in the THIS.InteractiveChange event, we could call to our AutoCompleteInteractiveChange method to do any custom lookups necessary.
The fifth piece of good news is that AutoComplete is not slow. I was curious and concerned about this as well. Unfounded worry, thankfully. AutoComplete adds its own index to the table. I know they have made speed improvements to LOCATE and the SQL SELECT, but nothing beats a fully utilized index on a table.
So there you go. Go forth and use AutoComplete where necessary and don’t worry! There are times where you want the user to be semi-constrained by the previous entries for a field (normally constrained by a combobox control) but also need the ability to add a value on the fly (not available normally via a combobox control). This is a great tool for the right job. VFP, once again, surprises me on what a great tool it is.
Source: http://cully.biz/2007/07/11/visual-foxpro-autocomplete-tips-and-tricks/
Virtual Earth Class Library for Visual FoxPro
Virtual Earth
Billed as “…the integrated mapping, imaging, search, and data visualization platform”, Microsoft’s Virtual Earth (VE) is an awesome mapping service that can be consumed and manipulated using the VE Map contol and JavaScript. In order to get a feel for what VE is capabable of, head on over to the VE Interactive SDK and try some of the examples provided. There is plenty of documentation, blog entries and articles available online that provide general VE overviews and detail the specific features the service provides, so I won’t spend a lot of time explaining what VE is and what it does. I want to explain enough so that you can understand the goals of the VFP Virtual Earth wrapper class library that I’m presenting in this blog entry. I figure you can do a few online searches if you’re interested in a deeper or broader understanding of VE and its specific features.
Virtual Earth Map Control
In order to understand VE from a developer standpoint you’ll need to be familiar with the Virtual Earth Map Control. The VE Map Control and the JavaScript classes/enumerations that make it up are provided via a web handler (.ashx file). I find it is more understandable to think of it as a library rather than a control. The VEMap class (which is part of the VE Map Control) is what I would consider the actual control that is seen within a web page. Perhaps I’m splitting hairs here, but I found it to be a helpful distinction in my mind when first starting to work with VE.
Creating Your First VE Web Page
It’s pretty simple to add the VE Map Control to a web page and use it to instantiate an instance of the VEMap class. Here’s the basic HTML needed in order to create such a page…
<html xmlns=“http://www.w3.org/1999/xhtml”>
<head>
<title>Virtual Earth</title>
<meta http-equiv=“Content-Type” content=“text/html; charset=utf-8” />
<script type=“text/javascript” src=“http://dev.virtualearth.net/mapcontrol/mapcontrol.ashx?v=6.2”></script>
<script type=“text/javascript”>
var map = null;
function startVE()
{
map = new VEMap(‘myMap’);
map.LoadMap();
}
function cleanUp()
{
if (map != null) map.Dispose();
}
</script>
</head>
<body onload=“startVE();” onunload=“cleanUp();” style=“margin: 0px”>
<div style=“overflow: hidden; width: 100%; height: 100%;” id=“myMap” />
</body>
</html>
You should note that the first script tag specifies the web handler mapcontrol.ashx as the source. Also, note how an instance of VEMap is created using the body’s onload and is eventually disposed of in the cleanUp() function via the body’s onunload. In any event, it’s pretty easy stuff given the results (go ahead and cut and paste that bit of code into an HTML file and view it in Internet Explorer).
What About Desktop Applications?
Obviously, given the architecture and heavy reliance on JavaScript, this stuff was made for web applications. But what about desktop applications that require geocoding of addresses, distance measurements, scheduling based on zones, or routing with turn-by-turn directions? I mean, Virtual Earth would work great if there was just some way to run an HTML page on a VFP form… Oh wait, Microsoft has provided us the WebBrowser control! Just drop that ActiveX control on a form and make a quick call to something like, Thisform.Olecontrol1.Navigate(“file:///C:\VETest.html”). Looks like we’re in business.
The only thing that’s left to do after that is write all of the JavaScript code that interacts with the VE Map Control. Awesome! Except that… erm… you may not know very much JavaScript (even though you’re a kick-butt VFP developer), and you may need VFP code to freely interact with the JavaScript objects (or vice versa), and how the heck do you get JavaScript to read your database where all of your addresses are stored? And… And…
Virtual Earth Wrapper for VFP
OK, so the previous section outlines the thought process that eventually lead to the class library that I built to tackle these problems. While working on a VFP project for a customer that involved VE, I looked at that list of classes and enumerations for the Virtual Earth Map Control version 6.2 and basically thought, “Wouldn’t it be super cool if those were all VFP classes??!!”.
I needed a class library (.vcx) wrapper for VE. This would contain all of the same classes (VFP equivalents) and a few super slick ways to commuicate and convert between VFP and JavaScript. With those in hand it would simply be a matter of hammering out the 5-10 thousand lines of code and comments to make it happen. I won’t go into the particulars of everything I ended up doing in order to make this work (you can look in the class library’s code yourself if you’re interested), but I did end up using the JSON class I created to handle most of the conversions and transport (data interchange) between the two languages.
As you’ll see in the download, the class library wrapper (virtualearth.vcx) is pretty much a carbon copy of the VE classes. So, most of the documentation and code that’s online for VE is of benefit when using it. In addition to the virtualearth.vcx, I’ve also provided a sample project and form (see screenshot below), so you can try this stuff out right away. The sample project is also intended to provide a little more information regarding how to do some of the things that a developer might want to do with VE in a desktop application. I usually find that learning by example is one of the fastest ways to get up to speed on a technology I’m not very familiar with.
Special Thank You
Before I wrap this blog entry up, I want to express my appreciation for Marc Lyon‘s efforts. He provided me early feedback and testing for this class library. His enthusiasm for this project was also one of the driving forces behind my completing it as fast as I did.
I also want to thank my client (you know who you are) who has been kind enough to allow me to share this work with the VFP Community. You’re about as generous and kind as they come.
What’s Next
Just have fun with Virtual Earth in VFP and see if virtualearth.vcx is useful to you and your applications. In a perfect world, you’d report back to me with any bugs you find, create some examples of your own and blog about them, or seek to improve the library in some way. If you do improve virtualearth.vcx, I’d appreciate it if you would contact me and share the improvements you’ve implemented. Thank you in advanced to those of you that decide to follow any of the above suggestions.
Until next time… Visual FoxPro Rocks!