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

Buffering (buffering)

The buffering technology in Visual FoxPro

When the remote data is downloaded to the client, the data is pressed into the buffer. The various movements of the data by the client user are not reflected in the data source, but only after the user confirms the change to the data, the various changes are sent to the backend in the form of SQL description. So why not let Visual FoxPro manipulate remote data directly, just as if you were not using buffer technology to control Visual FoxPro local data. I think the reason is:

1. In the application of Client/server architecture, the database server needs to handle many client's requests at the same time, if there is a customer "direct" control (lock) it, the multi-user system can not be discussed.

2. Visual FoxPro communicates with the remote database through ODBC, and if one moves, the communication between the two will surely multiply, which increases the burden on the database server that is burdening the network.

For these reasons, Visual FoxPro enforces the use of buffering techniques in remote data processing. We know that there are four options for buffering technology in Visual FoxPro with lock binding:

1. Conservative line Buffering. So-called "conservative" means "lock on edit", "line buffering" means "to buffer only the data records edited by the user". So once this pattern is used, the corresponding data record for the data source is locked when the edit action is started, and the data changes are sent when the following two actions are performed: Move the data pointer, execute the tableupdate () function.
   This pattern is not used by Remote data processing because it locks the corresponding row of the data source when editing begins.

2. Open row buffering. The term "open" means "lock on Update", "buffer" means "only the data record that the user is edited". Therefore, in this mode, data changes are sent only when the following two actions are performed, and the data source is locked for Row Records: Moving the data pointer, executing the tableupdate () function.

3. Conservative table Buffering. "Conservative" means "lock on edit", "table buffering" means "a dataset (cursor) that is buffered for the entire user to edit." So once this pattern is used, the related recordset for the data source is locked when the edit action is started, and the data changes are sent when the following action is performed: Execute the TableUpdate () function.
   This pattern is not used by Remote data processing because it locks the entire corresponding table or recordset of the data source at the start of the edit.

4. Open row buffering. The term "open" means "lock on Update", "table buffering" means "a dataset (cursor) that is buffered for the entire user to edit." Therefore, with this pattern, data changes are sent only when the TableUpdate () function is executed, and the related recordset of the data source is locked.

Well, we have the following conclusion: When manipulating remote data, Visual FoxPro will use open row buffering or open table buffering for the cursor, and the default setting is open row buffering.

In the future, when discussing the remote data processing, it is not specially pointed out that the line buffering means the open row buffering, and the table buffering is the open table buffering.

Under open row buffering, because only a single edited record is buffered, there are two ways to confirm edits, send updates: Move the pointer (we've used it in the example above), TableUpdate () function. I wonder if you understand the meaning of "pointer move confirmation update"? That's how I understand it: row buffering is only useful for an edited record, and if you move the pointer, you must confirm the update (if the data changes), because if you do not confirm the update (freeing the buffer), Visual FoxPro cannot make a buffer for the next line-remember: This is a line buffer.

Under Open Table buffering, Visual FoxPro opens a buffer on the entire recordset, so moving the pointer does not confirm the update. Only the TableUpdate () function is used.

At first glance, open row buffering requires less system resources than open row buffering, which seems like a good choice, I don't think so:

1. Some Visual FoxPro commands or functions move the pointer "involuntarily", causing the developer to lose control of the update's confirmation.

2. Sometimes the maintenance of data is in batches.

The following code shows how to control buffering:

Use Vcustomers
Cursorsetprop ("Buffering", 3, "Vcustomers")
* Set the vcustomers buffer mode to "open row buffer". This sentence can be omitted because of the default setting for Visual FoxPro.
Use Vorders
Cursorsetprop ("Buffering", 5, "Vorders")
* Set the Vorder buffer mode to "open table buffering."

Understanding Update conflicts with Buffering

In Figure 8 I was prompted when an update error occurred: "Originally phone=030-0074321, now phone=00000, both ...", then this is "when", "Now" what is the concept? (Assume that process one or two uses row buffering mode, "keyword and updated fields" to detect conflicts).

"Original" refers to the record value in the SQL Server datasheet when the view is opened in process one or the last time the success was refreshed. Let's stop, how do we refresh the cursor and buffer?

1. The remote View cursor is opened (whether using row buffering or table buffering).

2. The Requery () function is executed successfully (whether using row buffering or table buffering).

3. Send updates (whether successful or not)

As you can imagine, when a process opens a remote view, Visual FoxPro automatically presses the data values of SQL Server into the cache at this time, and the process says: My modifications to the data on SQL Server should be based on that phone= 030-0074321, if this foundation does not exist, this update error occurs.

Process II also reads SQL Server data when process one has not yet sent it to the data source for client-side changes to the phone, and note that process two says: My modifications to the data on SQL Server should be based on that phone=030-0074321. So process two modify phone 00000, and in the process before the confirmation of the data changes, there will be no update conflict, because process II modify the data based on the establishment.

The process slow the data to 123456, sending updates. Here's the problem: the process tells SQL Server how to modify the data:

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 '

When I fill in the parameters, you can see exactly what the problem is:

UPDATE dbo. Customers SET phone= ' 123456 ' WHERE customerid= ' ALFKI ' and phone= ' 030-0074321 '

See no: Visual FoxPro automatically puts the phone=030-0074321 in the buffer(in the Browse window you've changed the phone to 123456,visual FoxPro "premeditated", putting the original data in a buffer , let your surface change million times-I am not afraid of) take out and combine keywords as the basis for updates, however, because process two has changed the value of Phone, in SQL Server where there will be eligible customerid= ' ALFKI ' and phone= ' 030-0074321 ' line, only customerid= ' ALFKI ' and phone= ' 00000 ' record line. So SQL Server told Visual FoxPro that the target record was not found, and Visual FoxPro said to the User: update conflict.

So, the buffer is right here: the client and the server through the ODBC translation of "teaser"-but ODBC is very benzene-can only pass some SQL statements. In fact, any changes to the data can be summed up as: Insert, Update, Delete. The SQL statement differs greatly from the Visual FoxPro command function-target positioning must rely on conditional statements (Where clauses) (Visual FoxPro can easily be positioned to nth rows); buffering provides the basis for these vital positioning conditions, Cannot generate a location statement without buffering!

Confirm updates, discard updates

Confirm Update

As we've mentioned several times, there are basically two ways to confirm updates: Move the pointer, use the TableUpdate () function. The move pointer can only be used under open line buffering, and developers are less able to control the law and are generally used in interactive tools, such as the Enterprise Manager tool for SQL Server we used earlier. Here we only discuss the tableupdate () function.

Use the TableUpdate () function under open row buffering:

1. Syntax: TableUpdate (0[,lforce][,nworkaear|ctablealias])

2. Return value: Update succeeded ——. T., update failed ——. F.

3. Required Parameters: 0. Represents updating only the current record to the data source--here is the record buffer, of course: "Update only current record to data source."

4. Optional parameter--lforce. The default is. F., means: If an update error occurs to confirm the update error, this function returns. F.; If you set this parameter to. T, which indicates that an update error occurs, the new value of the client is overridden to overwrite the updates of other users that have been confirmed on the network, and if the overwrite succeeds, this function returns. T..
   Default value when this parameter is represented by default.
   The parameter is set to. The essence of T. The update conflict is detected as a "critical field,"so as long as the keyword does not conflict, no update conflict occurs and the new value of the client overwrites the changes made by other users.

5. Optional parameter--nworkaear|ctablealias. Represents a workspace that implements TableUpdate () and is in effect for the current workspace by default.

For example:

Use Vcustomers
REPLACE PHONE with ' 123456 '
? TableUpdate (0)
Returns. T. Update succeeded, otherwise failed.

Use Vcustomers
REPLACE PHONE with ' 123456 '
? TableUpdate (0,.t., ' Vcostomers ')
* Because Lforce is set to. t.,visual FoxPro temporarily modifies the update detection mode as "critical field", so as long as the keyword CustomerID does not conflict, Visual FoxPro does not detect even if other fields have been modified by another user. Visual FoxPro will force overwriting changes made by other users.
* In the conflict detection mode for critical and Modified fields: Visual FoxPro sends the following statement to SQL Server:
sp_executesql N ' UPDATE dbo. Customers SET phone= @P1 WHERE customerid= @P2 ', n ' @P1 nvarchar (), @P2 varchar ', n ' 123456 ', ' ALFKI '

Use the TableUpdate () function under Open Table buffering:

2. Syntax: TableUpdate (Nrows[,lforce][,nworkaear|ctablealias][,cerrorarray])

4. Return value: Update succeeded ——. T., update failed ——. F.

6. Required parameter--nrows. There are two kinds of values: 1, 2. Suppose the user modifies the 1th, 2, 4, 5, 7 Records of the cursor, and if the update changes, an update conflict will occur on the 4th record. Now, to perform this function, Visual FoxPro will send five update-sql descriptions in turn:
   When this parameter is 1 o'clock and an update conflict occurs when the 4th record is executed, Visual FoxPro will stop the update description of the 5 and 7 records sent and return the function. F. Value, the Visual FoxPro record pointer rests on the fourth record.
   When this parameter is 2 o'clock and an update conflict occurs when the 4th record is executed, Visual FoxPro will continue to send the update description of the 5, 7 records and return the function. F. Value, the Visual FoxPro record pointer eventually stops on the last modified record (this is the 7th record). If an optional parameter--cerrorarray exists, Visual FoxPro writes the record number (RECNO ()) of the update error to the array, and if the 5th record conflicts, the array is a column of two rows of arrays, cerrorarray[1]=4, Cerrorarray[2]=5

8. Optional parameter--lforce. The default is. F., means: If an update error occurs to confirm the update error, this function returns. F.; If you set this parameter to. T, which indicates that an update error occurs, the new value of the client is overridden to overwrite the updates of other users that have been confirmed on the network, and if the overwrite succeeds, this function returns. T..
   Default value when this parameter is represented by default.
   The parameter is set to. The essence of T. The update conflict is detected as a "critical field,"so as long as the keyword does not conflict, no update conflict occurs and the new value of the client overwrites the changes made by other users.

10. Optional parameter--nworkaear|ctablealias. Represents a workspace that implements TableUpdate () and is in effect for the current workspace by default.

12. Optional parameter--cerrorarray. This is a column array, and it records the record number of the record in which the update conflict occurs, and only if the required parameter nrows is 2 o'clock, if no update conflicts occur or if the required parameter nrows is not 2 o'clock, the array is one row, and the value is-1.

For example: (assuming that "critical and modified fields" are used as the Update Conflict Detection scheme)

Use Vcustomers
Cursorsetprop ("Buffering", 5, "Vcustomers")
REPLACE Phone with ' 9999 ' next 4
* Change the 1th, 2, 3, 4, and record of the phone to 9999
BROWSE

* Create update conflicts using SQL Server's query Analyzer
* Start SQL Server Query Analyzer, log on to the Northwind database
* Enter the following statement and execute:
Update customers set phone= ' 00000 ' where customerid= ' anatr ' or customerid= ' ANTON '

Case one: tableupdate (1,. F., ' Vcustomers ')

* Return to Visual FoxPro
? TableUpdate (1,. F., ' Vcustomers ')
* The function returned because a conflict occurred while recording the 2 update. F.
? recno (' Vcustomers ')
* The pointer stops at the 2nd record.
? Aerror (ERR)
Use the Aerror function to get Visual FoxPro error messages into the Err array
? Err (1)
* Error Number: 1585
? Err (2)
* Error message: "Update conflict"

* Back to SQL Server Query Analyzer
* Enter the following statement and execute:
Select Customerid,phone from Customers
* You will see: The value of the first record phone has been modified by the client of Visual FoxPro, the value is: 9999. The subsequent three records have not changed. Indicates that Visual FoxPro, when sending SQL descriptions to SQL Server in turn, stops working when an update error is encountered.
In fact, the Profiler tool that looks at SQL Server also proves the above argument:
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 ' 9999 ', ' ALFKI ', N ' bbbb '
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 ' 9999 ', ' Anatr ', N ' 1234 '//Where the update error occurred, Visual FoxPro stop working down

Situation Two: TableUpdate (1,. T., ' Vcustomers ')

* Return to Visual FoxPro
? TableUpdate (1,. T., ' Vcustomers ')
* function returns. T.
? recno (' Vcustomers ')
* The pointer stops at the 4th record.

* Back to SQL Server Query Analyzer
* Enter the following statement and execute:
Select Customerid,phone from Customers
* You will see: The value of the first four records phone has been modified by the client of Visual FoxPro, the value is: 9999. It stands to reason that the 2nd record will conflict when it is updated, but because Visual FoxPro temporarily changed the detection scheme for the update conflict to "critical field", the conflict that should have been detected was ignored, and the new value of the Visual FoxPro client forcibly overwrites the modifications of other clients.
In fact, the Profiler tool that looks at SQL Server also proves the above argument:
sp_executesql N ' UPDATE dbo. Customers SET phone= @P1 WHERE customerid= @P2 ', n ' @P1 nvarchar (), @P2 varchar ', n ' 9999 ', ' ALFKI '
sp_executesql N ' UPDATE dbo. Customers SET phone= @P1 WHERE customerid= @P2 ', n ' @P1 nvarchar (), @P2 varchar ', n ' 9999 ', ' Anatr '
sp_executesql N ' UPDATE dbo. Customers SET phone= @P1 WHERE customerid= @P2 ', n ' @P1 nvarchar (), @P2 varchar ', n ' 9999 ', ' ANTON '
sp_executesql N ' UPDATE dbo. Customers SET phone= @P1 WHERE customerid= @P2 ', n ' @P1 nvarchar (), @P2 varchar ', n ' 9999 ', ' arout '

Situation three: TableUpdate (2,. F., ' Vcustomers ', Arry)

* Return to Visual FoxPro
? TableUpdate (2,. F., ' Vcustomers ', Arry)
* The parameter nrows is set to 2, and the Visual FoxPro continues to execute, but the parameter function returns, even if the record 2 and 3 update conflicts occur. F.
? recno (' Vcustomers ')
* The pointer stops at the 4th record.
? Aerror (ERR)
Use the Aerror function to get Visual FoxPro error messages into the Err array
? Err (1)
* Error Number: 1585
? Err (2)
* Error message: "Update conflict"
? ARRY[1]
*2
? ARRY[2]
*3
*arry returns the record number where the update error occurred

* Back to SQL Server Query Analyzer
* Enter the following statement and execute:
Select Customerid,phone from Customers
* You will see: The value of the first and fourth record phone has been modified by the client of Visual FoxPro, the value is: 9999. The second and third records have not changed. Explains that Visual FoxPro, when sending SQL descriptions to SQL Server in turn, encounters an update conflict that is ignored and continues to work.
In fact, the Profiler tool that looks at SQL Server also proves the above argument:
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 ' 9999 ', ' ALFKI ', N ' CCCC '
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 ' 9999 ', ' Anatr ', N ' CCCC '//update conflict, Visual FoxPro continue down
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 ' 9999 ', ' ANTON ', N ' CCCC '//update conflict, Visual FoxPro continue down
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 ' 9999 ', ' arout ', N ' CCCC '

Situation four: TableUpdate (2,. T., ' Vcustomers ', Arry)

* Return to Visual FoxPro
? TableUpdate (2,. T., ' Vcustomers ', Arry)
* But the parameter function returns. T.
? recno (' Vcustomers ')
* The pointer stops at the 4th record.
? ARRY[1]
*-1
* No update error occurred

* Back to SQL Server Query Analyzer
* Enter the following statement and execute:
Select Customerid,phone from Customers
* You will see that all record phone values have been modified by the Visual FoxPro client, with a value of: 9999. It stands to reason that the 2nd and 3rd records will conflict when they are updated, but because Visual FoxPro temporarily changed the detection scheme for the update conflict to "critical fields", the conflict that should have been detected was ignored, and the new values of the Visual FoxPro client forcibly overwrite the modifications of other clients.
In fact, the Profiler tool that looks at SQL Server also proves the above argument:
sp_executesql N ' UPDATE dbo. Customers SET phone= @P1 WHERE customerid= @P2 ', n ' @P1 nvarchar (), @P2 varchar ', n ' 9999 ', ' ALFKI '
sp_executesql N ' UPDATE dbo. Customers SET phone= @P1 WHERE customerid= @P2 ', n ' @P1 nvarchar (), @P2 varchar ', n ' 9999 ', ' Anatr '
sp_executesql N ' UPDATE dbo. Customers SET phone= @P1 WHERE customerid= @P2 ', n ' @P1 nvarchar (), @P2 varchar ', n ' 9999 ', ' ANTON '
sp_executesql N ' UPDATE dbo. Customers SET phone= @P1 WHERE customerid= @P2 ', n ' @P1 nvarchar (), @P2 varchar ', n ' 9999 ', ' arout '

Discard Updates

What if you want to "discard changes that the client has already implemented on the cursor"? This is simple, please use the Tablerevert () function. Here is a concept that is important: no case of executing this function will communicate with the remote data source, and Visual FoxPro simply retrieves the original value from the buffer and fills it in the cursor.

So how does Visual FoxPro retrieve data from the buffer? You can use the Oldval () function to get the same effect, its usage is not introduced here!

Use the Tablerevert () function under open row buffering:

1. Syntax: TableUpdate (. F. [, Nworkaear|ctablealias])

2. return value: 1. Returns 0 if the current record has not been modified.

3. Optional parameter--nworkaear|ctablealias. Represents a workspace that implements Tablerevert () and is in effect for the current workspace by default.

For example:

Use Vcustomers
Cursorsetprop ("Buffering", 3, "Vcustomers")
REPLACE Phone with ' 9999 '
？ Vcustomer.phone
*9999
? Tablerevert (. F., ' Vcustomers ')
* Return 1
？ Vcustomer.phone
*123456

Use Vcustomers
Cursorsetprop ("Buffering", 3, "Vcustomers")
? Tablerevert (. F., ' Vcustomers ')
* Return 0

Use the Tablerevert () function under Open Table buffering:

1. Syntax: TableUpdate (Lallrows[,nworkaear|ctablealias])

2. Return value: Discard updated number of records

3. Required parameter--lallrows. The default value is. F., indicates that the update is discarded for the current record, and this parameter is set to. T., discard updates for all the records that have been modified.

4. Optional parameter--nworkaear|ctablealias. Represents a workspace that implements Tablerevert () and is in effect for the current workspace by default.

Use Vcustomers
Cursorsetprop ("Buffering", 5, "Vcustomers")
REPLACE Phone with ' 9999 ' next 4
* Change the 1th, 2, 3, 4, and record of the phone to 9999
BROWSE
Go 2
? Tablerevert (. F., ' Vcustomers ')
*1
? Tablerevert (. T., ' Vcustomers ')
*3