Incremental data fetch from sql server to a vfp client

[MathiasB]

Hi all

Looking to make incremental data fetch (like in block sets of 1000 records per page/view) I have come to a conclusion I need to use SQL Server's Row_Number() and that works fine. But it's in a stored proc running on the server. I simply pass the starting number and the next 1000s are fetched.

First does anyone know if there's a way to define a remote view in vfp that would achieve the same outcome? And my stored procedure returns a set of records. It would be great not to lose the previous results. So the client can return the 2 lots of 1000 fetched. At the moment the cursor is refreshed with the new set. There must be a decent approach to make it additive.

Or any advice please if I am heading the wrong way, thank you

Mathias

 

[Mike Lewis]

I'm not sure if this is relevant, but did you know that there is a setting within VFP that allows you to fetch a pre-determined number of records in each batch. The default is 100.

If you go to Tools / Options / Remote Data, you will see "Records to fetch at a time". After setting the required value, click Set As Default.

To do the same thing programmatically, you can use CUSROSETPROP(), passing the FetchSize parameter.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

I just tried this, and you still get the full result with all rows (more than Fetchsize) in the first go (Change the connection string to yours):

Cd GetEnv("TEMP")
Create Database dbcRemoteViews
Create Connection mssql CONNSTRING "Driver={ODBC Driver 13 for SQL Server};Server={(local)\SQL14};Trusted_Connection=yes;"

Create SQL View rv_sysobjects REMOTE CONNECTION mssql as Select * from sys.objects
DBSetProp("rv_sysobjects","VIEW","FetchSize",10)

Use rv_sysobjects
? Reccount("rv_sysobjects")

I would recommend you read about the OFFSET/FETCH Paging SQL options available since SQL2012:

Local lnH
lnH = SQLStringConnect("Driver={ODBC Driver 13 for SQL Server};Server={(local)\SQL14};Trusted_Connection=yes;")
  
lnResult = SQLExec(lnH,"Select * from sys.objects ORDER BY object_id OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;","crsObjectsPage1")
Select crsObjectsPage1
Browse nowait

lnResult = SQLExec(lnH,"Select * from sys.objects ORDER BY object_id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;","crsObjectsPage2")
Select crsObjectsPage2
Browse nowait

SQLDisconnect(lnH)

But as you can see: No, this won't simply add records to an already partially retrieved result. That's what you would need to do yourself. It's also not so easy, as OFFSET will be about the momentary offset with current data. Between the fetching of single pages data can of course change, be deleted or inserted by other users, so you can get gaps or overlap and would need to manage the situations, eg of having a double id. The simplest way to collect data would of course be to append the single page results into one VFP cursor. But if that cursor is set to be updatabale, appended data will get the fieldstate of being new data, so a TABLEUPDATE() will try to INSERT those rows instead of doing UPDATEs.

I did go the full mileage once with cursoradapter classes fetching pages into secondary cursors and operating on the main cursoradapter cursor via cursor detachment and setfieldfstate. It's not the simplest, which is the major reason people usually do without that data accumulation. You display a page and to go back you actually refetch the previous page again, that also has the advantage of fetching that as it is then. So in short work with the page cursor as having the data of the one page only.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Mike Lewis]

An advantage of doing it within VFP (with FetchSoze) is that the same code will work with any back end. There is nothing SQL Server-specific in it (give or take the connection string, of course).

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Mike,

it would be an advantage, but I don't see it really only fetching 10 rows. And I don't see that I missed another setting to let this really happen. As far as I see the FetchSize settings only limits the size of a single network packet and in a multi-user environment may contribute to a smoother load balancing and serving multiple user sessions.

But the single user can't page data this way, you still get the full result.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Mike Lewis]

Olaf,

I was under the impression that that was what the Mathias wanted. But I'm not sure. Perhaps he will come back with some more information.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Again, Mike.

I don't see FetchSize making a view or SQLEXEC stop at 10 rows, also not if FetchAsNeeded is set .T. Also not, if I set Asynchronous .T.

As far as I google this only makes fetching more a background process, but it still happens. If you want to page data neither FetchSize nor MaxRecords help, you need to have SQL for pagewise navigation through data.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[MathiasB]

Hi Mike & Olaf

Thank you both for responding. It's great to know you've been down that road Olaf - I am definitely not going there it sounds messy already. Actually a 1000 records at a time isn't bad at all in responsiveness I will go down the pages route. 1000 records per page make it work first. Thank you

Kind regards

Mathias