Converting Set Filters to Remote Views

[stanlyn]

Hi,

Using remote views, what does the syntax look like to convert "set filters" to something more for remote views and how are they tied to the form's datasource?

This code is in a combobox where the user selects what is to be shown.

Case This.DisplayValue = 'Available'
	Set Filter To rv_list.registered <> 1
	Go Top

Case This.DisplayValue = 'MeterLocked'
	Set Filter To rv_list.meterlocked = 1
	Go Top

Case This.DisplayValue = 'Publicly List'
	Set Filter To rv_list.publicly_list = 1
	Go Top

Thanks, Stanley

 

[vernpace]

You need to parameterize the remote view. Try to forget about using SET FILTER - it is a notoriously flaky command.

Example of parameterized view:

CREATE SQL VIEW rv_Customers ;
REMOTE CONNECTION Northwind SHARE ;
AS SELECT * FROM Customers ;
WHERE customerid LIKE ?lcCustomerID

lcCustomerID is a memvar

 

[vernpace]

Also, you should consider dumping remote views for a combination of pass-through and CursorAdapters. Both have major advantages over remote views. For example, say you want to automatically get the primary key on an insert (SQL @@IDENTITY is sooo retro... ):

lcCmdIns = "INSERT INTO lbdocument (ikey_folder, ckey_entity, ckey_iv, cfiletype, vheader) " + ;
                           "OUTPUT inserted.ikey_document AS ikey_document " + ;
                           "VALUES (?liKeyFolder, ?tcKeyEntity, ?lcKeyIV, ?lcFileType, ?lcHeader)"

IF SQLEXEC(liHandle, lcCmdIns, "cur_temp") <> 1
   SQLROLLBACK(liHandle)
   SQLDISCONNECT(liHandle)
   RETURN -1
ENDIF

liKeyDocument = cur_temp.ikey_document

CursorAdapters are an extremely powerfull and flexible "hook fest". I don't understand why more people don't use them. There is a bit of a learning curve. Best way is to create a PRG template of a CursorAdapter class and then fill in the properties:

LOCAL p_folderkey, loCAD

p_folderkey = 1000000001
loCAD = CREATEOBJECT("cad_headeralbum")
loCAD.DataSource = SQLSTRINGCONNECT(loCAD.ConnectString)
loCAD.CursorFill(.T., .F.) && Note that the BeforeCursorFill event hook (see below) is called before CursorFill

DEFINE CLASS cad_headeralbum AS CursorAdapter

Alias                  = "cur_headeralbum"
AllowSimultaneousFetch = .T.
BatchUpdateCount       =  1
BufferModeOverride     =  5
CompareMemo            = .F.
ConflictCheckType      =  3
ConnectString          = "Driver={SQL Server Native Client 11.0};Server=(local)\LipBits;Database=LipBitsMusic;Trusted_Connection=Yes"
ConversionFunc         = ""
CursorSchema           = "ikey_album i, ikey_folder i, valbum c(100), vartist c(100), vgenre c(50), iyear i, lhasbeenflag l, balbumartlg m"
DataSourceType         = "ODBC"
FetchAsNeeded          = .F.
FetchMemo              = .T.
FetchSize              = -1
KeyFieldList           = ""
MapBinary              = .T.
MapVarchar             = .T.
MaxRecords             = -1
Name                   = "cad_headeralbum"
NoData                 = .T.
RefreshCmd             = ;
"SELECT lbmusic.ikey_album, lbmusic.ikey_folder, lbalbum.valbum, lbartist.vartist, lbgenre.vgenre, lbyear.iyear, lbalbum.lhasbeenflag, lbalbum.balbumartlg " + ;
  "FROM lbalbum " + ;
  "JOIN lbartist ON lbartist.ikey_artist = lbalbum.ikey_artist " + ;
  "JOIN lbgenre ON lbgenre.ikey_genre = lbalbum.ikey_genre " + ;
  "JOIN lbyear ON lbyear.ikey_year = lbalbum.ikey_year " + ;
  "JOIN lbmusic ON lbmusic.ikey_album = lbalbum.ikey_album " + ;
 "WHERE lbmusic.ikey_folder = ?p_folderkey " + ;
 "GROUP BY lbmusic.ikey_album, lbmusic.ikey_folder, lbalbum.valbum, lbartist.vartist, lbgenre.vgenre, lbyear.iyear, lbalbum.lhasbeenflag, lbalbum.balbumartlg"
SelectCmd              = ;
"SELECT lbmusic.ikey_album, lbmusic.ikey_folder, lbalbum.valbum, lbartist.vartist, lbgenre.vgenre, lbyear.iyear, lbalbum.lhasbeenflag, lbalbum.balbumartlg " + ;
  "FROM lbalbum " + ;
  "JOIN lbartist ON lbartist.ikey_artist = lbalbum.ikey_artist " + ;
  "JOIN lbgenre ON lbgenre.ikey_genre = lbalbum.ikey_genre " + ;
  "JOIN lbyear ON lbyear.ikey_year = lbalbum.ikey_year " + ;
  "JOIN lbmusic ON lbmusic.ikey_album = lbalbum.ikey_album " + ;
 "WHERE lbmusic.ikey_folder = ?p_folderkey " + ;
 "GROUP BY lbmusic.ikey_album, lbmusic.ikey_folder, lbalbum.valbum, lbartist.vartist, lbgenre.vgenre, lbyear.iyear, lbalbum.lhasbeenflag, lbalbum.balbumartlg"
SendUpdates            = .F.
Tables                 = ""
TimestampFieldList     = ""
UpdatableFieldList     = ""
UpdateNameList         = ""
UpdateType             =  1
UseCursorSchema        = .T.
UseMemoSize            =  255 
UseTransactions        = .F.
WhereType              =  3

PROCEDURE AfterCursorAttach
LPARAMETERS tcAlias AS String, tlResult AS Logical
ENDPROC

PROCEDURE AfterCursorClose
LPARAMETERS tcAlias AS String, tlResult AS Logical
ENDPROC

PROCEDURE AfterCursorFill
LPARAMETERS tlUseCursorSchema AS Logical, tlNoDataOnLoad AS Logical, tcSelectCmd AS String, tlResult AS Logical
ENDPROC

PROCEDURE AfterCursorUpdate
LPARAMETERS tiRows AS Integer, tlTableUpdateResult AS Logical, tuErrorArray AS Variant
ENDPROC

PROCEDURE AfterDelete
LPARAMETERS tcFldState AS String, tlForce AS Logical, tcDeleteCmd AS String, tlResult AS Logical
ENDPROC

PROCEDURE AfterInsert
LPARAMETERS tcFldState AS String, tlForce AS Logical, tcInsertCmd AS String, tlResult AS Logical
ENDPROC

PROCEDURE AfterUpdate
LPARAMETERS tcFldState AS String, tlForce AS Logical, tiUpdateType AS Integer, tcUpdateInsertCmd AS String, tcDeleteCmd AS String, tlResult AS Logical
ENDPROC

PROCEDURE BeforeCursorAttach
LPARAMETERS tcAlias AS String
ENDPROC

PROCEDURE BeforeCursorClose
LPARAMETERS tcAlias AS String
ENDPROC

PROCEDURE BeforeCursorFill
LPARAMETERS tlUseCursorSchema AS Logical, tlNoDataOnLoad AS Logical, tcSelectCmd AS String
tcSelectCmd = "SELECT lbmusic.ikey_album, lbmusic.ikey_folder, lbalbum.valbum, lbartist.vartist, lbgenre.vgenre, lbyear.iyear, lbalbum.lhasbeenflag, lbalbum.balbumartlg " + ;
                "FROM lbalbum " + ;
                "JOIN lbartist ON lbartist.ikey_artist = lbalbum.ikey_artist " + ;
                "JOIN lbgenre ON lbgenre.ikey_genre = lbalbum.ikey_genre " + ;
                "JOIN lbyear ON lbyear.ikey_year = lbalbum.ikey_year " + ;
                "JOIN lbmusic ON lbmusic.ikey_album = lbalbum.ikey_album " + ;
               "WHERE lbmusic.ikey_folder = ?p_folderkey " + ;
            "GROUP BY lbmusic.ikey_album, lbmusic.ikey_folder, lbalbum.valbum, lbartist.vartist, lbgenre.vgenre, lbyear.iyear, lbalbum.lhasbeenflag, lbalbum.balbumartlg"
ENDPROC

PROCEDURE BeforeCursorRefresh
LPARAMETERS tcSelectCmd AS String
ENDPROC

PROCEDURE BeforeCursorUpdate
LPARAMETERS tiRows AS Integer, tlForce AS Logical
ENDPROC

PROCEDURE BeforeDelete
LPARAMETERS tcFldState AS String, tlForce AS Logical, tcDeleteCmd AS String
ENDPROC

PROCEDURE BeforeInsert
LPARAMETERS tcFldState AS String, tlForce AS Logical, tcInsertCmd AS String
ENDPROC

PROCEDURE BeforeUpdate
LPARAMETERS tcFldState AS String, tlForce AS Logical, tiUpdateType AS Integer, tcUpdateInsertCmd AS String, tcDeleteCmd AS String
ENDPROC

PROCEDURE Destroy
ENDPROC

PROCEDURE Init
ENDPROC

PROCEDURE Error(tnError AS Integer, tcMethod AS String, tnLine AS Integer)
ENDPROC

PROCEDURE Release
   SQLDISCONNECT(This.DataSource)
   RELEASE This
ENDPROC

ENDDEFINE

Here is the result of the album cursor:

 

[stanlyn]

Hi Vernpace,

Looks good...

I don't understand why more people don't use them.

Thats easy... Too many options that makes it too complex, at least for me, given all the nightmares I've had with remote views. I've had the best experience with SPT, but is extremely counterproductive as everything has to be built up, can't drag and drop for forms and grids, and more.

I do like your version into a prg and that looks similar to filling out a property sheet. I would like to implement them, but don't have weeks to learn and implement, before being pulled off to deal with another fire... repeat, so my time is too fragmented. I will be looking at it closer and will start my 4th go around/attempt with them. If you look at my previous posts here on Tek-Tips over the years, you will see my attempt to get them working.

And there is all this talk about mixing technologies (views, spt and ca) that complicates things.

So how does one wireup a simple form using your prg based class? And how is the next and previous navigation skipping work? What goes in the next button? I have all of Doug's auticals and have attempted to use them in the past. Where would start?

Thanks,
Stanley

 

[stanlyn]

Hi Vernpace,

CREATE SQL VIEW rv_Customers ;
REMOTE CONNECTION Northwind SHARE ;
AS SELECT * FROM Customers ;
WHERE customerid LIKE ?lcCustomerID

lcCustomerID is a memvar

Where does this all go, and how are nav buttons wired? In a button, form's init or load

If the where criteria changes, do you scrap the whole view and build a new one and somehow make all the controlsources lineup.

Thanks,
Stanley

 

[Chris Miller]

Where does this all go, and how are nav buttons wired?

The view definition goes before anything else, once, it's a view design step and the result is having a view with parameters.

To use the view it's parameters must be given, so a view like that can only go into a forms DE if you set it to not query anything and wait for that until the form establishes the view parameters. That is possible, as you can set a DE object to nodataonload=.T. (notice that property is fixed for tables, this only is available for views).

And then you will not know parameters before a) they are passed in to the form init or b) a user interactively enters or picks them.

In the example of a view that has a customerid as parameter, well, the user either picks the customer in a form running previous to this one and that call this form and passes in a customerid, so the init can then set lcCustomerID and the requery() the view. Or the user picks the customer in the form itself, but then the view will only be available to use when that's picked.

There are several situations where you could know a parameter in advance, choose to always use the maximum ID, or whatever, but I don't understand how you think the CREATE SQL VIEW has to go anywhere in the form. You of course define the view outside of the form, a view is a static part of the database, what has to change is only the parameters.

All that said, I don't even know if view parameters would help you with the tableupdate problem at all. They are a solution to not needing individually built queries. No more, no less. And, well, they unbind you from using SET FILTER. That's a performance improvement, and surely it is a necessary improvement if instead you'd always need to query all data and then SET FILTER to it. It's just saving a waste of bandwidth usage for data you filter out after you already queried it.

Think about SET FILTER, it works fine with the USE of a table, as you don't load data when you USE a table, you only load data when a grid or browse fills and that only loads as much as it needs to fill in the visible area. And then you use the filter while querying. But a view doesn't work like that. When you query a view you query all of its data, no matter if a browse or grid only needs 5 rows of it. So you load a lot of data you then actually don't want. That's the reason to parameterize views. And it's not a suggestion you could do, it's a really strong reason to let things work at all. Otherwise this will only be fine as long as your amount of data is low.

Chriss

 

[vernpace]

Stanlyn,

As Mike indicated, the cursor cur_headeralbum is created from the cad_headeralbum CursorAdapter object. And yes, TEXT...ENDTEXT is the better way to go :>. Once you have the cursor, you can bind it anyway you want.

Using CADs can force a rethink about how to construct data environments. I agree with Mike that DBCs are problematic. In the old days, they were cool for visual representation... but not practical IMHO. With CADs, you have an opportunity and the flexibility to construct your own data environments. You might ask how? I'll leave it to your imagination. The most fun and challenging thing about software developement is design. It will keep you up at night, but it's very rewarding.

 

[Chris Miller]

every user the uses that view gets a little hit. They can add up until you have several users waiting on the view.

The common solution to that is having a local view DBC, so nobody has to wait for others to get t a view (see threads on ERROR 1709, also see

https://jeffpar.github.io/kbarchive/kb/170/Q170917/)

But don't worry, we're on the same page that CAs are the better option. You can do everything views can do and more.



Chriss

 

[Chris Miller]

I cannot imagine two users attempting to execute a query should require a lock on a dbc record.

But that's how they designed it. It's the access to a database object, the record of the view in the DBC, that can cause the lock contention on the DBC. Even though there's nothing to be locked when reading a query to execute it. AS the KB article says it's just a matter of a RETRY to get to the view, but if that's a local DBC there only is one user and so locks work right away.

One way I used to deploy view DBCs is embedding them in the EXE and let them be copied out at start.

I think the DBC object lock is not only limited to views, it happens at any use of a dbc object. I also don't get why they designed it that way, as the main access is read only, there's no need for a lock and the lock contention that could occur. Another reason, I guess, why people also still prefer free DBFs.

It's a sidetrack anyway. Stanlyn's problem is not accessing/opening/using the view but view design with parameterization.

Chriss

 

[vernpace]

Ah yes, middle-tier objects - otherwise known as business objects: COM+ SOAP webservice dlls running on MS Server 2000. Actually, some of these were designed like CAs with before and after hooks. Too bad MS cut VFP from that technology...

 

[stanlyn]

Hi,

I don't understand how you think the CREATE SQL VIEW has to go anywhere in the form.

Because the data has to be bound to the form's controls.

they unbind you from using SET FILTER.

So, instead the code

Case This.DisplayValue = 'Available'
  Set Filter To rv_list.registered <> 1
  Go Top

* would become 

Case This.DisplayValue = 'Available'
  select * from rv_list where registered <> 1
  =requery('rn_list')
  Go Top

When you query a view you query all of its data

Please a yes, no, or other answer, then elaborate if needed.

Does this mean the data in the remote view, or the underlying SQL data that makes up the view, as they can be different, as another user could have made and saved their changes to SQL and your view may not be reflecting them? So, when you query the view, does the query only look at the data in the view and not the sql table?

Any I understanding this correctly, yet?

SET FILTER is not flaky

I use it very extensively and never had an issue with it, and its fast enough for most things. On large data sets, I will use an index, if speed becomes an issue.

Stanley

 

[stanlyn]

Hi,

CREATE SQL VIEW rv_Customers ;
REMOTE CONNECTION Northwind SHARE ;
AS SELECT * FROM Customers ;
WHERE customerid LIKE ?lcCustomerID

Is this correct...

1. from outside a form and within the command window or view designer I run the code shown code to create a view named 'rv_Customers'

2. create a form and add the view into the form's DE

3. add controls to the form from the view as the proper binding occurs

When running the form

4. in the form's load or init, set lcCustomerID = '' which should load nothing

5. once the form loads the user will make a selection on what fields they wish to search. Then using the combobox's selected displayvalue we can popup an input box to take user input to create the query.

Combobox lostfocus code...

Case This.DisplayValue = 'Customer ID'
  lcCustomerID = upper(allt(inputbox('Enter CompanyID', 'Select by Company ID')))
    *old set filter way * Set Filter To upper(allt(rv_list.company_id)) = lcCustomerID
  
  select * from rv_Customers WHERE customerid LIKE ?lcCustomerID
  =requery('rv_Customers')
  Go Top

Case This.DisplayValue = 'Customer Name'
  lcCustomerName = upper(allt(inputbox('Enter Company Name', 'Select by Company Name')))
    *old set filter way * Set Filter To upper(allt(rv_list.company_name)) = lcCustomerName
  
  select * from rv_Customers WHERE customer_name LIKE ?lcCustomerName
  =requery('rv_Customers')
  Go Top

Case This.DisplayValue = 'Full Text'
  lcFullText = upper(allt(inputbox('Enter Text', 'Full Text Serach')))
    *old set filter way * Set Filter To lcFullText $ rv_list.company_name ;
                           .or. lcFullText $ rv_list.comments ;
                           .or. lcFullText $ rv_list.address
   
  select * from rv_Customers WHERE contains(company_name, ?lcFullText)
    .or. contains(comments, ?lcFullText)
    .or. contains(address, ?lcFullText)
  =requery('rv_Customers')
  Go Top

.......

I know I'm missing something as the created view doesn't know anything about the other parameters, (lcFullText and lcCompanyName) I used above.

Now for the nav button code, will it be as simple as skip, skip -1, go top, and go bottom followed by a requery?

Note that many times a user doesn't know exactly how its in the system, so most times the user will need to see rows before an exact match and rows after the match, then land them on their match. They can go prev and see prior records. I also factor in the sort order as selected from a similar combobox.

Thanks,
Stanley

 

[vernpace]

So I quess that parameterized remote views are out of the question for you. To bad - they have been used by VFP devs for years. Instead of going around in circles with this, why don't you put your head down, practice a little trial and error until you get it. I have found that to be the best way to learn. You previously implied that you have no time to learn new things. How unfortunate - what a pity.

 

[stanlyn]

Nike,

Use TEXT...ENDTEXT

Been using this for years with SPT and was thinking the PreText option does most of the things that your utility does, except your utility extends it greatly.

cur_headeralbum = cursor
cad_headeralbum = cursor adapter ?? (the D part)

Stanely

 

[stanlyn]

Hi,

More info has surfaced related to the cmdApply.click event and could be related.

Start VFP, load single view form and navigate to a record via skip
Change a single string/char value and click cmdApply

In the code below...
The tableupdate() succeeds with .T.
The requery() fails, see image below.
The underlying SQL table WAS updated correctly.

With Thisform As Form
	Select 'rv_Domain'

	lnKey = rv_Domain.pk
	=INKEY(.2)
	
	IF Tableupdate(0, .F., 'rv_Domain') = .F.		&&, laError)
		Messagebox('TableUpdate Error')
	ENDIF

	Requery('rv_Domain')
	Locate For rv_Domain.pk = lnKey
	.Refresh
Endwith

So I quess that parameterized remote views are out of the question for you.

What makes you say that? Is it the requirements? Remote Views not the right tech? I'm in left field? I've been giving customers what they want using old VFP ways (dbf and dbc) and this new tech will need a lot of ?? to achieve the same? Seriously, I need to know. I've told you a little about what our customers expect, so given that, how should I proceed?

head down, practice a little trial and error until you get it,

Thats exactly how I've learned what I've learned so far. FoxTalk, Hentzenwerke's fox books, Doug Hennig's stuff, Tek-Tips, Foxite, and many others and old stuff from the Compuserve forums. No formal training, other than SouthwestFox and Will Hentzenwerke's Great Lakes conferences.

Also, I ask questions here only after much googling, searching and tons of trial and error. When all this fails, I ask here...

You previously implied that you have no time to learn new things. How unfortunate - what a pity.

Please don't pity me, as I'm pursuing new business opportunities that replaces the excellent income we have enjoyed over the past 33 years from vertical VFP applications I wrote and still support.

I'm using these in-house sql apps as a learning tool. Many have said I should forget about VFP all-together going forward and may very well be true for remote access apps and get going with the c# and web stack.

For the short term, I'd be happy to build desktop apps connecting to sql. I have built a few actually using spt and vfp. It just takes too long and too much code, even for simple stuff. I was hoping for something closer to native vfp and that is where remote views came in. Then, what should be simple issues is taking days to fix and looks like I'm going to live with the inkey(.2) fix for now.

I have some time, however I do not work as a full time programmer xx hours a week like many others here that relies on a paycheck from a boss do. Between this stuff, the new business opportunities, and servicing our vendors and customers, I'm spending on average 16 hours a day, 6 days a week and sometimes 7 days a week. But, still, don't pity me as I know what will come from these efforts. I'm sure there are some here with similar roles that I have. Currently, I am near the end of what I can give this project for now. I do have it in a working state and have progressed with a lot of new features added and successful tested these past 3 weeks.

I would be interested in discussing hiring someone to help with building the new needed tooling. Drop me a message if interested.

I have an Exchange Server 2016 mail server down that I have to get fixed this weekend and I do not enjoy the powershell management required to fix an expired certificate that was removed using the IIS applet, that Exchange needs. And a bear to fix...

Also, anyone have any experience building a tool for adding 10000+ catalog items with images into a WordPress WooCommerce Marketplace store database? I have most of the heavy lifting done (such as image acquisition and exporting the file for Woo import) using native VFP. If so, we need to talk...

And please, lets continue the conversation...

Thanks, Stanley

 

[Chris Miller]

That's a strange case you have there. If a successful tableupdate (returning .T.) means still buffered changes, then the logic would say, since your tableupdate() only updates 1 row, buffered changes are in other rows. But then your inkey() kicks in and the buffer will be emptied? I would perhaps get it as something needing time to spread when you'd need to wait after the tableupdate, but before?

Hm.

How is the view defined? I don't need to see the details, but is it a view on a view? That might cause complications with buffering. Clearly just a straw of having a complicated situation. I don't think it is.

So more straight forward: How is the view buffered? It surely is set to something, otherwise no tableupdate.
If you're so sure you only ever need single row updates, then it should be row buffering. And if its row buffering, you could also use the full tableupdate, it'll know the row, you don't need to tell it to only update the current row. In short: I never use single row tableupdates.

Change a single string/char value and click cmdApply

What's in the control of the field you are changing in its valid and lostfocus events? That's the only code directly running before cmdApply.when and .gotfcus and .click. Notice the event chain of things potentially having code in it before the click code runs. There are a lot of things happening in the current control and then next control, if you change focus. Always, not just in corner cases. You'll usually think correct in only caring for the click code, as you don't have anything in all these events. But it would be quite likely you have something in valid, even if it returns .t. and isn't stopping the cmdApply button to get focus.

Chriss

 

[Chris Miller]

Another thought is, the backend is SQL Server, running local, was it? The connection to it can be asynchronous. But I don't see how a tableupdate() can return a preliminary .T., it only returns .T. after the buffered changes are successfully saved and the buffere is cleared. In a single row update for that row only and not necessarily the whole buffer.

From that perspective of "no matter what happens before" the only solution is there are other row changes not yet saved. Which requires table buffering and only row update. But then that can't be mended with an inkey() waiting time. The buffers are VFP internal and don't depend on SQL Server reactions, it's VFP which needs to know the changes are saved and then it purges the buffer, not SQL Server. The buffers are under full VFP control.

Chriss

 

[Chris Miller]

Well, and a straight forward thing to do when you get error 1545 about uncommitted buffered changes: Look what is in the buffer:

Select rv_Domain
nnModifiedRecno=0
Do While .T.
   nModifiedRecno = GetNextModified(nModifiedRecno)
   If nModifiedRecno<>0
      Go nModifiedRecno
      ? nModifiedRecno, GetFldState(-1)
   Else 
      Exit
   Endif
EndDo

Maybe you'll detect where you err in thinking nothing else but the one value you actively changed is buffered.

Chriss

 

[stanlyn]

but is it a view on a view?

I don't know what that is of how it would be constructed.

How is the view buffered?

Currently it is 5, optimistic table and only resides in the form's load method.

need single row updates, then it should be row buffering.

I need both. So, is it legal to change cursorsetprop('buffering', values to whatever for a one-up situation then return it back to what was set in the form' load event?

Follow this and is accurate?

select 'view'
cursorsetprop('buffering', 5, 'view')
go 50
replace status with 'Taken' next 10

cursorsetprop('buffering', 3, 'view')
go 100
replace status with 'Hold'
tableupdate(0, 'view')
* at this point, only row 100's changes are saved to the backend?

cursorsetprop('buffering', 5, 'view')
tabelrevert(.T., 'view')
* at this point, all other previous changes prior to row 100 are NOT changed and the buffer is cleared.

Just making sure I'm understanding this correctly.

What's in the control of the field you are changing in its valid and lostfocus events? That's the only code directly running before cmdApply.when and .gotfcus and .click. Notice the event chain of things potentially having code in it before the click code runs.

I got excited to see that you have solved this, but commenting the lines out did not help... What I found was the first control with tab index = 1 which is the control the form stops at, once loaded. There is a 'lostfocus' on it as I was changing some values in the control that is bound to the view, and thought this was surely the issue, so I commented them out, saved, ran and issue still persists. Also, no valids anywhere. There are two other controls with lostfocus events, but none are in the open and save flow as they never receive focus.

Another thought is, the backend is SQL Server, running local, was it

MSSQL on same network, different machine. What do you consider local? same machine or network? I ask because this has been asked before and I need to be on same page.

From that perspective of "no matter what happens before" the only solution is there are other row changes not yet saved.

You raise some questions here where I may have some confusion...
Q1. If you close VFP, is there any way that "uncommitted changes" could presist into the next vfp session? I would think that all buffers would be flushed and a new session would be free from previous session's buffering statuses. Actually my experience is vfp won't close if there are uncommitted changes. I have to revert or update. This question is about where and what sessions can have unsaved changes that affects my session, and what I need to know to consider my session as clean.

Q2. Does tableupdate buffereing from a different user/session have any bearing on my session?

Q3. Chriss, you have kinda of indicated that tabe buffering can have two concurrent statuses, one that a tab;eupdate(0, will deal with and another tableupdate(1, for all unsaved records. I have never heard of that and need clarification. Whereas in an environment where cursorsetprop('buffering' is 5 (optimistic table buffering), I could change values in many view rows, then goto a specific record and change another value there and issue a tableupdate(0, and only that specific record would be updated. This would still leave all the other changed records unsaved waiting for a tableupdate or revert. Is this correct?

Q4. To my knowledge, tableupdate only works on the buffered copy of the view. Or does it only work on the view where the changed data is in the view?

Q5. Is there anything to quoting the view as is select statements? Like Select rv_Domain
vs Select 'rv_Domain' I read somewhere it should not be quoted. I do generally quote them.

Thanks, Stanley

 

[Chris Miller]

Q1: No, there's no session persistence. At quit connection properties can also determine whether changes are reverted or committed.

Q2: No, the buffer is related to a workarea, not to a dbf or to a remote backend. So it's always local

Q3: single row update only cares for single row, yes. A view requery requires no buffered changes. None at all. If you're only interested in saving the one row, then tablerevert() all other changes, then you can also requery.

Q4: There is no such thing as a buffered copy of the view. The view itself is just it's query, the result of using a view is the workarea the results from doing that query, and the buffer is on that workarea. It starts empty, no matter if you pick row or table buffering. Buffered is what is changed or added, or deleted. Changing between row and table bufferig is no good idea, I think. What happens to uncommitted changes of a table buffer when you switch to row buffer is, I assume they are automaatically reverted or committed. I don't know, or an error is thirwn quite like that of view requery when there is something buffered.

There is no need to use anything else but tablebuffering, as you can pick to only update single rows in that mode, too. The benefit of switching doesn't occur to me.

Q5: VFP doesn't care, it understands SELECT 'rv_domain' just like SELECT rv_domain. There's really no point in quoting the name, as a workarea name isn't allowed to have spaces.

But the most important point would be what do you see in the buffer after your tableupdate()?

Is it perhaps really just a single misconception that requery of a view only refreshes he current row? You can only requery a view when the buffer of the workrea it uses is completely emptied, either by commit or revert. There's the whole point of why you don't have a view on the whole table and then set filter afterwards. You requery the view, the whole view. Nothing of it is buffered to begin with, but all of it is loaded, I think you're misinterpreting the meaning of the buffer.

Chriss