2007-07Jul-31

Application scope gotcha

Visual FoxPro gives us the flexibility to embed files into the APP/EXE file. Other languages have the ability, but require the developer to use resource functions to access such files. In Visual FoxPro, on the other hand, accessing embedded files happens completely transparently. When you open a file that has the same file name as an embedded one, Visual FoxPro uses the internal one.

With only one application you rarely have to think about this mechanism. With multiple applications, however, the concept of application scope becomes important. As Visual FoxPro executes code it keeps track of what application this code belongs to. Code can only access files that are embedded in its own application.

For instance, you can include a DBF file into an application. This application can open the DBF file without any problems. However, a second application might try to open the same table again using code like:

USE DBF("alias") AGAIN IN 0

This code line could suddenly fail, because the second application doesn't have access to the embedded DBF file. Why am I talking about this? Because Visual FoxPro 9 introduces application scope issues to applications that hadn't had a problem before. I'm talking about the new report engine that relies on three external APP files to perform its duty.

In versions prior to 9 we often used embedded FOXUSER files to control the preview toolbar, for example, to remove the print button from the toolbar. The report printing routine first changed the resource file, printed the report and restored the resource file. If you leave this code unchanged and just replace the REPORT FORM . PREVIEW line, you might receive an error in the OpenResourceFile method. The offending code actually looks pretty innocent:

select 0
use (set("RESOURCE",1)) again shared
set order to 1

When VFP loads a FOXUSER file it always creates a temporary index. The code works just fine. if it would be executed in the same application scope as your application. It's part of REPORTPREVIEW.APP, though. What in fact happens is this:

Your application loads the embedded resource and VFP creates a temporary index. The report preview attempts to save and restore the preview window position. The USE statement should open the current resource file. Since the preview code runs in a different application scope than your application, Visual FoxPro searches for an external file with the same name. Two things could happen now:

If there's no external file of this name, Visual FoxPro throws an error message that the file cannot be found. If the file exists externally, Visual FoxPro opens it. As far as VFP is concerned, this file is now a different one than the one that VFP opened as a resource file.

Visual FoxPro opens tables always only once. When you open a table in multiple work areas or data sessions, all these instances refer to actual table entry. That's one reason why you need USE.AGAIN when you open a table in more than one work area. The table entry keeps track of opened index files. That's the reason that when you open a table again, it kind of inherits all open index files.

When you open the resource file again using code like the one above, Visual FoxPro reuses the existing table entry with the index that VFP created internally. In our scenario of an embedded resource file, however, the USE statement creates a new table entry which doesn't have an index associated with it. Consequently, you get an error on the SET ORDER TO line. You can avoid this particular problem by turning resource OFF while you access the new print preview form.

However, it's not just about a bug in the preview application. It's about an issue that might become more and more important as we incorporate features in our application that are spread out across multiple APP files. Application scope issues can come in many flavors including in restoring settings like SET CLASSLIB, SET PROCEDURE, and the like, that all might refer to embedded files.

2007-07Jul-31

Memo file is missing or invalid

Suddenly you get a "Memo file is missing or invalid" error message when you open a table. You close all open applications, run Visual FoxPro, open the table. and no error. All seems to be well, until a few weeks later you get the error again.

Seems that Visual FoxPro is a bit too sensitive to read-only files here. You can reproduce the issue when you run VFP and keep it open. Next set the read-only attribute of your FoxUser.FPT file. When you now run a second instance of VFP, VFP ignores the settings in your FoxUser file. To reproduce the error, enter the following in your Command Window:

USE SYS(2005) AGAIN

if you clear the read-only flag, you can open the table without error, even in the second instance that failed to open the file a few seconds ago. The error only happens, when the file wasn't read-only when the first instance opened it.

2007-07Jul-27

Looking at Locking

A long time I believed like so many others that Visual FoxPro uses the area beyond 2 GB for locking purposes. wOOdy was the first to point out to me that this isn't the case. He said that this fact further reduces the number of records in a table. So how does locking really work in Visual FoxPro?

The locking area starts at position 2,147,483,646 or 0x7FFFFFFE. Since positions start with 0, Visual FoxPro can make use of 0x7FFFFFFF bytes in a file. From that position the locking area grows down towards the beginning of the file. There's one byte in the locking area for each record.

As far as locking is concerned, Visual FoxPro treats the header as record number 0. This allows for using the same code for locking records as well as the header. Whenever Visual FoxPro updates the table header, it locks this byte first. Locking the header is required for adding a record. You can manually lock the header using

Lock("0",Alias())

The record lock bytes start at position 2,147,483,645 for record number 1 and go down from there. Hence, record number 2 is locked at 2,147,483,644, and so on. When Visual FoxPro locks a single record or the header, it only locks the corresponding byte.

FLOCK() locks the entire table. Technically, locking the table is identically to locking all records and the table header. If Visual FoxPro would only lock existing records this could lead to a raise condition. On one machine, Visual FoxPro would read the current number of records to determine which part of the locking area needs to be locked. After performing this calculation but before actually requesting the lock, another machine could add a new record. As a result, Visual FoxPro would either have to extent the locking area afterwards, or lock the table header before locking records.

Because that impacts performance, Visual FoxPro locks the highest possible number of records instead. The following expression returns the maximum number of records for any given table:

Int((2^31-Header()-2)/(Recsize()+1))

If you add more records than this to a table, you should either get an error message, or you would suffer from serious problems accessing the last records as they overlap with their own locking area. I haven't yet tested what happens in this case. Visual FoxPro starts locking at position:

2^31 - 1 - Int((2^31-Header()-2)/(Recsize()+1))

2007-05Jul-26

Invalid object name 'dbo.customers'

Microsoft SQL server assigns character fields a collate sequence that determines how a field is sorted. These collate sequences can be case-sensitive or case-insensitive which makes it possible to write queries without using UPPER() on every single field name.

Additionally, Microsoft SQL server maintains a collate sequence for the database as a whole. This collate sequence is not just the default for all new fields, as it might appear to be the case at first glance. The database collate sequence is used to parse queries, as well. When you pick a case-sensitive collate sequence for the database, all table, field, constraint, etc. names used in a query must match in case with their counterpart in the database.

A table that is named "Customers" can not be accessed as "customers" in a query. Doing so results in an "Invalid object name" error message. If you work with a single SQL server this behaviour might only be confusing. Unless you know about it, though, it can make working with multiple servers a real hassle.

The database collate sequence is specified at the time you setup the server and a second time when you create the database. Unless you specify a different collate sequence at this point, SQL Server will use the server default value.

Database settings are easy to miss when you compare multiple databases or run your scripts to update the structure. SQL Compare, for instance, reports that two databases are identical when they differ only in the database collate sequence. Most other tools likely do the same as they usually compare database objects.

Even after finding out what the problem is, you are still a step away from a solution. At least on SQL Server 2000 you cannot change this setting using Enterprise Manager. Instead you have to do it in code, for example, in a query window in Query Analyzer. The following statement requires that there's no other connection to the database. If you opened Query Analyzer and Enterprise Manager, you have to close one of them. The statement changes the database to case-insensitive configuration. Be prepared that this statement might take a few minutes to run:

ALTER DATABASE <case sensitive name of database>
  COLLATE SQL_Latin1_General_CP1_CI_AS

Afterwards it doesn't matter whether you access "Customers" or "customers". Change the collate sequence name as needed.