This can be done, but it is not a trivial exercise. At first you may think "Oh, this is a piece of cake! Even though the grid is positioned on the correct record, this record is in the middle of the page and you must use the PAGE DOWN key to see the last record. If this is a common requirement for your grids, this sample code in the Init method of EndOfGrid.

Author:Juramar Fetaur
Language:English (Spanish)
Published (Last):25 March 2007
PDF File Size:5.52 Mb
ePub File Size:5.19 Mb
Price:Free* [*Free Regsitration Required]

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.

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. Currently Version 6. 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.

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. 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. This means, over time, that a database can get large, even though it actually contains very few current items.

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. 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. 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 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? Unfortunately it also requires that we be able to open the table. The backlink itself is held as the last bytes of the table header.

However, the only certain way of getting those vital 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 fields per record so we need, using the formula above, to read in 8, bytes. This is well within the new upper limit of 16,, characters per character string or memory variable so all is well.

So if we locate this string within the block we have read from the table, we will have the start of the backlink. 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. This is, no Chapter 7: Working with Data 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.

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. 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. 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.

Figure 7. The rules defined in the example above resulted in lines of code in 12 separate procedures. Adding more tables and more rules increases the amount of code.

Moreover this code is not well commented, and in early versions, contained several bugs which could cause it to fail under certain conditions - at least one of which has persisted through into the latest version of Visual FoxPro.

The following code is taken directly from the stored procedures generated by VFP V6. Apart from this specific bug, and despite the criticisms leveled at the mechanism for generating the RI code, the code actually works well when used with tables that are structured in the way that was expected. It is certainly easier to use the RI builder than to try and write your own code! There is, however, one additional caution to bear in mind when using the RI builder. What on earth does this mean? Clearly it is a serious warning or Visual FoxPro would not generate it!

The answer is that the regular index used as the target for the persistent relationship between the Orders and OrdItems table is actually based on a compound key comprising the foreign key to the Orders Table plus the Item Number. This was set up so that when items are displayed they will appear in line number order.

Not really an unreasonable thing to do! The problem is that any rule set up on this table which needs to refer to the parent table will use, as a key for the SEEK , the concatenated field values from the child table. As the warning says, you can fairly easily edit the generated code so that the correct key value is used in these situations.

Unfortunately your changes will only hold good as long as you do not re-generate the RI code. The real answer to this problem, however, is very simple. As we have already suggested, surrogate keys should always be used to relate tables so that you have an unambiguous method of joining two tables together.

In addition to their other virtues, they also ensure that you never need to use compound keys in order to enforce RI. What about other RI options? In practice this is not necessarily the case providing that you design for that contingency!

Consider the situation when a salesman, who is responsible for a group of customers and hence for their orders, leaves the company. But what about orders the salesman actually took while working for the company? If we simply delete the salesman, an RI "Restrict" rule would disallow the delete because there are "child" records for that key. What is really needed is a variant on the deletion rule that says: "If the parent record is being deleted, and a valid key is supplied assign any existing child records to the specified key.

Using triggers and rules in Visual FoxPro First we need some definitions. Triggers and rules can only be implemented for tables that are part of a database container to allow the developer to handle issues relating to data integrity. Triggers only fire when data is actually written to the physical table - so they cannot be used for checking values as they are entered into a buffered table. There are actually two sets of rules available - Field Rules and Table rules.

Rules are fired whenever the object to which they refer loses focus - whether the table is buffered or not - and so can be used for validating data as it is entered. A trigger, as implemented in Visual FoxPro, is a test that is applied whenever the database detects a change being written to one of its tables. There are, therefore, three types of triggers - one for each of the types of change that can occur Insert, Update and Delete. The essence of a trigger is that the expression which calls it must return a logical value indicating whether the change should be allowed to proceed or not.

This is simplest if the trigger itself always returns a logical value, so if a trigger returns a value of. Triggers cannot be used to change values in the record that has caused them to fire, but they can be used to make changes in other tables.

A very common use of triggers is, therefore, for the creation of audit logs! By the way, if you are wondering why this restriction exists, just consider what would happen if you could change the current row in code that was called whenever changes in the current row were detected! Like triggers, calls to rules must also return a logical value; but unlike triggers, they can make changes to the data in the row to which they refer. A rule can also be used to modify fields that are not part of the UI for example a "last changed" date, or a user id field.

In practice the only difference between Field and Table rules is when they are fired. Both triggers and rules apply like any other stored procedure whether the table in question is opened in an application or just in a Browse window. The differences between Triggers and Rules can be summarized like this: Table 7.

Normally this indicates that the data that you have in your table already conflicts with the rule you are trying to apply. You will sometimes see a similar problem when trying to add a candidate index to a populated table. When you alter a table to add a trigger or rule, Visual FoxPro applies that test to all existing records - if the data in an existing record fails the rule, then you will get this error. The only solution is to correct the data before re-applying the rule.

In fact we might have added these buttons directly to the form class itself but have not done so because we can never be sure that we will always want both buttons Remember - you cannot 2 56 Things You Always Wanted to Know About Visual FoxPro delete an object that is defined by the parent class in either a sub-class or an instance.

This follows from our assertion see Chapter 3 that if the. Clearly it is a serious warning or Visual FoxPro would not generate.


1001 things you wanted to know about Visual FoxPro



1001 Things You Wanted to Know about Visual FoxPro


CD4001 PDF

1001 Things You Wanted to Know About Visual FoxPro



1001 Things You Always Wanted to Know About Visual FoxPro


Related Articles