DOCUMENT:Q269881  01-MAY-2001  [foxpro]
TITLE   :PRB: Problems with VFP Driver "Fetch Data In Background" Option
PRODUCT :Microsoft FoxPro
PROD/VER:WINDOWS:6.0
OPER/SYS:
KEYWORDS:kbDatabase kbODBC kbvfp600 KbDBFDBC kbGrpDSFox kbDSupport

======================================================================
-------------------------------------------------------------------------------
The information in this article applies to:

 - Microsoft ODBC Driver for Visual FoxPro (Build 6.00.8281.00), version 6.0 
-------------------------------------------------------------------------------

SYMPTOMS
========

When you configure a Data Source Name (DSN) by using the Microsoft Visual FoxPro
driver, you have a Fetch Data in Background (FDIB) option. This option was
designed to allow records to be fetched progressively.

FDIB is selected by default when you create a DSN to a FoxPro data source.
However, there is inconsistent behavior when this option is chosen. For
example:

 - When you use a FoxPro DSN with FDIB selected from a COM SERVER in an
   Microsoft Transaction Server package, deadlocks can occur when the COM server
   is under stress.

 - Queries that use a DSN to a FoxPro data source with FDIB selected may
   intermittently fail on multiprocessor computers.

 - An Active Server Pages (ASP) application that uses ActiveX Data Objects (ADO)
   with the Visual FoxPro ODBC Driver randomly crashes.

 - Inconsistent numeric values may be returned from your FoxPro data source.
   This may cause inaccurate results from any mathematical calculations you
   perform on the result set.

RESOLUTION
==========

To resolve these issues, deselect the Fetch Data In Background option on the
FoxPro DSN.


MORE INFORMATION
================

Steps to Reproduce Behavior
---------------------------

Use the following code to demonstrate how the FDIB setting may result in
erroneous numeric data being returned from a FoxPro data source. The program
does the following:

 - Creates a Table (DBF) file and a Database (DBC) file.

 - Populates the one numeric field in the DBF (12,2) with random values between
   -25257.00 and 53860.00.

 - Selects all records from the table. This is done by using either native
   FoxPro commands, SQL PassThrough using with FDIB = YES, or SQL PassThrough
   with FDIB = NO.

 - SUMS the records in the result set and prints the results to the screen.

To view the results:

 - In Native FoxPro Commands, run the program a few times in succession.

 - In SQL PassThrough with FDIB = YES, run the program with a .T. parameter:

   Do <ProgramName> with .T.

 - In SQL PassThrough with FDIB = NO, run the program with a .F. parameter:

   Do <ProgramName> with .F.

Note that there are inconsistent SUM results when using the result set generated
from SQL PassThrough with Fetch Data In Back Ground selected.

   ********* BEGIN SAMPLE CODE *********
   LPARAMETERS llWith_FetchDataInBack
   CD JUSTPATH(SYS(16))
   SET STRICTDATE TO 0

   LOCAL lnSQLHand, lcSQLCmd, lnSQLSuccess, lcDSN_WithOutFetch, ;
   	lcDSN_WithFetch, lcFDIB, lcOldTalk

   lcOldTalk = SET('TALK')
   SET TALK ON

   IF PARAMETERS() = 1
   	MakeData()
   	IF  VARTYPE(llWith_FetchDataInBack) = "L"
   		lcFDIB = IIF(llWith_FetchDataInBack,"YES","NO")
   	ELSE
   		lcFDIB = "YES"
   	ENDIF
   	? "Using ODBC with Fetch Data In Background = " + lcFDIB
   	lcDSN = "DRIVER=Microsoft Visual FoxPro Driver;" + ;
   		"UID=;Deleted=Yes;Null=Yes;Collate=Machine;BackgroundFetch=" + lcFDIB + ";" + ;
   		"Exclusive=No;SourceType=DBC;SourceDB=" + JUSTPATH(SYS(16)) + "\FDIB.DBC;"

   	lnSQLHand = SQLSTRINGCONNECT(lcDSN)

   	? "SQL Handle: " + TRANSFORM(lnSQLHand)
   	IF lnSQLHand < 0
   		RaiseError()
   		RETURN .F.
   	ENDIF

   	lcSQLCmd = "SELECT * FROM FDIB"

   	lnSQLSuccess = SQLEXEC(lnSQLHand,lcSQLCmd,"ResultSet")
   	? "SQL Query Success: " + TRANSFORM(lnSQLSuccess)
   	IF lnSQLSuccess < 0
   		RaiseError()
   		RETURN .F.
   	ENDIF

   ELSE

   	MakeData()
   	? "Using Native Fox Commands"
   	SELECT * FROM fdib INTO CURSOR ResultSet

   ENDIF

   ? TRANSFORM(RECCOUNT("ResultSet")) + " Records Returned."
   SUM n_amount TO amt_sum
   RELEASE amt_sum
   USE IN ResultSet
   SQLDISCONNECT(0)
   CLOSE DATABASES ALL
   SET TALK &lcOldTalk

   *~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   PROCEDURE RaiseError
   	LOCAL laErrArray[1], lcErrMsg
   	AERROR(laErrArray)
   	lcErrMsg = laErrArray(3)
   	MESSAGEBOX(lcErrMsg,"SQL Error!",0)
   	SQLDISCONNECT(0)

   PROCEDURE MakeData
   	LOCAL lnLower, lnUpper, lnCounter, lnInsertVal
   	CLOSE DATA ALL
   	IF !FILE('FDIB.DBC')
   		CREATE DATABASE 'FDIB.DBC'
   		CREATE TABLE 'FDIB.DBF' NAME 'FDIB' (n_amount N(12, 2) NOT NULL)
   		lnLower = -25257.00
   		lnUpper = 53860.00
   		WAIT WINDOW "Populating table...please wait." NOCLEAR NOWAIT
   		FOR lnCounter = 1 TO 1000
   			lnInsertVal = INT((lnUpper - lnLower + 1) * RAND( ) + lnLower)
   			INSERT INTO fdib VALUES (lnInsertVal)
   		ENDFOR
   		WAIT CLEAR
   		CLOSE DATABASES ALL
   	ENDIF
   ********* END SAMPLE CODE *********

REFERENCES
==========

For additional information about the Fetch Data In Backgroup option on the
Microsoft Visual FoxPro ODBC driver, click the article numbers below to view the
articles in the Microsoft Knowledge Base:

   Q236019 PRB: ASP App Using ADO with Visual FoxPro ODBC Driver Stops IIS

   Q175801 PRB: Accessing FoxPro Table in ASP Returns Error 80040e14

   Q270106 PRB: Visual FoxPro COM DLL in an MTS Package May Hang Under Stress

(c) Microsoft Corporation 200, All Rights Reserved. Contributions by Trevor
Hancock, Microsoft Corporation.


Additional query words:

======================================================================
Keywords          : kbDatabase kbODBC kbvfp600 KbDBFDBC kbGrpDSFox kbDSupport 
Technology        : kbVFPsearch kbAudDeveloper kbODBCSearch kbODBCVFP600828100
Version           : WINDOWS:6.0
Issue type        : kbprb
Solution Type     : kbnofix

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS
PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND.  MICROSOFT DISCLAIMS
ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.  IN NO
EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR
ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL,
CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF
MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGES.  SOME STATES DO NOT ALLOW THE EXCLUSION
OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES
SO THE FOREGOING LIMITATION MAY NOT APPLY.

Copyright Microsoft Corporation 2001.