Visual FoxPro

Choosing Data Types

noreply@blogger.com (Albert Chuah) — Tue, 14 Jun 2011 02:28:00 -0700

As you create each table field you also choose a data type for the data the field is to store. When you choose a field’s data type, you’re deciding:

What kind of values to allow in the field. For example, you can’t store text in a Numeric field. How much storage space Visual FoxPro is to set aside for the values stored in that field. For example, any value with the Currency data type uses 8 bytes of storage.
What types of operations can be performed on the values in that field. For example, Visual FoxPro can find the sum of Numeric or Currency values but not of Character or General values.
Whether Visual FoxPro can index or sort values in the field . You can’t sort or create an index for Memo or General fields.

Tip For phone numbers, part numbers, and other numbers you don’t intend to use for mathematical calculations, you should select the Character data type, not the Numeric data type.

To choose a data type for a field

In the Table Designer, choose a data type from the Type list.

-or-

Use the CREATE TABLE command.

For example, to create and open the table products with three fields, prod_id, prod_name, and Values As you build a new table, you can specify whether one or more table fields will accept null values. When you use a null value, you are documenting the fact that information that would normally be stored in a field or record is not currently available. For example, an employee’s health benefits or tax status may be undetermined at the time a record is populated. Rather than storing a zero or a blank, which could be interpreted to have meaning, you could store a null value in the field until the information becomes available.

To control entering null values per field

In the Table Designer, select or clear the Null column for the field. When the Null column is selected, you can enter null values in the field.

-or-

Use the NULL and NOT NULL clauses of the CREATE TABLE command.

For example, the following command creates and opens a table that does not permit null values for the
cust_id and company fields but does permit null values in the contact field:

CREATE TABLE customer (cust_id C(6) NOT NULL, company C(40) NOT NULL, contact C(30) NULL)

You can also control whether null values are permitted in table fields by using the SET NULL ON
command.

To permit null values in all table fields

In the Table Designer, select the Null column for each table field.

-or-

Use the SET NULL ON command before using the CREATE TABLE command.

When you issue the SET NULL ON command, Visual FoxPro automatically checks the NULL column for each table field as you add fields in the Table Designer. If you issue the SET NULL command before issuing CREATE TABLE, you don’t have to specify the NULL or NOT NULL clauses. For example, the following code creates a table that allows nulls in every table field:

SET NULL ON
CREATE TABLE test (field1 C(6), field2 C(40), field3 Y)

The presence of null values affects the behavior of tables and indexes. For example, if you use APPEND FROM or INSERT INTO to copy records from a table containing null values to a table that does not permit null values, then appended fields that contained null values would be treated as blank, empty, or zero in the current table.

For more information about how null values interact with Visual FoxPro commands, see Handling Null
Values.

Visual FoxPro Fields

noreply@blogger.com (Albert Chuah) — Tue, 14 Jun 2011 02:23:00 -0700

Creating Fields
When you create table fields, you determine how data is identified and stored in the table by specifying a field name, a data type, and a field width. You also can control what data is allowed into the field by specifying whether the field allows null values, has a default value, or must meet validation rules. Setting the display properties, you can specify the type of form control created when the field is added onto a form, the format for the contents of the fields, or the caption that labels the content of field. Note Tables in Visual FoxPro can contain up to 255 fields. If one or more fields can contain null values, the maximum number of fields the table can contain is reduced by one, from 255 to 254.

Naming Fields
You specify field names as you build a new table. These field names can be 10 characters long for free tables or 128 characters long for database tables. If you remove a table from a database, the table’s long field names are truncated to 10 characters.

To name a table field

In the Table Designer, enter a field name in the Name box.

-or-

Use the CREATE TABLE command or ALTER TABLE command.

For example, to create and open the table customer with three fields, cust_id, company, and contact, you could issue the following command:

CREATE TABLE customer (cust_id C(6), company C(40), contact C(30))

In the previous example, the C(6) signifies a field with Character data and a field width of 6. Choosing
data types for your table fields is discussed later in this section.

Using the ALTER TABLE command, you add fields, company, and contact to an existing table customer:
ALTER TABLE customer ;
ADD COLUMN (company C(40), contact C(30))

Using Short Field Names
When you create a table in a database, Visual FoxPro stores the long name for the table’s fields in a record of the .dbc file. The first 10 characters of the long name are also stored in the .dbf file as the field name.

If the first 10 characters of the long field name are not unique to the table, Visual FoxPro generates a name that is the first n characters of the long name with the sequential number value appended to the end so that the field name is 10 characters. For example, these long field names are converted to the following 10-character names:

Long Name Short Name
customer_contact_name customer_c
customer_contact_address customer_2

customer_contact_city customer_3
customer_contact_fax customer11

While a table is associated with a database, you must use the long field names to refer to table fields. It is not possible to use the 10-character field names to refer to fields of a table in a database. If you remove a table from its database, the long names for the fields are lost and you must use the 10-character field names (stored in the .dbf) as the field names.

You can use long field names composed of characters, not numbers, in your index files. However, if you create an index using long field names and then remove the referenced table from the database, your index will not work. In this case, you can either shorten the names in the index and then rebuild the index; or delete the index and re-create it, using short field names. For information on deleting an index, see “Deleting an Index”.

The rules for creating long field names are the same as those for creating any Visual FoxPro identifier, except that the names can contain up to 128 characters.

For more information about naming Visual FoxPro identifiers, see Creating Visual FoxPro Names.

Saving a Table as HTML

noreply@blogger.com (Albert Chuah) — Tue, 14 Jun 2011 02:16:00 -0700

You can use the Save As HTML option on the File menu when you are browsing a table to save the contents of a table as an HTML (Hypertext Markup Language) file.

To save a table as HTML
1. Open the table.
2. Browse the table by issuing the BROWSE command in the Command window or by choosing Browse from the View menu.
3. Choose Save As HTML on the File menu.
4. Enter the name of the HTML file to create and choose Save.

Copying and Editing Table Structure

noreply@blogger.com (Albert Chuah) — Tue, 14 Jun 2011 02:15:00 -0700

To modify the structure of an existing table, you can use the Table Designer or ALTER TABLE.

Alternatively, you can create a new table based on the structure of an existing table, then modify the structure of the new table.

To copy and edit a table structure
1. Open the original table.
2. Use the COPY STRUCTURE EXTENDED command to produce a new table containing the structural information of the old table.
3. Edit the new table containing the structural information to alter the structure of any new table created from that information.
4. Create a new table using the CREATE FROM command.
The new table is empty.
5. Use APPEND FROM or one of the data copying commands to fill the table if necessary.

Duplicating a Table

noreply@blogger.com (Albert Chuah) — Tue, 14 Jun 2011 02:13:00 -0700

You can make a copy of a tables structure, its stored procedures, trigger expressions, and default field values by using the language. There is no menu option to perform the same function. This procedure does not copy the contents of the table.

To duplicate a table
1. Open the original table.
2. Use the COPY STRUCTURE command to make a copy of the original table.

3. Open the empty table created with the COPY STRUCTURE command.
4. Use the APPEND FROM command to copy the data from the original table.

Deleting a Free Table

noreply@blogger.com (Albert Chuah) — Tue, 14 Jun 2011 02:08:00 -0700

If a table is not associated with a database, you can delete the table file through the Project Manager or with the DELETE FILE command.

To delete a free table

In the Project Manager, select the free table, choose Remove, and then choose Delete.

-or-

Use the DELETE FILE command.

For example, if sample is the current table, the following code closes the table and deletes the file from disk:

USE
DELETE FILE sample.dbf

The file you want to delete cannot be open when DELETE FILE is issued. If you delete a table that has other associated files, such as a memo file (.fpt) or index files (.cdx or .idx), be sure to delete those files as well. For example, if the file sample.dbf also has an associated memo file, you could delete both files with the following commands:

USE
DELETE FILE sample.dbf
DELETE FILE sample.fpt

Deleting a Database Table

noreply@blogger.com (Albert Chuah) — Tue, 14 Jun 2011 02:07:00 -0700

If a table is associated with a database, you can delete the table as a by-product of removing the table from its database. Deleting a table is different from removing a table from a database, however. If you just want to remove a table from a database but do not want to physically delete the table from disk, see “Removing a Table from a Database” in Creating Databases.

To delete a database table from disk

In the Project Manager, select the table name, choose Remove, and then choose Delete.

-or-

From the Database Designer, select the table, choose Remove from the Database menu, and then choose Delete.

-or-

To delete the table plus all primary indexes, default values, and validation rules associated with the table, use the DROP TABLE command.

-or-

To delete just the table file (.dbf), use the ERASE command.

Caution If you use the ERASE command on tables associated with a database, the command does not update the backlink to the database and can cause table access errors.

The following code opens the database testdata and deletes the table orditems and its indexes, default values, and validation rules:

OPEN DATABASE testdata

DROP TABLE orditems

If you delete a table using the DELETE clause of the REMOVE TABLE command, you also remove the

associated .fpt memo file and .cdx structural index file.

To rename a free table

noreply@blogger.com (Albert Chuah) — Tue, 14 Jun 2011 02:03:00 -0700

Use the RENAME command.

Caution If you use the RENAME command on tables associated with a database, the command does not update the backlink to the database and can cause table access errors.

Renaming a Table

noreply@blogger.com (Albert Chuah) — Tue, 14 Jun 2011 02:02:00 -0700

You can rename database tables through the interface because you are changing the long name. If you remove the table from the database, the file name for the table retains the original name. Free tables do not have a long name and can only be renamed using the language.

To rename a table in a database

In the Database Designer, select the table to rename.
From the Database menu, choose Modify.
In the Table Designer, type a new name for the table in the Table Name box on the Table tab.

Naming a Table

noreply@blogger.com (Albert Chuah) — Tue, 14 Jun 2011 02:02:00 -0700

When you issue the CREATE TABLE command, you specify the file name for the .dbf file Visual FoxPro creates to store your new table. The file name is the default table name for both database and free tables. Table names can consist of letters, digits, or underscores and must begin with a letter or underscore.

If your table is in a database, you can also specify a long table name. Long table names can contain up to 128 characters and can be used in place of short file names to identify the table in the database. Visual FoxPro displays long table names, if you’ve defined them, whenever the table appears in the interface, such as in the Project Manager, the Database Designer, the Query Designer, and the View Designer, as well as in the title bar of a Browse window.

To give a database table a long name

In the Table Designer, enter a long name in the Table Name box.

-or-

Use the NAME clause of the CREATE TABLE command.

For example, the following code creates the table vendintl and gives the table a more understandable long name of vendors_international:

CREATE TABLE vendintl NAME vendors_international (company C(40))

You can also use the Table Designer to rename tables or add long names to tables that were created without long names. For example, when you add a free table to a database, you can use the Table Designer to add a long table name. Long names can contain letters, digits, or underscores, and must begin with a letter or underscore. You can’t use spaces in long table names.

Creating a Free Table

noreply@blogger.com (Albert Chuah) — Tue, 14 Jun 2011 02:01:00 -0700

A free table is a table that is not associated with a database. You might want to create a free table, for example, to store lookup information that many databases share.

To create a new free table

In the Project Manager, select Free Tables, and then New to open the Table Designer.

-or-

Use the FREE keyword with the CREATE TABLE command.

For example, the following code creates the free table smalltbl with one column, called name:

CLOSE DATABASES

CREATE TABLE smalltbl FREE (name c(50))

If no database is open at the time you create the table, you do not need to use the keyword FREE.

Creating a Database Table

noreply@blogger.com (Albert Chuah) — Tue, 14 Jun 2011 02:01:00 -0700

You can create a new table in a database through the menu system, the Project Manager, or through the language. As you create the table, you can create long table and field names, default field values, field and record-level rules, as well as triggers.

To create a new database table

In the Project Manager, select a database, then Tables, and then New to open the Table Designer.

-or-

Use the CREATE TABLE command with a database open.

For example, the following code creates the table smalltbl with one column, called name:

OPEN DATABASE Sales

CREATE TABLE smalltbl (name c(50))

The new table is automatically associated with the database that is open at the time you create it. This association is defined by a backlink stored in the table’s header record.

Creating Tables

noreply@blogger.com (Albert Chuah) — Tue, 14 Jun 2011 01:59:00 -0700

You can create a table in a database, or just create a free table not associated with a database. If you put
the table in a database, you can create long table and field names for database tables. You can also take
advantage of data dictionary capabilities for database tables, long field names, default field values, fieldand
record-level rules, as well as triggers.

Designing Database vs. Free Tables
A Visual FoxPro table, or .dbf file, can exist in one of two states: either as a database table (a table
associated with a database) or as a free table that is not associated with any database. Tables associated
with a database have several benefits over free tables. When a table is a part of a database you can create:

Long names for the table and for each field in the table.
Captions and comments for each table field.
Default values, input masks, and format for table fields.
Default control class for table fields.
Field-level and record-level rules.
Primary key indexes and table relationships to support referential integrity rules.
One trigger for each INSERT, UPDATE, or DELETE event.

Some features apply only to database tables. For information about associating tables with a database,
see Creating Databases.
Database tables have properties that free tables don’t.

You can design and create a table interactively with the Table Designer, accessible through the Project
Manager or the File menu, or you can create a table programmatically with the language. This section
primarily describes building a table programmatically. For information on using the Table Designer to
build tables interactively, see Creating Tables and Indexes, in the User’s Guide.
You use the following commands to create and edit a table programmatically:

Commands for Creating and Editing Tables

ALTER TABLE
CLOSE TABLES
CREATE TABLE
DELETE FILE
REMOVE TABLE
RENAME TABLE
DROP TABLE

How shall we proceed Functions and Procedures ?

noreply@blogger.com (Albert Chuah) — Tue, 14 Jun 2011 01:42:00 -0700

In prior versions of FoxPro, applications were limited to a single active procedure file at any time. This meant that all user-defined functions and commonly used procedures were kept in a single file. With Visual FoxPro, the ability to have multiple procedure files active at any given time provides much more flexibility. Such procedures can now be grouped logically, by functionality, into different procedure files that can be loaded incrementally as needed. The downside, of course, is that procedure files are loaded into memory by Visual FoxPro and held until explicitly released. This may not be the best use of that precious resource.

Fortunately, it is also possible to define procedure classes. The individual functions that would previously have been kept in a procedure file can now become methods of a class. This approach has the benefit that, when defined visually in the Class Designer, all the functions in the procedure can be neatly viewed under the methods tab of the property sheet. Procedure classes containing specific functionality can be dropped onto forms that require this functionality. A second major benefit is that a procedure class can be sub-classed - for those special situations when standard functionality must be augmented.

The approach that we like is a combination of these two approaches. We recommend using a procedure file for truly generic functions (i.e. those that are used, unchanged, by many different applications). For example, our procedure file contains a NewID function for generating surrogate primary keys, a SetPath function to set the path for the application and a few key functions that Visual FoxPro should have, but doesn't. Two examples of such functions are the Str2Exp and Exp2Str functions (used later in this chapter These functions, as the names imply, are used to convert character strings into the values of another specified data
type and vice versa.

Separate Procedure Classes contain application specific functionality. For example, an accounting application might require several functions to calculate tax and invoice totals. The Accounting Procedures Class can be dropped onto any form that requires these functions – or can be instantiated as an Application Level object. Clearly, such functions are not generic, nor are they even required by the entire application. By grouping them into a single procedure class, we can make this functionality available at the specific parts of the application that require it without compromising the rest of the application.

Parameters (an aside)
We have all used parameters extensively in FoxPro but in Visual FoxPro’s object oriented environment, parameters have taken on a new importance as the principle means of implementing messaging – the very lifeblood of an OO application! (We have more to say on this subject later!)

By reference, by value?
Parameters are passed either by reference or by value. When a parameter is passed to a function or procedure by reference, any changes made to its value in the called code are reflected in the original value in the calling program. Conversely when a parameter is passed by value, the called code can change that value but the value in the calling program remains unchanged. Visual FoxPro interprets the code being called by the mechanism by which parameters are passed. So when the calling syntax looks like this:
luRetVal = CallMyFunction( param1, param2 )
Visual FoxPro treats this as a Function Call and passes the parameters by value. However if the same code is called like this:
DO CallMyFunction WITH param1, param2
then Visual FoxPro treats this as a Procedure Call and passes the parameters by reference.

The old coding rule that a “Function must always return a value” is not really true in Visual FoxPro, but it does make sense when the calling syntax is considered.

You can change this default behavior in two ways. One way is to:
SET UDFPARMS TO REFERENCE or SET UDFPARMS TO VALUE

However, we do not consider this a good idea because it affects the way all functions in your entire application handle the parameters they are passed. (It is never a good idea to use a global solution to solve a local problem). In this case there is a simple solution because parameters can be passed by value explicitly just by enclosing them in parentheses. Thus:
DO CallMyFunction WITH (param1), (param2)

passes the parameters by value, even though the syntax used would normally cause them to be passed by reference. To pass parameters explicitly by reference, simply preface the parameter with the “@” symbol. (This is, by the way, the only way to pass an entire array to a procedure, function or method). So we could also make our function call and pass its parameters by reference like this:
luRetVal = CallMyFunction( @param1, @param2 )

How do I know what was passed?
There are two ways for a function to determine how many parameters were passed to it. The
PARAMETERS() function returns the number of parameters that were passed to the most recently called function or procedure. This can give unexpected results since it is reset each time a function or procedure is called. Most importantly, it is also reset by functions that are not called explicitly, such as ON KEY LABEL routines.

A better way of determining how many parameters were passed to a function is to use the PCOUNT() function. This always returns the number of parameters that were passed to the currently executing code. Save yourself a lot of grief and unnecessary hair pulling by always using PCOUNT() for determining the number of parameters passed.

How should I position my parameters?
The best advice is that if a function takes optional parameters, you should place these at the end of the parameter list. PCOUNT() can then be used in the function to determine whether or not the optional parameters were passed allowing the function to take the appropriate action.

You can take advantage of the fact that Visual FoxPro always initializes parameters as logical false. By setting up your function to expect a logical false as its default, you can invoke the function without passing it any parameters. Then, in those cases where you want an alternative behavior, just invoke the function by passing it a logical true.

How can I return multiple values from a function?
Of course, returning values is simply the reverse of passing parameters - with one gotcha!. While you can easily pass multiple parameters to a function, there is no obvious mechanism for returning multiple values! The RETURN command only allows a single value to be passed back to the calling program.

One solution is to pass multiple values as a comma-delimited string. This is a little messy, however, as you will need to convert your values into character format to build the return string and then parse out the individual values again in the receiving code.

Another possibility is to define all the values you want populated by the function as Private variables in the calling program. As such, they will be available to any function or procedure that is called subsequently and they can be populated directly. However, this is neither specific nor easy to maintain and is not really a good solution.

A better possibility is to create an array in the calling code for the return values and then pass that array by reference. The called function can then simply populate the array and the values will also be available in the calling program. This is workable, at the very least, and was probably the most common method of handling the issue prior to the introduction of Visual FoxPro.

Once again Visual FoxPro has made life a lot easier. Returning multiple values from a UDF is easy if you create, and use, a parameter class. Ours is based on the Line baseclass and is named xParameters. You can find it in the CH02.VCX class library. All it needs is one custom array property, aParameters, to hold the return values and this line of code in its INIT():
LPARAMETERS taArray
ACOPY(taArray, This.aParameters)

The user-defined function can then simply populate its own local array with the values it needs to return and create the parameter object on the fly — and populate the object’s array property with a single line of code:
RETURN CREATOBJECT( 'xParameters', @laArray )

Date and time functions
Visual FoxPro has several handy dandy, built in functions for manipulating dates. The functions below illustrate how they can be used to perform a few of the most common tasks required when dealing with dates in your applications.

Elapsed time
Simply subtracting one DateTime expression from another gives you the elapsed time – this is good. Unfortunately Visual FoxPro gives you this result as the number of seconds between the two – this is bad. This value is seldom directly useful! You can use this little set of functions, which rely on the Modulus operator (%), to calculate the components of elapsed time in days, hours, minutes and seconds.

FUNCTION GetDays( tnElapsedSeconds )
RETURN INT(tnElapsedSeconds / 86400)
FUNCTION GetHours( tnElapsedSeconds )
RETURN INT(( tnElapsedSeconds % 86400 ) / 3600 )
FUNCTION GetMinutes( tnElapsedSeconds )
RETURN INT(( tnElapsedSeconds % 3600 ) / 60 )
FUNCTION GetSeconds( tnElapsedSeconds )
RETURN INT( tnElapsedSeconds % 60 )

Of course, there is more than one way to skin the fox. You can also use a single function to return an array containing the elapsed time positionally, with days as its first element through to seconds as its fourth like so:
FUNCTION GetElapsedTime( tnElapsedSeconds )

LOCAL laTime[4]
laTime[1] = INT( tnElapsedSeconds / 86400 )
laTime[2] = INT(( tnElapsedSeconds % 86400 ) / 3600 )
laTime[3] = INT(( tnElapsedSeconds % 3600 ) / 60 )
laTime[4] = INT( tnElapsedSeconds % 60 )
RETURN CREATEOBJECT( 'xParameters', @laTime )

If you prefer named parameters to the positional variety, the following code accomplishes the task:

FUNCTION GetElapsedTime( tnElapsedSeconds )
LOCAL loObject
loObject = CREATEOBJECT( 'Line' )
WITH loObject
.AddProperty( 'nDays', INT( tnElapsedSeconds / 86400 ) )
.AddProperty( 'nHours', INT(( tnElapsedSeconds % 86400 ) / 3600 ) )
.AddProperty( 'nMins', INT(( tnElapsedSeconds % 3600 ) / 60 ) )
.AddProperty( 'nSecs', INT( tnElapsedSeconds % 60 ) )
ENDWITH
RETURN loObject

Alternatively, if you merely require a string that contains the elapsed time in words, you can just reduce it to a single line of code!

FUNCTION GetElapsedTime( tnElapsedSeconds )
RETURN PADL( INT( tnElapsedSeconds / 86400 ), 3 )+' Days ';
+ PADL( INT(( tnElapsedSeconds % 86400 ) / 3600 ), 2, '0' )+' Hrs ' ;
+ PADL( INT(( tnElapsedSeconds % 3600 ) / 60 ), 2, '0')+' Min ' ;
+ PADL( INT( tnElapsedSeconds % 60 ), 2, '0' )+' Sec '

Date in words
Converting a value from date format into text can be a tricky business, especially when you are writing international applications. Visual FoxPro makes this task a lot easier with its native MDY(), CMONTH(), CDOW(), MONTH(), DAY() and YEAR() functions, to name but a few.

Version 6.0, with its ability to use strict dates, makes this task even easier. The following function provides one example of how to use these functions.

FUNCTION DateInWords( tdDate )
RETURN CDOW( tdDate ) + ', ' + MDY( tdDate )

However, the function listed above will not attach the ordinal suffix to the day portion of the date. If your application requires these suffixes when formatting the date in words, use the longer form of the function listed below. You could even extract the portion that calculates the suffix and place it in a function called MakeOrdinal. It can then be invoked any time you need to format a given number n as nth.

FUNCTION DateInWords( tdDate )
LOCAL lnDay, lnNdx, lcSuffix[31]
*** Initialize suffix for day
lnDay = DAY( tdDate )
lnNdx = lnDay % 10
IF NOT BETWEEN( lnNdx, 1, 3 )
lcSuffix = 'th'
ELSE
IF INT( lnDay / 10 ) = 1
lcSuffix = 'th'
ELSE

lcSuffix = SUBSTR( 'stndrd', ( 2 * lnNdx ) - 1, 2 )
ENDIF
ENDIF
RETURN CDOW( tdDate ) + ', ' + CMONTH( tdDate ) + ;
' ' + ALLTRIM( STR( lnDay )) + lcSuffix + ;
', ' + ALLTRIM( STR( YEAR( tdDate )))

Gotcha! Strict date format and parameterized views
Visual FoxPro's StrictDate format is especially comforting with the specter of the millennium bug looming large in front of us. At least it is as we are writing this. There, is however, one small bug that you should be aware of. If you have SET STRICTDATE TO 2 and try to open a parameterized view that takes a date as its parameter, you will be in for trouble. If the view parameter is not defined or is not in scope when you open or re-query the view, the friendly little dialog box prompting for the view parameter will not accept anything you enter. It will keep saying you have entered an ambiguous date/datetime constant.

The workaround is to ensure your view parameter is defined and in scope before trying to open or re-query the view. This means that, if your view is part of a form’s data environment, its NoDataOnLoad property must be set to avoid getting the dialog as the form loads.

The other workaround, setting StrictDate to 0 and then back to 2, is not recommended. As we have already mentioned, using a global solution for a local problem is a little bit like swatting flies with a sledgehammer.

Working with numbers
Mathematical calculations have been handled fairly well since the days of Eniac and Maniac, except for the notable bug in the Pentium math co-processor. The most common problems arise because many calculations produce irrational results such as numbers that carry on for an infinite number of decimal places. Rounding errors are impossible to avoid because computing demands these numbers be represented in a finite form. The study of numerical analysis deals with how to minimize these errors by changing the order in which mathematical operations are performed as well as providing methods such as the trapezoidal method for calculating the area under the curve. A discussion of this topic is beyond the scope of this book, but we can give you some tips and gotchas to watch out for when working with numbers in your application.

Converting numbers to strings
Converting integers to strings is fairly straightforward. ALLTRIM( STR( lnSomeNumber ) ) will handle the conversion if the integer contains ten digits or less. If the integer contains more than ten digits, this function will produce a string in scientific notation format unless you specify the length of the string result as the second parameter. When converting numeric values containing decimal points or currency values, it is probably better to use another function.

Although it can be accomplished using the STR() function, it is difficult to write a generic conversion routine. In order to convert the entire number you must specify both the total length of the number (including the decimal point) and the number of digits to the right of the decimal point. Thus STR(1234.5678) will produce '1235' as its result, and to get the correct conversion you must specify STR(1234.5678, 9, 4).

In Visual FoxPro 6.0, the TRANSFORM() function has been extended so that when called without any formatting parameters, it simply returns the passed value as its equivalent character string. Thus TRANSFORM(1234.5678) will correctly return '1234.5678'.

In all versions of Visual FoxPro you can use ALLTRIM( PADL ( lnSomeNumber, 32 ) ) to get the same result (providing that the total length of lnSomeNumber is less than thirty-two digits).

Configuration files

noreply@blogger.com (Albert Chuah) — Tue, 14 Jun 2011 01:20:00 -0700

There are several ways of handling the initialization of Visual FoxPro, but the easiest, and most flexible, is still to use a configuration file. Visual FoxPro uses a simple formatted text file, called "CONFIG.FPW" by default, as the source for a number of environmental values which can be set as the system starts up.

How to specify a config.fpw file
The actual file name does not matter as you can specify the configuration file which Visual FoxPro is to use as a command line parameter by using the '-c' switch in the command line which is used to start Visual FoxPro. So to set up your own configuration file (for example for a specific application) use the following command line:
G:\VFP60\APPS\MYAPP.EXE –cG:\VFP60\myconfig.txt

How VFP locates its configuration file
The default behavior of Visual FoxPro, in the absence of a specific configuration file, is to search the following locations for a file named 'config.fpw' in this order:

The current working directory
The directory from which Visual FoxPro is being started
All directories in the DOS path

If you are using the '-c' switch to specify a file named other than the default, or in a specific location, you must include the fully qualified path and file name. This provides a simple method of handling the initialization of different applications installed on the same machine.

How VFP starts up when no configuration file is found
If no configuration file is found or specified, then Visual FoxPro will be started with only those settings that are specified in the Options Dialog (located on the TOOLS pad of the main Visual FoxPro menu).

Why these settings in particular?
The answer is simply that all of the settings from this dialog are actually stored in the
Windows Registry and can be found under the Registry Key:
HKEY_CURRENT_USER\Software\Microsoft\VisualFoxPro\6.0\Options

Including a configuration file in the project
One little "trap" to watch out for – if you add a configuration file named 'config.fpw' to your project as a text file, it will be INCLUDED in the project by default. When you build an .exe from the project, the config.fpw file will be built into the resulting file. Since Visual FoxPro looks for a file named 'config.fpw' during startup, it will always find the built-in version first and will not look any further. This would apply even if you were to explicitly specify a different configuration file using the '-C' switch! Your specified file would be ignored, and the built-in congfiguration file would be executed. The best solution is NOT to add your configuration file to the project at all, but if you do, to make sure that it is marked as 'excluded' from the build.

How to suppress a configuration file
Starting Visual FoxPro with the command line parameter '-c' alone suppresses the default behavior and prevents any configuration file that may be found from being run. The result is that you can force Visual FoxPro to start up with its default settings only.

How to determine which configuration file is being used
One of the commonest problems with configuration files is failing to ensure that Visual FoxPro is reading the correct CONFIG.FPW. As noted above, if Visual FoxPro can't find a configuration file, it will search the DOS path and simply use the first one it finds. This could be anywhere on a network. The SYS(2019) function will return the full path and file name of the configuration file that Visual FoxPro actually used. If no configuration file was found, the function merely returns an empty string.

What goes into the configuration file?
Now that we know something about how the configuration file is used, the next question is what can we put into it? The answer is quite a lot! Essentially there are three categories of things that can be specified in the configuration file as follows:

Special settings
There are a number of settings that can ONLY be made in a configuration file. (For full details see the "Special Terms for Configuration Files" topic in the Visual FoxPro online Help and the entry under "Configuring Visual FoxPro" in the online documentation.) Notice that the ability to set the location for temporary files is also available in the Options Dialog. Specifying the TMPFILES location in the configuration file will override any setting that is made there and can be useful when you need to differentiate between development and run time locations for temporary files.

One other setting that can be used in the configuration file only, but which is not included in the Help file list is "SCREEN = OFF". This prevents the Visual FoxPro main screen from being displayed when your application starts and prevents the annoying 'flash' of the VFP screen that still occurs even if your startup program turns off the main screen with the command _Screen.Visible = .F. (which enables you to present your application's initial form, or a "splash" screen, without displaying the main VFP window first).

SET Commands
Virtually all of the standard SET commands can be issued in a configuration file. The only thing to watch out for is that some settings are actually scoped to the datasession. (See the "Set DataSession" topic in the online Help for a full listing.) So, there is little point in specifying them in the configuration file if you plan to use Private DataSessions for your forms. The syntax for specifying SET commands in the configuration file is a simple assignment in which the keyword 'SET' is omitted:

DEFAULT = C:\VFP60\TIPSBOOK
DATE = BRITISH

Commands
Well actually you can only specify one (count 'em!) command, and it must be the last line of the configuration file. Like other configuration file entries, it is entered as a simple assignment:

COMMAND = DO setupfox

What is the use of just ONE command? Well, quite a lot really because that one command can call a FoxPro program, and that can do a lot of things!

One of the main limitations of the configuration file is that you cannot actually set things that are internal to Visual FoxPro (e.g. system variables) because, when the configuration file runs, Visual FoxPro hasn't actually started. Using this setting allows you to specify a program file to run immediately after Visual FoxPro has started up – even before the command window is displayed.

This program can then be used to set up your development environment the way that you really want it. For example, here are some of the things that are in our standard setup file:

*** Standard 'SET' options (These could be entered directly into the Config File)
SET TALK OFF
SET BELL OFF
SET SAFETY OFF
SET STATUS OFF
SET STATUS BAR ON
SET DATE BRITISH
SET CENTURY ON
*** Re-define Function Keys
SET FUNCTION 2 TO "CLOSE TABLES ALL;CLEAR WINDOWS;"
SET FUNCTION 3 TO "CANCEL;SET SYSMENU TO DEFA;ACTIVATE WINDOW COMMAND;"
SET FUNCTION 4 TO "CLEAR ALL;SET CLASSLIB TO;SET PROC TO;"
SET FUNCTION 5 TO "DISP STRU;"
SET FUNCTION 6 TO "DISP STAT;"
SET FUNCTION 7 TO "DISP MEMO LIKE *"
SET FUNCTION 8 TO "CLEAR;CLEAR WINDOWS;"
SET FUNCTION 9 TO "MODI FORM "

SET FUNCTION 10 TO "MODI COMM "
SET FUNCTION 11 TO "DO setpath WITH "
SET FUNCTION 12 TO "=CHGDEFA(); "
***Set up the Screen properties
_SCREEN.CAPTION = "VFP 6.0 (Development Mode)"
_SCREEN.CLOSABLE = .F.
_SCREEN.FONTNAME = "Arial"
_SCREEN.FONTSIZE = 10
_SCREEN.FONTBOLD = .F.
*** Run Cobb Editor Extensions
DO G:\VFP60\CEE6\CEE6.APP
*** Set up some On Key Labels
ON KEY LABEL CTRL+F10 suspend
ON KEY LABEL CTRL+F11 o=SYS(1270)
ON KEY LABEL CTRL+F12 RELEASE o
*** Set up any system variables required
_Include = HOME() + "Foxpro.h"
_Throttle = 0.1
*** Set up any standard "Public" variables
PUBLIC gcUserName, gcAppPath, gcDataPath
STORE "" TO gcUserName, gcAppPath, gcDataPath
*** Run Standard Path Set-up
DO setpath WITH 1

As you can see, apart from setting up the basic VFP system environment and handling our own particular requirements, this 'one' command available in the configuration file has now been used to run a couple of other programs (CEE and our own SetPath procedure) so we get three for the price of one. By putting these settings in a program, we also have a simple way of re-initialising the environment by re-running this program at any time.

Giving VFP a path
Visual FoxPro has the ability to use its own search path and, as a general rule, you should always specify a path for Visual FoxPro for both development and production environments - although they may be very different (see above for one way of handling this requirement). Setting a path for Visual FoxPro does not change the normal DOS search path but can significantly speed up your application by limiting the places that Visual FoxPro has to search in order to find its files – especially in a network environment.

How VFP looks for files
By default Visual FoxPro uses the current directory as its 'path' and you can always restore this setting by simply issuing: SET PATH TO
However for more sophisticated applications, and in development, you will normally have some sort of directory structure and you should always set a proper search path to include all required directories.
Setting the default directory Normally you still will want to set a default (or "working") directory – this is where Visual FoxPro will look first for any file that it requires. This can be done in a number of ways,
depending on your requirements:

Specify a default in the Configuration File using DEFAULT = <path to directory>
Set the default directly in code using SET DEFAULT TO <path to directory>
Change to a directory using the CD <path to directory> and issue SET PATH TO

Note that using the Get, Put or Locate functions (e.g. GetDir()) does not change either the default directory or the path. To change the default directory interactively use: SET DEFAULT TO ( GetDir() ). (The 'CD' (or 'CHDIR') command can also be used to change both drive and directory to the specified location).

Using the SET PATH command
Setting the path is simplicity itself. Just issue the SET PATH command followed by a list of the directories that you wish to include. You do not need to fully qualify subdirectories – separating them with either commas or semi-colons is sufficient. The example shows a typical Visual FoxPro search path:

SET PATH TO G:\VFP60;C:\VFP60\TIPSBOOK\;DATA;FORMS;LIBS;PROGS;UTILS;

To retrieve the current path setting you can use the SET() function (which will work with most of the Visual FoxPro SET commands) as shown below. You can assign the result directly to a variable or, as shown below, directly to the clipboard so that you can then paste the current path into a program or documentation file:

_ClipText = SET( 'PATH' )

Visual FoxPro allows the use of both UNC path names, like:

\\SERVERNAME\DIRECTORYNAME\.

and allows the use of embedded spaces (when enclosed in quotation marks) in directory names like:

"..\COMMON DIRECTORY\"

However, while the latter may be allowed, we tend to subscribe to the principle that 'although you can use embedded spaces, arsenic is quicker.' (The same, by the way, applies to file names with spaces!) While improving readability, spaces can also cause problems when trying to handle files and directories programmatically and we still feel that the best advice is to avoid them wherever possible in applications. For example, the following code works perfectly well for conventional directory names, but will fail if the selected directory contains embedded spaces:

LOCAL lcDir
lcDir = GETDIR()
IF ! EMPTY(lcDir)
SET DEFAULT TO &lcDir
ENDIF

Making sure VFP is only started once
So far so good! We have managed to cover the process of starting VFP and setting up the basic environments for both development and production. At this point one of the things we all come across is the absent-minded user who minimizes an application and then, ten minutes later, starts a fresh copy from their desktop. Within the hour they have six copies of the application running and are complaining that their machine is slowing down. What to do? (Apart from shooting the user which, appealing though the idea may be, is generally frowned upon and, depending on their seniority, may also be a career-limiting move.)

Closing VFP down
Thus far we have been concentrating on setting up and managing the Visual FoxPro environment, however, the way in which you close down Visual FoxPro is just as important. In the run-time environment there are two ways of initiating the closing down process. Firstly through the use of the CLEAR EVENTS command, either within a menu or in the Release method of a form. Secondly through the standard windows close button of the main Visual FoxPro window. Fortunately, Visual FoxPro provides us with a global handler for the close down process, irrespective if how it is initiated – the ON SHUTDOWN command.

What is an On ShutDown procedure?
Like other ON commands, the ON SHUTDOWN command is implemented by a special handler that is outside of the normal Visual FoxPro event processing loop. When invoked, control is immediately transferred to whatever command, or function, has been specified as the target. This is by far the best way (if not the only way) to ensure that Visual FoxPro closes down cleanly without the irritating the 'Cannot Quit Visual FoxPro' message.

What triggers an On Shutdown procedure?
The command specified in ON SHUTDOWN is executed if you try to exit Visual FoxPro by clicking the 'Close' button in the main Visual FoxPro screen, by choosing Exit from the FoxPro control menu, or by issuing the QUIT command in a program (or the command window!). Additionally it will be triggered if you try to exit Windows while Visual FoxPro is open. (Control is returned to Visual FoxPro and the specified ON SHUTDOWN procedure is executed.)

Starting Visual FoxPro

noreply@blogger.com (Albert Chuah) — Tue, 14 Jun 2011 00:47:00 -0700

Visual FoxPro, like most applications, supports several 'command line switches'. Most of the
time these tend to get forgotten, but they do exist and are all documented in the online Help
files in the 'Customizing Visual FoxPro Startup Options' topic. Probably the most useful ones
to remember are:

-C which specifies the configuration file to use
-T which suppresses the VFP sign-on screen
-R which refreshes the VFP registry settings (Note, the settings that get refreshed are

those relating to information about VFP, such as file associations. The –R switch does
not update the settings controlled through Visual FoxPro's Options Dialog. This is
only done when 'Set As Default' is used to exit the dialog).
So to start Visual FoxPro without the sign-on screen display and with a refresh of the
registry settings the required command line would be:
G:\VFP60\VFP6.EXE –R -T

About Visual FoxPro

noreply@blogger.com (Albert Chuah) — Mon, 13 Jun 2011 23:50:00 -0700

Visual FoxPro is a data-centric object-oriented and procedural programming language produced by Microsoft. It is derived from FoxPro (originally known as FoxBASE) which was developed by Fox Software beginning in 1984. Fox Technologies merged with Microsoft in 1992, after which the software acquired further features and the prefix "Visual". The last version of FoxPro (2.6) worked under Mac OS, DOS, Windows, and Unix: Visual FoxPro 3.0, the first "Visual" version, reduced platform support to only Mac and Windows, and later versions were Windows-only. The current version of Visual FoxPro is COM-based and Microsoft has stated that they do not intend to create a Microsoft .NET version.

FoxPro originated as a member of the class of languages commonly referred to as "xBase" languages, which have syntax based on the dBase programming language. Other members of the xBase language family include Clipper and Recital. (A history of the early years of xBase can be found in the dBase article.)

Visual FoxPro, commonly abbreviated as VFP, is tightly integrated with its own relational database engine, which extends FoxPro's xBase capabilities to support SQL query and data manipulation. Unlike most database management systems, Visual FoxPro is a full-featured, dynamic programming language that does not require the use of an additional general-purpose programming environment. It can be used to write not just traditional "fat client" applications, but also middleware and web applications.

In late 2002, it was demonstrated that Visual FoxPro can run on Linux under the Wine Windows compatibility suite. In 2003, this led to complaints by Microsoft: it was claimed that the deployment of runtime FoxPro code on non-Windows machines violates the End User License Agreement.

In December 2005, VFP broke into the top 20 on Tiobe index for the first time. In March 2007 it was at position 19, making it a "B" language. As of August, 2010, VFP (combined with xBase) is at position 25.

In March 2007, Microsoft announced that there will be no VFP 10, thus making VFP 9 (released to manufacturing on December 17, 2004) the last commercial VFP release from Microsoft. The support of Version 9 is ongoing with service packs that were released December 8, 2005 and October 11, 2007.

At the time of the end of life announcement, work on the next release codenamed Sedna (named after a recently discovered dwarf planet) which was built on top of the VFP9 codebase had already begun. "Sedna" is a set of add-ons to VFP 9.0 of xBase components to support a number of interoperability scenarios with various Microsoft technologies including SQL Server 2005, .NET Framework, Windows Vista, Office 2007, Windows Search and Team Foundation Server (TFS). Microsoft released Sedna under the Shared source license on the CodePlex site. Microsoft has clarified that the VFP core will still remain closed source. Sedna was released on January 25, 2008[4]. As of March 2008, all xBase components of the VFP 9 SP2 (including Sedna) were available for community-development on CodePlex.

In late March 2007 a grassroots campaign was started by the Spanish-speaking FoxPro community at MasFoxPro (MoreFoxPro in English) to sign a petition to Microsoft to continue updating Visual FoxPro or release it to the community as Open Source. On April 3, 2007 the movement was noted by the technical press.