Using VFP and SQL Server to build client/server application (remote View) (3)

Updatable view (updateble views)

Above we explained "how to read the data from the server through the remote view", then we will explain how to operate the remote view cursor, of course, we will not discuss some of the Visual FoxPro data set of ordinary operations here, we only explain the remote view of the data update.

When a remote view is opened, the user can use the Visual FoxPro commands and functions as cursors for various operations, such as querying, adding new data, deleting records, modifying records, and so on, and the next three will change the data. Remote views have the ability to automatically analyze various changes and send the results of changes to the back-end database to update the data source.

Incidentally, the ability of visual FoxPro to update this data is very powerful-if a view is made up of multiple data source tables, visual FoxPro can automatically analyze the data source information that corresponds to the client data changes and "categorize" the updates to the remote data data. Now some very famous data engine does not have this feature, please see this site in the anthology of "Visual FoxPro Ramble" a text.

Key value columns, updatable fields, SQL update switches

We can use the View Designer to make an updatable remote view (which, of course, can be used to design the remote views mentioned above), as shown in Figure 7. In order to set a remote view as an updatable view, you must do three more things in the View Designer (than a remote view that is not updatable):

1. Sets the key value bar. The system is able to know that you have changed the record in the view and made the corresponding changes in the data source, relying on the key value to judge. You can understand that key values are tags that uniquely identify data records in a table. The key value cannot be duplicated. Imagine that in an employee table, you set the gender to a key value, modify the name of an employee, and then send an update, which results in what (in fact, Visual FoxPro reports incorrect key values), we assume that visual FoxPro do not complain): With the modified name of employees with the same gender of the employee's name has been modified, it is ridiculous!
   General Visual FoxPro automatically reads the key value information from SQL Server and automatically sets the key value bar.
   If you don't have a field in place that uniquely identifies a record, you can use a union field.

2. Set updatable fields. Only those fields that are set to be updatable are reflected in the data source table. Not all fields are updatable, such as a field with the Identity property in SQL Server (the system maintains its numeric value, often as a key value).

3. Open the Send SQL Update option. Many people in the View Designer "painstakingly" set a lot of information, but the client data changes are not sent to the backend, because they forget the most basic options. Remember: For the view to be updatable, this must be set.

Figure 7. View Designer

We can set the above three options through the Dbsetprop () function:

Dbsetprop ("Vcustomers.customerid", "Field", "Keyfield"). T.)
* Set key Value column
Dbsetprop ("Vcustomers.customerid", "Field", "updatable"). T.)
* Set updatable fields, there are many fields to write, here omitted
Dbsetprop ("Vcustomers", "View", "sendupdates"). T.)
* Turn on "Send SQL Update"

In Figure 7 we still have two properties that are not set: "Use Update", "SQL WHERE clause includes".

Update mode

The purpose of using updates is to tell the Visual FoxPro how to decompose the update operation, which is an "update mode" choice. For example, we modified the field values for a row in the view. If this property is set to SQL Update, Visual FoxPro sends a UPDATE-SQL statement back when the update is sent, and if this property is set to SQL DELETE and then update, visual FoxPro will send a delete-sql statement back--delete the original record, then send a insert-sql statement--Add the record with the new value to the data source. It's easy to see that the previous setting is more efficient, but why do you have the latter option? There are old databases that do not support update-sql ... There should be no such problem with the mainstream database system, so we typically choose SQL UPDATE.

SQL WHERE clause includes--Update conflict detection method

"Update conflict" means that when a user modifies a particular piece of data, the contents of the same data record are modified by another user. The following figure is a diagram of the update conflict, red for the data source, black for process one, and blue for process two.

Figure 8. UPDATE conflict hint

This setting has four options: key fields, keywords and updatable fields, keywords and updated fields, keywords and timestamps.

1. Key field: If you select this option, the system will only check that the contents of the key Value column of the source table are modified.

2. Keywords and updatable fields: If you select this option, the system will check whether the key value column of the data source table and the contents of the Updatable field are modified.

3. Keywords and updated fields: If you select this option, the system will check whether the key value columns of the data source table and the Modified field contents have been modified. Here it is noted that this update conflict detection scheme should not be used when large text objects (Image,text type fields) are set to updatable fields, because this is too much for the system and in fact Visual FoxPro does not allow you to do so.
   This is the most common detection update conflict detection scheme.

4. Keywords and timestamps: when any field in any of the data records in SQL SERVER is updated (new, deleted, modified), the system is branded with a timestamp-timestamp. A timestamp is a globally unique binary character and must not be understood as a date-type data. You must also be aware that the timestamp field for the Data source table must appear in the remote view when you update the conflict detection scheme using the keyword and timestamp (Visual FoxPro converts it to the character binary type). Keyword and timestamp update the conflict detection scheme is primarily used for the verification of update conflicts that are used by many people. Selecting this option will use the key value bar and timestamp field to check whether the data record has been modified, which is more efficient and more rigorous (theoretically) than the three options, because it has a minimal system burden.

To give you a better understanding of update conflicts and update conflict detection scenarios, figure 8 is explained in conjunction with examples.

CREATE SQL VIEW vcustomers;
REMOTE CONNECTION Northwind SHARE;
As SELECT Customerid,companyname,phone from Customers
* New Remote View
Dbsetprop ("Vcustomers.customerid", "Field", "Keyfield"). T.)
Dbsetprop ("Vcustomers.companyname", "Field", "updatable"). T.)
Dbsetprop ("Vcustomers.phone", "Field", "updatable"). T.)
Dbsetprop ("Vcustomers", "View", "sendupdates"). T.)
Dbsetprop ("Vcustomers", "View", "Wheretype", 3)
* Set update conflict resolution for "keywords and updated fields"
Dbsetprop ("Vcustomers", "View", "Updatetype", 1)

* Process One
Use Vcustomers
BROWSE
* Rest the pointer on the first record, that is: customerid= ' ALFKI '
REPLACE Phone with ' 123456 '
* Leave the visual FoxPro, don't move the record pointer

Process II
Using SQL Server, Enterprise Manager opens the Customers table, stops the pointer on the first record, modifies the phone's value of ' 00000 ', and moves the pointer to the next record.

Back to Visual FoxPro, move the pointer, and you'll see Figure 9:

Figure 9. Update conflict

Press the Restore button. The test is over.

Think about it and you'll see what an update conflict is.

In the example above, if we set the keyword way to detect an update conflict:

Dbsetprop ("Vcustomers", "View", "Wheretype", 1)

Others follow the previous steps, and you will find that no update conflicts have been generated. Because Visual FoxPro only detects that the keyword changes, there are no changes to the keyword in process one or two, but there is no update conflict.

In the example above, if we set the "Keyword and Updatable fields" method to detect update conflicts:

Dbsetprop ("Vcustomers", "View", "Wheretype", 2)

All others follow the original steps, and there will be an update conflict. Because Visual FoxPro not only detects whether keywords change, but also detects if all updatable field fields (in this case, all fields) change, here process two process one modifies the updatable field Phone, process one of course there will be an update conflict occurs.

If you are using SQL Server's Profiler program you can better understand the above content:

Figure 10. SQL Server's Profiler program

1. Using the Critical field conflict detection method, Visual FoxPro automatically generates the following statement to execute in SQL Server when an update is sent:

sp_executesql N ' UPDATE dbo. Customers SET phone= @P1 WHERE customerid= @P2 ', n ' @P1 nvarchar (), @P2 varchar ', n ' 123456 ', ' ALFKI '

Visible, the WHERE clause of the UPDATE includes only the key fields: CustomerID. Customerid= ' ALFKI ' in the visual FoxPro buffer, the visual FoxPro is based on whether the data source has changed for this value. If SQL Server does not find a record of customerid= ' ALFKI ' when it executes this UPDATE statement (we think the CustomerID was modified earlier by another user)--sql Server tells the Visual FoxPro that the update conflict occurred.

2. Using the "keyword and updatable Fields" conflict detection method, Visual FoxPro automatically generates the following statement to execute in SQL Server when an update is sent:

sp_executesql N ' UPDATE dbo. Customers SET phone= @P1 WHERE customerid= @P2 and companyname= @P3 and phone= @P4 ', N ' @P1 nvarchar (a), @P2 varchar (m), @P3 NV Archar, @P4 nvarchar ', n ' 123456 ', ' ALFKI ', n ' alfreds Futterkiste ', n ' 030-0074321 '

Visible, the WHERE clause of the update includes key fields: CustomerID, and all updatable fields: CompanyName, Phone. Visual FoxPro buffer customerid= ' ALFKI ', companyname= ' Alfreds futterkiste ', phone= ' 030-0074321 ', if SQL Server cannot find (customerid= ' ALFKI ' and companyname= ' Alfreds Futterkiste ' and phone= ' 030-0074321 ') when executing this UPDATE statement Records (we think other users have modified any of these three values in advance)--sql Server tells the Visual FoxPro update conflict occurred.

3. Using the "keyword and updated fields" Conflict detection method, Visual FoxPro automatically generates the following statement to execute in SQL Server when an update is sent:

sp_executesql N ' UPDATE dbo. Customers SET phone= @P1 WHERE customerid= @P2 and phone= @P3 ', n ' @P1 nvarchar (in), @P2 varchar (a), @P3 nvarchar (a) ', n ' 1234 ", ' ALFKI ', N ' 030-0074321 '

Visible, the WHERE clause of the update includes key fields: CustomerID, and all fields that have been updated by Visual FoxPro: Phone. Customerid= ' ALFKI ', phone= ' 030-0074321 ' in the Visual FoxPro buffer if SQL Server cannot find (customerid= ' ALFKI ' and phone= ') when executing this UPDATE statement 030-0074321 ') (we believe that other users have modified any one of these two values in advance)--sql Server tells the Visual FoxPro update conflict occurred.

4. If you want to try the "keyword and timestamp" Conflict detection method, add the timestamp field to the remote view of SQL Server and Visual FoxPro. When an update is sent, Visual FoxPro automatically generates the following statement to execute in SQL Server:

UPDATE dbo. Customers SET phone=n ' 12345 ' WHERE customerid= ' ALFKI ' and timestamp=0x0000000000000199

Visible, the WHERE clause of the UPDATE includes key fields: CustomerID, and Timestamp fields. Visual FoxPro buffer customerid= ' ALFKI ', timestamp is: 0x0000000000000199, if SQL Server executes this UPDATE statement, it cannot find (customerid= ' ALFKI ' and timestamp=0x0000000000000199 ') (we believe that the data from the row in the Data source table was modified by other users in advance, and the timestamp will change automatically whenever there is any change)--sql Server tells the Visual FoxPro update conflict occurred.

If you have not understood the update conflict-this is normal, please look down.