SQL - The Original

You taught me language; and my profit on ‘t
Is, I know how to curse: the red plague rid you,
For learning me your language!
—William Shakespeare, The Tempest, 1611–1612

SQL, which stands for Structured Query Language, is a set-based language. Unlike Xbase, which cares about individual records, SQL is interested mostly in groups of records. (The Xbase concept most similar to SQL’s point of view is the FOR clause.) The name “SQL” is read by some folks as a series of letters (“ess queue ell”) and by others as “sequel”—we use either one, depending on the phase of the moon, though we lean more toward the latter these days.

The biggest difference between SQL and Xbase is that Xbase is procedural while SQL is not. In Xbase, you have to tell the computer exactly what to do to get from point A to point B—without step-by-step instructions, Xbase has no clue. SQL, on the other hand, lets you tell it what you want and it figures out how to give it to you. This is most apparent with SELECT-SQL, which lets you indicate what records should be placed in a result set without having to navigate the tables containing them.

In database theory, languages for manipulating databases are divided into two sets: the Data Definition Language (DDL) and the Data Manipulation Language (DML). Despite its name, SQL (like Xbase) incorporates both. The entire SQL language (defined by an ANSI standard) is quite large; FoxPro has supported a subset for many years now, with that subset growing in almost every version. (Supporting only a subset is not a unique FoxPro weakness. In fact, few vendors support the entire standard at any level, and most have proprietary, incompatible extensions as well.)

SQL first poked its way into FoxPro 2.0 with two DDL commands (CREATE TABLE and CREATE CURSOR) and two DML commands (SELECT and INSERT). Naturally, this wasn’t enough for FoxPro users and they immediately started clamoring for more. Visual FoxPro finally added to the set, with the addition of both DDL and DML commands.

The most recently added data-definition command (new in VFP 3) is ALTER TABLE, an extremely powerful command that lets you add and remove fields and indexes, set various properties of fields, and create persistent relations among tables. All this in one command—no wonder its syntax diagram occupies nearly a full page.

CREATE TABLE has been significantly enhanced in Visual FoxPro, too. Its syntax diagram is also pretty hefty.

VFP 3.0 also introduced two new SQL data manipulation commands: DELETE and UPDATE. DELETE is fairly similar to the Xbase version of DELETE, though it has a few wrinkles of its own. (See its entry in the “Visual FoxPro Reference” for the nasty details.) UPDATE is a lot like Xbase’s REPLACE, though again there are some differences, primarily in syntax. What makes these two commands such welcome additions is that you can use exactly the same commands on FoxPro data and on data originating from other sources. Besides, all those folks who’ve been writing SQL code in other languages really want to do it their way.

VFP 5 didn’t add any new SQL commands to the language, but it seriously enhanced our old favorite, SELECT. Queries offer the chance to lay out joins explicitly and to use the various flavors of outer joins. VFP 5 also gave us the ability to limit SELECT’s result set by number or percent of matching records, though this isn’t quite as powerful as it sounds. VFP 7 added to SELECT again, finally giving us the ability to make the cursors it creates read-write (see below for an explanation of “cursors”).

So Which One Do I Use?

In theory, you could do all your data definition and manipulation using only FoxPro’s SQL commands. Or you could ignore the SQL commands and use only the traditional Xbase commands. We know people who prefer either approach, including some who’ll zealously campaign for their chosen method.

We don’t recommend either, though, for two reasons. Some things are easier to do in Xbase while others are easier in SQL. (And some work best with a marriage of the two.) If you’re using one approach and your code starts to seem convoluted, step back and see if you can use the other. For example, if a process takes five successive complex queries, stop and think about whether you can do it by setting relations instead.

The second reason we can’t arbitrarily recommend one approach over the other is speed: Some things are faster in Xbase; others are faster in SQL. There’s really no way to tell which approach is faster without testing. Any time you can think of both procedural and SQL solutions, you should probably test both solutions to see which runs faster. Sometimes, the results will surprise you.

Is a Cursor Someone Who Uses Bad Words?

In addition to tables, SQL works with data in cursors. The term “cursor” is shorthand for “CURrent Set Of Records.”

In FoxPro, a cursor is similar to a table, though it has some differences. The most important difference is that cursors are temporary. When you close them, they disappear into thin air, never to be seen again—at least not until you create them again from scratch.

There are three ways to actively create cursors in Visual FoxPro. As you’d expect, the CREATE CURSOR command creates cursors. You list the fields you want and their characteristics, and FoxPro does the rest, thoughtfully leaving the new cursor open in the current work area.

SELECT-SQL also can create cursors to hold query results. Cursors created this way are read-only, unless you explicitly make them read-write. (The simple approach to making cursors read-write was added in VFP 7; in older versions, there is a trick for this. USE the cursor AGAIN in another work area.)

The VFP-only method for creating cursors is to open a view. By definition, all SQL views use cursors. When you issue USE <viewname>, the underlying query is executed and the results put in a cursor. Views may be updateable, depending on how they’re defined.

In addition to these three types of cursors, there’s also a tendency in VFP to refer to any open table, cursor, or view as a cursor—hence, functions like CURSORSETPROP().

Work Areas? What Work Areas?

Oh, why don’t you work
Like other men do?
—Anonymous, Hallelujah, I’m a Bum, c. 1907

Unlike Xbase, SQL has no concept of a current work area. Each SQL command, instead, indicates what table it’s working on. Tables are opened automatically if they’re not already open and, when appropriate, they’re secretly reopened.

Even though they don’t officially recognize work areas, several of the SQL commands do change the current work area. Both CREATE commands (CREATE CURSOR and CREATE TABLE) leave you in the previously empty work area containing the newly created object. If you send query results to a cursor or table, SELECT makes the work area containing the results current.

INSERT INTO, DELETE and UPDATE don’t change work areas, but they do open the table if it’s not already open.

Who Needs Consistency Anyway?

Consistency is contrary to nature, contrary to life.
The only completely consistent people are the dead.
—Aldous Huxley

The seven SQL commands available in Visual FoxPro have no fewer than three different ways of referring to the table (or tables) they operate on.

SELECT and DELETE use a FROM clause to list tables. INSERT uses INTO. The others don’t have a special keyword—the table name simply follows the command itself (as in CREATE TABLE Inconsistent …). We can’t blame Microsoft (or even Fox) for this one, though, because the commands do conform in this respect to the ANSI standard for SQL. This is a good place to point out that, like most other SQL database systems, while FoxPro’s SQL commands are ANSI-compliant in some respects, they deviate in others. The bottom line is, if you’re familiar with ANSI SQL, you’ll want to check the FoxPro manuals or Help before you start coding.

WHERE, Oh WHERE, Can My Data Be?

Three of the four DML commands in FoxPro’s version of SQL use a WHERE clause to determine which records are affected. WHERE is essentially identical to the Xbase FOR clause, with minor variations in string comparisons (see SET ANSI and SET EXACT) and operator syntax. In DELETE and UPDATE, WHERE filters the table. In SELECT, WHERE both filters the data and can specify join conditions used to combine records from multiple tables (but see “Won’t You JOIN me?” below).

The WHERE clause can contain any valid logical condition. To optimize these commands, make sure tags exist for each expression involving a field that appears in the WHERE clause.

Won’t You JOIN Me?

Unlike VFP’s other SQL commands, SELECT can involve multiple tables directly. (Other commands may include multiple tables through sub-queries, but not in the listing of tables.) When there is more than one table, you need to indicate how to match up records from the different tables. In VFP 3 and earlier versions of FoxPro, you did this in the WHERE clause along with your filter conditions. Beginning in VFP 5, you can also use the ANSI JOIN syntax to specify how to combine tables.

The advantage of the newer syntax is that it lets you specify outer joins (where unmatched records in one or more tables are included in the result) as well as the inner joins that WHERE lets you include. For details on the JOIN syntax, see the entry for SELECT-SQL in the “Visual FoxPro Reference.”

No Room at the Top

Set thine house in order.
—The Second Book of Kings, 20:1

Because SQL is set-based, certain concepts fundamental to Xbase don’t have exact analogues in SQL. SQL usually doesn’t care where a record is in the table; there’s no such thing as a SQL record number. When you manipulate records with SQL commands, you mostly do it based on the records’ contents, not their positions. SQL has no NEXT or REST clauses.

Similarly, FoxPro’s version of SQL doesn’t know from first and last (or, as we call them in Xbase, TOP and BOTTOM). Before VFP 5, there was no way to say “the first 10” or “the most recent 25.” VFP 5 added TOP n and TOP n PERCENT syntax to SELECT, but its utility is quite limited—the clause applies to the overall query results, not to each group within the result. When you need “the first 10” or whatever in each group of records, you have to use a hybrid approach that combines SQL with Xbase’s record number to produce the desired results. It requires a multi-step process and you still need enough space to select all the matching records first. (Tamar has published the solution to this one elsewhere, most recently in the November ‘99 issue of FoxPro Advisor.)

Fix ‘Er Up

Debugging SQL commands tends to be a little trickier than debugging Xbase or OOP language. That’s because a single SQL statement may be a whole page of code. Finding a syntax error in a 30-line query is like the proverbial search for a needle in a haystack.

So what do you do? The first step is to check for the obvious, especially if you’ve just been editing. Make sure every line but the last ends with a semicolon, make sure there are commas between all the fields in the field list (and not after the last field), check for typos, and so forth. If all that fails to turn up the problem, start taking the command apart: Remove and comment out (you’ll have to use some cut-and-paste here since VFP can’t handle commented lines in the middle of a command) whole clauses or parts of clauses until you figure out what the offender is. Another good approach is to avoid the problem in the first place by building up your SQL statements, especially queries, a little at a time. Then, when an error occurs, you’ll know it’s related to the last thing you added.

The other issue you can run into is performance—commands, especially queries, that don’t run fast enough. Check out “Faster than a Speeding Bullet” in “Franz and Other Lists” for overall performance tips, and look at SYS(3054) in the Reference section for a way to find out exactly what’s bogging your query down.