Wiki Home

Vfp Views

Namespace: WIN_COM_API
VFP views, both local and remote, can be thought of as a wrapper to the SQL commands SELECT, UPDATE and DELETE.
Views are an Sql query and wad of parameters to help VFP figure out what Vfp Datatypes the columns are, and what updates to send back.
When you USE a view, VFP just executes the query stored in the SQL property of the view. If any parameters have been defined by the parameters property, VFP will prompt the user for them if there are not already values. The result of the query is stored in a cursor with the same name as the view. None of the other properties have any effect on this operation. This is the extent of the SELECT part of the wapper.

Regardless of the view's Updateable setting, you can modify the data in this cursor. If SendUpdates is false, none of the changes will ever be written or saved, even if you do a TableUpdate(). When you close the view, the changes are lost.

If SendUpdates is marked true, your changes are not written anywhere either, unless a TableUpdate() is performed. At that time, a set of SQL UPDATE commands are constructed based on the properties Tables, Key Field, Updateable, UpdateName, and WhereType. (those are the important ones - someone else can add a description for UpdateType, CompareMemo, etc…).

The SQL UPDATE command syntax looks like this: UPDATE TableName SET Field1=Value1… WHERE WhereClause. A separate update command is constructed for each record that has been modified. For this example, lets assume that only one record was updated.

Looking at it in 3 parts:

First the easy one (assuming you are not doing anything crazy): TableName. The name of the table being updated is stored in the Tables property of the view. Crazy can be one of two things: 1. some table other than one of the ones used in the SQL SELECT query, and 2, it can be more than one table, in witch case more than one update command is constructed (one for each table), and that goes beyond what I want to write about. But for the most part, you only want to update one table.

FieldN = ValueN: for each field in the cursor that is flagged as 'updateable' and was modified (is this right??), the field name specified by the UpdateName and the new value is used to construct the FieldN= ValueN pair. Again, unless you are trying to be clever, it will be the same base table and field that the cursor data came from. The implication is: you can query from one field, and write the update back to another. It is important that you specify the name of the table, even if there is only one table involved. It makes sense if you consider what would happen if you were updating more than one table.

WhereClause: I am not sure how this affects local tables. Docs say it doesn’t, but they don’t say how local tables are handled. This is how you contend with multi user collisions. A WhereClause is constructed that tries to identify the record by a set of fields and the original values of those fields. If the fields were dirtied by modifications to the view, those changes affect the Field=value part, and maybe the WhereClause. If someone else changes the data on the server, the record that is being edited will not be found, and the UPDATE command will not update any records. The server will somehow respond with the number of record effected (0) and that is how a collision is detected.

WhereType = 1 – Key: what ever fields are marked as key (regardless of primary index settings) are paired up with their original value. If there are multiple fields marked as key, then it is a compound key, and the expressions are ANDed. Ex: WHERE cInvno=”123” AND cItem = “1”. As long as the key has not been changed, changes to this record by other users will just be overwritten. (no locking)

WhereType = 2 – key and updateable: a big honken where clause is constructed out of anything marked as key or updateable. WHERE pkPers =123 AND cFName = “Carl” and cLName = “Karsten” and cAdd1 = “8345 Newland Av” AND …. If someone else changes any of these fields, the WhereClause will not be true anymore, and so no records will be updated.

WhereType = 3 – Key and Modified: a smaller WhereClause is constructed out of the PK and any fields that were modified in the view. (It still uses the original values.) UPDATE Pers SET cCity = “Niles” WHERE pkPers =123 AND cCity = “Nils” (note the wrong spelling). In this case, other fields could have been changed, but as long as the cCity field is still incorrect, the update will happen.

WhereType = 4 - Key and TimeStamp: This assumes that the back end has a ‘last modified field’. When the query was executed, a Date Time (either current server time or the last modified value of the record) is included (call it xDateTime). The WhereClause then uses this value: WHERE pkPers =123 AND LastModified = xDateTime. If the record had been modified after xDateTime was set, then this will fale. This has a similar affect as 2, only it is a much smaller where clause, and it includes any field in the record, not just the updateable ones.

Here is the start of some examples. someone shoudld break this up and make a sample for each WhereType.
USE V_MyView && vfp gets PK and LastUpdateTime for each record.  (assume 1 and March24 1:00)
Replace cName with "Carl"
** with the next command,
** vfp does this: UPDATE MyTable SET cName="Carl"
**                   where MyTable_pk = 1
**                   and MyTable.LastUpdateTime = March24 1:00

As far as Remote Views are concerned, it is probably best to think of them as INCOMPLETE WRAPPERS around SQL Pass - Through -- John Petersen
How does that help?
Q: Is it known for a fact that deleting records in a view cause SQL DELETE commands to be issued against the backend?

A: Yes, if the view is updatable. However, ZAPing a view has no affect on the back end. -- ?CFK

A SQL DELETE will also be fired if the view's UpdateType = 2. This causes the backend to DELETE the existing record and INSERT the updated record as a new record instead of UPDATEing the existing record. - Trey Walpole

It is better to UPDATE than to DELETE and then INSERT as in the latter case you may hit DELETE and INSERT triggers. -- Alex Feldstein

I agree. UPDATE also avoids having foreign key constraints disallowing the parent DELETE before the INSERT. -- Trey Walpole
An error handler exposed this code. I think it helps explain how the WhereType effects the update command.

UPDATE ps!psTimeDepts SET cti_id=vpstimedepts.cti_id, cti_deptid=vpstimedepts.cti_deptid, cid=vpstimedepts.cid, nde_reg=vpstimedepts.nde_reg, nde_ot=vpstimedepts.nde_ot, nde_premium=vpstimedepts.nde_premium, nde_prem_ot=vpstimedepts.nde_prem_ot, nde_premvac=vpstimedepts.nde_premvac, nde_vacation=vpstimedepts.nde_vacation, nde_premsick=vpstimedepts.nde_premsick, nde_sick=vpstimedepts.nde_sick, nde_holiday=vpstimedepts.nde_holiday, nde_pt_pholi=vpstimedepts.nde_pt_pholi, nde_miles=vpstimedepts.nde_miles, nde_lisa=vpstimedepts.nde_lisa, nde_reliefhrs=vpstimedepts.nde_reliefhrs, dti_mod=vpstimedepts.dti_mod, dti_add=vpstimedepts.dti_add WHERE cti_id=OLDVAL('cti_id', 'vpstimedepts') AND cti_deptid=OLDVAL('cti_deptid', 'vpstimedepts') AND cid=OLDVAL('cid', 'vpstimedepts') AND nde_reg=OLDVAL('nde_reg', 'vpstimedepts') AND nde_ot=OLDVAL('nde_ot', 'vpstimedepts') AND nde_premium=OLDVAL('nde_premium', 'vpstimedepts') AND nde_prem_ot=OLDVAL('nde_prem_ot', 'vpstimedepts') AND nde_premvac=OLDVAL('nde_premvac', 'vpstimedep
nde_vacation=OLDVAL('nde_vacation', 'vpstimedepts') AND nde_premsick=OLDVAL('nde_premsick', 'vpstimedepts') AND nde_sick=OLDVAL('nde_sick', 'vpstimedepts') AND nde_holiday=OLDVAL('nde_holiday', 'vpstimedepts') AND nde_pt_pholi=OLDVAL('nde_pt_pholi', 'vpstimedepts') AND nde_miles=OLDVAL('nde_miles', 'vpstimedepts') AND nde_lisa=OLDVAL('nde_lisa', 'vpstimedepts') AND nde_reliefhrs=OLDVAL('nde_reliefhrs', 'vpstimedepts') AND dti_mod=OLDVAL('dti_mod', 'vpstimedepts') AND dti_add=OLDVAL('dti_add', 'vpstimedepts')

Contributors Carl Karsten Trey Walpole Alex Feldstein
See also Local Views Remote Views Vfp Views 101
Category Data Category Client / Server
( Topic last updated: 2005.03.11 02:30:09 PM )