PRB: Update Conflict When Updating Oracle Varchar Data Types

• Microsoft Visual FoxPro for Windows, versions 5.0, 5.0a

SYMPTOMS

When updating a remote view based on an Oracle table that contains fields of type "varchar," you may receive an Update Conflict error.

CAUSE

When a remote view retrieves data from the Oracle backend, all varchar fields are converted to character fields. Visual FoxPro pads the character fields with spaces to accommodate the overall length of the varchar field.

The Update Conflict occurs as a result of a mismatch in the SQL WHERE clause criteria due to the padded spaces. For example: if an Oracle table has a varchar(5) field with a value of "aaa". the actual value that the remote view uses in the SQL WHERE clause is "aaa ". The Update Conflict occurs because "aaa"<>"aaa ".

NOTE: This occurs when the varchar field size is less than the default set in the 'Use Memo Fields for Fields <=:' setting in Tools/Options/Remote Data. Any Oracle varchar field with a size greater than this setting will be viewed as a memo field.

RESOLUTION

There are two possible methods of avoiding the Update Conflict:

Set the SQL WHERE clause option in the Update Criteria such that no varchar fields will be included in the SQL WHERE clause.

-or-

Change the data type of all the varchar fields to Memo. This can be accomplished by clicking on the Properties button on the Fields tab of the Remote View Designer.

STATUS

Microsoft is researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

Steps to Reproduce Behavior

1. Create an Oracle table that contains at least one varchar field.

2. Create a remote view based on the Oracle table created in step 1.

3. Make the varchar field updateable and set the SQL WHERE clause option to "Key and modified."

4. Browse the view, and make a change to the varchar field.

5. Close the view.