For reporting purposes, I've created separate objects which query the data, manipulate the cursor, and return the results to the calling program to be printed on a reports. I'm including the option to return the data as a VFP cursor, ADO recordset, or XML. Since these cursors can be large, I found the conventional methods of converting to an ADO recordset to be rather slow. For example, a cursor with approximately 2200 records and 65 fields takes almost 12 seconds to convert with VFPCOM.CursorToRS(), and almost 8 seconds to convert with DBF2RS.prg. Considering that querying the data with VFP may take only a second or two, I found the overhead unacceptable.
So, I've tried a different approach. It involves using ADO to query the data from the underlying table or temporary file of the cursor. I go through the OLE DB for ODBC and the Visual FoxPro Free Tables ODBC drivers to get the data. For large cursors, there can be a significant performance improvement. The above mentioned cursor takes less than a second to convert. For small cursors, the results are mixed and it can actually be slower than CursorToRS() or DBF2RS. The code is below. There's some more work to do, such as making sure the temporary file exists and that the cursor is not just a filtered version of a table, but you get the general idea.
I haven't seen this approach mentioned before, probably because noone wants to convert large cursors. Also, there may be some flaws that I am missing. Any comments are appreciated.
LParameters lcAlias
Local lcFileName, lcDirectory, lcSQL, lcConnection, loRS
If Empty(lcAlias)
lcFileName = DBF()
Else
lcFileName = DBF(lcAlias)
Endif
lcDirectory = JustPath(lcFileName)
lcFileName = JustFName(lcFileName)
lcSQL = "Select * from "+lcFileName
lcConnection = 'Provider=MSDASQL.1;Persist Security Info=False;Extended Properties=' + ;
'"DSN=Visual FoxPro Tables;UID=;SourceDB=' + ;
lcDirectory + ';SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;' + ;
'Collate=Machine;Null=Yes;Deleted=Yes;"'
loRS = CreateObject('ADODB.RECORDSET')
loRS.Open(lcSQL,lcConnection)
Return loRS