Displaying the Structure of a View

Article
11/14/2006

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.

ODBC data type of remote field	Possible data types in Visual FoxPro cursor
SQL_CHAR SQL_VARCHAR SQL_LONGVARCHAR	Character or Memo¹ (default); also General or Picture
SQL_BINARY SQL_VARBINARY SQL_LONGVARBINARY	Memo (default); also Character, General, or Picture
SQL_DECIMAL SQL_NUMERIC	Numeric or Currency² (default); also Character, Integer, or Double
SQL_BIT	Logical (default); also Character
SQL_TINYINT SQL_SMALLINT SQL_INTEGER	Integer (default); also Character, Numeric, Double, or Currency
SQL_BIGINT	Character (default); also Integer, Numeric, Double, or Currency
SQL_REAL SQL_FLOAT SQL_DOUBLE	Double (default); the number of decimal places is the value of SET DECIMALS in Visual FoxPro; also Character, Integer, Numeric, or Currency
SQL_DATE	Date (default); also Character or DateTime
SQL_TIME	DateTime³ (default); also Character
SQL_TIMESTAMP	DateTime⁴ (default); also Character or Date

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.

Partager via