Hi All,

Is there a limit on the size a cursor can be when retrieving records using the VFP OLEDB provider?

Long story short, a client is converting all their VFP data to SQL Server and using VB components via the VFP 8 SP 1 OLEDB Provider to do it (heavy politics, don't ask). A problem arose where, for a table with 79 fields and only 111748 records, the provider is returning the following error after trying to move beyond record 12337:

OLE IDispatch exception code 0 from Microsoft Cursor Engine: Data provider or other service returned an E_FAIL status


I was able to duplicate their problem using the VFP provider with VFP code...

First attempts were using a client side recordset, both connected and disconnected, and converting the recordset to a cursor via the CursorAdapter. Even with a connected client recordset, the cursor created only has 12337 records. When trying to loop through the recordset directly, MoveNext throws the above-mentioned error when trying to pass record 12337, even though EOF is false (which is what they saw on the VB side). Is there something that needs to be done to get the additional rows into the client-side recordset?

Next I tried using a server side recordset. In this case a loop through the recordset directly hits all 111748 records, however, when converting to a cursor with the CursorAdapter, the resulting cursor only has 12438 records. So server side fixed their problem of getting to all the records with VB, but now I'm curious as to the size limit the VFP side when it comes to converting recordsets to cursors...



Thanks,

- Brian


Some Code:

lcSide = "SERVER"
*lcSide = "CLIENT"

*-- Create Recordset object with some default settings
loRS = CREATEOBJECT("ADODB.Recordset")
loRS.CursorType       = 3  && adOpenStatic
loRS.CursorLocation   = 3  && adUseClient
loRS.LockType = 4  && adLockBatchOptimistic
IF lcSide = "SERVER"
	loRS.CursorLocation = 2  && adUseServer
ENDIF


*-- Create Connection object with some default settings
loConn = CREATEOBJECT("ADODB.Connection")
loConn.CursorLocation = 3  && adUseClient
loConn.CommandTimeout = 0
loConn.ConnectionTimeout = 0
IF lcSide = "SERVER"
	loConn.CursorLocation = 2  && adUseServer 
ENDIF

*-- Set location of VFP tables as the DataSource
lcVFPDataSourceFullPath  = "C:\Work\Dev\Test\dbfs\"

*-- SQL Command to execute
lcSQLCommand = "Select * from master"

lcOLEConnString = "Provider=vfpoledb;" ; 
			+ "Data Source=" + lcVFPDataSourceFullPath + ";" ; 
			+ "Mode=ReadWrite|Share Deny None;" ; 
			+ "Collating Sequence=MACHINE;" ; 
			+ "Password=''"

*-- Set the ConnectionString and Open connection
loConn.ConnectionString = lcOLEConnString
loConn.Open()

*-- Set up the Recordset and Open
loRS.Source = lcSQLCommand
loRS.ActiveConnection = loConn
loRS.Open()


*-- Close any previous instance of our result cursor
USE IN SELECT("csrResult")

*-- Convert the Recordset to a cursor
lCA = CREATEOBJECT("CursorAdapter")
lCA.DataSourceType = "ADO"
lCA.Alias = "csrResult"
lCA.CursorFill(,,,loRS)

BROWSE
*-- Scrolling to the bottom of the cursor after fetching is complete
*-- will show only 12337 records

*-- Interesting Note:  With server-side, scrolling to the bottom shows 12438 rows
*-- and the last row has a slew of non-printing characters in the text fields, 
*-- blanks in numeric and dates...

*-- Loop through Recordset
loRS.MoveFirst
i = 1
DO WHILE NOT loRS.EOF
	WAIT WINDOW "Recordset row: " + TRANSFORM(i) NOWAIT NOCLEAR 
	i = i + 1
	loRS.MoveNext  && Client-side will fail after record 12337, Server-side OK
ENDDO

? "Recordset Rows: " + TRANSFORM(i-1)