creating a view with (buffering=.t.) 2

[markftwain]

Hi experts,

I am displaying a grid based on a view. The view needs to be reading from the table buffer. I am unable to add "with (buffering=.t.)" in the view designer, so have resorted to "CREATE [SQL] VIEW " with the needed "with (buffering=.t." The app works correctly until I quit and attempt to rebuild the project at which time I get "File access denied" on the database container. Using "clear all" does allow me to recompile.

Should not have "quit" closed the dbc? Is there another way of having a view see the table buffer and not always just read from the disk?

Thank you

 

[Olaf Doschke]

You can SET TABLEBUFFERING ON before using the view.
But normally a view would read from the DBF or you can do views on views, which then require the view they read from to be present.

But why a view to read from the table buffer? You typically use views instead of the table. The table opens together with the view, as that is necessary in VFP for SQL anyway, but the table isn't used directly, you do all data manipulations to the view instead.

So I wonder: Why do you need that construct?

Bye, Olaf.

 

[Dan Freeman]

Like Olaf, I don't really see what you think this feature will bring you.

Having said that, if the problem is that you can't compile when you have one of these views in a DBC, then use a temporary workstation-specific DBC at runtime and CREATE SQL VIEW on the fly as needed. That solves your problem, no?

 

[markftwain]

Hi,

I thought using a view might be the simpliest way. This view is intended as read-only and is parameterized in a manner to show two different sets of records from the same table, depending on the options selected on the form. It seems I need to build the grid from a known table or view in the dbc, or else go through the complexity of a "grid cursor" and copy the records I need into the grid cursor.

Suggestions are very welcome!

 

[Dan Freeman]

A view might still be the simplest way, but (WITH buffering=.t.) doesn't have a darned thing to do with that decision.

Using a parameterized view to display varied sets of data in a grid is a good use, and is easy to do. Don't make it hard on yourself.

 

[Olaf Doschke]

Just one corection of myself: The setting is SQLBUFFERING: SET SQLBUFFERING ON/OFF.

Besides you only need sql to read from the buffer, if explicitly need to read via SQL from the not yet saved changes. To read " different sets of records from the same table" you don't need to read from the buffer, you may even not need two different views, simply parameterize a view and use it with two alias names and different parameter values.

There is also the CREATE SQL VIEW command, which enables you to do queries the view designer is not capable to show. I haven't tried, but this will most propbably include with (buffering=.t.).

I really think you have a misunderstanding of what you could read from the buffer. The buffer is not a single thing located on the server, it's per user, per datasession, per alias/workarea, it's the alias, that you set buffered, not the table.

So if you thought you can prefetch all changes users did not yet save via SQL with (buffering=.t.) I need to disappoint you there.

Bye, Olaf.

 

[markftwain]

The create sql view command does allow with (buffering=.t.), although this will be overwritten if the view designer is used to read the created view. But I'm gathering since you have not had to do this before, that I probably have a design error in the first place.

Here is a more complete picture. A form is being edited with its changes being stored into an optimistically buffered table. No final changes are to be written to disk until "save" is pressed. While this form is still on the screen, a second form is brought up that uses the same buffered records and the same grid to view it. I'd like the second form to see the changes made by the first form, and, upon exiting the second form, have the activation code of the first form refresh the grid from the as yet unsaved records.

("set sqlbuffering on" did not seem to work with views?)

Much thanks.

 

[Dan Freeman]

Perhaps a little background is in order.

With (Buffering = .t.) was added to VFP in version 9. Prior to that, we worked just fine without it for a decade.

In the scenario you describe, make sure the first form has a private data session (and it should anyway if you're allowing other forms into the mix), and the second form will automagically share the same data session, unless you've over-engineered that handoff too.

 

[Olaf Doschke]

Mark,

Dan has given the advice I would have given too: Let the secondary form not have a private datasession and you can reuse the same viewcursor in it without requerying data.

Anyway: If your second form would have a private datasession you couldn't query from the table buffer, as I said the buffer is NOT connected to the table, it's connected to the workarea/alias, so you would need to be in the same datasession anyway to query from the buffered alias of the table.

That may also be the reason you don't see SET SQLBUFFERING ON working. It is, but the buffer is not where you expect it. It's not a per Computer, per User, per Table but a per Alais buffer. I already ssaid that, but perhaps you understand now.

You could also say that working on the same dataset with two forms is a design error - or perhaps just a design flaw. It's not that bad, as you surely seperate a form to make it reusable.

You could put that secondary form on a page of a pageframe, you can make use of the memberclasses and put together a pageframe with all needed pages and seperately design them, so in all aspects of OOP you can handle seperate pages the same way as seperate forms, eg reuse it in several forms, like you can start the subform from several forms.

Bye, Olaf.

 

[markftwain]

So don't use requery("view") when switching between forms? How else to update grid as new records are being edited on each form?

 

[Dan Freeman]

So don't use requery("view") when switching between forms? How else to update grid as new records are being edited on each form?

If they're sharing the same data session, and the same cursor, there's no need to requery() because the records should already be there, no?

But what the devil are you doing here having two different forms editing the same set of records? How complicated are you making this?

There's a general rule of thumb that has served me well. If something seems to be inordinately difficult for me to accomplish, perhaps I've mis-engineered what I'm trying to do and should step back and think it over.

 

[Olaf Doschke]

To refesh what controls show, you refresh the controls, not the controlsource.

Thisform.Refresh() should do the refresh or explicitly only refresh the grid via Thisform.grid1.Refresh().

Of course editing in one grid, while the second one (on the seperate form) is scrolled to the same position you don't see the second grid update as if you edit in both grids at the same time. You'd need to call the refresh from each control to the other, but that doesn't make good sense, you're causing a kind of "refresh hell" with an architechture like this.

Bye, Olaf.

 

[markftwain]

Thanks, programs are easier to change then incorrect thinking.

If I could back track some, here is the purpose behind these forms and grids.

The first form is fully detailed with multiple pageframes as needed to replicate its physical (human) counterpart. I am both user and critic. The first form is ultimately what everybody must use to satisfy the complete data requirments and answer questions about previous history. Often once I get into the first form in real-time, I need a visually quick solution to double-check that all necessary current entries have been made--and if not, a quick method of making them without returning to the more complex form which takes time and too many clicks (for my likes) to navigate. Hence the reason for a grid with the parameterized view. The first form, the detailed one, needs to be able to switch between current and historical data whereas the second form only needs to see the current and unsaved data. Usually I start with the first form in real-time and realize somewhere into it that it is unnecessary so switch to the second form. Thus, both forms need to be able to see and write the current data before making a save and the first form needs to be able to switch to historical data as well--without dropping the as yet unsaved records for the current session. I hope you follow me.

Requery("view") was an attempt to display the correct record set in each form. The view being parameterized to the form using it. But since that is out, I'm thinking there is no use to use a view at all.

Would it be better to use the grid based on the table/table buffer itself with perhaps a filter depending on the form being used?

I'm sure interested in how you would approach this, my newbie way is taking the wrong path.

Thank you for pointing me in the right direction.

 

[markftwain]

Oh, forgot to mention. The reason the grid on the first form shows both historical and current entries at the same time is to show what and when an item was posted, current and in the past, before possibly unnecessarily writing a similar record again. Space is at a premium since all must be shown on a netbook display.

Thanks again for your help.

 

[Olaf Doschke]

Well, no, views are not out of the play, just notusing them in different datasessions. The simpler form can only show changes not yet saved when using the exact same workarea, not only the exact smae name, but the exact "instance" of the view containing the changes. Simply set form2.datasession to 1 (Default Data Session), which starts it in the same session as the form it's been started from.

As I understand it, the view needs to contain both historcal and current data and changes for the complex form. Then you simply need to SET FILTER for the second form to only show current data with unsaved changes. There is no other way then, if the changes are to be seen. Otherwise you need to save, which you don't want.

If current and historical data should be in two aliases, you can use the alis for current data in both forms and a different alias for historical data in the main form.

Eg if you have a view parameter lHIstorical for a boolean field flagged .T. for historical data you can use the view once with that parameter set to .F., and once with it set to .T.

In any case both forms just need to use the same datasession, this recommendation remains.

Bye, Olaf.

 

[markftwain]

Great! Thanks much!

 

[markftwain]

Hi,

Just a follow-up note for other newbies like me. The view designer can be used to create views that look at the table buffers. In order to do this, issue "set sqlbuffering on" before requery(), and in the view desisgner, change the default "database!table" to simply "table", leave out the "database!" preface.

Thank you Olaf and Dan