CREATE CURSOR

This command lets you create a temporary table. Cursors created this way are read-write (like those created for views, but unlike those created by SELECT-SQL without the READWRITE clause). Cursors are not part of a database, but can have several features normally found only in database-contained tables.

Usage

CREATE CURSOR Alias
       ( Fieldname1 Fieldtype1 [( nSize1 [ , nDecimals1 ] )
          [ NULL | NOT NULL ]
          [ CHECK lFieldRule1 [ ERROR cRuleText1 ] ]
          [ DEFAULT eDefault1 ]
          [ UNIQUE ]
          [ NOCPTRANS ] ]
          [ , Fieldname2 ... ] )
        | FROM ARRAY aFieldArray

Cursors are really handy when you just need to work with some data temporarily, then throw it out. You don’t have to worry about unique names or about erasing files. Create the cursor, populate it, use it, and then close it, at which point it goes away as if it had never been. You can change the data in cursors created with CREATE CURSOR, so they’re good for tasks like grabbing a set of records and letting a user mark those to be printed. The original data is left untouched, but you get what you need.

The clauses of CREATE CURSOR are a subset of those for CREATE TABLE and are explained in detail there. (CREATE TABLE has some other clauses not mirrored in CREATE CURSOR.)

There are a few interesting things about this command. First, the name you give the cursor is an alias (like in the ALIAS clause of USE). There’s no reason to create a unique name for it. In fact, you can’t use a name generated with SYS(3) because, starting with FoxPro 2.5, aliases beginning with digits don’t work. CREATE CURSOR does support long names, both for the alias and for the field names.

Like other cursors, the ones you create with this command disappear when you close them.

Most interesting about cursors in Visual FoxPro is that they accept some of the database-related clauses. We’d expect CHECK, ERROR and DEFAULT to be restricted to tables that are part of a DBC and, in fact, when working with tables rather than cursors, they are. But there’s some special mechanism that lets you use these clauses with cursors.

In VFP 3.0 and 3.0b, when you use the UNIQUE clause to create a compound index (CDX) for a cursor, closing the cursor doesn't clean up the CDX. Instead, the CDX file is left behind in the directory specified for temporary files. You can identify it there by its all-digit name followed by a CDX extension. This bug is fixed in later versions.

We’re particularly fond of the FROM ARRAY form of CREATE CURSOR and, in programs, are more likely to use that than the form listing all the fields explicitly. One handy trick is to use AFIELDS() to create an array with field information from an existing table or cursor, modify the array as needed, and then CREATE CURSOR (or TABLE) FROM ARRAY.

Beware of the number of fields in the cursor you are trying to create. CREATE CURSOR FROM ARRAY crashes Visual FoxPro 3.x with an Invalid Page Fault if there are more than 316 rows (that is, fields to be created), and generates the error "Function argument value, type or count is invalid" for 313 to 315 fields listed. Weirdly enough, it can create up to 312 fields. VFP 5.0 crashes with an Invalid Page Fault at 256 fields. VFP 6 finally gets it right with either "Array dimensions are invalid" for 5-column arrays or "Too many columns" for 5+ column arrays if the array contains 256 or more rows. VFP 7 just returns "Array dimensions are invalid." The bottom line is that no version of FoxPro supports any more than 255 fields. We wouldn't count on creating these nonstandard cursors.

If any of the fields supports NULL, you are limited to 254 columns because of the hidden "_NullFlags" column (see "Nulls" in "DBF, FPT, CDX, DBC—Hike!")

Once you CREATE a cursor, you can pretty much treat it like a table, as long as you’re careful not to close it. This means that you can use table and row buffering with cursors—an advantage in designing forms, since you needn’t worry about whether the actual data resides in a table, a view or a cursor.

Interestingly, CURSORGETPROP() doesn’t distinguish between CREATEd cursors and tables. In both cases, it returns 3 (“table”) for SourceType. You can tell if you’re working on a cursor by checking DBF() or CURSORGETPROP(“SourceName”)—for a cursor, you’ll get a temporary file name composed of digits and a TMP extension.

Example

CREATE CURSOR Temp (CharFld C(10) UNIQUE, ;
                    NumFld N(3) CHECK NumFld>25, ;
                    DateFld D DEFAULT DATE())

* Create a cursor identical to the Customer table.
USE Customer
=AFIELDS(aFieldList)
CREATE CURSOR NewCust FROM ARRAY aFieldList

See Also

AFields(), Create, Create Table, CursorGetProp(), Index, Select-SQL, Use