DBF, FPT, CDX, DBC

DBF, FPT, CDX, DBC—Hike!

Let's look at the record.

Alfred E. Smith, 1928

The transition from the FoxPro 2.x model of the universe to the Visual FoxPro mindset was pretty revolutionary on a lot of fronts. While we cringe to use the hackneyed and overused term, the introduction of databases in Visual FoxPro was a major paradigm shift and one that took some getting used to. The introduction of the database container, local and remote views, vast improvements in client-server connectivity, and a slew of new data types, commands, and functions was mind-boggling. We'll try to give you an overview of the data changes here, and refer you to areas where you can get more information throughout the book.If you're coming from an Xbase background, you might think you're encountering some NewSpeak in Visual FoxPro, but it's all for a good cause. Individual DBF files used to be referred to as "databases" but now, a better term, that used by Visual FoxPro, is to call individual DBF files "tables" and the new DBC a "database container" or just "database" for short. While it's frustrating to try to change terminology you've been using for years, we've found it does lead to greater understanding of the new scheme of Visual FoxPro. Not only that, but the new names bring us in line with the rest of the relational database world.

The Database Container

A database container (DBC file) is a Visual FoxPro table that links together the tables, indexes, views and special code associated with your data. When a table is added to a database, many new features become available. You can define code to validate individual fields or entire records before they are saved, each with its own individual error message. A default value and caption can be specified for each field. Database containers also allow you to specify persistent relations, saving hours of tedium in system development. These relations form the basis for enforcement of relational integrity (RI) at the database level, through the use of stored procedures and record-level triggers. In addition to RI enforcement, our own program code can be triggered on the insertion of a new record, the updating of an existing record, or the deletion of a record. Program code can also run at the field level, specifying default values, validation rules and error messages. Individual fields can have default captions and control classes assigned to them.All of these features are controlled by the database engine itself, with no need for the developer to write supporting code. Even cooler, all of these features are available when directly editing the table, like in a Browse. This offers far greater reliability and integrity for the data we use.

Better Tables

Cool new features were added to the DBF table, too. A table can be "free," not associated with a particular database, or it can be "contained" within a DBC. This "containership" is not the same as, say, Access's monolithic MDB files—no data from the tables is actually stored within the DBC, just links to the tables, views and other elements. This structure is like the Project Manager, which holds references to source documents but not the documents themselves. Whether free or contained, tables gained new features: several new field types, the capability to store NULL values within fields, and the ability to flag character or binary data in fields not to be translated between different language versions of Visual FoxPro.

The Database Container

Xbase programmers had gotten into a rut. In every application, in every screen, in every routine, they had to code the same functionality. "Customer.DBF is related to Orders.DBF by the Cust_ID field." "Customer mailing address state needs to be validated against the States.DBF table." "Every time a record is added to the AR table, run the routine to post an audit trail record." If the developer forgot one of these rules in just one place in an application, the consistency of the data was in jeopardy and a long, arduous troubleshooting session was sure to result. To compound the error, it was possible that these rules were in place in all programs, but a database change made interactively by a programmer or user could still be responsible for bad data. What a headache!Visual FoxPro's competition, the Relational DataBase Management Systems (RDBMSs) and Client-Server systems, developed solutions to these problems. RDBMSs made leaps and bounds in functionality, while the basic data model of Xbase hadn't changed since the development of dBASE. With the release of Visual FoxPro, Microsoft determined it was time for an improvement in the basic model. Visual FoxPro introduced some new terminology and some incredible power in the form of the new DBC databases. As we mentioned above, we used to call each individual DBF file a database, but this terminology is not really consistent with most other database management systems. Besides, those folks with their poky-slow RDBMSs would sneer at us: "That's not a real database—where are the persistent relations? Relational Integrity? Security? Triggers? Stored Procedures?"It's in there.Visual FoxPro databases contain and support:

Tables—DBF files specially marked for use only within a database.
Long, long, long table and field names (128 characters!).
Field-level validation functions, default values, error message text and comments.
Record-level validation.
Separate trigger functions for insert, update and delete.
Primary and candidate keys.
Persistent relationships—define a relation once and it is preserved.
Local views—updateable cursors spanning multiple tables.
Remote views—easy access to data stored within other DBMSs.
Stored procedures—common method code accessible from all procedures within the DBC.
Database events—events that fire when something is done to the structure of the database or to one of its contained members.

Tables added to a DBC can have long names associated with the table itself and its constituent fields. These names are stored in the DBC.Triggers and stored procedures are Visual FoxPro code fragments that run automatically when their associated event occurs. Field-level procedures fire when a field is modified. Record-level procedures fire when an attempt is made to commit a new record to a file, update an existing record, or delete a record from a table.Primary and candidate keys both uniquely distinguish a record from all others. Indexes designated as either of these don't accept duplicate values, but instead generate an error message. Persistent relationships can be defined once within the DBC, and are then available throughout the system. When tables engaged in a persistent relationship are brought into the data environment of a form or report, the relationship is brought with them. While the relationship can be modified in each place it exists, if the most common relationship is defined within the DBC, far less work is needed in each subsidiary form and report to put a system together.After creating tables and their relationships within the DBC, run the Relational Integrity Builder by issuing the command Modify Database and then choosing Edit Referential Integrity from the Database menu or the context menu. The Relational Integrity Builder appears. When you are done and choose OK, the builder regenerates the RI stored procedures needed to ensure relational integrity in the database.Views are cursors on warp speed. A view is defined like a SQL SELECT, allowing you to join multiple tables (and use their persistent relations, if set), select the output fields, and order and group records just like a SELECT. But views are really cool because they can be updateable, so changes made to the records in the resulting cursor can be "written through" onto the tables. This has fantastic implications for manipulating and updating data. Remote views have all the coolness of the local views just mentioned, with a simple but profound variation—the data is not Visual FoxPro data. Using ODBC, the Connection Designer, and the View Designer, Visual FoxPro has become, in one fell swoop, one of the most powerful clients in a client-server relationship. Even cooler, because both local and remote views can be defined, a Visual FoxPro client-server system can be designed, prototyped and tested on local data stores, and converted to remote data storage when ready to go into production. This is a big attraction to developers who want to work on client-server systems on their client site, but don't want or need to set up servers in their own offices. For more information on using client-server architectures, see "Your Server Will Be With You in a Moment."Had enough views yet? There's one more variation on the theme: offline views. An offline view is defined as any other, but it allows an operator to actually "check out" a set of records, make changes, and then re-synchronize with the original source. This is a cool feature for "road warriors," anyone who needs to disconnect from the main network, go on the road, do some work, and then reconnect and update the main files.Database events, added in VFP 7, let you control the structure of a database the way rules and triggers let you control the content. Although they're not turned on by default, once you turn them on for a particular DBC, VFP fires events whenever you perform pretty much any action on the database container or the tables, views, relations and connections it contains. For example, when you create a table, the BeforeCreateTable and AfterCreateTable events fire. When you remove a view, the BeforeDropView and AfterDropView events fire. By putting code into the procedures called by these events (named "DBC_" followed by the event name, such as "DBC_BeforeCreateTable"), you can perform additional actions when these events occur. For example, you could update an audit log whenever a table's structure is modified, or update a security log whenever a user opens a view or table. Even better is the ability to disallow the action by returning .F. in the "before" event. This allows you to do things such as preventing unauthorized users from opening the payroll table and seeing who makes how much money (or worse, changing salaries) or from making changes to stored procedures. See "Mr. Database Event, You're Fired!" later in this section for some ideas of things you can do with database events.Stored procedures allow programming code for the triggers, rules and database events, as well as any other associated code, to be stored within the DBC. For example, the Referential Integrity Builder's code for performing cascaded updates or deletes is placed in the stored procedures area. This is also where you can place code that otherwise might be a "calculated field," an item wisely not supported within the data model, or a UDF. For example, the routine to format a postal code based on the country is a routine you might include within your main DBC to produce the effect of a calculated field on your customer table. Rather than requesting Customer.PostalCode, you could specify CustPostCode(Customer.PostalCode) and get the built-in code to do the work. This has advantages over a stand-alone UDF because it's always available when the data is. The downside is that this code is only available for the current SET DATABASE database, so stored procedures are not a replacement for stand-alone or procedure libraries. They are, however, still a great place to store database-specific code.One issue to be aware of with stored procedures is that, while VFP does a great job of locating them, it doesn't make the database containing the procedure the current one. (For example, if the DefaultValue property of a field in a table belonging to a database that isn't the current one calls a stored procedure in that database, VFP will run the proper stored procedure, even if a procedure with the same name exists in the current database.) That is, DBC() returns the name of the current database, which isn't necessarily the one the procedure is in. This may have implications for your code. For example, say a stored procedure called from the DefaultValue property for a primary key field in a table opens the NEXTID table, which contains the next available primary key to use. If that table doesn't exist in the currently selected database, the USE command will fail. The solution is to either specifically SET DATABASE to the desired database in the stored procedure (be sure to SET DATABASE back to the previously selected one at the end of the procedure) or to include the database name in the USE command (USE MYDATABASE!NEXTID).

Compatibility—The Good, the Bad and the Ugly

Consistency is the last refuge of the unimaginative.

Oscar Wilde

The best—and the worst—feature of Xbase is the cross-compatibility (sometimes) of the basic file structures. It's great news if you're trying to tie together an analysis piece in a spreadsheet with a data-entry product written in a front-end language and output via a third-party reporting tool, but it's hell when you're trying to hide the payroll figures.Cross-compatibility has allowed the development of "clone" products, which perform many of the functions of the original, but either extend the functionality or improve the performance. (Fox was originally an example of both—see "It's Always Been That Way"). In addition, this compatibility allowed the development of a third-party marketplace where add-on products—database repair tools, viewers and translators—could flourish.The flip side of compatibility is that, like the cop when someone else is speeding, it's never there when you need it. DBFs created by one product (such as FoxPro 2.x) might not be readable in another (FoxBASE+) because of enhancements to the language, and backward (but not forward) compatibility. VFP 7 continues this trend: If you turn on database events for a database, that database and its tables are no longer accessible in earlier versions of VFP or in the VFP ODBC driver (they are accessible with the VFP OLE DB provider, however). Fortunately, you can turn database events back off again, making them accessible once more, so it's not a drastic one-time-only thing.

We will not repeat the file extensions table, found under "File Extensions and File Types" in Help. However, we do recommend you use the Help and a good introductory FoxPro book to review the formats of PJX/PJT projects, SCX/SCT screens, FRX/FRT reports, and LBX/LBT labels.

There are a few file extensions and structures you might not see described elsewhere, but you should be aware of them. If you are called upon to examine (or exhume) applications written in another Xbase language, you might see and need to examine these files:

Extension	Purpose
BAK	Backup files—sometimes DBFs, sometimes something else.
DBT	dBASE III (and later) memo files.
NDX, IDX	Clipper or FoxBASE/FoxPro stand-alone indexes, compact or non-compact.
MDX	dBASE IV compound indexes.
NTX, NDX	Clipper and dBASE indexes, respectively.

A Rose by Any Other Name

You might think a DBF is a DBF, but alas, this is not so. Tables created with older products, such as FoxBASE and dBASE III, have the DBF extension, but may not be fully compatible with Visual FoxPro. Visual FoxPro DBFs cannot be read with these products, either. The first clue you may get is an error when attempting to USE a table. FoxPro determines this by reading the first byte in the DBF file (see the SYS(2029) function). If the byte is wrong, the dreaded "Not a table" message appears.Visual FoxPro continues the tradition of backward-compatibility, since it can read DBF files created with earlier products. However, in order to facilitate linking with DBC database containers, Visual FoxPro 3.0 introduced changes to the DBF header structure that make Visual FoxPro DBFs unreadable with earlier products. If you need to "regress" a Visual FoxPro table to an earlier format, you can use the TYPE FOX2X keywords with the COPY TO command.

Header Structure, bytes 0 – 31
Location	Meaning
0	DBF Type, reported by SYS(2029).
1, 2, 3	Date last updated as YY, MM, DD. See LUPDATE(). Yes, astute reader, this is a Y2K problem, but was resolved in VFP 6.
4, 5, 6, 7	Number of records, returned by RECCOUNT().
8, 9	Location of first data record, also HEADER().
10, 11	Record length, returned by RECSIZE().
12 – 27	Unused.
28	Bit 0: Is there a structural CDX? Bit 1: Is there an associated memo file? Bit 2: Is this file used as a DBC?
29	Code page signature. See CPZero.PRG for translation of these values to code page values.
30, 31	Unused.

Field Records: one for each field in the table, each 32 bytes long
Offset	Meaning
0 – 10	Field name, padded with CHR(0).
11	Field type, same values as TYPE().
12, 13, 14, 15	Starting location of field within record.
16	Length of the field (binary), like FSIZE().
17	Decimal length, if applicable.
18	Field-level flags: Bit 0: Is this a "system" (hidden) field? Bit 1: Is this field nullable? Bit 2: Is this field NOCPTRANS?
19 – 31	Unused.

End of table header
CHR(13)	Terminating character to indicate end of field information.
263 bytes	"Backlink" containing the path and filename of the database container that owns this table. CHR(0) if a free table.

The tables above show the internal structure of a Visual FoxPro table. Several VFP traits are of key interest. Byte 0, the so-called "signature byte," is always 48 (hexadecimal 0x30) for Visual FoxPro tables. Byte 28 was used in earlier FoxPro versions to designate that a CDX file was used by storing a CHR(01) in that location. This has been expanded in VFP to include whether a memo file is used for memo or general field information and also whether the table is a database container. This is accomplished by adding 2 for memo fields and 4 for DBCs. A similar pattern of "bit flags" occurs for each field record stored in the header. Byte 18 of each field record contains three bit flags: Bit 0 indicates whether the field is displayed or is a hidden ("system") field; bit 1 flags whether the field can store null values; and bit 2 determines whether the field is translated to the current code page or treated as binary data.

Nulls

What is man in nature? Nothing in relation to the infinite, everything in relation to nothing, a mean between nothing and everything.

Blaise Pascal, Pens�es, 1670

How many answers can there be to a simple question? How about three? "Yes," "No," and "I Don't Know." For years, Xbase had no good way to store the "I Don't Know" answer for many fields. Logical fields were restricted to .T. and .F. A character field left empty was indistinguishable from one for which the value was unknown. Numeric values were treated as zeroes if they were not filled in.So what, you ask? Who needs them? Consider this case: You ask 10 septuagenarians their age. Eight answer: 72, 78, 73, 76, 70, 79, 72, 74. Two refuse to answer. You plug your eight answers into a field named AGE and issue the command CALCULATE AVG(AGE) for the 10 people. What's your answer? 59.4. Now, who's going to believe that? If, instead, you REPLACE AGE WITH .NULL. for the two people who refused to answer, your average is a far more believable 74.25. Nulls are very useful in many statistical calculations.Nulls can be used in any field designated as nullable. Fields can be made nullable by checking the box at the right edge of the field description within the Table Designer, or by using the NULL keyword in CREATE TABLE or ALTER TABLE. Fields in remote views from server systems can be defined as nullable by their DBMS server, and this carries over into the view.Understanding how nulls work within calculations is important. If any of the fields or memory variables within your system is allowed to take on the null value, you must anticipate how this value can affect calculations, functions and processing within your application. The ISNULL() function can be used to test for the presence of a null value, and the NVL() function can substitute another value (such as zero or a blank) for a value found to be .NULL. Why can't we just test a variable to see if it is equal to .NULL.? This gets back to the concept at the beginning of this section: .NULL. means "I don't know." What's the value of a mathematical expression involving .NULL.? I don't know—.NULL. One half of .NULL.? I don't know—.NULL. Is .NULL. equal to .NULL.? I don't know—.NULL. The first three characters of .NULL.? I don't know—.NULL.Null values "propagate" through functions—if any value is not known, the result can't be known. We can't test an unknown value against another value (even another unknown) and know if they're equal. Hence the need for an ISNULL() function.Because null values can propagate through the calculations of a system, we discourage their indiscriminate use. Carefully bracket your use of them to test and properly handle the null values. When a null value is appropriate for the data design, nothing else will do. We applaud the fact that Visual FoxPro has been endowed with this cool feature.An interesting feature is how nulls are actually stored within a table. Since many of the field types can hold any value from 0x00 to 0xFF in each byte, it is impossible to store null values within the current disk space allocated for each field. Instead, Microsoft created a new field, called _NullFlags. _NullFlags is a "system" field (bit 0 of byte 18 in the field record portion of the file header is set to 1). This field contains a bitmap, one bit for each nullable field in the table, in the physical order of the fields in the table. If a field is to be null, the associated bit is set on (to 1). This seems awfully kludgy to us, but it does work, and reliably: Physically rearranging the order of fields in the table, or programmatically using only some fields with SET FIELDS TO doesn't seem to trip it up. There doesn't seem to be any way within the language to access _NullFlags directly (our hacker instincts made us try), which is probably all for the best. However, having hidden, system fields in a table, which don't show up in a DISPLAY STRUCTURE, and which can trip up your space calculations (see the reference sections on AFIELDS() and RECSIZE()) is not what we consider a great leap forward. In this era of "what do you know and when did you know it," a little more in the way of full disclosure should be expected.

Take a Memo, Miss Jones

Memo and general fields store their data in a separate file, the FPT. It makes sense, since memo field data can be of varied lengths, from an empty note to a monstrous embedded WinWord dissertation. Storing this data in a separate, non-fixed-length format should minimize the use of disk space. However, poor tuning or cleanup practices can lead to severe memo field bloat. Here are two tips to minimize the "out of disk space" blues.Each time data is written to a memo field, new blocks are added to the end of the FPT, data is added to them, and then the pointer contained within the memo header is updated to point at the new data. The old data remains within the file, and the space it occupied is not reclaimed. Over a period of time, memo fields can grow beyond control without containing that much information. This is the dreaded "memo field bloat." The ADDITIVE clause of the REPLACE command does not alleviate this, it just makes it easier to tack one more sentence onto the end of a long memo—internally, the same process occurs.In development, you can reclaim the space with the PACK MEMO command. This packs the file in place, replacing the memo field with a far more compact one. However, as we discuss in "Commands to Use Only Interactively," the PACK command leaves the developer in the dark if something goes wrong in mid-flight. See that section for suggested work-arounds.VFP provides the SET BLOCKSIZE command to allow you to tune and optimize your use of memo fields. BLOCKSIZE accepts a numeric argument: Passing it 1 through 32 creates blocks of 512 bytes times that number; a number greater than 32 creates blocks of that number of bytes. A newer option, SET BLOCKSIZE TO 0, stores the memo blocks as individual bytes, rather than as blocks. It seems to us that this method wastes the least "slack space" at the end of each memo, but might in some circumstances lead to greater overhead in processing and reading millions of teeny little blocks. We're not sure where the breakpoint is between the speed of I/O and the speed of the processor overhead, and like many other benchmark items, we encourage you to try it in your environment with your machines and data, to see what works out best for you.dBASE III had a somewhat different method of storing the memo fields (general fields did not exist) in the DBT file. FoxPro can read and write DBT files, but should you choose to COPY a table containing a DBT memo, the new file will have an FPT memo field instead.

But We Speak Icelandic Here

Nothing could be worse than porting an application to a new platform, tweaking all the forms and programs to handle the new (or missing) features, and then discovering the data is unreadable. But this was exactly what happened to many FoxPro developers as they brought their information from DOS to Windows in the 2.5 release. What happened?What happened was code pages. A code page is the translation table that the computer uses to translate each of the characters stored on disk—8 little bits, storing one of 256 different patterns—into the single character we're used to seeing on the screen. While some of these codes are pretty standardized, people who speak any one of the thousands of languages other than English use a different code page to represent their characters. Code pages can be loaded and manipulated in DOS using the NLSFUNC, CHCP and MODE commands. A typical U.S. code page is 437 for DOS and 1252 for Windows.In most database applications, code page translation would be a one-step, pain-in-the-neck translation from the "old" way to the "new" way, but FoxPro supports access from multiple code pages simultaneously. Remarkably, it accomplishes this feat, pretty much transparently, through the use of a code page byte, stored within DBF headers and also stored as part of compiled code.That sounds like the happy end to the story, right? We should all ride off into the sunset now. Well, it's not always that simple, pardner.What happens if you're storing data in a field in some sort of a packed or encrypted format, where you use all 256-byte combinations, and you need to share that data with someone whose code page differs from yours? Well, without any other actions, the other user will read your data and see different numbers, translated courtesy of the Visual FoxPro engine, automatically and transparently. It's not a bug, it's a feature.Luckily, there's a solution to this one. As part of defining a table at creation (see CREATE TABLE) or while maintaining a table (see ALTER TABLE), a field can be flagged as NOCPTRANS, which tells the FoxPro engine "Hands off! Don't mess with this one."Note that the NOCPTRANS flag stored within the table itself is automatically set for Double, Integer, Datetime and Currency fields, even though it can't (and shouldn't!) be set ON or OFF programmatically for these field types. That's because the values in these fields are stored in a binary/packed format, and translation would lead to some awfully funny numbers.

Date Math

Date math is really cool. Amaze your friends, astound your competition and baffle the crowd with your ability to glibly say, "Of course, everyone knows there have been over 280,000 days since the signing of the Magna Carta, and in that time, blah blah blah..." while simply calculating:

? date() - {^1297-03-28}

A note about the curly braces above. One of the more common questions we hear is about these funny looking things, and why expressions such as:

{06/ + ALLTRIM(STR(RAND ()*30)) + /90}

return empty dates. The key to understanding these braces is to understand that they are delimiters, wrapping around a set of characters and giving FoxPro an idea of what's contained inside, but they are not functions with the power to evaluate their contents. Just as double and single quotes delimit a character expression, curly braces designate a date or datetime expression. Use a conversion function, such as CTOD() or DATE(), to evaluate a character function and return a date.The second strange thing most veteran Xbase developers will notice is the prefixed caret and the strange ordering of YYYY-MM-DD. This is the strict date format, stealthily introduced into the product in Visual FoxPro 5.0. In VFP 6, the SET STRICTDATE command provides us with some ability to audit existing code and detect potential Year 2000 compatibility problems. See "Strictly Speaking�" below for more details.There are practical uses for this neat technology, too. Calculating 30-60-90-day aging on an account is a piece of cake. A number of days can be added or subtracted from a date, or one date can be subtracted from another to return the difference in days. The various parts of the date can be returned using the DAY(), MONTH() and YEAR() functions for numeric calculation or CMONTH() and CDOW() functions for character display. Dates can be converted to character format (DTOC()) or from character to date (CTOD()) relatively easily.In Visual FoxPro, dates are stored within tables as eight characters of the format YYYYMMDD. Obviously, this practically limits dates to the range of Year Zero to 9999, but that should be long enough for most of the business processes we hope to model in VFP.A few cautions are in order. In the "good old days," we often extracted portions of a date using substring functions, modified it, and plunked it back into the value, as in the following:

* BAD CODE *
* Calculate the last day of the month for a supplied date dDate
nMonth=VAL(LEFT(DTOC(dDate),2)) + 1  && increment the month
nYear = VAL(RIGHT(DTOC(dDate),2))   && extract the year
if nMonth = 13
  nMonth = 1
  nYear = nYear + 1
endif
* Now create a new date, the first of next month, 
* and decrement it one day
* to get the last date of the current month
return CTOD(STR(nMonth,2)+"/01/"+STR(nYear,2)) -1

Pretty clever, huh? This worked great for many small U.S.-centric companies in the 1980s, but with the internationalization of trade, this code is far too bigoted to make it in the 21st century. The assumptions (and we all know what an assumption does, right?) that the first two characters of a date are the month, the last are the year and the middle is the day, all separated by slashes, are Stone Age logic. Check out the SET DATE command—you can bet that your branch offices in Toronto and London have. Make no assumptions about the internal position of digits within a date. Let's try this again. Trapped in a dBase III world, we could just rewrite the function, preserving the state of SET DATE, SET MARK and SET CENTURY, changing them as needed, dissecting and reassembling a date, and then resetting the SET variables again, but there's a far more graceful way, using newer FoxPro functions:

* Dlast() - Return the last day of the month from supplied dDate
dNewDate = GOMONTH(dDate,1)  && add a month to the date
dNewDate = dNewDate - DAY(dNewDate)  && subtract number of days in month
return dNewDate

On the Other Hand...

You can do some really dumb things with date calculations. The date and datetime field types are really meant for storing contemporary dates and times, and are inappropriate for storing date/time fields in historical, archeological, astronomical or geological time spans. It's overly precise to try to store the start of the Jurassic era in a date field, and in fact, it's impossible to store dates Before the Common Era (BCE) or BC. Since no one really seems to know what time it is, even dates as recent as four centuries ago make the precision of the date math functions questionable.For example, GOMONTH() won't go back farther than the year 1753, the year after England took on the "Gregorian shift" of the calendar, jumping 11 days overnight and adding the bizarre leap-year rules of "every four, except in years ending in 00's, except those divisible by 400." Okay, got it? Sheesh. Star-dates had better be easier than this. So GOMONTH() works for Mozart, but falls apart for Bach.It's not just GOMONTH(), either. Adding and subtracting enough days yields wild results too. For example: {^1999-7-5} – 730246 yields "03/00/0000". Yes, DAY() verifies this date is Day Zero, and YEAR() says Year Zero. Hmmph.Stick with the recent past, present and future, and you should be okay.

It's About Time, It's About Dates...

My object all sublime
I shall achieve in time—
To make the punishment fit the crime.

Sir W. S.Gilbert, The Mikado, 1885

A new field type, datetime, was introduced in VFP 3. While primarily intended as a compatibility feature for ease of use with similar fields in tables in a client-server environment, datetimes offer the intrepid FoxPro programmer some neat opportunities.Datetime is stored as an eight-byte field. Supposedly the first four bytes store the date and the last four store the time. We haven't hacked this one apart, but we'd love to hear from the hackers who have. Like currency fields stored without a unit of measure, we suggest there may be problems of determining just when this time occurred—there is no "time zone" designation. Is this GMT, Eastern Daylight Savings Time, or Bering? If you anticipate dealing with a database with multiple time zones, we suggest you consider a region-to-GMT offset table and store all times as absolute GMT datetimes for ease of calculation.Datetimes, like dates, can be specified explicitly by using curly braces. As we explain above, delimiters don't work as conversion functions, evaluating the expression given to them, but rather just indicate constants. Nonetheless, Visual FoxPro is pretty clever, accepting any of the standard date delimiters (slash, dot or hyphen) and either 12- or 24-hour formatted time, regardless of the settings of SET MARK TO or SET HOURS. The order of the month, day and year, however, must be the same as that set by SET DATE. The only exception to that is the use of the strict date form described above. In that case, the order of the date or datetime is always in the form:{^YYYY-MM-DD[,][HH[:MM[:SS]][A|P]]}That syntax diagram also is a little misleading. It appears that you could supply only an hours component and the datetime would resolve. But, in fact, you get an error message. If you include only the hours component, you must either include the comma separating the date from the time, or follow the hours with a colon to have VFP interpret your datetime constant without error. And a bit of trivia: The smallest expression to generate an empty datetime is {/:}.

Strictly Speaking�

Mere facts and names and dates communicate more than we suspect.

Henry David Thoreau, Journals

Visual FoxPro 5 introduced the idea of "strict" date entry with the cleverly named StrictDateEntry property. The property allows "loose" data entry where we depend upon the machine to interpret the varieties of hyphens, dashes and slashes we throw at it. At the same time, the Fox team added a curveball: a new format for loose StrictDateEntry that allows the data-entry operator to override the preformatted date sequence by preceding the date with a caret. Following the caret, the date is always interpreted in a consistent manner: year, month, day, and, in the case of datetime values, hour, minute and second.This innovation in VFP 5 laid the groundwork for the introduction in VFP 6 of the SET STRICTDATE command. This command, essential for ensuring Year 2000 compliance in code, generates errors at compile time, and optionally at runtime, reporting that code contains dates that can be ambiguous. Since the ordering of day, month and year is determined by SET DATE, both in the runtime and development environments, "constants" (as well as expressions using the date conversion functions like CTOD()) can be interpreted in more than one way. The SET STRICTDATE command lets you flag these variable constants unless they, too, now use the strict date format of caret, year, month, day. For conversion from string to date or datetime, the DATE() and DATETIME() functions have been beefed up.

Float, Double, Integer, Numeric and Currency—What's in a Number

There are a number (sorry) of different fields in Fox, all of which seem to store the same or similar data. The reason for this is primarily backward and sideways compatibility with previous Fox and Xbase products. There are some differences, however...

Float

Seems to be same as numeric. Float exists to allow compatibility with other database products that treated numeric and float fields differently. Visual FoxPro treats them no differently internally.

Double

Always stores as length 8, but allows you to change decimal places from zero to 18. A fixed format used primarily for client-server compatibility, it's manipulated internally the same as any other numeric by FoxPro, but stored differently.

Integer

Integer was probably one of the most useful data types introduced in Visual FoxPro 3.0. Stored in only four bytes on disk, the field has a range from –2147483647 to plus 2147483647. If you need to track whole numbers only, this can be a compact and efficient way to do it. We find these fields to be ideal as primary keys, since they're small and form smaller indexes, and also are easy to manipulate and increment. They're also the fastest way to join tables in a SQL SELECT.

Numeric

A numeric field allows up to 20 numeric digits to be entered, but one space is occupied by the decimal point, if used. Microsoft describes accuracy as 16 digits, but 14 seems closer to the truth. Check this out:

lnNumeric = 98765432109876543210  && Here's 20 digits
* In VFP 3, you'll see these numbers with no 
* digits to the left of the decimal point and
* with exponents of 20. In VFP 5, Microsoft adopted the
* more common syntax of making the mantissa a single digit shown below:
? lnNumeric                       && displays 9.8765432109876E+19
? STR(lnNumeric)                  && displays 9.876E+19
? STR(lnNumeric,16)               && displays 9.876543210E+19
? STR(lnNumeric,20)               && displays 9.8765432109876E+19
? STR(lnNumeric,25)               && displays 9.876543210987639000E+19

Numeric fields are stored in character format, such as ".98765432109876E+20".

Currency

Only one fellow in ten thousand understands the currency question, and we meet him every day.

Kin Hubbard

A currency field is a fixed numeric field, always stored as eight bytes, with four decimal places. These fields are marked by default as NOCPTRANS, since the data is packed. Currency is a funny field type. Just as datetime stores a time without a time zone, currency stores a value without a unit. Also like datetime, this field type was introduced primarily for compatibility with client-server databases. But is this currency in dollars, euros, or yen? An international application needs to know if it's running in Zurich or New Delhi.Like datetime, currency introduces some new functions and delimiters into the language. NTOM() and MTON() convert numerics to currency and vice versa (think "money" rather than "currency"). The dollar-sign delimiter preceding a numeric literal forces the type to currency.Math involving currency and other numerics introduces a new kink. What's the result of multiplying two currency values—a numeric or a currency value? What about trigonometry on these values? We could engage in quite a diatribe on the meaning of unitless and "unit-ed" variables being processed together, but it doesn't really matter—Microsoft has a method to its madness, and while it might be different from what we would've come up with, it works under most circumstances: Basic four-function math (addition, subtraction, multiplication and division) involving currency gives results in currency. Exponentiation and trigonometry yield numerics.

Logical

"Contrariwise," continued Tweedledee, "if it was so, it might be; and if it were so, it would be; but as it isn't, it ain't. That's logic."

Lewis Carroll, Through the Looking-Glass, 1872

Not too much has changed with the logical field type since the FoxPro 2.x days. With the introduction of NULLs, described above, a logical field can contain a third value, .NULL., as well as the standard values of .T. and .F. (Okay, it's true they could contain a fourth state of BLANK, but we strongly argue against ever using it.) Logical fields take up one byte in a table, even though they really only need a single bit. With the data compression Microsoft implemented in double, datetime, currency and integer fields, as well as shrinking the size of the memo and general fields from 10 bytes to four, we're surprised they didn't try to implement some sort of byte-and-offset addressing for storing multiple logical fields in a single byte as well.

Hip Hip Array!

Arrays are not truly a different type of variable, but they are a method of aggregating several values, of the same or different types, into one place. Arrays make it easier to handle things like disk directories (ADIR()), field descriptions (AFIELDS()), and object properties, events and methods (AMEMBERS()). Arrays can be used to hold data on its way to and from tables—SCATTER, GATHER and INSERT all support array clauses. Understanding how to manipulate these arrays, especially how they are referenced by different functions, is an important aspect of Visual FoxPro programming.Arrays come in two flavors—one-dimensional and two-dimensional—distinguished by the number of subscripts supplied with them. However, internally, both array types are stored the same way, and functions that work with one type work with the other as well. This can lead to some confusion. Suppose you create an array:

LOCAL ARRAY aRay[2,5]

We would view this array as a table of two rows and five columns, and on the whole, Visual FoxPro would be willing to go along with us on this. But if we try to locate a value that we know is in the third element of the second row by using the ASCAN() function:

aRay[2,3] = "My value"
? ASCAN(aRAY,"My value")

Visual FoxPro returns 6! Well, what would you expect? 2? 3? Since Visual FoxPro is limited to returning a single value from a function, it returns the ordinal number of the element in the array. We can use the function ASUBSCRIPT() to get the appropriate row and column values:

? ASUBSCRIPT(aRay,ASCAN(aRay,"My value"),1)  && returns 2
? ASUBSCRIPT(aRay,ASCAN(aRay,"My value"),2)  && returns 3

(Actually, starting in version 7, ASCAN() can optionally return the row where it found the value. See the Reference section for details.)Even more interesting, we can just use the single digit returned. The fact is that FoxPro is willing to use any combination of rows and columns we supply to reference an element, as long as we do not exceed the defined number of rows:

? aRay[6]    && displays "My Value"
? aRay[1,6]  && also displays "My value"
? aRay[2,3]  && "My value" again
? aRay[6,1]  && Whoa! Errors with "Subscript is outside defined range"

You can determine the number of rows and columns of an array by using the ALEN() function.You can change the dimensions of an array on the fly by issuing another DIMENSION, LOCAL ARRAY or PUBLIC statement, depending on the scope of your variable. Redimensioning does not erase the values of the array, but it can rearrange them in some pretty funny ways. The values originally assigned to the array in ordinal form are reassigned to the new array with the same ordinal values. This can result in some pretty useless-looking arrays, with values slipping diagonally across the columns. Instead, try out our aColCopy() function (under ACOPY() in the Reference section) for a better way to do this.Many functions also redimension arrays automatically to fit the contents of the function. As a general rule, functions redimension arrays to fit only those values the function returns. Typically, the array is created or redimensioned only if the function has something to put in it. We note in the Reference section where functions don't follow these thumbrules.The array manipulations you'll often want to do are inserting and deleting rows from the array, and you'll probably suspect that AINS() and ADEL() are the functions to do this. Foolish mortal. AINS() does, in fact, create a row of new values (all initialized to .F.), but it does this by pushing the following rows down, until the last falls off the array into the bit bucket. ADEL() reverses the process, causing rows of elements to move up over the deleted one, and leaving an empty row at the bottom. In both cases, a call to DIMENSION or its equivalent before or after the function, respectively, will complete what you need to do. Again, more information on this, and the far less simple column operations, is available in the Reference section, under the associated functions, as well as in the overview "Array Manipulation."

Passing an Array

An array can only be passed to another routine using explicit referencing. That means the array name must be preceded with the symbol "@" when passed as a parameter. Forgetting to append this symbol causes only the first element of the array to be passed; this is one of our favorite programming omissions that drive us mad trying to troubleshoot.An array passed by reference, as we explain in "XBase XPlained" really has only one occurrence in memory, and all changes performed by called routines have their effect on this one array. Caution is called for.

Returning an Array

VFP 7 added the ability to return an array from a function by again using the "@" symbol and the array name in the RETURN statement. However, there are some gotchas with this technique; see RETURN in the Reference section for details.

International Settings: Using the Control Panel

Henry IV's feet and armpits enjoyed an international reputation.

Aldous Huxley

There's a wonderful though little-used resource for international settings—dates, times, currency, etc.—available through the Windows Control Panel, in the Regional Settings applet. These dialogs are available to your users, and you don't have to maintain the code! What you do have to do is check to see if your users have modified them. Check out the Registry under HKEY_CURRENT_USER\Control Panel\International and modify the behavior of your application appropriately. See SET SYSFORMATS for more information on using the user's Windows settings.

General Fields: Object Linking and Embedding

I drink to the general joy of the whole table.

William Shakespeare, Macbeth

General fields are Visual FoxPro's implementation of the technology formerly known as "Object Linking and Embedding," then "OLE," and then "Active" something or other. While the marketeers don't seem to be happy with any name they've thought of so far, the idea of this portion of the implementation remains the same: Provide a portal between FoxPro and some other application (the "OLE Server") and store the information about that link and the data shared here.OLE, er, Active, er, this stuff is no cakewalk. For many of the gory details, see the section "Active Something" as well as the individual reference sections for APPEND GENERAL, MODIFY GENERAL, OLEControl and OLEBoundControl. A couple of cautions here. General fields contain several pieces of information: All contain "display data," and have either a path to data (for linked data) or the embedded data itself. The "display" or "presentation" data is a raw BMP that Visual FoxPro uses to show that the field is occupied. In Word 2.0, this was the big blue W logo. Word 6.0 allowed you to store a representation of the first page of a document. MS Graph showed—surprise!—the graph. But some OLE severs can be a problem. Graphics servers, which store pictures, are usually forced to provide Visual FoxPro with a BMP for presentation data, even if their graphic is in another format (like the far more compact JPG format). This BMP can be HUGE; a large image rendered in 24-bitplanes (roughly a bazillion colors) can take megabytes of space. This space is used even if the data is only linked to the field! Anticipate a large amount of storage space if you choose to link very large or very high-resolution documents. Consider other techniques (such as just storing the path and filenames in a character field, and using a cursor to hold one image at a time) if disk space is a concern.One last note about general fields. A general field is nothing more than a special form of the memo field that contains binary data, including a "wrapper" around the data that tells FoxPro who to call to manipulate this data—the OLE server. When data is called up and changed and saved back to the memo field, new blocks of the memo field are allocated for the new data, and the old blocks are internally marked as not used. These old blocks are never reused. What happens over a period of time is that the memo file will grow and grow and grow. To alleviate this problem, you can consider using the PACK MEMO command to shrink the file (but only after reading the cautions in "Commands Never to Use") or use the equivalent COPY/SELECT, RENAME, DELETE routine to refresh the file.

Mr. Database Event, You're Fired!

Database events fire whether you do something visually or programmatically, in a runtime or development environment, through VFP code or through ADO. So, they're sort of like triggers, except they fire in response to things you do to a database or its members rather than the contents of a table. Think of them as "Events for the Data Definition Language" instead of "Events for the Data Manipulation Language." See "Database Events" and related topics in the Reference section for complete details on the events that are available and how each works.Let's explore some ideas for where you might use database events. There are two different kinds of things you can use them for: development tools and runtime behavior.

Development Tools

Database events can be used in a number of tools that can make development easier and more productive. Examples include enforcing standards, handling renamed objects, and team development.Your organization might have standards for naming tables, views and fields (for example, perhaps all tables in the Accounts Receivable database should start with "AR", the first character of all field names must represent the data type or be a four-letter abbreviation for the table, and so on). You may also require the Comment property of every table, view and field to be filled in. Rather than writing an auditing tool you have to remember to run, you could create database events that automatically warn if the standard isn't followed. The following events are all candidates for this: AfterAddTable, AfterCreateTable, AfterModifyTable, BeforeRenameTable, AfterCreateView, AfterModifyView, BeforeRenameView, AfterCreateConnection, AfterModifyConnection, and BeforeRenameConnection.While changing the name of a table is easy, it isn't so easy to track down every place the former name is used. Stored procedures, forms, reports and PRG files can all reference the former table name. You can put code in AfterRenameTable (as well as AfterRenameView and AfterRenameConnection) to open a project, go through all files in the project, look for the former name (the cPreviousName parameter passed to the event contains this), and either replace it with the new name (contained in the cNewName parameter) or at least print the locations or add them to the Task List so a developer can make the changes manually. Because a database might be used by more than one project, you might have to provide a way to track which projects to process.Handling renamed fields and indexes is trickier; because AfterModifyTable and AfterModifyView don't tell you what changes were made, you have to store the previous structure somewhere (such as in a cursor in BeforeModify events or in metadata), and then in the AfterModify events try to figure out what happened. It's not easy; a renamed field looks no different than if you were to delete one field and add another.When anything in the database changes (such as tables or views being added, removed or altered), database event code could automatically send an email to every member of the development team informing them of the changes. You could also log who changed something and when, and even prompt the user to enter a short comment about the change.Besides these serious uses, imagine the fun you can have with your fellow developers when you put code in various "before" events that returns .F. to prevent them from doing something or makes fun of them in some way (think of the French knight mocking King Arthur in "Monty Python and the Holy Grail"; sound files are available for download from various Web sites). Sit back and watch them try to alter the structure of a table, remove a table, and so on. For even more fun, make the events time-specific, such as only between 3 p.m. and 4 p.m. on Thursdays. April 1 is a particularly good day to wreak havoc!

Runtime Behavior

Database events can also be used in a runtime environment to provide functionality VFP developers have requested for years. Some uses include table and database security and hacker prevention.Returning .F. from BeforeOpenTable prevents a table or view from being opened. Obviously, you don't want to do this for every table and view under all conditions (otherwise, the database would be rendered useless), but opening tables based on user security may make sense for some applications. Here's an example:

PROCEDURE DBC_BeforeOpenTable(cTableName)
IF UPPER(cTableName) = "PAYROLL"
    RETURN gcUserName == "ADMIN"
ELSE
    RETURN .T.
ENDIF

This code assumes that a global variable named gcUserName contains the name of the logged-in user, and prevents anyone but ADMIN from opening the PAYROLL table.As with table security, an entire database can be secured by conditionally returning .F. from the OpenData event.Sometimes, we need to prevent someone from altering the structure of a table or view. We're not so worried about malicious hackers as those users who embody the expression "a little knowledge is a dangerous thing." Such users could be using a development copy of VFP or ADO via Access or Excel. To prevent this, return .F. in the BeforeModifyTable and BeforeModifyView events.To prevent someone from changing or disabling the events for the DBC (which would allow them to get around everything that database events are providing for you), return .F. in the BeforeModifyProc and BeforeAppendProc events, and in BeforeDBSetProp if the property being changed is "DBCEvents" or "DBCEventFileName." This approach isn't foolproof, since someone with a development copy of VFP can USE the DBC (that is, open it as a table, which is what it really is), modify the code in the Code memo of the StoredProceduresSource record, and then close and COMPILE DATABASE. To prevent this, use a separate PRG file for the database event code, and build that PRG into the application's EXE so it can't be viewed or altered. The downside is that now the DBC can be opened only when the EXE is running, preventing its access from ADO. In some situations, that may not be a bad thing, though.To prevent someone from seeing the code in the stored procedures (for example, because it contains proprietary information), return .F. in the BeforeModifyProc and BeforeCopyProc events.

Conclusion

File storage within Visual FoxPro is similar to earlier versions of FoxPro, but with some powerful enhancements provided by the Database Container. New fields and field capabilities have been added. VFP 7 still retains the trademark backward compatibility, allowing it to read all dBase III and Fox tables. Some compatibility with older versions has been lost, but we feel the benefits of the new features far outweigh the limitations, and that workarounds are available for most of the incompatibilities.

Back to Table of Contents