What's in a name?

A recent thread on foxforum.com pointed out an interesting question about the syntax of the Visual FoxPro INSERT - SQL command. The VFP Help file gives the syntax for this command as

INSERT INTO dbf_name [(FieldName1 [, FieldName2, ...])]
  VALUES (eExpression1 [, eExpression2, ...])

where "dbf_name" is defined as "the name of the table for appending a new record".

The question raised (and answered) in the forum thread was, can you use an alias for "dbf_name" in an INSERT statement? The answer of course is yes, and most of us probably do so without even thinking about it.

For example, most of us would be perfectly comfortable writing code like this

  CREATE TABLE table1 ( cField1 C(10))
  INSERT INTO table1 ( cField1) VALUES ( "Bob")

which creates a table named table1.dbf and adds Bob to it, as expected. But there's a hidden question lurking here: in the INSERT statement, is "table1" the name of a table or the alias of a work area?

The answer is, it depends. If there is an open work area whose alias is table1, as in the example above, then VFP inserts the record into the table that's open in that work area (which may or may not be table1.dbf, as we'll see in a minute). If there is no open work whose alias is table1, then VFP looks for a table named table1.dbf either already open under another alias, or not open but existing on disk. If it finds table1.dbf, VFP inserts the record into that table.

When a work area's alias is the same as the name of the table that's open in that work area, there's no ambiguity. But if the alias is different than the table name, things may not work as expected. Therefore it's important to recognize when we're using an alias and when we're using a table name.

Consider the following:

  CREATE TABLE table1 ( cField1 C(10))
  CREATE TABLE table2 ( cField1 C(10))
  CLOSE ALL
  USE table1 IN 0 ALIAS table2
  USE table2 IN 0 ALIAS table1

Note that table1.dbf is now open in work area 1 using alias table2, and table2.dbf is now open in work area 2 using alias table1, like this:

--------  -------  ----------
WORKAREA  ALIAS()    DBF()
--------  -------  ----------
   1      table2   table1.dbf
   2      table1   table2.dbf
--------  -------  ----------

So now if we write

  INSERT INTO table1 ( cField1) VALUES ( "Bob")

then the question is, where's Bob? In table1.dbf or in table2.dbf?

The answer is, Bob's in table2.dbf. The INSERT statement can be read as "insert a record into the table that's open in the work area whose alias is table1". The table that's open in that work area is table2.dbf, so that's where Bob goes.

Similarly, if we follow up with

  INSERT INTO table2 ( cField1) VALUES ( "Carol")

then Carol ends up in table1.dbf.

To avoid confusion, we can tell VFP we're specifying a table name instead of an alias by including the file name extension.

  INSERT INTO table1.dbf ( cField1) VALUES ( "Ted")
  INSERT INTO table2.dbf ( cField1) VALUES ( "Alice")

This is unambiguous regardless of any aliases currently in use, so Ted ends up in table1.dbf with Carol, and Alice goes in table2.dbf with Bob. (Which is sort of what happens in the movie, I think. Either that, or all four of them ended up in table3. It's been a long time since I saw that movie...)

It may be helpful to remember that an alias refers to a work area, not to a specific table. In any case, the point is it's important to know when "dbf_name" is an alias and when it's a table name in a VFP INSERT statement.

Tags: Visual FoxPro