Displaying the Structure of a View

You can open and display just the structure of a view with the NODATA clause of the USE command. This option is particularly useful when you want to look at the structure of a remote view without waiting to download data.

To open a view without data

• Access the view programmatically with the USE command and the NODATA clause.

The following code displays customer_remote_view without data in a Browse window:

OPEN DATABASE testdata
USE customer_remote_view NODATA in 0
BROWSE

Using a view with the NODATA clause always opens a new view cursor. The NODATA clause is the fastest way to get the view's structure because it creates the smallest cursor possible on the remote data source. When you use the NODATA clause, Visual FoxPro creates a WHERE clause for the view that always returns a false value. Because no records on the data source can meet the WHERE clause condition, no rows are selected into the remote data source's cursor. Your view is retrieved quickly because you're not waiting for the remote data source to build a potentially large cursor.

Tip   Using the NODATA clause is more efficient than using a MaxRecords property setting of 0 on your view or cursor. When you use the MaxRecords property, you must wait while the remote data source builds a cursor for the view containing all data rows that meet the view's normal WHERE clause conditions. Rows from the full remote view cursor are then downloaded according to the setting of the MaxRecords property.

Creating an Index on a View

You can create local indexes on a view, just as on a table, using the INDEX ON command. Unlike indexes you build on a table, local indexes you create on a view are not stored persistently: they vanish when you close the view.

Tip   Consider the size of your view's result set when deciding whether to create a local index on a view. Indexing a large result set can take a long time, and slow down performance of your view.

For more information on creating indexes, see Working with Tables, or see INDEX Command.

Creating Temporary Relationships on Views

You can create temporary relationships between view indexes or between view indexes and table indexes with the SET RELATION command.

For better performance, when you use the SET RELATION command to relate a view and a table, make the view the parent and the table the child in the relationship. Making the table the child is more efficient because the structural index of the table is constantly maintained, quickly accessed, and can be used by the data environment to order the records. The index on the view must be rebuilt each time the view is activated and takes more time than the index on the table. An index on a view is not part of the view definition; so, if you use a data environment, the view cannot be the child because the index on the child has to exist as part of the definition, which views don't support.

Setting View and Connection Properties

When you create a view, the view inherits property settings, such as UpdateType and UseMemoSize, from the environment cursor, or cursor 0 of the current session. You can change these default property settings by using the CURSORSETPROP( ) function with 0 as the cursor number. After the view has been created and is stored in a database, you can change view properties with the DBSETPROP( ) function. The changes you make to view properties in a database are stored persistently in the database.

When you use a view, the property settings stored for the view in the database are inherited by the active view cursor. You can change these properties on the active cursor using the CURSORSETPROP( ) function for the view cursor. Changes you make with the CURSORSETPROP( ) function are temporary. The temporary settings for the active view disappear when you close the view; the temporary settings for cursor 0 go away when you close the Visual FoxPro session.

Connections inherit properties in a similar fashion. Default properties for connection 0 are inherited when you create and store a named connection in a database. You can change these default property settings for connection 0 with the SQLSETPROP( ) function. After the connection has been created and is stored in a database, you can change connection properties with the DBSETPROP( ) function. When you use a connection, the property settings stored for the connection in the database are inherited by the active connection. You can change these properties on the active connection using the SQLSETPROP( ) function for the connection handle.

Both views and connections can use a named ODBC data source. If you use an ODBC data source in a view, the connection inherits properties from the session defaults.

The following diagram illustrates property inheritance for views and connections. The gray lines represent the flow of property inheritance; the black lines represent Visual FoxPro commands.

View and Connection properties and their inheritance

Changing Default Data Types When Downloading Remote Views

When you create a view, the DataType property for all fields in the view is set to a default value. The value is the data type letter (D, G, I, L, M, P, T, Y) for fixed-length data types and the letter followed by precision and scale parameters in parentheses (B(d), C(n), N(n,d)) for variable length types. This property is read-only for local views. For a list of default data types, see Downloading and Uploading Remote View Data in Implementing a Client/Server Application.

You can modify the setting of the DataType property for the remote view field with the DBSETPROP( ) function as shown in this table.

1. If the ODBC field width is less than the value of the cursor property UseMemoSize, it becomes a Character field in the Visual FoxPro cursor; otherwise, it becomes a Memo field.

2. If the server field is a money data type, it becomes a Currency data type in Visual FoxPro.

3. The day defaults to 1/1/1900.

4. If the value in the SQL_TIMESTAMP field contains fractions of seconds, the fractions are truncated when the value is converted to a Visual FoxPro DateTime data type.

Using the DataType Property

You can use the DataType property to choose a different data type than the default. For example, you might want to download a server timestamp field to Visual FoxPro, but the default data type mapping into a Visual FoxPro DateTime field would truncate any fractions of seconds stored in the server timestamp. You might use the DataType property to map the remote timestamp field into a Visual FoxPro character field to preserve the fractions of seconds.

Closing a View's Base Tables

The local base tables opened automatically when you use a view are not automatically closed when you close a view; you must explicitly close them. This is consistent with the SELECT - SQL command.

See Also

Opening Multiple Instances of a View | Updating Data in a View | Creating Queries | USE | SET RELATION | Updating Multiple Tables in a View | Updating Data