Data Features Enhancements

Article
11/14/2006

Visual FoxPro contains the following additions and improvements to its data features:

Remote data connectivity using CursorAdapter class
Creating, subclassing, and specifying a DataEnvironment class
Automatically incrementing field values
Support for collating sequences
Implicit data conversion for SQL SELECT...UNION command
Inserting rows from a SQL SELECT command

Remote Data Connectivity Using CursorAdapter Class

You can work with a wider range of data sources using the new CursorAdapter class. The CursorAdapter class offers the following capabilities:

Support for native, Open Database Connectivity (ODBC), ActiveX Data Object (ADO), or Extensible Markup Language (XML) data source types
Use different data sources dynamically.
Use either the data source of the CursorAdapter object or the data environment.
Share data sources within the limits of the data source technology.
Use the CursorAdapter class as a standalone class without an associated data environment.
Optionally define the structure of a cursor associated with a CursorAdapter object.
Render data from the data source into a Visual FoxPro cursor automatically and properly, based on the data source type.
Control loading of data into the cursor associated with a CursorAdapter object.
Control how data is added, updated, and deleted using CursorAdapter properties and methods.
Add CursorAdapter objects to containers other than the data environment, such as forms, form sets, and other containers.

To support the new CursorAdapter functionality, Visual FoxPro includes the following enhancements:

Adds definition and subclassing capabilities by including the CursorAdapter class in the Class Designer and providing the CursorAdapter class for the cBaseClassName parameter in the CREATE CLASS and MODIFY CLASS commands.
Provides the new GETCURSORADAPTER( ) function for returning an object reference to a CursorAdapter object at run time.
Updates the TABLEUPDATE() function so that it recognizes and handles CursorAdapter objects.
Opens internal cursors as buffered cursors and sets the buffering default to Optimistic Row Buffering.
Includes the new CursorAdapter Builder.

For more information, see Data Access Management Using CursorAdapters, CursorAdapter Class, GETCURSORADAPTER( ) Function, and CursorAdapter Builder.

Creating, Subclassing, and Specifying a DataEnvironment Class

You can now define and create a subclass of the DataEnvironment class. When you use the Visual FoxPro IDE or the CREATE CLASS command to create a new class, Visual FoxPro provides the DataEnvironment class in the Based On list in the New Class dialog box. You can also specify the DataEnvironment class as a base class for the cBaseClassName parameter in the CREATE CLASS and MODIFY CLASS commands.

On a form, you can save the DataEnvironment class as a visual class library (.vcx) by selecting the form, choosing Save As Class from the File menu, and selecting DataEnvironment. All DataEnvironment properties, methods, and events are available as appropriate in the Properties window after opening the Class Designer.

You can specify and load an external DataEnvironment class in a form (.scx) at design or run time. You can set the form's DEClass and DEClassLibrary properties to specify an external DataEnvironment class and the class library that contains it so that when the form instantiates, the specified DataEnvironment class loads automatically.

For more information, see DataEnvironment Object, Form Object, DEClass Property and DEClassLibrary Property.

Automatically Incrementing Field Values

You can now increment field values automatically for both DBC tables and free tables using the new autoincrementing feature in Visual FoxPro. You can use the AUTOINC clause in the SQL CREATE TABLE and SQL ALTER TABLE commands to specify values for NEXTVALUE and STEP. You can also select the Integer (AutoInc) data type for a field and set values for the Next Value and Step fields in the Fields tab in the Table Designer. For more information, see CREATE TABLE - SQL Command, ALTER TABLE - SQL Command, Fields Tab, Table Designer, Autoincrementing Field Values in Tables, and Setting Autoincrementing Field Values.

The AFIELDS( ) function now returns 18 columns instead of 16 due to new support for autoincrementing field values. Column numbers 17 and 18 now specify the NextValue and Step fields, which contain values of Numeric (integer) type. For more information, see AFIELDS( ) Function.

The COPY STRUCTURE EXTENDED command adds the numeric FIELD_NEXT and FIELD_STEP fields to support autoincrementing. For more information, see COPY STRUCTURE EXTENDED Command.

Visual FoxPro modifies the table header when you turn on or add autoincrementing for field values. For more information, see Table File Structure.

You can also specify whether attempts to update values in a field using autoincrementing generate an error. For more information, see SET AUTOINCERROR Command or the AutoIncError property in the CURSORSETPROP( ) Function.

Support for Collating Sequences

In previous versions, you needed to use the SET COLLATE TO command to specify a collating sequence other than the default when creating an index or index tag. You can now specify a different collating sequence when you create or modify a table using the Indexes tab in the Table Designer and when you use the COLLATE clause and its parameter with the SQL CREATE TABLE, ALTER TABLE, and INDEX commands. For more information, see Indexes Tab, Table Designer, CREATE TABLE - SQL Command, ALTER TABLE - SQL Command, and INDEX Command.

Implicit Data Conversion for SQL SELECT...UNION Command

The SQL SELECT command with the UNION keyword now performs implicit data conversion for data types that support implicit conversions. For example, UNION operations with columns of different lengths or that contained different types previously failed unless you explicitly matched the column sizes using a conversion function. Suppose you have the following code:

CREATE CURSOR t1 (f1 c(10))
INSERT INTO t1 VALUES ('1111111111')
CREATE CURSOR t2 (f1 c(2))
INSERT INTO t2 VALUES ('11')
SELECT * FROM t1 INTO CURSOR t3 UNION SELECT * FROM t2 && Fails.

This code failed unless you explicitly match the column sizes as shown:

SELECT * FROM t1 INTO CURSOR t3 UNION SELECT SUBSTR(f1,1,2) FROM t2

You no longer need to specify or use explicit conversion functions for data types that support implicit data conversion. For more information about data type conversion and precedence, see Data Type Conversion and Precedence in SELECT - SQL Command.

Inserting Rows from a SQL SELECT Command

You can now insert rows from a SQL SELECT command when you use the SQL INSERT command. You can copy the contents of selected columns or all columns in a row. However, you must make sure the data you copy is compatible with the columns in the rows that you copy to. For more information, see the INSERT - SQL Command.

You can retrieve the number of records processed by the most recently executed table command using the _TALLY system variable, which is updated with the number of rows inserted. For more information, see _TALLY System Variable.