DBC file preprocessor. DBC files for different models of the same brand have a lot of overlap. Therefore, we wrote a preprocessor to create DBC files from a brand DBC file and a model specific DBC file. The source DBC files can be found in the generator folder. After changing one of the files run the generator.py script to regenerate the output.

• Db File Viewer Free Download
• Free Dbc File Viewer
• Free Can Dbc File Viewer Free
• Can Dbc File Format
• Can Dbc File Example

DBF, FPT, CDX, DBC—Hike!

Let’s look at the record.
—Alfred E. Smith, 1928

Free DBF Viewer supports conversion between the specified types of databases (TXT, XLS, CSV, TAB, HTML, XML, DBF) and exports to Excel and plain text. Free DBF Viewer has a Windows Explorer-like. With the this new format in memory of the editor select the CAN cluster you want to export as DBC file, and click write to ' export to DBC.' You have to double check if you do not lose some messages or signals from the original file. Using this method I got a usable dbc file with all the signal and frames from autosar arxml file. Free Can Dbc File Viewer. Before moving on, note that J1939 is a bit special in regards to the CAN DBC file format:. The ID is extended 29 bit - in DBC context, this means that the the leading bit of the ID is “flipped” and needs to be re-flipped. Secondly, the relevant ID is the PGN, i.e. A sub part of the CAN. File Viewer Lite is a free universal file viewer for Windows. This one too lets you view files of different types and formats, such as documents, media files, etc. To be specific, it supports Text files, PDF documents, Spreadsheet files, Audio/Video files, Image files, Camera Raw files, Archive, Emails, Source Code, and many more.

The transition from the FoxPro 2.x model of the universe tothe Visual FoxPro mindset was pretty revolutionary on a lot of fronts. While wecringe to use the hackneyed and overused term, the introduction of databases inVisual FoxPro was a major paradigm shift and one that took some getting usedto. The introduction of the database container, local and remote views, vastimprovements 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 ofthe data changes here, and refer you to areas where you can get moreinformation throughout the book.

If you’re coming from an Xbase background, you might thinkyou’re encountering some NewSpeak in Visual FoxPro, but it’s all for a goodcause. Individual DBF files used to be referred to as “databases” butnow, 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 changeterminology you’ve been using for years, we’ve found it does lead to greaterunderstanding of the new scheme of Visual FoxPro. Not only that, but the newnames bring us in line with the rest of the relational database world.

The Database Container

A database container (DBC file) is a Visual FoxPro tablethat links together the tables, indexes, views and special code associated withyour data. When a table is added to a database, many new features becomeavailable. You can define code to validate individual fields or entire recordsbefore they are saved, each with its own individual error message. A defaultvalue and caption can be specified for each field.

Database containers also allow you to specify persistent relations, saving hours oftedium in system development. These relations form the basis for enforcement ofrelational integrity (RI) at the database level, through the use of storedprocedures and record-level triggers. In addition to RI enforcement, our ownprogram code can be triggered on the insertion of a new record, the updating ofan existing record, or the deletion of a record. Program code can also run atthe field level, specifying default values, validation rules and errormessages. Individual fields can have default captions and control classesassigned to them.

All of these features are controlled by the database engineitself, 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 aBrowse. 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 tablecan be “free,” not associated with a particular database, or it canbe “contained” within a DBC. This “containership” is notthe same as, say, Access’s monolithic MDB files—no data from the tables isactually stored within the DBC, just links to the tables, views and otherelements. This structure is like the Project Manager, which holds references tosource 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, andthe ability to flag character or binary data in fields not to be translatedbetween different language versions of Visual FoxPro.

The Database Container

Xbase programmers had gotten into a rut. In everyapplication, in every screen, in every routine, they had to code the samefunctionality. “Customer.DBF is related to Orders.DBF by the Cust_IDfield.” “Customer mailing address state needs to be validated againstthe 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 forgotone of these rules in just one place in an application, the consistency of thedata was in jeopardy and a long, arduous troubleshooting session was sure toresult. To compound the error, it was possible that these rules were in placein all programs, but a database change made interactively by a programmer oruser could still be responsible for bad data. What a headache!

Visual FoxPro’s competition, the Relational DataBaseManagement Systems (RDBMSs) and Client-Server systems, developed solutions tothese problems. RDBMSs made leaps and bounds in functionality, while the basicdata model of Xbase hadn’t changed since the development of dBASE. With therelease of Visual FoxPro, Microsoft determined it was time for an improvementin the basic model. Visual FoxPro introduced some new terminology and someincredible 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 isnot really consistent with most other database management systems. Besides,those folks with their poky-slow RDBMSs would sneer at us: “That’s not areal 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 withthe table itself and its constituent fields. These names are stored in the DBC.

Triggers and stored procedures are Visual FoxPro codefragments that run automatically when their associated event occurs. Field-levelprocedures fire when a field is modified. Record-level procedures fire when anattempt is made to commit a new record to a file, update an existing record, ordelete a record from a table.

Primary and candidate keys both uniquely distinguish a recordfrom all others. Indexes designated as either of these don’t accept duplicatevalues, 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 apersistent relationship are brought into the data environment of a form orreport, the relationship is brought with them. While the relationship can bemodified in each place it exists, if the most common relationship is definedwithin the DBC, far less work is needed in each subsidiary form and report toput a system together.

After creating tables and their relationships within theDBC, run the Relational Integrity Builder by issuing the command ModifyDatabase and then choosing Edit Referential Integrity from the Database menu orthe context menu. The Relational Integrity Builder appears. When you are doneand choose OK, the builder regenerates the RI stored procedures needed toensure relational integrity in the database.

Views are cursors on warp speed. A view is defined like aSQL SELECT, allowing you to join multiple tables (and use their persistentrelations, if set), select the output fields, and order and group records justlike a SELECT. But views are really cool because they can be updateable, sochanges made to the records in the resulting cursor can be “writtenthrough” onto the tables. This has fantastic implications for manipulatingand updating data.

Remote views have all the coolness of the local views justmentioned, with a simple but profound variation—the data is not Visual FoxProdata. Using ODBC, the Connection Designer, and the View Designer, Visual FoxProhas become, in one fell swoop, one of the most powerful clients in aclient-server relationship. Even cooler, because both local and remote viewscan be defined, a Visual FoxPro client-server system can be designed,prototyped and tested on local data stores, and converted to remote datastorage when ready to go into production. This is a big attraction todevelopers who want to work on client-server systems on their client site, butdon’t want or need to set up servers in their own offices. For more informationon using client-server architectures, see “Your Server Will Be With You in a Moment.”

Had enough views yet? There’s one more variation on thetheme: offline views. An offline view is defined as any other, but it allows anoperator to actually “check out” a set of records, make changes, andthen re-synchronize with the original source. This is a cool feature for“road warriors,” anyone who needs to disconnect from the mainnetwork, go on the road, do some work, and then reconnect and update the mainfiles.

Database events, added in VFP 7, let you control thestructure 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 aparticular DBC, VFP fires events whenever you perform pretty much any action onthe database container or the tables, views, relations and connections itcontains. For example, when you create a table, the BeforeCreateTable andAfterCreateTable events fire. When you remove a view, the BeforeDropView andAfterDropView events fire. By putting code into the procedures called by theseevents (named “DBC_” followed by the event name, such as“DBC_BeforeCreateTable”), you can perform additional actions whenthese events occur. For example, you could update an audit log whenever atable’s structure is modified, or update a security log whenever a user opens aview 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 aspreventing unauthorized users from opening the payroll table and seeing whomakes how much money (or worse, changing salaries) or from making changes tostored procedures. See “Mr. Database Event, You’re Fired!” later inthis 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 storedwithin the DBC. For example, the Referential Integrity Builder’s code forperforming cascaded updates or deletes is placed in the stored procedures area.This is also where you can place code that otherwise might be a “calculatedfield,” an item wisely not supported within the data model, or a UDF. Forexample, the routine to format a postal code based on the country is a routineyou might include within your main DBC to produce the effect of a calculatedfield on your customer table. Rather than requesting Customer.PostalCode, youcould specify CustPostCode(Customer.PostalCode) and get the built-in code to dothe work. This has advantages over a stand-alone UDF because it’s alwaysavailable when the data is. The downside is that this code is only availablefor the current SET DATABASE database, so stored procedures are not areplacement for stand-alone or procedure libraries. They are, however, still agreat 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 databasecontaining the procedure the current one. (For example, if the DefaultValueproperty of a field in a table belonging to a database that isn’t the currentone calls a stored procedure in that database, VFP will run the proper storedprocedure, even if a procedure with the same name exists in the currentdatabase.) That is, DBC() returns the name of the current database, which isn’tnecessarily the one the procedure is in. This may have implications for yourcode. For example, say a stored procedure called from the DefaultValue propertyfor a primary key field in a table opens the NEXTID table, which contains thenext available primary key to use. If that table doesn’t exist in the currentlyselected database, the USE command will fail. The solution is to eitherspecifically SET DATABASE to the desired database in the stored procedure (besure to SET DATABASE back to the previously selected one at the end of theprocedure) or to include the database name in the USE command (USEMYDATABASE!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 totie together an analysis piece in a spreadsheet with a valign='top'>

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 DBFextension, but may not be fully compatible with Visual FoxPro. Visual FoxProDBFs cannot be read with these products, either. The first clue you may get isan error when attempting to USE a table. FoxPro determines this by reading thefirst 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 ofbackward-compatibility, since it can read DBF files created with earlierproducts. However, in order to facilitate linking with DBC database containers,Visual FoxPro 3.0 introduced changes to the DBF header structure that makeVisual FoxPro DBFs unreadable with earlier products. If you need to“regress” a Visual FoxPro table to an earlier format, you can use theTYPE FOX2X keywords with the COPY TO command.

The tables above show the internal structure of a VisualFoxPro table. Several VFP traits are of key interest. Byte 0, the so-called “signaturebyte,” is always 48 (hexadecimal 0x30) for Visual FoxPro tables. Byte 28was used in earlier FoxPro versions to designate that a CDX file was used bystoring a CHR(01) in that location. This has been expanded in VFP to includewhether a memo file is used for memo or general field information and alsowhether the table is a database container. This is accomplished by adding 2 formemo fields and 4 for DBCs. A similar pattern of “bit flags” occursfor each field record stored in the header. Byte 18 of each field recordcontains three bit flags: Bit 0 indicates whether the field is displayed or isa hidden (“system”) field; bit 1 flags whether the field can storenull values; and bit 2 determines whether the field is translated to the currentcode 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 aboutthree? “Yes,” “No,” and “I Don’t Know.” Foryears, Xbase had no good way to store the “I Don’t Know” answer formany fields. Logical fields were restricted to .T. and .F. A character fieldleft 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: Youask 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 andissue 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 morebelievable 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 fielddescription within the Table Designer, or by using the NULL keyword in CREATETABLE or ALTER TABLE. Fields in remote views from server systems can be definedas nullable by their DBMS server, and this carries over into the view.

Understanding how nulls work within calculations isimportant. If any of the fields or memory variables within your system isallowed to take on the null value, you must anticipate how this value canaffect calculations, functions and processing within your application. TheISNULL() function can be used to test for the presence of a null value, and theNVL() function can substitute another value (such as zero or a blank) for avalue found to be .NULL. Why can’t we just test a variable to see if it isequal 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 mathematicalexpression involving .NULL.? I don’t know—.NULL. One half of .NULL.? I don’tknow—.NULL. Is .NULL. equal to .NULL.? I don’t know—.NULL. The first threecharacters of .NULL.? I don’t know—.NULL.

Null values “propagate” through functions—if anyvalue is not known, the result can’t be known. We can’t test an unknown valueagainst another value (even another unknown) and know if they’re equal. Hencethe need for an ISNULL() function.

Because null values can propagate through the calculationsof a system, we discourage their indiscriminate use. Carefully bracket your useof them to test and properly handle the null values. When a null value isappropriate for the data design, nothing else will do. We applaud the fact thatVisual FoxPro has been endowed with this cool feature.

An interesting feature is how nulls are actually storedwithin a table. Since many of the field types can hold any value from 0x00 to0xFF in each byte, it is impossible to store null values within the currentdisk space allocated for each field. Instead, Microsoft created a new field,called _NullFlags. _NullFlags is a “system” field (bit 0 of byte 18in the field record portion of the file header is set to 1). This fieldcontains a bitmap, one bit for each nullable field in the table, in thephysical order of the fields in the table. If a field is to be null, theassociated bit is set on (to 1). This seems awfully kludgy to us, but it doeswork, and reliably: Physically rearranging the order of fields in the table, orprogrammatically using only some fields with SET FIELDS TO doesn’t seem to tripit 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 allfor the best. However, having hidden, system fields in a table, which don’tshow 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 considera great leap forward. In this era of “what do you know and when did youknow 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, froman empty note to a monstrous embedded WinWord dissertation. Storing this datain 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 areadded to the end of the FPT, data is added to them, and then the pointercontained within the memo header is updated to point at the new data. The olddata remains within the file, and the space it occupied is not reclaimed. Overa period of time, memo fields can grow beyond control without containing thatmuch information. This is the dreaded “memo field bloat.” TheADDITIVE clause of the REPLACE command does not alleviate this, it just makesit easier to tack one more sentence onto the end of a long memo—internally, thesame process occurs.

In development, you can reclaim the space with the PACK MEMOcommand. This packs the file in place, replacing the memo field with a far morecompact one. However, as we discuss in “Commands to Use OnlyInteractively,” the PACK command leaves the developer in the dark ifsomething goes wrong in mid-flight. See that section for suggestedwork-arounds.

VFP provides the SET BLOCKSIZE command to allow you to tuneand 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 numbergreater than 32 creates blocks of that number of bytes. A newer option, SETBLOCKSIZE TO 0, stores the memo blocks as individual bytes, rather than asblocks. It seems to us that this method wastes the least “slackspace” at the end of each memo, but might in some circumstances lead togreater 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 speedof the processor overhead, and like many other benchmark items, we encourageyou to try it in your environment with your machines and data, to see whatworks out best for you.

dBASE III had a somewhat different method of storing thememo fields (general fields did not exist) in the DBT file. FoxPro can read andwrite 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 newplatform, tweaking all the forms and programs to handle the new (or missing)features, and then discovering the data is unreadable. But this was exactlywhat happened to many FoxPro developers as they brought their information fromDOS to Windows in the 2.5 release. What happened?

What happened was code pages. A code page is the translationtable that the computer uses to translate each of the characters stored ondisk—8 little bits, storing one of 256 different patterns—into the singlecharacter we’re used to seeing on the screen. While some of these codes arepretty standardized, people who speak any one of the thousands of languagesother than English use a different code page to represent their characters.Code pages can be loaded and manipulated in DOS using the NLSFUNC, CHCP andMODE commands. A typical U.S. code page is 437 for DOS and 1252 for Windows.

In most database applications, code page translation wouldbe a one-step, pain-in-the-neck translation from the “old” way to the“new” way, but FoxPro supports access from multiple code pagessimultaneously. Remarkably, it accomplishes this feat, pretty muchtransparently, through the use of a code page byte, stored within DBF headersand also stored as part of compiled code.

That sounds like the happy end to the story, right? Weshould 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 sortof a packed or encrypted format, where you use all 256-byte combinations, andyou 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 seedifferent 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 defininga table at creation (see CREATE TABLE) or while maintaining a table (see ALTERTABLE), 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 itselfis automatically set for Double, Integer, Datetime and Currency fields, eventhough it can’t (and shouldn’t!) be set ON or OFF programmatically for thesefield types. That’s because the values in these fields are stored in abinary/packed format, and translation would lead to some awfully funny numbers.

Date Math

Date math is really cool. Amaze your friends, astound yourcompetition and baffle the crowd with your ability to glibly say, “Ofcourse, everyone knows there have been over 280,000 days since the signing ofthe Magna Carta, and in that time, blah blah blah…” while simplycalculating:

A note about the curly braces above. One of the more commonquestions we hear is about these funny looking things, and why expressions suchas:

return empty dates. The key to understanding these braces isto understand that they are delimiters, wrapping around a set of characters andgiving FoxPro an idea of what’s contained inside, but they are not functionswith the power to evaluate their contents. Just as double and single quotesdelimit a character expression, curly braces designate a date or datetimeexpression. Use a conversion function, such as CTOD() or DATE(), to evaluate acharacter function and return a date.

The second strange thing most veteran Xbase developers willnotice is the prefixed caret and the strange ordering of YYYY-MM-DD. This isthe strict date format, stealthily introduced into the product in Visual FoxPro5.0. In VFP 6, the SET STRICTDATE command provides us with some ability toaudit 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 ofdays can be added or subtracted from a date, or one date can be subtracted fromanother to return the difference in days. The various parts of the date can bereturned using the DAY(), MONTH(), and YEAR() functions for numeric calculationor CMONTH() and CDOW() functions for character display. Dates can be convertedto character format (DTOC()) or from character to date (CTOD()) relativelyeasily.

In Visual FoxPro, dates are stored within tables as eightcharacters of the format YYYYMMDD. Obviously, this practically limits dates tothe range of Year Zero to 9999, but that should be long enough for most of thebusiness processes we hope to model in VFP.

A few cautions are in order. In the “good olddays,” we often extracted portions of a date using substring functions,modified it, and plunked it back into the value, as in the following:

Pretty clever, huh? This worked great for many smallU.S.-centric companies in the 1980s, but with the internationalization oftrade, 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 charactersof a date are the month, the last are the year and the middle is the day, allseparated by slashes, are Stone Age logic. Check out the SET DATE command—youcan bet that your branch offices in Toronto and London have. Make noassumptions about the internal position of digits within a date. Let’s try thisagain. 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 asneeded, dissecting and reassembling a date, and then resetting the SETvariables again, but there’s a far more graceful way, using newer FoxProfunctions:

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 contemporarydates and times, and are inappropriate for storing date/time fields inhistorical, archeological, astronomical or geological time spans. It’s overlyprecise to try to store the start of the Jurassic era in a date field, and infact, it’s impossible to store dates Before the Common Era (BCE) or BC. Sinceno one really seems to know what time it is, even dates as recent as fourcenturies ago make the precision of the date math functions questionable.

For example, GOMONTH() won’t go back farther than the year1753, the year after England took on the “Gregorian shift” of thecalendar, 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 subtractingenough 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 youshould 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. Whileprimarily intended as a compatibility feature for ease of use with similarfields in tables in a client-server environment, datetimes offer the intrepidFoxPro programmer some neat opportunities.

Datetime is stored as an eight-byte field. Supposedly thefirst four bytes store the date and the last four store the time. We haven’thacked this one apart, but we’d love to hear from the hackers who have.

Like currency fields stored without a unit of measure, wesuggest there may be problems of determining just when this time occurred—thereis no “time zone” designation. Is this GMT, Eastern Daylight SavingsTime, or Bering? If you anticipate dealing with a database with multiple timezones, we suggest you consider a region-to-GMT offset table and store all timesas absolute GMT datetimes for ease of calculation.

Datetimes, like dates, can be specified explicitly by usingcurly braces. As we explain above, delimiters don’t work as conversionfunctions, evaluating the expression given to them, but rather just indicateconstants. Nonetheless, Visual FoxPro is pretty clever, accepting any of thestandard date delimiters (slash, dot or hyphen) and either 12- or 24-hourformatted time, regardless of the settings of SET MARK TO or SET HOURS. Theorder of the month, day and year, however, must be the same as that set by SETDATE. The only exception to that is the use of the strict date form describedabove. 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 appearsthat 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, youmust either include the comma separating the date from the time, or follow thehours 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 propertyallows “loose” data entry where we depend upon the machine tointerpret the varieties of hyphens, dashes and slashes we throw at it. At thesame time, the Fox team added a curveball: a new format for looseStrictDateEntry that allows the data-entry operator to override thepreformatted date sequence by preceding the date with a caret. Following thecaret, 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 theintroduction in VFP 6 of the SET STRICTDATE command. This command, essentialfor ensuring Year 2000 compliance in code, generates errors at compile time,and optionally at runtime, reporting that code contains dates that can beambiguous. Since the ordering of day, month and year is determined by SET DATE,both in the runtime and development environments, “constants” (aswell as expressions using the date conversion functions like CTOD()) can beinterpreted in more than one way. The SET STRICTDATE command lets you flagthese variable constants unless they, too, now use the strict date format ofcaret, 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, allof which seem to store the same or similar data. The reason for this isprimarily backward and sideways compatibility with previous Fox and Xbaseproducts. There are some differences, however…

Float

Seems to be same as numeric. Float exists to allowcompatibility with other database products that treated numeric and floatfields differently. Visual FoxPro treats them no differently internally.

Double

Always stores as length 8, but allows you to change decimalplaces from zero to 18. A fixed format used primarily for client-servercompatibility, it’s manipulated internally the same as any other numeric byFoxPro, but stored differently.

Integer

Integer was probably one of the most useful data typesintroduced in Visual FoxPro 3.0. Stored in only four bytes on disk, the fieldhas a range from –2147483647 to plus 2147483647. If you need to track wholenumbers only, this can be a compact and efficient way to do it. We find thesefields to be ideal as primary keys, since they’re small and form smallerindexes, and also are easy to manipulate and increment. They’re also thefastest way to join tables in a SQL SELECT.

Numeric

A numeric field allows up to 20 numeric digits to beentered, but one space is occupied by the decimal point, if used. Microsoftdescribes accuracy as 16 digits, but 14 seems closer to the truth. Check thisout:

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 aseight bytes, with four decimal places. These fields are marked by default asNOCPTRANS, since the data is packed. Currency is a funny field type. Just asdatetime stores a time without a time zone, currency stores a value without aunit. Also like datetime, this field type was introduced primarily forcompatibility with client-server databases. But is this currency in dollars,euros, or yen? An international application needs to know if it’s running inZurich or New Delhi.

Like datetime, currency introduces some new functions anddelimiters into the language. NTOM() and MTON() convert numerics to currencyand vice versa (think “money” rather than “currency”). Thedollar-sign delimiter preceding a numeric literal forces the type to currency.

Math involving currency and other numerics introduces a newkink. What’s the result of multiplying two currency values—a numeric or acurrency value? What about trigonometry on these values? We could engage inquite a diatribe on the meaning of unitless and “unit-ed” variablesbeing processed together, but it doesn’t really matter—Microsoft has a methodto its madness, and while it might be different from what we would’ve come upwith, it works under most circumstances: Basic four-function math (addition,subtraction, multiplication and division) involving currency gives results incurrency. 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 sincethe FoxPro 2.x days. With the introduction of NULLs, described above, a logicalfield 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 westrongly argue against ever using it.) Logical fields take up one byte in atable, even though they really only need a single bit. With the datacompression Microsoft implemented in double, datetime, currency and integerfields, as well as shrinking the size of the memo and general fields from 10bytes to four, we’re surprised they didn’t try to implement some sort ofbyte-and-offset addressing for storing multiple logical fields in a single byteas well.

Hip Hip Array!

Arrays are not truly a different type of variable, but theyare 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 andmethods (AMEMBERS()). Arrays can be used to hold data on its way to and fromtables—SCATTER, GATHER and INSERT all support array clauses. Understanding howto manipulate these arrays, especially how they are referenced by differentfunctions, is an important aspect of Visual FoxPro programming.

Arrays come in two flavors—one-dimensional and two-dimensional—distinguishedby the number of subscripts supplied with them. However, internally, both arraytypes are stored the same way, and functions that work with one type work withthe other as well. This can lead to some confusion. Suppose you create an array:

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

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

(Actually, starting in version 7, ASCAN() can optionallyreturn the row where it found the value. See the Reference section fordetails.)

Even more interesting, we can just use the single digitreturned. The fact is that FoxPro is willing to use any combination of rows andcolumns we supply to reference an element, as long as we do not exceed thedefined number of rows:

You can determine the number of rows and columns of an arrayby using the ALEN() function.

You can change the dimensions of an array on the fly byissuing another DIMENSION, LOCAL ARRAY or PUBLIC statement, depending on thescope of your variable. Redimensioning does not erase the values of the array,but it can rearrange them in some pretty funny ways. The values originallyassigned to the array in ordinal form are reassigned to the new array with thesame ordinal values. This can result in some pretty useless-looking arrays,with values slipping diagonally across the columns. Instead, try out ouraColCopy() function (under ACOPY() in the Reference section) for a better wayto do this.

Many functions also redimension arrays automatically to fitthe contents of the function. As a general rule, functions redimension arraysto fit only those values the function returns. Typically, the array is createdor redimensioned only if the function has something to put in it. We note inthe Reference section where functions don’t follow these thumbrules.

The array manipulations you’ll often want to do areinserting and deleting rows from the array, and you’ll probably suspect thatAINS() and ADEL() are the functions to do this. Foolish mortal. AINS() does, infact, create a row of new values (all initialized to .F.), but it does this bypushing the following rows down, until the last falls off the array into thebit bucket. ADEL() reverses the process, causing rows of elements to move upover the deleted one, and leaving an empty row at the bottom. In both cases, acall 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 farless simple column operations, is available in the Reference section, under theassociated functions, as well as in the overview “ArrayManipulation.”

Passing an Array

An array can only be passed to another routine usingexplicit referencing. That means the array name must be preceded with thesymbol “@” when passed as a parameter. Forgetting to append thissymbol causes only the first element of the array to be passed; this is one ofour favorite programming omissions that drive us mad trying to troubleshoot.

An array passed by reference, as we explain in “XBaseXPlained,” really has only one occurrence in memory, and all changesperformed by called routines have their effect on this one array. Caution iscalled for.

Returning an Array

VFP 7 added the ability to return an array from a functionby again using the “@” symbol and the array name in the RETURNstatement. However, there are some gotchas with this technique; see RETURN inthe 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 forinternational settings—dates, times, currency, etc.—available through theWindows Control Panel, in the Regional Settings applet. These dialogs areavailable to your users, and you don’t have to maintain the code! What you dohave to do is check to see if your users have modified them. Check out theRegistry under HKEY_CURRENT_USERControl PanelInternational and modify thebehavior of your application appropriately. See SET SYSFORMATS for moreinformation 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 technologyformerly known as “Object Linking and Embedding,” then“OLE,” and then “Active” something or other. While themarketeers don’t seem to be happy with any name they’ve thought of so far, theidea of this portion of the implementation remains the same: Provide a portalbetween FoxPro and some other application (the “OLE Server”) andstore the information about that link and the data shared here.

OLE, er, Active, er, this stuff is no cakewalk. For many ofthe gory details, see the section “Active Something” as well as theindividual reference sections for APPEND GENERAL, MODIFY GENERAL, OLEControland OLEBoundControl.

A couple of cautions here. General fields contain severalpieces of information: All contain “display data,” and have either apath to data (for linked data) or the embedded data itself. The“display” or “presentation” data is a raw BMP that VisualFoxPro uses to show that the field is occupied. In Word 2.0, this was the bigblue W logo. Word 6.0 allowed you to store a representation of the first pageof a document. MS Graph showed—surprise!—the graph. But some OLE severs can bea problem. Graphics servers, which store pictures, are usually forced toprovide Visual FoxPro with a BMP for presentation data, even if their graphicis in another format (like the far more compact JPG format). This BMP can beHUGE; a large image rendered in 24-bitplanes (roughly a bazillion colors) cantake megabytes of space. This space is used even if the data is only linkedto the field! Anticipate a large amount of storage space if you choose to linkvery large or very high-resolution documents. Consider other techniques (suchas just storing the path and filenames in a character field, and using a cursorto hold one image at a time) if disk space is a concern.

One last note about general fields. A general field isnothing more than a special form of the memo field that contains binary data,including a “wrapper” around the data that tells FoxPro who to callto manipulate this data—the OLE server. When data is called up and changed andsaved back to the memo field, new blocks of the memo field are allocated forthe new data, and the old blocks are internally marked as not used. These oldblocks are never reused. What happens over a period of time is that the memo filewill grow and grow and grow. To alleviate this problem, you can consider usingthe PACK MEMO command to shrink the file (but only after reading the cautionsin “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 orprogrammatically, in a runtime or development environment, through VFP code orthrough ADO. So, they’re sort of like triggers, except they fire in response tothings 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 “DatabaseEvents” and related topics in the Reference section for complete detailson the events that are available and how each works.

Let’s explore some ideas for where you might use databaseevents. 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 canmake development easier and more productive. Examples include enforcingstandards, 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 Receivabledatabase should start with “AR”, the first character of all fieldnames must represent the data type or be a four-letter abbreviation for thetable, 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 haveto remember to run, you could create database events that automatically warn ifthe 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 easyto 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 codein AfterRenameTable (as well as AfterRenameView and AfterRenameConnection) toopen a project, go through all files in the project, look for the former name(the cPreviousName parameter passed to the event contains this), and eitherreplace it with the new name (contained in the cNewName parameter) or at leastprint the locations or add them to the Task List so a developer can make thechanges 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; becauseAfterModifyTable and AfterModifyView don’t tell you what changes were made, youhave to store the previous structure somewhere (such as in a cursor inBeforeModify events or in metadata), and then in the AfterModify events try tofigure out what happened. It’s not easy; a renamed field looks no differentthan if you were to delete one field and add another.

When anything in the database changes (such as tables orviews being added, removed or altered), database event code could automaticallysend an email to every member of the development team informing them of thechanges. You could also log who changed something and when, and even prompt theuser to enter a short comment about the change.

Besides these serious uses, imagine the fun you can have withyour fellow developers when you put code in various “before” eventsthat returns .F. to prevent them from doing something or makes fun of them insome way (think of the French knight mocking King Arthur in “Monty Pythonand the Holy Grail”; sound files are available for download from variousWeb 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 particularlygood day to wreak havoc!

Runtime Behavior

Database events can also be used in a runtime environment toprovide functionality VFP developers have requested for years. Some usesinclude table and database security and hacker prevention.

Returning .F. from BeforeOpenTable prevents a table or viewfrom being opened. Obviously, you don’t want to do this for every table andview 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:

This code assumes that a global variable named gcUserNamecontains the name of the logged-in user, and prevents anyone but ADMIN fromopening the PAYROLL table.

As with table security, an entire database can be secured byconditionally returning .F. from the OpenData event.

Sometimes, we need to prevent someone from altering thestructure of a table or view. We’re not so worried about malicious hackers asthose users who embody the expression “a little knowledge is a dangerousthing.” Such users could be using a development copy of VFP or ADO viaAccess or Excel. To prevent this, return .F. in the BeforeModifyTable andBeforeModifyView events.

To prevent someone from changing or disabling the events forthe DBC (which would allow them to get around everything that database eventsare providing for you), return .F. in the BeforeModifyProc and BeforeAppendProcevents, and in BeforeDBSetProp if the property being changed is“DBCEvents” or “DBCEventFileName.” This approach isn’tfoolproof, since someone with a development copy of VFP can USE the DBC (thatis, open it as a table, which is what it really is), modify the code in theCode memo of the StoredProceduresSource record, and then close and COMPILEDATABASE. 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 badthing, though.

To prevent someone from seeing the code in the storedprocedures (for example, because it contains proprietary information), return.F. in the BeforeModifyProc and BeforeCopyProc events.

Conclusion

File storage within Visual FoxPro is similar to earlierversions of FoxPro, but with some powerful enhancements provided by theDatabase Container. New fields and field capabilities have been added. VFP 7still retains the trademark backward compatibility, allowing it to read alldBase 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, andthat workarounds are available for most of the incompatibilities.

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 tothe Visual FoxPro mindset was pretty revolutionary on a lot of fronts. While wecringe to use the hackneyed and overused term, the introduction of databases inVisual FoxPro was a major paradigm shift and one that took some getting usedto. The introduction of the database container, local and remote views, vastimprovements 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 ofthe data changes here, and refer you to areas where you can get moreinformation throughout the book.

If you’re coming from an Xbase background, you might thinkyou’re encountering some NewSpeak in Visual FoxPro, but it’s all for a goodcause. Individual DBF files used to be referred to as “databases” butnow, 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 changeterminology you’ve been using for years, we’ve found it does lead to greaterunderstanding of the new scheme of Visual FoxPro. Not only that, but the newnames bring us in line with the rest of the relational database world.

The Database Container

A database container (DBC file) is a Visual FoxPro tablethat links together the tables, indexes, views and special code associated withyour data. When a table is added to a database, many new features becomeavailable. You can define code to validate individual fields or entire recordsbefore they are saved, each with its own individual error message. A defaultvalue and caption can be specified for each field.

Database containers also allow you to specify persistent relations, saving hours oftedium in system development. These relations form the basis for enforcement ofrelational integrity (RI) at the database level, through the use of storedprocedures and record-level triggers. In addition to RI enforcement, our ownprogram code can be triggered on the insertion of a new record, the updating ofan existing record, or the deletion of a record. Program code can also run atthe field level, specifying default values, validation rules and errormessages. Individual fields can have default captions and control classesassigned to them.

All of these features are controlled by the database engineitself, 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 aBrowse. 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 tablecan be “free,” not associated with a particular database, or it canbe “contained” within a DBC. This “containership” is notthe same as, say, Access’s monolithic MDB files—no data from the tables isactually stored within the DBC, just links to the tables, views and otherelements. This structure is like the Project Manager, which holds references tosource 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, andthe ability to flag character or binary data in fields not to be translatedbetween different language versions of Visual FoxPro.

The Database Container

Xbase programmers had gotten into a rut. In everyapplication, in every screen, in every routine, they had to code the samefunctionality. “Customer.DBF is related to Orders.DBF by the Cust_IDfield.” “Customer mailing address state needs to be validated againstthe 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 forgotone of these rules in just one place in an application, the consistency of thedata was in jeopardy and a long, arduous troubleshooting session was sure toresult. To compound the error, it was possible that these rules were in placein all programs, but a database change made interactively by a programmer oruser could still be responsible for bad data. What a headache!

Visual FoxPro’s competition, the Relational DataBaseManagement Systems (RDBMSs) and Client-Server systems, developed solutions tothese problems. RDBMSs made leaps and bounds in functionality, while the basicdata model of Xbase hadn’t changed since the development of dBASE. With therelease of Visual FoxPro, Microsoft determined it was time for an improvementin the basic model. Visual FoxPro introduced some new terminology and someincredible 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 isnot really consistent with most other database management systems. Besides,those folks with their poky-slow RDBMSs would sneer at us: “That’s not areal 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 withthe table itself and its constituent fields. These names are stored in the DBC.

Triggers and stored procedures are Visual FoxPro codefragments that run automatically when their associated event occurs. Field-levelprocedures fire when a field is modified. Record-level procedures fire when anattempt is made to commit a new record to a file, update an existing record, ordelete a record from a table.

Primary and candidate keys both uniquely distinguish a recordfrom all others. Indexes designated as either of these don’t accept duplicatevalues, 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 apersistent relationship are brought into the data environment of a form orreport, the relationship is brought with them. While the relationship can bemodified in each place it exists, if the most common relationship is definedwithin the DBC, far less work is needed in each subsidiary form and report toput a system together.

After creating tables and their relationships within theDBC, run the Relational Integrity Builder by issuing the command ModifyDatabase and then choosing Edit Referential Integrity from the Database menu orthe context menu. The Relational Integrity Builder appears. When you are doneand choose OK, the builder regenerates the RI stored procedures needed toensure relational integrity in the database.

Views are cursors on warp speed. A view is defined like aSQL SELECT, allowing you to join multiple tables (and use their persistentrelations, if set), select the output fields, and order and group records justlike a SELECT. But views are really cool because they can be updateable, sochanges made to the records in the resulting cursor can be “writtenthrough” onto the tables. This has fantastic implications for manipulatingand updating data.

Remote views have all the coolness of the local views justmentioned, with a simple but profound variation—the data is not Visual FoxProdata. Using ODBC, the Connection Designer, and the View Designer, Visual FoxProhas become, in one fell swoop, one of the most powerful clients in aclient-server relationship. Even cooler, because both local and remote viewscan be defined, a Visual FoxPro client-server system can be designed,prototyped and tested on local data stores, and converted to remote datastorage when ready to go into production. This is a big attraction todevelopers who want to work on client-server systems on their client site, butdon’t want or need to set up servers in their own offices. For more informationon using client-server architectures, see “Your Server Will Be With You in a Moment.”

Had enough views yet? There’s one more variation on thetheme: offline views. An offline view is defined as any other, but it allows anoperator to actually “check out” a set of records, make changes, andthen re-synchronize with the original source. This is a cool feature for“road warriors,” anyone who needs to disconnect from the mainnetwork, go on the road, do some work, and then reconnect and update the mainfiles.

Database events, added in VFP 7, let you control thestructure 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 aparticular DBC, VFP fires events whenever you perform pretty much any action onthe database container or the tables, views, relations and connections itcontains. For example, when you create a table, the BeforeCreateTable andAfterCreateTable events fire. When you remove a view, the BeforeDropView andAfterDropView events fire. By putting code into the procedures called by theseevents (named “DBC_” followed by the event name, such as“DBC_BeforeCreateTable”), you can perform additional actions whenthese events occur. For example, you could update an audit log whenever atable’s structure is modified, or update a security log whenever a user opens aview 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 aspreventing unauthorized users from opening the payroll table and seeing whomakes how much money (or worse, changing salaries) or from making changes tostored procedures. See “Mr. Database Event, You’re Fired!” later inthis 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 storedwithin the DBC. For example, the Referential Integrity Builder’s code forperforming cascaded updates or deletes is placed in the stored procedures area.This is also where you can place code that otherwise might be a “calculatedfield,” an item wisely not supported within the data model, or a UDF. Forexample, the routine to format a postal code based on the country is a routineyou might include within your main DBC to produce the effect of a calculatedfield on your customer table. Rather than requesting Customer.PostalCode, youcould specify CustPostCode(Customer.PostalCode) and get the built-in code to dothe work. This has advantages over a stand-alone UDF because it’s alwaysavailable when the data is. The downside is that this code is only availablefor the current SET DATABASE database, so stored procedures are not areplacement for stand-alone or procedure libraries. They are, however, still agreat 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 databasecontaining the procedure the current one. (For example, if the DefaultValueproperty of a field in a table belonging to a database that isn’t the currentone calls a stored procedure in that database, VFP will run the proper storedprocedure, even if a procedure with the same name exists in the currentdatabase.) That is, DBC() returns the name of the current database, which isn’tnecessarily the one the procedure is in. This may have implications for yourcode. For example, say a stored procedure called from the DefaultValue propertyfor a primary key field in a table opens the NEXTID table, which contains thenext available primary key to use. If that table doesn’t exist in the currentlyselected database, the USE command will fail. The solution is to eitherspecifically SET DATABASE to the desired database in the stored procedure (besure to SET DATABASE back to the previously selected one at the end of theprocedure) or to include the database name in the USE command (USEMYDATABASE!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 totie together an analysis piece in a spreadsheet with a valign='top'>

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 DBFextension, but may not be fully compatible with Visual FoxPro. Visual FoxProDBFs cannot be read with these products, either. The first clue you may get isan error when attempting to USE a table. FoxPro determines this by reading thefirst 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 ofbackward-compatibility, since it can read DBF files created with earlierproducts. However, in order to facilitate linking with DBC database containers,Visual FoxPro 3.0 introduced changes to the DBF header structure that makeVisual FoxPro DBFs unreadable with earlier products. If you need to“regress” a Visual FoxPro table to an earlier format, you can use theTYPE FOX2X keywords with the COPY TO command.

The tables above show the internal structure of a VisualFoxPro table. Several VFP traits are of key interest. Byte 0, the so-called “signaturebyte,” is always 48 (hexadecimal 0x30) for Visual FoxPro tables. Byte 28was used in earlier FoxPro versions to designate that a CDX file was used bystoring a CHR(01) in that location. This has been expanded in VFP to includewhether a memo file is used for memo or general field information and alsowhether the table is a database container. This is accomplished by adding 2 formemo fields and 4 for DBCs. A similar pattern of “bit flags” occursfor each field record stored in the header. Byte 18 of each field recordcontains three bit flags: Bit 0 indicates whether the field is displayed or isa hidden (“system”) field; bit 1 flags whether the field can storenull values; and bit 2 determines whether the field is translated to the currentcode page or treated as binary data.

Nulls

Db File Viewer Free Download

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 aboutthree? “Yes,” “No,” and “I Don’t Know.” Foryears, Xbase had no good way to store the “I Don’t Know” answer formany fields. Logical fields were restricted to .T. and .F. A character fieldleft 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: Youask 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 andissue 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 morebelievable 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 fielddescription within the Table Designer, or by using the NULL keyword in CREATETABLE or ALTER TABLE. Fields in remote views from server systems can be definedas nullable by their DBMS server, and this carries over into the view.

Understanding how nulls work within calculations isimportant. If any of the fields or memory variables within your system isallowed to take on the null value, you must anticipate how this value canaffect calculations, functions and processing within your application. TheISNULL() function can be used to test for the presence of a null value, and theNVL() function can substitute another value (such as zero or a blank) for avalue found to be .NULL. Why can’t we just test a variable to see if it isequal 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 mathematicalexpression involving .NULL.? I don’t know—.NULL. One half of .NULL.? I don’tknow—.NULL. Is .NULL. equal to .NULL.? I don’t know—.NULL. The first threecharacters of .NULL.? I don’t know—.NULL.

Null values “propagate” through functions—if anyvalue is not known, the result can’t be known. We can’t test an unknown valueagainst another value (even another unknown) and know if they’re equal. Hencethe need for an ISNULL() function.

Because null values can propagate through the calculationsof a system, we discourage their indiscriminate use. Carefully bracket your useof them to test and properly handle the null values. When a null value isappropriate for the data design, nothing else will do. We applaud the fact thatVisual FoxPro has been endowed with this cool feature.

An interesting feature is how nulls are actually storedwithin a table. Since many of the field types can hold any value from 0x00 to0xFF in each byte, it is impossible to store null values within the currentdisk space allocated for each field. Instead, Microsoft created a new field,called _NullFlags. _NullFlags is a “system” field (bit 0 of byte 18in the field record portion of the file header is set to 1). This fieldcontains a bitmap, one bit for each nullable field in the table, in thephysical order of the fields in the table. If a field is to be null, theassociated bit is set on (to 1). This seems awfully kludgy to us, but it doeswork, and reliably: Physically rearranging the order of fields in the table, orprogrammatically using only some fields with SET FIELDS TO doesn’t seem to tripit 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 allfor the best. However, having hidden, system fields in a table, which don’tshow 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 considera great leap forward. In this era of “what do you know and when did youknow 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, froman empty note to a monstrous embedded WinWord dissertation. Storing this datain 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 areadded to the end of the FPT, data is added to them, and then the pointercontained within the memo header is updated to point at the new data. The olddata remains within the file, and the space it occupied is not reclaimed. Overa period of time, memo fields can grow beyond control without containing thatmuch information. This is the dreaded “memo field bloat.” TheADDITIVE clause of the REPLACE command does not alleviate this, it just makesit easier to tack one more sentence onto the end of a long memo—internally, thesame process occurs.

In development, you can reclaim the space with the PACK MEMOcommand. This packs the file in place, replacing the memo field with a far morecompact one. However, as we discuss in “Commands to Use OnlyInteractively,” the PACK command leaves the developer in the dark ifsomething goes wrong in mid-flight. See that section for suggestedwork-arounds.

VFP provides the SET BLOCKSIZE command to allow you to tuneand 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 numbergreater than 32 creates blocks of that number of bytes. A newer option, SETBLOCKSIZE TO 0, stores the memo blocks as individual bytes, rather than asblocks. It seems to us that this method wastes the least “slackspace” at the end of each memo, but might in some circumstances lead togreater 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 speedof the processor overhead, and like many other benchmark items, we encourageyou to try it in your environment with your machines and data, to see whatworks out best for you.

dBASE III had a somewhat different method of storing thememo fields (general fields did not exist) in the DBT file. FoxPro can read andwrite 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 newplatform, tweaking all the forms and programs to handle the new (or missing)features, and then discovering the data is unreadable. But this was exactlywhat happened to many FoxPro developers as they brought their information fromDOS to Windows in the 2.5 release. What happened?

What happened was code pages. A code page is the translationtable that the computer uses to translate each of the characters stored ondisk—8 little bits, storing one of 256 different patterns—into the singlecharacter we’re used to seeing on the screen. While some of these codes arepretty standardized, people who speak any one of the thousands of languagesother than English use a different code page to represent their characters.Code pages can be loaded and manipulated in DOS using the NLSFUNC, CHCP andMODE commands. A typical U.S. code page is 437 for DOS and 1252 for Windows.

In most database applications, code page translation wouldbe a one-step, pain-in-the-neck translation from the “old” way to the“new” way, but FoxPro supports access from multiple code pagessimultaneously. Remarkably, it accomplishes this feat, pretty muchtransparently, through the use of a code page byte, stored within DBF headersand also stored as part of compiled code.

That sounds like the happy end to the story, right? Weshould 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 sortof a packed or encrypted format, where you use all 256-byte combinations, andyou 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 seedifferent 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 defininga table at creation (see CREATE TABLE) or while maintaining a table (see ALTERTABLE), 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 itselfis automatically set for Double, Integer, Datetime and Currency fields, eventhough it can’t (and shouldn’t!) be set ON or OFF programmatically for thesefield types. That’s because the values in these fields are stored in abinary/packed format, and translation would lead to some awfully funny numbers.

Date Math

Date math is really cool. Amaze your friends, astound yourcompetition and baffle the crowd with your ability to glibly say, “Ofcourse, everyone knows there have been over 280,000 days since the signing ofthe Magna Carta, and in that time, blah blah blah…” while simplycalculating:

A note about the curly braces above. One of the more commonquestions we hear is about these funny looking things, and why expressions suchas:

return empty dates. The key to understanding these braces isto understand that they are delimiters, wrapping around a set of characters andgiving FoxPro an idea of what’s contained inside, but they are not functionswith the power to evaluate their contents. Just as double and single quotesdelimit a character expression, curly braces designate a date or datetimeexpression. Use a conversion function, such as CTOD() or DATE(), to evaluate acharacter function and return a date.

The second strange thing most veteran Xbase developers willnotice is the prefixed caret and the strange ordering of YYYY-MM-DD. This isthe strict date format, stealthily introduced into the product in Visual FoxPro5.0. In VFP 6, the SET STRICTDATE command provides us with some ability toaudit 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 ofdays can be added or subtracted from a date, or one date can be subtracted fromanother to return the difference in days. The various parts of the date can bereturned using the DAY(), MONTH(), and YEAR() functions for numeric calculationor CMONTH() and CDOW() functions for character display. Dates can be convertedto character format (DTOC()) or from character to date (CTOD()) relativelyeasily.

In Visual FoxPro, dates are stored within tables as eightcharacters of the format YYYYMMDD. Obviously, this practically limits dates tothe range of Year Zero to 9999, but that should be long enough for most of thebusiness processes we hope to model in VFP.

A few cautions are in order. In the “good olddays,” we often extracted portions of a date using substring functions,modified it, and plunked it back into the value, as in the following:

Pretty clever, huh? This worked great for many smallU.S.-centric companies in the 1980s, but with the internationalization oftrade, 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 charactersof a date are the month, the last are the year and the middle is the day, allseparated by slashes, are Stone Age logic. Check out the SET DATE command—youcan bet that your branch offices in Toronto and London have. Make noassumptions about the internal position of digits within a date. Let’s try thisagain. 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 asneeded, dissecting and reassembling a date, and then resetting the SETvariables again, but there’s a far more graceful way, using newer FoxProfunctions:

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 contemporarydates and times, and are inappropriate for storing date/time fields inhistorical, archeological, astronomical or geological time spans. It’s overlyprecise to try to store the start of the Jurassic era in a date field, and infact, it’s impossible to store dates Before the Common Era (BCE) or BC. Sinceno one really seems to know what time it is, even dates as recent as fourcenturies ago make the precision of the date math functions questionable.

For example, GOMONTH() won’t go back farther than the year1753, the year after England took on the “Gregorian shift” of thecalendar, 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 subtractingenough 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 youshould 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. Whileprimarily intended as a compatibility feature for ease of use with similarfields in tables in a client-server environment, datetimes offer the intrepidFoxPro programmer some neat opportunities.

Datetime is stored as an eight-byte field. Supposedly thefirst four bytes store the date and the last four store the time. We haven’thacked this one apart, but we’d love to hear from the hackers who have.

Like currency fields stored without a unit of measure, wesuggest there may be problems of determining just when this time occurred—thereis no “time zone” designation. Is this GMT, Eastern Daylight SavingsTime, or Bering? If you anticipate dealing with a database with multiple timezones, we suggest you consider a region-to-GMT offset table and store all timesas absolute GMT datetimes for ease of calculation.

Datetimes, like dates, can be specified explicitly by usingcurly braces. As we explain above, delimiters don’t work as conversionfunctions, evaluating the expression given to them, but rather just indicateconstants. Nonetheless, Visual FoxPro is pretty clever, accepting any of thestandard date delimiters (slash, dot or hyphen) and either 12- or 24-hourformatted time, regardless of the settings of SET MARK TO or SET HOURS. Theorder of the month, day and year, however, must be the same as that set by SETDATE. The only exception to that is the use of the strict date form describedabove. 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 appearsthat 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, youmust either include the comma separating the date from the time, or follow thehours 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 propertyallows “loose” data entry where we depend upon the machine tointerpret the varieties of hyphens, dashes and slashes we throw at it. At thesame time, the Fox team added a curveball: a new format for looseStrictDateEntry that allows the data-entry operator to override thepreformatted date sequence by preceding the date with a caret. Following thecaret, 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 theintroduction in VFP 6 of the SET STRICTDATE command. This command, essentialfor ensuring Year 2000 compliance in code, generates errors at compile time,and optionally at runtime, reporting that code contains dates that can beambiguous. Since the ordering of day, month and year is determined by SET DATE,both in the runtime and development environments, “constants” (aswell as expressions using the date conversion functions like CTOD()) can beinterpreted in more than one way. The SET STRICTDATE command lets you flagthese variable constants unless they, too, now use the strict date format ofcaret, 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, allof which seem to store the same or similar data. The reason for this isprimarily backward and sideways compatibility with previous Fox and Xbaseproducts. There are some differences, however…

Float

Seems to be same as numeric. Float exists to allowcompatibility with other database products that treated numeric and floatfields differently. Visual FoxPro treats them no differently internally.

Double

Always stores as length 8, but allows you to change decimalplaces from zero to 18. A fixed format used primarily for client-servercompatibility, it’s manipulated internally the same as any other numeric byFoxPro, but stored differently.

Integer

Integer was probably one of the most useful data typesintroduced in Visual FoxPro 3.0. Stored in only four bytes on disk, the fieldhas a range from –2147483647 to plus 2147483647. If you need to track wholenumbers only, this can be a compact and efficient way to do it. We find thesefields to be ideal as primary keys, since they’re small and form smallerindexes, and also are easy to manipulate and increment. They’re also thefastest way to join tables in a SQL SELECT.

Numeric

A numeric field allows up to 20 numeric digits to beentered, but one space is occupied by the decimal point, if used. Microsoftdescribes accuracy as 16 digits, but 14 seems closer to the truth. Check thisout:

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 aseight bytes, with four decimal places. These fields are marked by default asNOCPTRANS, since the data is packed. Currency is a funny field type. Just asdatetime stores a time without a time zone, currency stores a value without aunit. Also like datetime, this field type was introduced primarily forcompatibility with client-server databases. But is this currency in dollars,euros, or yen? An international application needs to know if it’s running inZurich or New Delhi.

Like datetime, currency introduces some new functions anddelimiters into the language. NTOM() and MTON() convert numerics to currencyand vice versa (think “money” rather than “currency”). Thedollar-sign delimiter preceding a numeric literal forces the type to currency.

Math involving currency and other numerics introduces a newkink. What’s the result of multiplying two currency values—a numeric or acurrency value? What about trigonometry on these values? We could engage inquite a diatribe on the meaning of unitless and “unit-ed” variablesbeing processed together, but it doesn’t really matter—Microsoft has a methodto its madness, and while it might be different from what we would’ve come upwith, it works under most circumstances: Basic four-function math (addition,subtraction, multiplication and division) involving currency gives results incurrency. 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 sincethe FoxPro 2.x days. With the introduction of NULLs, described above, a logicalfield 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 westrongly argue against ever using it.) Logical fields take up one byte in atable, even though they really only need a single bit. With the datacompression Microsoft implemented in double, datetime, currency and integerfields, as well as shrinking the size of the memo and general fields from 10bytes to four, we’re surprised they didn’t try to implement some sort ofbyte-and-offset addressing for storing multiple logical fields in a single byteas well.

Hip Hip Array!

Arrays are not truly a different type of variable, but theyare 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 andmethods (AMEMBERS()). Arrays can be used to hold data on its way to and fromtables—SCATTER, GATHER and INSERT all support array clauses. Understanding howto manipulate these arrays, especially how they are referenced by differentfunctions, is an important aspect of Visual FoxPro programming.

Arrays come in two flavors—one-dimensional and two-dimensional—distinguishedby the number of subscripts supplied with them. However, internally, both arraytypes are stored the same way, and functions that work with one type work withthe other as well. This can lead to some confusion. Suppose you create an array:

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

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

(Actually, starting in version 7, ASCAN() can optionallyreturn the row where it found the value. See the Reference section fordetails.)

Even more interesting, we can just use the single digitreturned. The fact is that FoxPro is willing to use any combination of rows andcolumns we supply to reference an element, as long as we do not exceed thedefined number of rows:

You can determine the number of rows and columns of an arrayby using the ALEN() function.

You can change the dimensions of an array on the fly byissuing another DIMENSION, LOCAL ARRAY or PUBLIC statement, depending on thescope of your variable. Redimensioning does not erase the values of the array,but it can rearrange them in some pretty funny ways. The values originallyassigned to the array in ordinal form are reassigned to the new array with thesame ordinal values. This can result in some pretty useless-looking arrays,with values slipping diagonally across the columns. Instead, try out ouraColCopy() function (under ACOPY() in the Reference section) for a better wayto do this.

Many functions also redimension arrays automatically to fitthe contents of the function. As a general rule, functions redimension arraysto fit only those values the function returns. Typically, the array is createdor redimensioned only if the function has something to put in it. We note inthe Reference section where functions don’t follow these thumbrules.

The array manipulations you’ll often want to do areinserting and deleting rows from the array, and you’ll probably suspect thatAINS() and ADEL() are the functions to do this. Foolish mortal. AINS() does, infact, create a row of new values (all initialized to .F.), but it does this bypushing the following rows down, until the last falls off the array into thebit bucket. ADEL() reverses the process, causing rows of elements to move upover the deleted one, and leaving an empty row at the bottom. In both cases, acall 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 farless simple column operations, is available in the Reference section, under theassociated functions, as well as in the overview “ArrayManipulation.”

Passing an Array

An array can only be passed to another routine usingexplicit referencing. That means the array name must be preceded with thesymbol “@” when passed as a parameter. Forgetting to append thissymbol causes only the first element of the array to be passed; this is one ofour favorite programming omissions that drive us mad trying to troubleshoot.

An array passed by reference, as we explain in “XBaseXPlained,” really has only one occurrence in memory, and all changesperformed by called routines have their effect on this one array. Caution iscalled for.

Returning an Array

VFP 7 added the ability to return an array from a functionby again using the “@” symbol and the array name in the RETURNstatement. However, there are some gotchas with this technique; see RETURN inthe 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 forinternational settings—dates, times, currency, etc.—available through theWindows Control Panel, in the Regional Settings applet. These dialogs areavailable to your users, and you don’t have to maintain the code! What you dohave to do is check to see if your users have modified them. Check out theRegistry under HKEY_CURRENT_USERControl PanelInternational and modify thebehavior of your application appropriately. See SET SYSFORMATS for moreinformation on using the user’s Windows settings.

General Fields: Object Linking and Embedding

Free Dbc File Viewer

I drink to the general joy of the whole table.
—William Shakespeare, Macbeth

General fields are Visual FoxPro’s implementation of the technologyformerly known as “Object Linking and Embedding,” then“OLE,” and then “Active” something or other. While themarketeers don’t seem to be happy with any name they’ve thought of so far, theidea of this portion of the implementation remains the same: Provide a portalbetween FoxPro and some other application (the “OLE Server”) andstore the information about that link and the data shared here.

OLE, er, Active, er, this stuff is no cakewalk. For many ofthe gory details, see the section “Active Something” as well as theindividual reference sections for APPEND GENERAL, MODIFY GENERAL, OLEControland OLEBoundControl.

A couple of cautions here. General fields contain severalpieces of information: All contain “display data,” and have either apath to data (for linked data) or the embedded data itself. The“display” or “presentation” data is a raw BMP that VisualFoxPro uses to show that the field is occupied. In Word 2.0, this was the bigblue W logo. Word 6.0 allowed you to store a representation of the first pageof a document. MS Graph showed—surprise!—the graph. But some OLE severs can bea problem. Graphics servers, which store pictures, are usually forced toprovide Visual FoxPro with a BMP for presentation data, even if their graphicis in another format (like the far more compact JPG format). This BMP can beHUGE; a large image rendered in 24-bitplanes (roughly a bazillion colors) cantake megabytes of space. This space is used even if the data is only linkedto the field! Anticipate a large amount of storage space if you choose to linkvery large or very high-resolution documents. Consider other techniques (suchas just storing the path and filenames in a character field, and using a cursorto hold one image at a time) if disk space is a concern.

One last note about general fields. A general field isnothing more than a special form of the memo field that contains binary data,including a “wrapper” around the data that tells FoxPro who to callto manipulate this data—the OLE server. When data is called up and changed andsaved back to the memo field, new blocks of the memo field are allocated forthe new data, and the old blocks are internally marked as not used. These oldblocks are never reused. What happens over a period of time is that the memo filewill grow and grow and grow. To alleviate this problem, you can consider usingthe PACK MEMO command to shrink the file (but only after reading the cautionsin “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 orprogrammatically, in a runtime or development environment, through VFP code orthrough ADO. So, they’re sort of like triggers, except they fire in response tothings 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 “DatabaseEvents” and related topics in the Reference section for complete detailson the events that are available and how each works.

Let’s explore some ideas for where you might use databaseevents. 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 canmake development easier and more productive. Examples include enforcingstandards, handling renamed objects, and team development.

Free Can Dbc File Viewer Free

Your organization might have standards for naming tables,views and fields (for example, perhaps all tables in the Accounts Receivabledatabase should start with “AR”, the first character of all fieldnames must represent the data type or be a four-letter abbreviation for thetable, 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 haveto remember to run, you could create database events that automatically warn ifthe 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 easyto 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 codein AfterRenameTable (as well as AfterRenameView and AfterRenameConnection) toopen a project, go through all files in the project, look for the former name(the cPreviousName parameter passed to the event contains this), and eitherreplace it with the new name (contained in the cNewName parameter) or at leastprint the locations or add them to the Task List so a developer can make thechanges 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; becauseAfterModifyTable and AfterModifyView don’t tell you what changes were made, youhave to store the previous structure somewhere (such as in a cursor inBeforeModify events or in metadata), and then in the AfterModify events try tofigure out what happened. It’s not easy; a renamed field looks no differentthan if you were to delete one field and add another.

When anything in the database changes (such as tables orviews being added, removed or altered), database event code could automaticallysend an email to every member of the development team informing them of thechanges. You could also log who changed something and when, and even prompt theuser to enter a short comment about the change.

Besides these serious uses, imagine the fun you can have withyour fellow developers when you put code in various “before” eventsthat returns .F. to prevent them from doing something or makes fun of them insome way (think of the French knight mocking King Arthur in “Monty Pythonand the Holy Grail”; sound files are available for download from variousWeb 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 particularlygood day to wreak havoc!

Runtime Behavior

Database events can also be used in a runtime environment toprovide functionality VFP developers have requested for years. Some usesinclude table and database security and hacker prevention.

Returning .F. from BeforeOpenTable prevents a table or viewfrom being opened. Obviously, you don’t want to do this for every table andview 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:

This code assumes that a global variable named gcUserNamecontains the name of the logged-in user, and prevents anyone but ADMIN fromopening the PAYROLL table.

As with table security, an entire database can be secured byconditionally returning .F. from the OpenData event.

Can Dbc File Format

Sometimes, we need to prevent someone from altering thestructure of a table or view. We’re not so worried about malicious hackers asthose users who embody the expression “a little knowledge is a dangerousthing.” Such users could be using a development copy of VFP or ADO viaAccess or Excel. To prevent this, return .F. in the BeforeModifyTable andBeforeModifyView events.

To prevent someone from changing or disabling the events forthe DBC (which would allow them to get around everything that database eventsare providing for you), return .F. in the BeforeModifyProc and BeforeAppendProcevents, and in BeforeDBSetProp if the property being changed is“DBCEvents” or “DBCEventFileName.” This approach isn’tfoolproof, since someone with a development copy of VFP can USE the DBC (thatis, open it as a table, which is what it really is), modify the code in theCode memo of the StoredProceduresSource record, and then close and COMPILEDATABASE. 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 badthing, though.

To prevent someone from seeing the code in the storedprocedures (for example, because it contains proprietary information), return.F. in the BeforeModifyProc and BeforeCopyProc events.

Conclusion

Can Dbc File Example

File storage within Visual FoxPro is similar to earlierversions of FoxPro, but with some powerful enhancements provided by theDatabase Container. New fields and field capabilities have been added. VFP 7still retains the trademark backward compatibility, allowing it to read alldBase 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, andthat workarounds are available for most of the incompatibilities.