BUG: SET ANSI ON Default Setting in VFPODBC Driver

• Microsoft Visual FoxPro ODBC Driver, versions 1.0, 5.0

SYMPTOMS

Queries with filter criteria run through the Visual FoxPro ODBC (VFPODBC) driver could return fewer records than the same query run in Microsoft Visual FoxPro.

CAUSE

The VFPODBC driver defaults SET ANSI to ON where the default for SET ANSI in Microsoft Visual FoxPro is OFF. This setting cannot be changed in the VFPODBC driver.

RESOLUTION

To work around not being able to use SET ANSI OFF for partial matches, you can either use wildcards in the SQL Select statement, or create an OLE Automation Server in Microsoft Visual FoxPro to manipulate the data in the Microsoft Visual FoxPro database.

Wildcard Example

In the example given in the Steps to Reproduce Behavior section below, replace the line:

   ?SQLEXEC(nh,'SELECT * FROM Customer WHERE Cust_id = "A"','cur1')

   ?SQLEXEC(nh,'SELECT * FROM Customer WHERE Cust_id LIKE "A%"','cur1')

   ?SQLEXEC(nh,'SELECT * FROM Customer WHERE Cust_id = "A"','cur2')

   ?SQLEXEC(nh,'SELECT * FROM Customer WHERE Cust_id LIKE "A%"','cur2')

Note that the above workaround will not be fully Rushmore optimized.

STATUS

Microsoft has confirmed this to be a bug in the Microsoft products listed at the beginning of this article. We are researching this bug and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

In Drvvfp.hlp (the VFPODBC Help file), there are references to the SET ANSI command being supported in the VFPODBC driver. While issuing this command to the ODBC driver does not return an error, it does not affect the SET ANSI setting.

Drvvfp.hlp also lists the SET ANSI command in the list of unsupported commands.

Steps to Reproduce Behavior

Run the following from a program in Microsoft Visual FoxPro:

   CLOSE DATABASES ALL
   CLEAR ALL
   CLEAR
   *Create the datasource pointed to the sample data
   DECLARE Integer SQLConfigDataSource in odbccp32.dll Integer, Integer, ;
      String, String

   lcPath = HOME()+'samples\data'

   settings="DSN=VFP Tables"+chr(0)+;
             "Description=VFP Desc"+chr(0)+;
             "SourceDB="+lcPath+chr(0)+;
             "SourceType=DBF"

   ? SQLConfigDataSource(0,1,"Microsoft Visual FoxPro Driver",settings)

   nh = SQLCONNECT('VFP Tables')
   ? nh
   ? SQLEXEC(nh,'SET ANSI OFF')
   ? SQLEXEC(nh,'SELECT * FROM Customer WHERE Cust_id = "A"','cur1')
   BROWSE LAST NOWAIT
   ? SQLEXEC(nh,'SET ANSI ON')
   ? SQLEXEC(nh,'SELECT * FROM Customer WHERE Cust_id = "A"','cur2')
   BROWSE LAST NOWAIT
   ? SQLDISCONNECT(nh)

Keywords          : FxprgRushmore FxprgSet FxprgSql vfoxwin kbcode
Version           : 1.0 5.0
Platform          : WINDOWS
Issue type        : kbbug