FoxPro is a database application programming system that ultimately derives from the dBase heritage. Multi-platform in ages long past, it principally continues today in the form of Microsoft Visual FoxPro, an unsurprisingly windows only system. In the company wiki, I called it a ‘mutli-generational tower of feature creep,’ which I still consider fair, but it’s a little like Perl – you can accomplish most things several ways (and other things only with great difficulty)

I got into FoxPro because our long off-support company ERP system was written in that language, so it has a good interface to the database for extra reporting and data patching. Outside of modifications to support company growth, we have had to patch the database – many-year-old version of the ERP product we have comes from a phase where the company admits it was putting bullet points over stability. The database system is also based on shared file access; FoxPro has methods to handle transactions, but they require manual (i.e. error prone) use, and you always have the danger of timing artifacts and duplicate key generation on computers running in parallel.

Besides the obvious reason that its what we had to work with, I find FoxPro useful because it basically a domain specific language for database manipulation. The key feature for me – which so far as I can tell is somewhat unique based on superficial research into other dbase clones – is the mixing of imperative and declarative data access.

Imperative languages are those like C, Perl, Java, etc. where you spell out every step. The dbase style languages had some scoping operators (which I still need to learn to use better) but to do anything really complicated, you start a SCAN loop and iterate over the records doing IF or CASE statements and possibly modifying the table appropriately.

Declarative languages state the goal and let the computer figure out how to do it. (Usually effective use of the systems still requires one to understand the basic strategy it uses to do this, but you don’t have to deal with the details in most cases.) Prolog is probably the most canonical example, but the relevant one here is SQL. FoxPro allows you to freely intermix inline SQL statements with more imperative operations – you can use declarative operations for the hum-drum work (“I want this; don’t bother me with the details”) and then switch to an imperative for the hard cases where an SQL statement would be huge, slow, or obscure.

The key feature of the integration is that a SELECT statement has an INTO CURSOR clause; a cursor is, the vast majority of the time, indistinguishable from a database table. You can iterate, delete, insert, or run more selects to add or remove columns without really caring much whether the table is ‘real’ or not. Actually cursors can be a major speed advantage – the database is usually stored on shared network folder, so the real applications tend to run quite slow – but it’s almost pleasant on a local computer. Once you select a cursors data, it is a temporary file (possibly only in memory) on your local computer and the network isn’t a factor anymore.

And if imperative and declarative aren’t good enough for you, FoxPro (or at least Visual FoxPro) is object oriented as well. Now it’s a slightly kludgy object orientation – all the classes are things ‘text control’, ‘from’, ‘list box’ etc., and you often have to choose one without too much other cruft to base truly independent classes on. Actually, there is an ‘Empty’ class in recent versions, but it also seems to be missing the basic object support structure – init methods and such.

FoxPro is a scripting language, and objects are dynamic as well. You can add or remove properties at run time (methods may be restricted; I’ve not gotten down and dirty with the object system yet.) While programs can be ‘compiled’, being a scripting language allows run time evaluation – you can always run an EVAL, and a ‘&’ notation allows any variable to be expanded inline in order so stitch together a SQL statement or other piece of code that wouldn’t normally be ‘first class’

As you may have guessed from some of the class names, FoxPro is also a ‘fourth generation language’ – it supports building of forms, reports, and other artifacts usefully to make the human interface portion of an application. Just about everything can be built in two ways – I know classes and forms can be built with code (I’ve tried to make a report that way), and all three are built as tables – which can be easily manipulated by database-based language like FoxPro. Typically of course, you use the GUi designers and let the compiler worry about doing something with them, but many of these resource ‘compilers’ are themselves FoxPro programs distributed with the system.

I’ve hardly scratched the surface of course (remember: multi-generational tower of feature creep) And of course, not-so-great things either accumulate or never get improved upon. The syntax frequently feels cumbersome once you step outside the core database operations. The namespace is also fantastically crowded – most any single word marginally relevant to databases is sure to get lit up by the syntax highlighter, and table aliases and columns pollute things further. Table aliases are globally visible – try to use one twice and you’ll either have to close the old one or find a new name. The currently selected table has it’s fields directly accessible as if they were memory variables – and they take precedence over memory variables. You can reference memory as if it was the ‘m’ table (i.e., m.variable) but I’m not (yet) in the habit of using this universally – just the other day I ran across a situation where one report was leaving a cursor open, one of the fields of which obscured the parameter the function for another report, signalling a data type mismatch error (and if the data types had been compatible? *shudder* – track that bug down, I dare you)

Much like Perl, FoxPro does it’s chosen job well enough to overlook shortcomings most of the time. I’ve the crazy idea that building a clean new database language using the best ideas of FoxPro would be an interesting exercise. Of course, it’s also highly impractical – rebuilding a database engine, GUI, report system, IDE, and the rest of the tower of feature creep would be massive effort. I’ve also not done sufficient research to say that something suitable (or nearly so) isn’t already out there, either unappreciated, or just escaping my attention.

Posted Friday, February 23rd, 2007 under Review.

Comments are closed.