×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Incremental data fetch from sql server to a vfp client

Incremental data fetch from sql server to a vfp client

Incremental data fetch from sql server to a vfp client

(OP)
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

RE: Incremental data fetch from sql server to a vfp client

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

RE: Incremental data fetch from sql server to a vfp client

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):

CODE

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:

CODE

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

RE: Incremental data fetch from sql server to a vfp client

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

RE: Incremental data fetch from sql server to a vfp client

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

RE: Incremental data fetch from sql server to a vfp client

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

RE: Incremental data fetch from sql server to a vfp client

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

RE: Incremental data fetch from sql server to a vfp client

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close