Hacker’s Guide to Visual FoxPro
An irreverent look at how Visual FoxPro really works. Tells you the inside scoop on every command, function, property, event and method of Visual FoxPro.
CPCONVERT(), CPCURRENT(), CPDBF(), IDXCOLLATE(), SET COLLATE, Set(“Collate”), SET NOCPTRANS
This slew of functions allows Visual FoxPro to manipulate variables, tables and indexes in a multiple-code-page environment.
Usage
cResult = CPConvert( cFromCP, cToCP, cText )
|
Parameter |
Value |
Meaning |
|
cFromCP |
Integer |
Original code page from which cText is to be translated. |
|
cToCP |
Integer |
Destination code page for result. |
|
cText |
Character or memo |
Text to be translated. |
|
cResult |
Character |
Text after translation. |
CPCONVERT() is not often needed. It accesses the underlying code page translation engine in Visual FoxPro to allow translation of individual phrases. Use this when you have code page-dependent information entered into a table with the incorrect code page for the table.
Example
WAIT WINDOW CPCONVERT(437,1252,"Jos"+CHR(130))
Usage
nCodePage = CPCurrent( nWhichOne )
|
Parameter |
Value |
Meaning |
|
nWhichOne |
Omitted or 1 |
In Windows, the Windows code page; in other FoxPro platforms, the operating system's code page. |
|
2 |
In Windows, the underlying (MS-DOS) code page. In the other platforms, this should return the same as 1. |
|
|
nCodePage |
Numeric |
Number of the code page requested. |
This function would be far simpler were it not for the idiosyncratic way in which IBM and Microsoft implemented their systems. MS-DOS was here first, and it uses the ASCII code set to produce symbols like the line and box-drawing characters. Windows, on the other hand, chose to base its character sets on the far more widely accepted ANSI characters. Since Windows is merely a thin veneer on top of MS-DOS, programmers on that platform need to be able to detect both code pages and deal with them.
Example
? CPCURRENT(2) && what's the MS-DOS code page?
Usage
nCodePage = CPDBF( [ cAlias | nWorkArea ] )
|
Parameter |
Value |
Meaning |
|
cAlias |
Character |
Alias of the table whose code page is to be returned. |
|
Omitted |
If nWorkArea is also omitted, use the table in the current work area. |
|
|
nWorkArea |
Integer |
Work area number of the table whose code page is to be returned. |
|
Omitted |
If cAlias is also omitted, use the table in the current work area. |
|
|
nCodePage |
Numeric |
Number of the code page requested. |
Use this function to detect the current code page assigned to a table. To change or zero out the code page, use the CPZERO.PRG, located in VFP\TOOLS\CPZERO. To accomplish the same thing in your program, use the GETCP() dialog to determine the correct code page, and then call the CPZERO.PRG, which you can include within your application, to change the code page.
Example
* The Wizard table should be flagged for US Windows codepage
DO CPZERO WITH HOME()+"\Wizards\Wizard.DBF",1252
USE Wizard.DBF IN 0 ALIAS DaWiz
? CPDBF("DaWiz") && should return 1252
Usage
cCollation = IDXCollate( [cCDXName, ] nIndexNumber
[, cAlias | nWorkArea ] )
|
Parameter |
Value |
Meaning |
|
cCDXName |
Character |
The name of the compound index file to examine. If no file is specified, IDXCollate() works on currently open indexes. |
|
nIndexNumber |
Integer |
As is typical of index functions, this refers to a number assigned to each open stand-alone index, in the order they were opened, then the tags of the structural index, then any other compound indexes open, with each tag enumerated in the order created. |
|
cAlias |
Character |
Alias of the table whose index collation is to be returned. |
|
Omitted |
If nWorkArea is also omitted, use the table in the current work area. |
|
|
nWorkArea |
Integer |
Work area number of the table whose index collation is to be returned. |
|
Omitted |
If cAlias is also omitted, use the table in the current work area. |
|
|
cCollation |
Empty string |
The number of the index specified exceeds the number of open indexes, or the work area number specified has no open file. |
|
Character |
The collation sequence which applies to this index. |
A collation sequence is the set of rules that determine the order in which values within the index will be presented. The most familiar (and the default) is MACHINE, which is a straight binary ordering of the values stored within the index. See SET COLLATE below for more details on this.
Example
USE HOME() + "\Wizards\BuilderD.DBF"
? IDXCOLLATE(1) && returns "MACHINE"
Usage
SET COLLATE TO cSequence
cSequence = SET( "COLLATE" )
|
Parameter |
Value |
Meaning |
|
cSequence |
Character |
Specifies the sequence in which items should be sorted. |
We Americans do not appreciate some of the conveniences of computing using the English language. Unfortunately, in being English-centric, many of the early developers of computers made data interchange between different languages a real challenge. Sort order is one very good example of this. In both Germanic and many Romance languages, the sort order is dictated not just by the individual value or weight of each character, but also the values of its nearby characters or the addition of diacritical marks or ligatures to those characters. A straight sort by the binary value of individual characters, even when correctly translated by code page into the correct alphabetic sequence, won’t do it.
A caution: Recently we’ve run into a few cases where the performance of FoxPro’s queries is far slower than we’d expect. In some cases, joins between tables where one table is lacking the proper index results in missing records. In all of these cases, the problem was that the indexes were created with a non-Machine collation. Our thumbrule: Always create index tags with the Machine sequence if they are to be used in Select statements.
Each collation sequence has specific ordering rules, needed for a specific purpose. For example, to list an alphabetical sequence of names in German, SET COLLATE TO “GERMAN”. A handy ordering sequence for case-insensitive sorts is the GENERAL sorting order.
|
All collation sequences except Machine use 2 bytes per key so the maximum size of a key expression is only 120 bytes, not 240 as Help states. |
Example
SET COLLATE TO "GENERAL"
Usage
SET NOCPTRANS to cFieldList
cFieldList = SET( "NOCPTRANS" )
SET NOCPTRANS is a relic from the 2.x days, where a field that should be treated as containing binary data, and not translated from one code page to another, was flagged by explicitly issuing the SET NOCPTRANS command each and every time it was open. Needless to say, somewhere in their code, nearly everyone forgot to issue the command, and binary data got translated into garbage left and right. Visual FoxPro solves the entire problem by allowing data to be specified as “Binary” within the Table Designer and with the NOCPTRANS keywords of the CREATE TABLE and ALTER TABLE commands.