1001 Things You Wanted To Know About Visual FoxPro phần 6 pptx

Chapter 7: Working with Data 223

CLOSE ALL

RETURN

PROCEDURE CheckStProc

DO dummy

DO OnlyaCH07

DO OnlybCH07

If you run this program from the command line you will see that with multiple DBCs

open, calling a stored procedure which exists only in one DBC is fine.It does not matter which

DBC is current, the correct procedure is located. However, if both DBCs contain a stored

procedure that is named the same, then the setting of the DBC is vitally important since Visual

FoxPro will always search the current database first and only then will it search any other open

databases.

Finally if NO DBC is defined as current, then Visual FoxPro executes the first procedure it

finds – in this example it is always the 'dummy' procedure in the aCH07 database container.

Reversing the order in which the two DBCs are opened in the ShoStPro program changes the

result for the last test. This suggests that Visual FoxPro is maintaining an internal collection of

open databases, in which the last database to be opened is at the head of the list, and is

searched first, when no database is set as current.

How to validate a database container

Visual FoxPro provides a VALIDATE DATABASE command that will run an internal consistency

check on the currently open database. Currently (Version 6.0a) this command can ONLY be

issued from the command window and by default its results are output to the main FoxPro

screen. Attempting to use it within an application causes an error.

You can validate a database without first gaining exclusive use, but the DBC index will

not be re-built, nor will you be able to fix any errors that the process may find. With exclusive

use you may choose either to rebuild the index (a plain VALIDATE DATABASE command will do

just that) or to invoke the repair mechanism by adding the ‘RECOVER’ clause to the command.

While not very sophisticated, the recovery option at least highlights anything that VFP

feels is wrong with your DBC and offers you options to either locate or delete a missing item

and to delete or re-build missing indexes (providing that the necessary information is available

in the DBC itself). The best way to avoid problems in the DBC is to ensure you always make

changes to its tables (or views) through the DBC’s own mechanisms. Avoid actions like

building temporary indexes outside the DBC or programmatically changing view or table

definitions without first getting exclusive use of the DBC.

In short, while not exactly fragile, the DBC relies heavily on its own internal consistency

and errors (real or imagined) will inevitably cause you problems sooner or later.

How to pack a database container

The Visual FoxPro database container is, itself, a normal Visual FoxPro table in which each

row contains the stored information for one object in the database. Like all Visual FoxPro

tables, deleting a record only marks that record for deletion and the physical record is not

224 1001 Things You Always Wanted to Know About Visual FoxPro

removed from the DBC. This means, over time, that a database can get large, even though it

actually contains very few current items.

The PACK DATABASE command is the only method that you should use to clean up a DBC.

Simply opening the DBC as a table and issuing a standard PACK command is not sufficient

because the DBC maintains an internal numbering system for its objects that will not be

updated unless the PACK DATABASE command is used. Using this command requires that you

gain exclusive use to the database.

Moving a database container

We mentioned earlier in this section that the only price for gaining all the functionality that a

database container provides is a minor modification to the header of the table to include a

backlink to the DBC. This is, indeed, a trivial thing UNTIL you try to move a database

container. Then its significance can assume monstrous proportions. The reason is that Visual

FoxPro stores the relative path from the table back to the owning DBC directly in the table

header. Providing that you always keep the DBC and all of its data tables in the same directory,

all will be well because all that gets stored is the name of the database container.

However, when you have a database container that is NOT in the same directory as its

tables you are laying yourself open to potential problems. We created a table in our working

directory (C:\VFP60\CH07) and attached it to a database that resided in the C:\TEMP

directory. The backlink added to the table was:

..\..\TEMP\TESTDBC.DBC

After moving this database container to the C:\WINDOWS\TEMP directory, any attempt to

open the table resulted in a ‘cannot resolve backlink’ error and the option to either locate the

missing DBC, delete the link and free the table (with all the dire consequences for long field

names that this entails) or to cancel. Being optimists we chose to locate the database container

and were given a dialog to find it. Unfortunately having found our DBC and selected it, we

were immediately confronted with Error 110 informing us that the "File must be opened

exclusively" Not very helpful!

Fixing the backlink for a table

So what can be done? Fortunately the structure of the DBF Header is listed in the Help file (see

the "Table File Structure" topic for more details) and Visual FoxPro provides us with some

neat low level file handling functions which allow us to open a file and read and write to it at

the byte level. So we can just write in the new location for the DBC and all will be well. The

only question is where to write it?

You will see from the Help file that the size of the table header is actually determined by

the formula:

32 + ( nFields * 32) +264 bytes

Where nFields is the number of fields in the table, which we could get using FCOUNT() – if

we could only open the table! (There is also a HEADER() – a useful little function that actually

Chapter 7: Working with Data 225

tells us how big the table header is. Unfortunately it also requires that we be able to open the

table.) But if we could open the table, we wouldn’t need to fix the backlink either.

The backlink itself is held as the last 263 bytes of the table header. However, the only

certain way of getting those vital 263 bytes is to try and read the maximum possible number of

bytes that could ever be in a table header. (Trying to read beyond the end of file does not

generate an error in a low level read, it just stops at the end of file marker.) Visual FoxPro is

limited to 255 fields per record so we need, using the formula above, to read in 8,456 bytes.

This is well within the new upper limit of 16,777,184 characters per character string or

memory variable so all is well.

Fortunately the field records section of the header always ends with a string of 13 "NULL"

characters (ASCII Character 0) followed by a "Carriage Return" (ASCII Character 13). So if

we locate this string within the block we have read from the table, we will have the start of the

backlink. The following function uses this technique to read the backlink information from a

table (when only the table file name is passed) or to write a new backlink string (pass both the

file name and the new backlink):

**********************************************************************

* Program....: BackLink.prg

* Compiler...: Visual FoxPro 06.00.8492.00 for Windows

* Abstract...: Sets/Returns Backlink Information from a table

* ...........: Pass both DBF File name (including extension) only to

* ...........: to return backlink, also pass new backlink string to

* ...........: write a new backlink

**********************************************************************

LPARAMETERS tcTable, tcDBCPath

LOCAL lnParms, lnHnd, lnHdrStart, lnHdrSize, lcBackLink, lcNewLink

lnParms = PCOUNT()

*** Check that the file exists

IF ! FILE( tcTable )

ERROR "9000: Cannot locate file " + tcTable

RETURN .F.

ENDIF

*** Open the file at low level - Read Only if just reading info

lnHnd = FOPEN( tcTable, IIF( lnParms > 1, 2, 0) )

*** Check file is open

IF lnHnd > 0

*** Backlink is last 263 bytes of the header so calculate position

*** Max header size is (32 + ( 255 * 32 ) + 264) = 8456 Bytes

lcStr = FREAD( lnHnd, 8456 )

*** Field records end with 13 NULLS + "CR"

lcFieldEnd = REPLICATE( CHR(0), 13 ) + CHR(13)

lnHeaderStart = AT( lcFieldEnd, lcStr ) + 13

*** Move file pointer to header start position

FSEEK( lnHnd, lnHeaderStart )

*** Read backlink

lcBackLink = UPPER( ALLTRIM( STRTRAN( FGETS( lnHnd, 263 ), CHR(0) ) ) )

*** If we are writing a new backlink

IF lnParms > 1

*** Get the path (max 263 characters!)

tcDBCPath = LEFT(tcDBCPath,263)

*** Pad it out to the full length with NULLS

lcNewLink = PADR( ALLTRIM( LOWER( tcDBCPath ) ), 263, CHR(0) )

*** Go to start of Backlink

FSEEK( lnHnd, lnHeaderStart )

226 1001 Things You Always Wanted to Know About Visual FoxPro

*** Write the new backlink information

FWRITE( lnHnd, lcNewLink )

*** Set the new backlink as the return value

lcBackLink = tcDbcPath

ENDIF

*** Close the file

FCLOSE(lnHnd)

ELSE

ERROR "9000: Unable to open table file"

lcBackLink = ""

ENDIF

*** Return the backlink

RETURN lcBackLink

What happens to views when I move the database container?

The good news is that moving a database container has no effect on views. Views are stored as

SQL statements inside the database container and, although they reference the DBC by name,

they do not hold any path information. So there is no need to worry about them if you move a

DBC from one location to another (phew!).

Renaming a database container

Renaming a database container presents a different set of problems. This time, both tables and

views are affected. Tables will be affected because of the backlink they hold - which will end

up pointing to something that no longer exists. However, this is relatively easy to fix, as we

have already seen, and can easily be automated. In this case, though, Views will be affected

because Visual FoxPro very helpfully includes the name of the DBC as part of the query that is

stored. Here is part of the output for a query (generated by the GENDBC.PRG utility that ships

with Visual FoxPro, and which can be found in the VFP\Tools sub-directory):

FUNCTION MakeView_TESTVIEW

***************** View setup for TESTVIEW ***************

CREATE SQL VIEW "TESTVIEW" ;

AS SELECT Optutil.config, Optutil.type, Optutil.classname FROM

testdbc!optutil

DBSetProp('TESTVIEW', 'View', 'Tables', 'testdbc!optutil')

* Props for the TESTVIEW.config field.

DBSetProp('TESTVIEW.config', 'Field', 'KeyField', .T.)

DBSetProp('TESTVIEW.config', 'Field', 'Updatable', .F.)

DBSetProp('TESTVIEW.config', 'Field', 'UpdateName', 'testdbc!optutil.config')

DBSetProp('TESTVIEW.config', 'Field', 'DataType', "C(20)")

* Props for the TESTVIEW.type field.

DBSetProp('TESTVIEW.type', 'Field', 'UpdateName', 'testdbc!optutil.type')

* Props for the TESTVIEW.classname field.

DBSetProp('TESTVIEW.classname', 'Field', 'UpdateName',

'testdbc!optutil.classname')

ENDFUNC

Notice that the database container is prepended to the table name on every occasion - not

just in the actual SELECT line but also as part of each field’s "updatename" property. This is, no

Chapter 7: Working with Data 227

doubt, very helpful when working with multiple database containers but is a royal pain when

you need to rename your one and only DBC. Unfortunately we have not been able to find a

good solution to this problem. The only thing we can suggest is that if you use Views, you

should avoid renaming your DBC if at all possible.

If you absolutely must rename the DBC, then the safest solution is to run GENDBC.PRG

before you rename it and extract all of the view definitions into a separate program file that you

can then edit to update all occurrences of the old name with the new. Once you have renamed

your DBC simply delete all of the views and run your edited program to re-create them in the

newly renamed database.

Note: The SQL code to generate a view is stored in the "properties" field of each "View"

record in the database container. Although it is stored as object code, the names of fields and

tables are visible as plain text. We have seen suggestions that involve hacking this properties

field directly to replace the DBC name but cannot advocate this practice! In our testing it

proved to be a thoroughly unreliable method which more often than not rendered the view both

unusable and unable to be edited. Using GENDBC may be less glamorous, but it is a lot safer!

Managing referential integrity in Visual FoxPro

The term "referential integrity", usually just abbreviated to "RI", means ensuring that the

records contained in related tables are consistent. In other words that every child record (at any

level) has a corresponding parent, and that any action that changes the key value used to

identify a parent is reflected in all of its children. The objective is to ensure that 'orphan'

records can never get into, or be left in place in, a table.

Visual FoxPro introduced built-in RI rules in Version 3.0. They are implemented by using

the persistent relationships between tables defined in the database container and triggers on the

tables to ensure that changes to key values in tables are handled according to rules that you

define. The standard RI builder allows for three kinds of rule as follows:

• Ignore: The default setting for all actions, no RI is enforced and any update to any

table is allowed to proceed - exactly as in earlier versions of FoxPro

• Cascade: Changes to the key value in the parent table are automatically reflected in

the corresponding foreign keys in all of the child tables to maintain the relationships

• Restrict: Changes which would result in a violation of RI are prohibited

Setting up RI in Visual FoxPro is quite straightforward and the RI builder handles all of

the work for you. Figure 7.3, below, shows the set-up in progress for a simple relational

structure involving four tables (We have used the tables from the VFP Samples "TestData"

database to illustrate this section). So far the following rules have been established: