Microsoft KB Archive/269881

= PRB: Problems with Visual FoxPro Driver &quot;Fetch Data In Background&quot; Option =

Article ID: 269881

Article Last Modified on 8/27/2002

-

APPLIES TO


 * Microsoft Open Database Connectivity Driver for Visual FoxPro 5.0

-



This article was previously published under Q269881



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.



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  with .T. In SQL PassThrough with FDIB = NO, run the program with a .F. parameter: Do  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) = &quot;L&quot; lcFDIB = IIF(llWith_FetchDataInBack,&quot;YES&quot;,&quot;NO&quot;) ELSE lcFDIB = &quot;YES&quot; ENDIF ? &quot;Using ODBC with Fetch Data In Background = &quot; + lcFDIB lcDSN = &quot;DRIVER=Microsoft Visual FoxPro Driver;&quot; + ; &quot;UID=;Deleted=Yes;Null=Yes;Collate=Machine;BackgroundFetch=&quot; + lcFDIB + &quot;;&quot; + ; &quot;Exclusive=No;SourceType=DBC;SourceDB=&quot; + JUSTPATH(SYS(16)) + &quot;\FDIB.DBC;&quot;

lnSQLHand = SQLSTRINGCONNECT(lcDSN)

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

lcSQLCmd = &quot;SELECT * FROM FDIB&quot;

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

ELSE

MakeData ? &quot;Using Native Fox Commands&quot; SELECT * FROM fdib INTO CURSOR ResultSet

ENDIF

? TRANSFORM(RECCOUNT(&quot;ResultSet&quot;)) + &quot; Records Returned.&quot; 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,&quot;SQL Error!&quot;,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 &quot;Populating table...please wait.&quot; 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 *********

