SET VARCHARMAPPING Command
Specifies how character data expressions are mapped to query result sets.
SET VARCHARMAPPING ON | OFF
Parameters
- ON
Character data expressions are mapped to Varchar Field Type in query result sets.
- OFF
(Default) Character data expressions are mapped to character type fields in query result sets.
Remarks
The VARCHARMAPPING setting determines how character data expressions are mapped into query result sets created with the SELECT - SQL Command and the Query and View Designers.
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
See Also
Reference
Visual FoxPro Data and Field Types
SELECT - SQL Command