ADO OleDB Visual Foxpro

Question

Hi.

Specific question: Does VARCHARMAPPING=True work on Ado OleDB connections for Visual FoxPro? and if so how should the connectionstring look like?

We're developing a Delphi application that uses parts of an old visual foxpro database as a base. This morning I had a working connection to the database that for all intents and purposes did the correct thing, but now, no longer.

The thing that did work was the varcharmapping, I.e. no padding.

Delphi 2010, using ADOConnection, AdoQuery. Using Microsoft Visual FoxPro 9.0 Sp2 . If there are any newer please tell me.

The connectionstring looks like this:

'Provider=VFPOLEDB.1;Data Source=C:\PROGRAMDATA\Folder\DataFolder;Collating Sequence=SWEFIN;DSN="";VARCHARMAPPING=True;'

I've tried every permutation (well feels like it) that there is of all attributes, but to no avail. Any suggestions much appreciated.

Answer 1

The SET VARCHARMAPPING command is used to determine how Visual Foxpro generates cursors (similar to but very different from ADO recordsets) within a Foxpro application. This command strictly affects Foxpro cursors and isn't supported through the Visual FoxPro Ole DB provider.

I believe the best alternative is to modify your FoxPro databases to use varchar data type for columns. The reasoning for this issue is the same difference between the char and varchar data types in SQL Server.

A char is a fixed width column. If the data in a record does not completely fill the column, padding is added. This is not true for varchar, which is a variable length field, and only allocates as much space as required (i.e. no padding).

Update Even when using the varchar data type, padding can still occur. So, for compatibility reasons, you should always trim values.