I need to fetch a very large amount of data from SQL Server, and want to be able to display a progress bar while the data is fetched.
I'm using ASYNCHRONOUS mode with SQL PASS-THROUGH.
What can I use to detect the number of records fetched at any one point? RECCOUNT() doesn't work as it attempts to finish collecting all records before it displays.
The information must be available in VFP somewhere, not least because if SET STATUS BAR is ON and SET TALK is ON then the record counter counts up at the bottom of the screen.
I use SQLSTRINGCONNECT to connect to the database, because I need to build the connection string dynamically - the system could connect to any number of databases.
Any ideas?
Mnay thanks
Ben
Code so far:
cConnectString="Driver=SQL Server;Server=X;Database=X;UID=X;PWD=X"
hConn=SQLStringConnect(cConnectString)
SQLExec(hConn, "select count(*) AS rcount from verylargefile","rcount")
nTotalRecordsToFetch=rcount.rcount
lSQLResult=SQLSETPROP(hConn,"Asynchronous",.T.)
llCancel = .F.
lnResult = 0
ON ESCAPE llCancel = .T.
DO WHILE (!llCancel AND lnResult = 0)
lnResult = SQLExec(hConn, "select * from verylargefile","getreg")
DOEVENTS
IF USED("getreg")
nCurrentRecordsFetched=RECCOUNT("getreg")
ShowProgressBar(nCurrentRecordsFetched, nTotalRecordsToFetch)
ENDIF
ENDDO
IF (llCancel AND lnResult = 0)
SQLCancel(hConn)
ENDIF
SQLDisconnect(hConn)