Versión en español de este artículo

Solving the "padded varchar" problem

How to avoid unwanted spaces in back-end tables

If you use remote views to update data on a back-end database, you've probably come up against the "padded varchar" problem.

When Visual FoxPro reads data from a back-end (such as SQL Server or Oracle) into a remote view, it converts any varchar columns into ordinary character fields. Each field is padded with spaces to bring it to its maximum width. If you then use the remote view to return updates to the server, it is these padded values that are sent back. The back-end stores the extra spaces in the table, thus robbing you of the benefits of using varchars.

In VFP 7.0 and below, there's not a lot you can do about this. In fact, this is one the main reasons that developers avoid using remote views when updating data, preferring instead to send UPDATE and INSERT commands directly via SQL pass-through. VFP 8.0 provides an alternative approach in the shape of the CursorAdapter; by adding a trim function to a CursorAdapter's ConversionFunc property, you can eliminate the extra spaces. But moving from remote views to CursorAdapters could involve quite a bit of work.

A better solution

VFP 9.0 offers a much better solution. Unlike earlier releases, version 9.0 supports the varchar data type in remote views. If both the RV and back-end fields are varchars, the data will not be padded with spaces, so the problem won't arise.

However, this does not happen automatically. By default, any varchar columns on the back-end will still be mapped to character fields in the view, as in earlier versions. To get the benefits of varchars, you have to explicitly change the data type in the view.

If you are starting a new application and you haven't yet created your remote views, you're in luck. All you have to do is to execute the following command before you create the views:

CURSORSETPROP("MapVarchar", .T., 0)

This tells VFP to map varchars in the view to varchars on the back-end. By passing 0 as the third parameter, you stipulate that this setting will apply to all views created in the session. This only affects new views that you create; any existing views are unaffected. The setting is not persistent, so be sure to execute the above command before you create any views in the current session.

Do it retrospectively

If you have already created your remote views, you will have to retrospectively alter the data types for each of the relevant fields. One way of doing that is from within the view designer. From the Query menu, open the View SQL window. You will see the SELECT statement which defines the view, followed by a series of DBSETPROP() calls. These will include, for each field in the view, a line of code which sets the field's data type. For example:

DBSetProp(ThisView+".company","Field","DataType","C(40)")

The C(40) in this example indicates a (fixed width) character field, 40 characters wide. To make this into a varchar, simply change that C to a V. Repeat the process for all the fields that you want to map to varchars, then save the view and close the designer. The change will take effect the next time you open the view.

Alternatively, you can use the command window to do the job. In this case, type a command similar to the one in the above example, but precede the column name with the name of the view, in place of ThisView. Again, change the data type from C to V:

DBSetProp("Customer.company","Field","DataType","V(40)")

Repeat the process for each field that you want to map in each of your remote views. As before, the changes will take effect the next time you open the view.

Mike Lewis Consultants Ltd. February 2005.