BeforeRowColChange | Hackers Guide to Visual FoxPro 7.0

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.
nWhichOne	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.
cAlias	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.
nWorkArea	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.
cAlias	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.
nWorkArea	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.
cCollation	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.