Visual FoxPro provides two built-in mechanisms for working with client/server data: remote views and SQL pass through. Other data access methods, such as ADO, can also be used in Visual FoxPro client/server applications. Each technique has its advantages and disadvantages. Remote views have the advantages of being extremely easy to use and being bindable to FoxPro controls. A remote view is a SQL SELECT statement stored in a Visual FoxPro database container (DBC). Remote views use
Open Database Connectivity (ODBC), a widely accepted data-access API, to access any ODBC-compliant data.
Although the examples in this book use Microsoft SQL Server on the back end, remote views can also be used with many other back ends such as Oracle, IBM DB2, Informix, Sybase, Microsoft Access or Excel, or even Visual FoxPro. With a remote view, youcan work with client/server data almost as if it were local Visual FoxPro data. In this chapter you will learn how to use this terrific tool as the foundation for a client/server application. In addition, by learning the fundamentals of remote views, you will be ready tolearn about SQL pass through in Chapter 6, “Extending Remote Views with SQL Pass Through.”
Before you can create a remote view, you must specify how the view will connect to the back end. There are several ways to do this, all of which use ODBC. Therefore, both ODBC itself
and the back-end-specific ODBC driver must be installed and configured on the client machine. For SQL Server development, ODBC installation is done when installing Visual Studio and/or SQL Server. For an application you distribute, ODBC installation can be done through the Visual FoxPro Setup Wizard.
Here is a very simple remote view that returns all rows and columns in the Northwind database’s Customers table:
CREATE SQL VIEW VCustomers ; REMOTE CONNECTION Northwind ; AS SELECT * FROM Customers
The second line specifies which connection VFP will use to execute the SELECT—in this case, one called Northwind. VFP will look for a connection called Northwind in two places:
first in the list of named connections in the current DBC, and then in the client machine’s list of
ODBC Data Source Names, or DSNs.
Named connections, which are stored in the DBC along with the view definitions, offer greater flexibility than DSNs. Named connections can use a string that defines the server, database, login name and password for connecting to the back end. A connect string allows you todefine your connection at run time, rather than requiring a DSN, which is especially useful
for applications that connect to multiple servers. Alternately, named connections can use an existing DSN to define the connection.
The quickest way to get rolling with the VCustomers view is to create a DSN to connect to the SQL Server Northwind database. To create a DSN, start the ODBC Data Sources Control Panel applet, which, depending on the version of ODBC installed on your machine, looks something like Figure 1.
Figure 1. The ODBC Data Source Administrator dialog.
There are three types of DSNs: user, system and file. A user DSN can be used only by one particular user, while a system DSN can be used by any user on the machine. User and system DSNs are stored in the registry of the client machine, while file DSNs are stored in text files and can be located anywhere. We typically use system DSNs because we only have to set up one DSN per machine rather than one per user. Each type of DSN is set up with its own tab in the dialog.
To create the Northwind system DSN, click on the System DSN tab in the ODBC Data
Source Administrator dialog, click the Add button, select the SQL Server driver, and then
click the Finish button. Now you will see the Create a New Data Source to SQL Server dialog. Fill in the fields as shown in Figure 2. The DSN name is what you will use when you create connections, while the description is optional. If SQL Server is running on the local machine, besure to put “(local)” in the Server field rather than the machine name. Using the machine name, particularly on Windows 95 or 98 machines, will frequently cause the connection to fail, at least with the driver versions available at the time of this writing.
Figure 2. The Create a New Data Source to SQL Server dialog filled in to create a connection to the Northwind database on the local machine.
When you click the Next button, ODBC will attempt to locate the specified server; if successful, you’ll be asked to configure the connection as shown in Figure 3 and Figure 4. If unsuccessful, you may have a problem with a network connection, or you may not have permission to access the server. Neither of these situations can be rectified here, but require checking your network or your SQL Server.
Figure 3. Configuring the Northwind connection to use SQL Server security with the default sa login.
Figure 4. Configuring the Northwind DSN to connect to the Northwind database.
Once you’ve created the connection to the Northwind database on SQL Server, create a
Visual FoxPro database by typing the following in the Command Window:
CREATE DATABASE Northwind
Then create a view, open and browse it:
CREATE SQL VIEW VCustomers ; REMOTE CONNECTION Northwind ; AS SELECT * FROM Customers
USE VCustomers
BROWSE
The next step is to use a named connection in the VFP database. A named connection is an object in the VFP database that contains connection information. Why use a named connection rather than just a DSN? One major reason is that once you have created one, you canshare the connection among multiple views. Each ODBC connection uses resources on both the server and the client. They take time to establish, they use memory (about 24K per connection on SQL Server), and having too many of them can seriously degrade performance in some systems. Although ODBC has a connection pooling feature that allows unused connections to be reused, you as a developer cannot control this feature from your application.
If the VCustomers view defined previously, and another view, are opened, two ODBC connections will be established. To demonstrate this, define a view of the Orders table, then open it and the VCustomers view:
CREATE SQL VIEW VOrders ;
REMOTE CONNECTION Northwind ;
AS SELECT * FROM Orders
USE VCustomers IN 0
USE VOrders IN 0
?CURSORGETPROP("ConnectHandle", "VCustomers")
?CURSORGETPROP("ConnectHandle", "VOrders")
First of all, note that you will be asked for the user ID and password twice. Also, the last two lines will display two different numbers. By using a named connection, the same ODBC connection can be used by both views. To create a named connection and a view that canshare it, open the Northwind DBC and type the following in the Command Window:
CREATE CONNECTION Northwind DATASOURCE Northwind
CREATE SQL VIEW VCustomers ;
REMOTE CONNECTION Northwind SHARE ;
AS SELECT * FROM Customers
DBSETPROP('VCustomers', 'View', 'ShareConnection', .T.)
Note the addition of the SHARE keyword to the view definition and the use of the DBSETPROP() call to set the ShareConnection property. You must do both of these in order share the connection. Now when you attempt to open the two views, you will only be asked to log inonce, and the connect handle will be the same for both cursors. Note that the SHARE keyword (in the CREATE SQL VIEW statement) and the ShareConnection property (in the DBSETPROP statement) have no effect with views using a DSN rather than a named connectionbecause a DSN connection cannot be shared by multiple views.
In Visual FoxPro, most environment settings are local to a data session. However, it is important to note that a named connection can be shared by multiple datasessions.
The Northwind named connection we just created uses the Northwind DSN, but you can also create named connections that use connect strings. A connect string is a string that contains the server name, login name, password and database:
CREATE CONNECTION Northwind2 ;
CONNSTRING "DSN=northwind;UID=sa;PWD=;DATABASE=northwind"
Each of the four parts of the connect string is delimited by a semicolon. No quote
marks are used for the individual parameters, though you can optionally surround the entire connect string in quotes. You will need the quotes, however, if you require spaces within the string.
Named connections can also be created with VFP’s Connection Designer. Right-click in an open Database Designer and select Connections to open a list of named connections. Click
New and you will see the Connection Designer, which looks like Figure 5.
Figure 5. The Visual FoxPro Connection Designer.
The Connection Designer has controls for setting additional properties for named connections. Each of these properties can also be set using the DBSETPROP() function. The Visual FoxPro documentation provides a complete listing of properties under the DBGETPROP()topic. We’ll cover a few of them here:
• Asynchronous. When set to .F., the default, the connection executes commands synchronously—that is, the next line of code doesn’t execute until the previous command on the connection has completed. Asynchronous execution allows commands on the connection toexecute in the background while your code continues to execute. While asynchronous processing may be useful for certain tasks, generally you want synchronous execution.
• ConnectTimeout. When set to any value other than 0 (the default), VFP will attempt to acquire the connection for the number of seconds specified. If Visual FoxPro is unable to connect within this time period, an error occurs.
• IdleTimeout. This is similar to ConnectTimeout, but it doesn’t actually disconnect when it times out. It merely deactivates the connection. If the connection is used for a view, VFP will reactivate the connection when you attempt to use it again. Use with care, as this can cause unclear errors to occur in your application (e.g.,
“Connectivity error: unable to retrieve specific error information. Driver is probably out of resources.”).
• DispLogin. This property determines whether and how the user is prompted for login information. The default setting is 1 (DB_PROMPTCOMPLETE from Foxpro.h), which will only prompt the user if some required login information is missing. DB_PROMPTALWAYS, or 2, will cause the user to be prompted each time a connection is made to the server. DB_PROMPTNEVER, or 3, will not prompt the
user, even if no login information is supplied, allowing the connection to fail. This last setting is required for using remote views or SQL pass through with Microsoft Transaction Server (MTS).
• DispWarnings. If this property is set to .T. (the default), then non-trappable ODBC errors will be displayed to the user in a message box. In an application, you’ll typically set this to .F. and deal with errors yourself. For more about error handling, see Chapter
8, “Errors and Debugging.”
‡
All connection properties can be set persistently in the database by using DBSETPROP() or, temporarily, for an open connection, by using SQLSETPROP(). SQLSETPROP() is covered in greater detail
in Chapter 6, “Extending Remote Views with SQL Pass Through.”
Remote views
In the “Connections” section of this chapter, you learned how to create a basic remote view of the Northwind Customers table. This view is nothing more than a SQL SELECT that gets all rows and all columns of the Customers table. If you run this on your development machine with SQL Server running on the same machine, the query will execute quickly, as there are only 91 records. But on a network with many users—particularly one with a low-bandwidth connection, and with thousands of customers in the table—this would be a terribly inefficient query. A more efficient view can be created by adding a WHERE clause to reduce the number of rows returned. The following view will only return rows where the customerid column contains ‘ALFKI’:
CREATE SQL VIEW VCustomers ;
REMOTE CONNECTION Northwind SHARE ;
AS SELECT * FROM Customers ;
WHERE customerid LIKE 'ALFKI'
Now the view will only return a single row, but it can only be used for a single customer. Visual FoxPro allows you to create parameterized views so that you can define the WHERE clause when the view is executed.
CREATE SQL VIEW VCustomers ;
REMOTE CONNECTION Northwind SHARE ;
AS SELECT * FROM Customers ;
WHERE customerid LIKE ?cCustomerID
The cCustomerID “thing” is a “view parameter,” which represents variable data that can be filled in at run time. By preceding cCustomerID with the question mark operator, you tell VFP to create the structure of the view in the DBC but to evaluate a memvar called cCustomerID at run time. If cCustomerID exists when the view is opened or REQUERY() or REFRESH() is issued, its value will be substituted into the WHERE clause. If the variable cCustomerID does not exist, the user will be prompted to supply it, as shown in Figure 6. In an application, you willusually want to specify the values of parameters yourself rather than allowing VFP to prompt the user like this.
Figure 6. When opening a parameterized view where the parameter does not already exist, the user is prompted to provide a parameter.
When creating a client/server application, we usually create one SELECT * view per table in the database and set the parameter to the primary key. We use these views for data entry and give the view the same name as the table, preceded by the letter “V.” Sometimes it makes sense to parameterize these views on some foreign key, but generally using the primary key assures you of views that bring down only a single record.
When views are used to return a range of records for reporting or lookups, it often makes sense to use parameters other than the primary key. For example, you might want to find all customers in London:
CREATE SQL VIEW VCustomersByCity ; REMOTE CONNECTION Northwind SHARE ; AS SELECT * FROM Customers ;
WHERE city LIKE ?cCity
When you set the variable cCity to the value “London” and open the VCustomersByCity view, the result set will be only those customers in London.
You can use wildcards in view parameters, too. To find all customers in any city beginning with the letter “L,” set cCity to a value of “L%” prior to executing the query.
‡
The syntax for wildcards is not the same in SQL Server as it is in FoxPro. While the % wildcard is the same in both, you cannot use the * wildcard in SQL Server. Setting cCity to a value of “L*” would not return customers in
cities beginning with “L,” but rather in cities beginning with “L*.” There probably aren’t any cities in your data with such a name. Instead, use “L%.”
As with any other SQL SELECT statement, you can specify a field list. SELECT * may be useful in some situations, but it is often more efficient to specify the field list explicitly in order
to bring down only the columns you need. For example, if you only need the customer ID, company name, city and country for each customer, a more efficient and equally useful view of customers would look like this:
CREATE SQL VIEW VCustomersByCity ; REMOTE CONNECTION Northwind SHARE ;
AS SELECT customerid, companyname, city, country FROM Customers ; WHERE city LIKE ?cCity
Remote views, like other SQL SELECTs, can also join multiple tables. For example, this view returns all sales territories and the employees responsible for them:
CREATE SQL VIEW VEmployeeTerritories ; REMOTE CONNECTION Northwind SHARE ;
AS SELECT territories.territoryid, territories.territorydescription, ;
employees.employeeid, employees.lastname, employees.firstname ;
FROM territories LEFT OUTER JOIN employeeterritories ;
ON territories.territoryid = employeeterritories.territoryid ;
LEFT OUTER JOIN employees ;
ON employeeterritories.employeeid = employees.employeeid
You must be certain to use join syntax that is supported by the back end. VFP and SQL Server 7.0 are pretty similar, but you may encounter back ends that are different.
‡
When creating views, avoid using * for the field list. The view will work without errors until a field is added to the base table(s) on the SQL Server. Since the view was defined with the previous version of the
table, Visual FoxPro does not know about the new field(s), and produces the error “Base table fields have been changed and no longer match view fields” when executed.
Updatable views
Remote views can be used to update the underlying data. You can append records in views, delete existing records and update fields. When you are ready to update the data on the back end, simply issue a call to TABLEUPDATE(), and VFP takes care of sending the changes to SQL Server.
Remote views can be made updatable in the View Designer, as shown in Figure 7. At a minimum, you must select one or more primary key columns, determine which columns to update, and check the “Send SQL updates” check box. Even if you mark every column as updatable,updates will not be sent unless you also check this check box. In Figure 7, note that the primary key column has also been marked as updatable. This is because this column’s value is set by the user, not by the database. If this were an identity column or if its value were set by aninsert trigger, you would not make this column updatable.
Figure 7. The Update Criteria page of the Visual FoxPro View Designer can be used to make remote views updatable.
‡
The Visual FoxPro View Designer is very limited in its ability to modify remote views. If you have remote views with joins, it’s likely that you won’t be able to edit them with the View Designer once you have saved them.
Use the View Designer to create your view and mark the updatable fields if you wish. But when you need to edit the view again, be prepared to do so in code. (See Chapter 5, “Upsizing: Moving from File-Server to Client/Server.”)
The Update Criteria tab of the View Designer simply provides a convenient user interface for setting view and field properties in the DBC. The same properties can be set with DBSETPROP(). The following two lines of code make the CustomerID field updatable and mark it as a primary key:
DBSETPROP("VCustomers.CustomerID", "Field", "KeyField", .T.) DBSETPROP("VCustomers.CustomerID", "Field", "Updatable", .T.)
And this line makes the view updatable:
DBSETPROP("VCustomers", "View", "SendUpdates", .T.)
Setting the KeyField and Updatable properties of fields and the SendUpdates property of the view is critical to updating data. Many a developer has spent a frustrating session trying to figure out why data isn’t being saved—when it’s because the view isn’t configured to doso.
Figure 7 shows two other properties that are important for updatable views. The first one,
SQL WHERE clause includes, sets the view’s WhereType property, which determines how
collisions are detected. The four option buttons in the View Designer correspond to the four numeric values that can be set for the WhereType property. Here’s the code that duplicates the setting shown in Figure 7:
DBSETPROP("VCustomers", "View", "WhereType", 3)
When you set WhereType to 1 (DB_KEY in Foxpro.h), no collisions will be detected unless there are changes to the primary key. The data in the table could be changed by another user prior to making your update, and those changes will be ignored. If your user changes the same column that another user changed, then the change will be wiped out.
If WhereType is set to 2, or DB_KEYANDUPDATABLE, collisions will be detected by looking for changes only in the columns that have been marked as updatable. If another user has changed an updatable column, an error is generated, whether or not your user changed that column, too.
The default setting for WhereType is 3, or DB_KEYANDMODIFIED. With this setting, a collision is detected anytime another user has changed a column that your user is changing. If both users changed the lastname column, an error is generated. But if one user changed lastname and another changed firstname, there is no collision.
The final option for WhereType is 4, or DB_KEYANDTIMESTAMP. SQL Server has a special data type called timestamp. Timestamp is an eight-byte binary type, not a time or datetime. If you have a timestamp column in a table, SQL Server will change the value each time therow is updated. The value is unique in the table and is incremented for each update. With DB_KEYANDTIMESTAMP, an error will be generated if another user has made any change to a row. You can create a timestamp column in a table simply by defining a column calledtimestamp. This CREATE TABLE statement will create a table with two columns, one integer and one timestamp:
CREATE TABLE mytable (mycolumn int, timestamp)
SQL Server will automatically assign a timestamp data type to a column named timestamp. You can also have timestamp columns with other names, in which case you must explicitly define the data type.
The final option group on the Update Criteria page of the View Designer, Update using, sets the view’s UpdateType property. The default is 1, or DB_UPDATE (Update in Place), and is what you will want to use most of the time. To let SQL Server choose the most appropriate action, leave this setting on DB_UPDATE; otherwise, you will force SQL Server to always delete and then insert records, causing extra work and slowing performance. The UpdateType property is set in code like this:
DBSETPROP("VCustomers", "View", "UpdateType", 1)
‡
Properties for a view are set persistently in the DBC with DBSETPROP(). They can also be set temporarily for an open view with CURSORSETPROP().
Buffering
Because you can’t work directly with a table in a client/server database, the data is automatically buffered, unlike with VFP tables, where your changes immediately affect the tables unless you use buffering. As with VFP, there are two ways to buffer a view: row buffering andtable buffering. Row buffering commits changes for one row at a time, while table buffering commits multiple rows.
‡ Unlike with VFP tables, views can only be buffered optimistically.
There is a popular misconception among VFP developers that row buffering should be used to buffer one row at a time and table buffering should be used to buffer multiple rows. While that is true, there’s one additional difference that may override any other considerations when trying to decide which scheme to use: Row buffering causes changes to be committed automatically whenever the record pointer moves, while table buffering requires an explicit call to TABLEUPDATE() to commit changes. Okay, you say, don’t move the record pointer until you’re ready to commit changes. Sometimes it isn’t that easy, as some VFP commands will move the record pointer unintentionally, thus causing the changes to be committed unintentionally. Also, you may want to wrap changes to multiple tables in a transaction. But
if these changes are happening automatically, you won’t be able to combine them into a transaction. Therefore, we never use row buffering, even if we’re working with only one row at a time.
When you open a view, it is row buffered by default. You should change it, either by setting cursor properties in a form’s data environment or by explicitly setting the buffer mode with CURSORSETPROP(). The following code will change a view’s buffer mode from row totable:
CURSORSETPROP("Buffering", 5, "myalias")
It’s preferable to open all of your views and then set table buffering for all open work areas in a method of your form class. Listing 1 shows the code for this method.
Listing 1. This method loops through all open work areas and sets the buffer mode.
PROCEDURE SetBufferMode(tnBufferMode) IF PCOUNT() = 0
*-- Default to table buffering tnBufferMode = 5
ENDIF
LOCAL i, lnCount
LOCAL ARRAY laUsed[1]
*-- Get an array of all open work areas lnCount = AUSED(laUsed)
FOR i = 1 TO lnCount
*-- Set desired buffer mode for each work area
CURSORSETPROP("Buffering", tnBufferMode, laUsed[i,1])
ENDFOR
ENDPROC
Committing and refreshing buffers
When views are table buffered, managing the state of those buffers is entirely up to the developer. Changes can be sent to the back end with TABLEUPDATE(). To restore the data in the buffer to its original state, you can use TABLEREVERT(). The data in the buffer can be refreshed to reflect its current state on the server with REQUERY(). With both TABLEUPDATE() and TABLEREVERT(), you determine how many rows in the buffer are updated or reverted with the first parameter. TRUE updates or reverts all records in the buffer, while FALSE only updates or reverts the current row.
The following line updates the current row in the current work area:
TABLEUPDATE(.F.)
This line reverts all rows in the current work area:
TABLEREVERT(.T.)
The following line updates all rows in the current work area, but stops when the first collision is detected:
TABLEUPDATE(.T.)
The following line updates all rows in the current work area, but continues after a collision is detected and attempts to update all the following rows:
TABLEUPDATE(2)
Note that TABLEUPDATE() can take a logical or a numeric first parameter. Numeric 0 is equivalent to .F., numeric 1 is equivalent to .T.
Collisions occur when two users are attempting to make changes to the same record. The WhereType property of a view or cursor, as described previously, determines how collisions are detected. When SQL Server detects a collision, it generates a non-trappable error. Ifan
automatic commit is made by moving the record pointer, you are not informed of the change. If you commit changes manually with the TABLEUPDATE() function, then the return value of
the function will inform you whether the update was successful. Collisions will only be detected if the second parameter to the TABLEUPDATE() function is FALSE, like this:
TABLEUPDATE(.T., .F.)
If a collision occurred, the TABLEUPDATE() function will return FALSE. If you choose to do so, you can attempt to resolve the collision and then commit the records again, this time using TRUE for the second parameter:
TABLEUPDATE(.T., .T.)
This will force the changes to be committed. Collision handling is covered in greater detail in Chapter 8, “Errors and Debugging.”
TABLEREVERT(), unlike TABLEUPDATE(), does not cause any changes to be
sent to the back end. It simply discards changes you have made and returns the buffer to the state it was in when you executed the query. As with TABLEUPDATE(), you can tell it to revert all rows by passing TRUE as the first parameter, or revert only the current row by passing FALSE.
REQUERY() loads a new record set into the buffer from data on the server. If any uncommitted changes have been made to the buffer, REQUERY() will generate a trappable error, requiring you to call TABLEUPDATE() or TABLEREVERT() before you can REQUERY() the view.You may want to use REQUERY() when a user changes the value of a parameter for a view or when you want to refresh the display with the most current data from the server.
Other view properties
Many properties of views can be set both persistently in the DBC or temporarily for an open cursor. All of these properties are covered in the VFP documentation; some of the more important properties are covered here as well.
FetchAsNeeded and FetchSize
The FetchAsNeeded and FetchSize properties work together to determine how multiple
rows are returned from the server. If a view will return many records, you may want some of them to be displayed in a grid while VFP continues to fetch the rest of the records in the background. Or you may just want to bring down a grid-sized set of rows and only fetch more whenthe user scrolls to the bottom of the grid. If FetchSize is set to a positive number, VFP will return control to your application as soon as SQL Server returns that number of rows. This line of code sets the number of rows returned at once to 100:
DBSETPROP("myview", "view", "FetchSize", 100)
If the view brings down 500 records, control is returned to the program as soon as the first
100 are returned. That means either the next line of code will be executed or control of the user interface will be returned to the user after 100 records. If the FetchAsNeeded property is set to
.T., then no more records will be fetched until the user attempts to scroll to record 101, at which time the next 100 rows are retrieved:
DBSETPROP("myview", "view", "FetchAsNeeded", .T.)
But if FetchAsNeeded is set to .F., then the remaining 400 rows will be fetched in the background. In some cases this works great, as a user can be looking at data right away while more is being fetched in the background. By the time the user gets through all of the first batch of data, there ought to be at least another batch waiting. However, if there is more code to execute, you must be cautious of how these properties are set. In the preceding example, if a following line of code queries another view on the same connection or attempts a SQL pass through command on the same connection, you will get a “connection is busy” error. To prevent such errors, you must set the FetchSize property to –1:
DBSETPROP("myview", "view", "FetchSize", -1)
However, if you set it to –1, then all records must be returned before program execution can continue. This is another good reason to refine your queries so they produce small
result sets.
‡
If you use GENDBC.PRG, which is distributed with Visual FoxPro, to create a program to recreate a DBC, remember that GENDBC improperly generates the line for the FetchSize property twice. If you have to change
that property, be sure you change the correct line, or delete the duplicate line!
MaxRecords
The MaxRecords property determines the maximum number of rows in a result set. The main reason this property exists is to help prevent a non-specific query from sending a large amount of data to the local workstation. By setting this property to a reasonable value, you prevent the users from accidentally filling their hard drives with useless data.
Another good example of using the MaxRecords property would be attempting a TOP n query on a back end that doesn’t support it, such as SQL Server 6.5. This query generates a syntax error on SQL Server 6.5:
CREATE SQL VIEW myview ;
REMOTE CONNECTION Northwind SHARE ;
AS SELECT TOP 1 lastname FROM employees ORDER by 1
But the same thing could be achieved by limiting the number of records returned by the view:
CREATE SQL VIEW myview ;
REMOTE CONNECTION Northwind SHARE ;
AS SELECT lastname FROM employees ORDER by 1
DBSETPROP("myview", "View", "MaxRecords", 1)
FetchMemo
The FetchMemo property determines whether the contents of memo fields are brought down with every record or just when they are needed. When set to .T., like this:
DBSETPROP("myview", "view", "FetchMemo", .T.)
the memo field contents will always be retrieved. This could mean a lot of unnecessary network traffic. When this property is set to .F., the memo contents will only be retrieved when you or the user perform some action to cause a MODIFY MEMO to be issued. These include
explicitly issuing MODIFY MEMO or implicitly doing so in a grid, or by navigating to a record when the memo is bound to an edit field.
Tables
The Tables property contains a list of tables included in the view. This property must be set correctly for TABLEUPDATE() to succeed. Most of the time it works just fine, but occasionally there are problems with it. Sometimes it works fine when you set the property in the DBC, like this:
DBSETPROP("VCustomers", "view", "Tables", "customers")
Other times, the Tables property doesn’t make its way to the cursor when the view is opened, even though the property exists in the DBC. In this case, TABLEUPDATE() returns an error because it can’t find the Tables property. The following line of code fixes the problemreliably:
CURSORSETPROP("Tables", "customers")
Other times, the Tables property makes its way to the cursor and yet VFP still gives an error that no Tables property can be found. Why this happens we don’t know, but we have occasionally even done this to make it work:
CURSORSETPROP("Tables", CURSORGETPROP("Tables"))
For what it’s worth, we’ve encountered this with both local and remote views. Setting the property at run time has always fixed it.
Field properties
Earlier in this chapter, you learned about the KeyField and Updatable properties for view fields. There are a few other important field properties, too. Unlike with connection and view properties, these can only be set persistently in the database. There is no field-level equivalent toSQLSETPROP() or CURSORSETPROP().
DefaultValue
The DefaultValue property allows you to set the default value of a field when a record is added. Some developers believe that default values and rules should exist on the back end so they are under the control of the database. Others believe that default values should be done onthe front end to provide immediate feedback to users rather than waiting for a round trip to the server. Still others believe in doing them in both places. If a default value exists in the database, it should exist in the view, too. That way, your user—and your code—can see it right away.
When you set up a DefaultValue property, it must be delimited in quotes, like this:
DBSETPROP("myview.myfield", "field", "DefaultValue", ".T. ")
If your default value is a string, it must be delimited with two sets of quotes:
DBSETPROP("myview.myfield", "field", "DefaultValue", "'Bob'")
RuleExpression
The RuleExpression property, like the DefaultValue property, can be used to help validate data up front, rather than waiting for a failed update. Rule expressions work like rules for fields in VFP tables, and the entire expression is delimited with quotes. This line will prohibit the postalcode field of the VCustomers view from accepting the value of ‘123’:
DBSETPROP("VCustomers.postalcode", "field", "RuleExpression", ; "postalcode != '123'")
UpdateName
The UpdateName property is very important for multi-table joins. If a column of the same name exists in more than one table in a join, it’s critical that the right field in the view get to the right column in the table. Include both the table and column name in the UpdateName property:
DBSETPROP("VCustomers.postalcode", "field", "UpdateName", ; "customers.postalcode")
DataType
The DataType property is one you may find yourself working with a lot because there isn’t an exact correspondence between data types in VFP and SQL Server. For example, VFP supports both date and datetime data types. SQL Server doesn’t support a date type, but it has twodatetime types: datetime and smalldatetime, which differ by storage size and precision.
When you create a remote view, VFP will automatically convert SQL Server data types to
FoxPro data types as shown in Table 1.
Table 1. The default data types Visual FoxPro uses for SQL Server data types.
SQL type
VFP type
binary, varbinary
Memo
bit
Logical
char, varchar
Character
datetime, smalldatetime
Datetime
decimal
Numeric
float
Double
image
General
int, smallint, tinyint
Integer
money, smallmoney
Currency
numeric
Numeric
sysname
Character
text
Memo
timestamp
Memo
You may use DBSETPROP() to change the data type for any field in a view. For example, if you would rather work with a date type than a datetime type for a birthdate field, you can change it like this:
DBSETPROP("Vemployees.birthdate", "field", "DataType", "D")
You can specify any valid VFP data type just as you would in a VFP CREATE TABLE statement, including length and precision, as long as the type you specify makes sense. You can’t convert a datetime to an integer, for example.
Summary
In this chapter, you learned the basics of creating Visual FoxPro remote views of SQL Server data. You learned about ODBC DSNs, VFP named connections, connection and view properties, and data type conversions. In the next chapter, you’ll learn about making the transitionfrom file-server to client/server applications and how to upsize data from VFP to SQL Server.
Posts
Thank you for this blog!
ReplyDelete