Using VFP and SQL Server to build client/server application (SPT) (2)

Set the dataset to updatable

Visual FoxPro Cursor Type

The English name of the cursor is cursor, and the idiom used in Visual FoxPro is temporary (temp table), but I think it's customary to call it the cursor. Because Visual FoxPro's cursor is absolutely strong, if you call temp table A misunderstanding, it seems that Visual FoxPro does not support cursors.

The Visual FoxPro has three cursors: a read-only cursor, a read-write cursor, an updatable cursor.

The read-only cursor is the kind of cursor that cannot be modified, and the cursor produced using the Sql-select statement in Visual FoxPro is a typical read-only cursor:

SELECT * from ORDERS into CURSOR mycursor

For the result set MyCursor we cannot perform any write operations on it, such as Replace, Delete, update, and so on.

A read-write cursor is a cursor that can read and write, but the data changes on the cursor are not reflected to the data source:

There are three types of typical read-write cursors, one of which is the SqlExec () cursor, which we can perform various operations (from a table compression command such as Zap and pack), but any changes to the data are not reflected in the data source.

The second type of read-write cursor is a new feature of visual FoxPro 7, and we can get this cursor by adding a keyword ReadWrite to the sql-select statement. This is a great feature, with it being more free to manipulate intermediate results in Visual FoxPro:

SELECT * from ORDERS into CURSOR mycursor ReadWrite

The third type of read-write cursor is the local view and remote view with no set send updates.

Remember I repeatedly emphasized in the "Remote View" chapter that if you want the view to be updatable, you must set its Sendupdate property to. T., if it is not set, the attempt is to read and write the cursor, any action on the image data can not be reflected in the data source.

An updatable cursor is a cursor that can read and write, and any data change will be reflected to the data source:

The typical updatable cursor is the updatable view, and I'm not going to comment on its benefits, because the details are discussed in the remote View chapter.

Five steps to set the result set of SqlExec () as an updatable cursor

In the remote view chapter, I have repeatedly emphasized how Visual FoxPro produces statement SQL descriptions that are sent to SQL Server. You can imagine configuring a sql-update or Sql-insert or sql-delete need, and how to translate the client changes into what the SQL statement needs is what we want to set:

A.cursorsetprop ("Tables", data source table names, updatable cursor names)

This step sets the name of the table to be updated in the data source (SQL Server), if it involves more than one table: Cursorsetprop ("TABLES", "T1,t2", "MyCursor").

B.cursorsetprop ("Keyfieldlist", key field, updatable cursor name)

This step is to set the key field, which is the field of the updatable cursor, not the field in the data source.

C.cursorsetprop ("Updatablefieldlist", updatable field list, updatable cursor name)

This step sets which fields in the updatable cursor are to be reflected to the data source, which fields are updatable.

D.cursorsetprop ("Updatenamelist", corresponding list of fields before and after, updatable cursor name)

This step sets the corresponding relationship between the front and back fields.

E.cursorsetprop ("Sendupdates",. T., updatable cursor name)

This step should not be said more, the most critical step, do not do before the efforts are in vain.

Here are three examples to illustrate the problem:

Example one:

Cnn=sqlconnect ("Northwind")
SqlExec (CNN, "Select CategoryID as ID, categoryname,description from Categories", "MyCursor")
SELECT MyCursor
Cursorsetprop ("Tables", "categories", "MyCursor")
Cursorsetprop ("Keyfieldlist", "id", "mycursor")
Cursorsetprop ("Updatablefieldlist", "Id,categoryname,description", "MyCursor")
Cursorsetprop ("Updatenamelist", "ID categories.categoryid,categoryname categories.categoryname," +;
"Description categories.description", "MyCursor")
Cursorsetprop ("Sendupdates",. T., "MyCursor")

1. The Data source table is the Categories table of the NorthWind database, and the updatable Light table is MyCursor

2. Cursorsetprop ("Tables", "categories", "MyCursor"), Tables property is set: The updated data source table Gategories

3. Cursorsetprop ("Keyfieldlist", "id", "mycursor"), keyword with updatable cursor field name: ID, not field name of Data source table: CategoryID

4. Cursorsetprop ("Updatablefieldlist", "Id,categoryname,description", "MyCursor"), the Updatable field list is represented by the field name of the updatable cursor, not the field name of the data source table.

5. Cursorsetprop ("Updatenamelist", "ID categories.categoryid,categoryname categories.categoryname,description Categories.description "," MyCursor "), please note the writing here: each group of corresponding relations separated by commas, write the field name that can update the cursor, then place a space, and then write the field name of the Data source table (Note must add the data source table name)

Case II

Cnn=sqlconnect ("Northwind")
SqlExec (CNN, "Select A.productid,a.productname,a.unitprice,b.categoryid,b.categoryname,c.supplierid," +;
"C.companyname as Suppliername,c.contactname" +;
"From [Products a INNER join categories B on A.categoryid=b.categoryid)" +;
"INNER JOIN suppliers C on A.supplierid=c.supplierid", "MyCursor")
SELECT MyCursor
Cursorsetprop ("Tables", "Products,categories,suppliers", "MyCursor")
Cursorsetprop ("Keyfieldlist", "Productid,categoryid,supplierid", "MyCursor")
Cursorsetprop ("updatablefieldlist", +;????????????????????? "Productid,productname,unitprice,categoryid,categoryname,supplierid,suppliername,contactname", "MyCursor")
Cursorsetprop ("Updatenamelist", "ProductID products.productid,productname," +;
"Products.productname,unitprice Products.unitprice," +;
"CategoryID Categories.categoryid,categoryname Categories.categoryname," +;
"SupplierID suppliers.supplierid,suppliername suppliers.companyname,contactname suppliers.contactname", "MyCursor" )
Cursorsetprop ("Sendupdates",. T., "MyCursor")

1. This is a three-table (categories,products,suppliers) connection result set, which is complex, and our goal is to make all the fields available to the corresponding tables.

2. Note the "Tables" attribute of the writing, involving three tables listed three tables!

3. Notice how the "Keyfieldlist" property is written, and the three table keywords are listed. If you do not have the SupplierID, then the fields from the Suppliers table cannot be updated to the Suppliers table.

Example Three

Cnn=sqlconnect ("Northwind")
SqlExec (CNN, "Select Orderid,productid,unitprice,quantity,discount from [Order Details]", "MyCursor")
SELECT MyCursor
Cursorsetprop ("Tables", "[Order Details]", "MyCursor")
Cursorsetprop ("Keyfieldlist", "Orderid,productid", "MyCursor")
Cursorsetprop ("Updatablefieldlist", "Orderid,unitprice,quantity,discount", "MyCursor")
Cursorsetprop ("Updatenamelist", "OrderID") [Order Details].orderid,unitprice Details].quantity,discount [Order Details].discount "," MyCursor ")
Cursorsetprop ("Sendupdates",. T., "MyCursor")

1. This result set comes from a table: order Details.

2. Notice how the Tables property is written, in SQL Server, the table name with spaces is delimited by the square number, and the tables property specifies the data source table, so it must be filled in with: [Order Details].

3. Note the "Keyfieldlist" attribute of the writing, the Order Details table's main key word is a composite keyword, there are OrderID and ProductID together, so here will be filled with them.

A very important attribute--wheretype

When we set the result set as an updatable cursor, there is an important attribute that is not set, that is, Wheretype. That is, there are four kinds of conditions in which the WHERE clause arises:

Cursorsetprop ("Wheretype", 1) && according to keywords
Cursorsetprop ("Wheretype", 2) && by keyword + updatable field
Cursorsetprop ("Wheretype", 3) && updated field according to keyword +
Cursorsetprop ("Wheretype", 4) && based on keyword + timestamp

For more details, you can see the "Remote View" section, where I have spoken a lot.