Using transactions with remote views

[Mike Lewis]

I've inherited an application that talks to a SQL Server back-end via updatable remote views. I want to introduce transaction processing into this scenario.

I understand that if multiple remote views are to take part in the same transaction, they must share a connection. So I've set the appropriate view property to achieve that.

To avoid any conflicts when multiple views are trying to use the same connection, I've set the views' "Number of Records to Fetch at a Time" setting to -1, which I believe should have the effect of making the views synchronous.

This seems to work OK, but, of course, it means that, when opening a form that includes a number of views, the user must wait until all the data for all the views has completely downloaded before getting control of the UI. That's a consequence of making the views synchronous. (Some of the views are unfortunately very large.)

I'd appreciate any comments on whether I am going about this the right way, or if there is a better approach.

Please note: I am not looking for a discussion on the rights and wrongs of using updatable remote views. This is not something I would have chosen to do myself, but I have no choice in the matter.

Thanks in advance for any help.

Mike




__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Olaf Doschke]

The ShareConnection Property of views seems the more important one.

The note on FetchSize says: Progressive fetching holds the connection until all rows are retrieved. Use caution coding with FetchSize if ShareConnection is True (.T.).

But what does that "use caution" mean? Should Fetchsize be -1 or rather the default 100?

I see: Help on FetchAsNeeded says Fetchsize = -1 means progressive fetching is disabled. So FetchSize>0 will hold the connection long, as data may only be fetched when needed or during idle time.

But does that mean you can't SQLCommit() or SQLRollback() then until the execution finishes?

AllowSimultaneousFetch is another DB property, and of course Asynchronous.

I think you don't need to disable progressive fetching for the views to use the same connection.

Bye, Olaf.

 

[Mike Lewis]

Hi Olaf,

Many thanks for your reply.

I'll experiment with FetchAsNeeded and also the Asynchronous setting (for the connection), and see if they make any difference.

Like you, I was puzzled by the "use caution" note. Not sure exactly how I should use caution in this case.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Mike Lewis]

Olaf,

I tried your suggestions, but unfortunately they didn't help.

FetchAsNeeded and SimultaneousFetch appear to have exactly the same effect as FetchSize (unless I'm missing something). Setting the connection to Synchronous caused the retrieval of data to be even slower than otherwise. I'm not sure why, but it slowed the whole application considerably.

I won't persue this for now. Basically, I've got it working, and the users will just have to put up with the fact that forms are going to take a long time to open. I hope I'll be allowed to make this into a proper client-server system some time in the future.

Thanks again for your help.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk