Specifies how character data expressions are mapped to query result sets.
SET VARCHARMAPPING ON | OFF
- Character data expressions are mapped to Varchar Field Type in query result sets.
- (Default) Character data expressions are mapped to character type fields in query result sets.
In scenarios where you want a query result to use fixed length character fields, SET VARCHARMAPPING to OFF so character fields are not mapped to variable length varchar type fields.
In the following example, the absence of character data expressions implies the character data should be preserved in its original format, include padding to maintain the fixed length of the character fields. In this case you should SET VARCHARMAPPING to OFF. SET VARCHARMAPPING to OFF to mimic the behavior in Visual FoxPro 8.0 and earlier versions.
SELECT * FROM customers *** -or- SELECT companyname, contactname FROM customers
In the next example, the presence of character data expressions means the query result set will contain character data of variable length. Here you may want the result set to use variable length Varchar type fields to prevent the results from being padded with extra characters. In this case you should SET VARCHARMAPPING to ON.
SELECT ALLTRIM(companyname), ALLTRIM(contactname) FROM customers
The VARCHARMAPPING setting also controls mapping of field expressions using PADL( ) | PADR( ) | PADC( ) Functions where the second parameter is not constant.
SELECT LEN(PADR(field1,field2)) FROM customers INTO CURSOR tmpcusts
The VARCHARMAPPING setting is scoped to the current data session. This command is supported both at design time and at run time, and can also be set in Config.fpw, the Visual FoxPro configuration file. See Special Terms for Configuration Files for information on setting VARCHARMAPPING in Config.fpw.
If you are using calculated fields such as those created by using the SET FIELDS Command, the VARCHARMAPPING setting will affect how this field is treated. If the calculated field evaluates to a character expression of variable length, such as following example, the field will be treated as a Varchar type if SET VARCHARMAPPING is ON. This can impact subsequent usage of that field such as with the COPY TO Command.
SET VARCHARMAPPING ON SET SAFETY OFF CLOSE DATABASES ALL USE HOME(2) + 'Northwind\Customers' SET FIELDS GLOBAL SET FIELDS TO cField = ALLTRIM(CompanyName) COPY TO crsTemp SET FIELDS LOCAL SET FIELDS OFF SET FIELDS TO SELECT 0 USE crsTemp LIST STRUCTURE