CREATE CURSOR – SQL Command
Creates a temporary table.
CREATE CURSOR alias_name (fname1 type [(precision [, scale])] [NULL | NOT NULL] [CHECK lExpression [ERROR cMessageText]] [DEFAULT eExpression] [UNIQUE] [NOCPTRANS] [, fname2 ...]) | FROM ARRAY ArrayName
- Specifies the name of the temporary table to create. alias_name can be a name expression.
- Specifies the name of a field in the temporary table. Each fname can be a name expression.
- Specifies a single letter indicating the data type for the field.
- Specifies the width of the field specified with fname. Some data types require that you specify a precision.
- Specifies the number of decimal places for the specified data type. Some data types require that you specify a scale.
The following table shows what type, precision, and scale can be:
FieldType nFieldWidth nPrecision Description C n – Character field of width n D – – Date T – – Datetime N n d Numeric field of width n with d decimal places F n d Floating numeric field of width n with d decimal places I – – Integer B – d Double Y – – Currency L – – Logical M – – Memo G – – General P – – Picture
nFieldWidth and nPrecision are ignored for D, T, Y, L, M, G, and P types. nPrecision defaults to zero (no decimal places) if nPrecision isn't included for the N, F, or B types.
- Allows null values in the field.
- NOT NULL
- Prevents null values in the field.
If you omit NULL and NOT NULL, the current setting of SET NULL determines if null values are allowed in the field. However, if you omit NULL and NOT NULL and include the PRIMARY KEY or UNIQUE clause, the current setting of SET NULL is ignored and the field defaults to NOT NULL.
- CHECK lExpression
- Specifies a validation rule for the field. lExpression can be a user-defined function.
- ERROR cMessageText
- Specifies the error message Visual FoxPro displays when the field validation rule generates an error. The message is displayed only when data is changed within a Browse window or Edit window.
- DEFAULT eExpression
- Specifies a default value for the field. The data type of eExpression must be the same as the field's data type.
- Creates a candidate index for the field. The candidate index tag has the same name as the field.
Note Candidate indexes (created by including the UNIQUE option) are not the same as indexes created with the UNIQUE option in the INDEX command. An index created with the UNIQUE option in the INDEX command allows duplicate index keys; candidate indexes do not allow duplicate index keys.
Null values and duplicate records are not permitted in a field used for a candidate index. However, Microsoft Visual FoxPro will not generate an error if you create a candidate index for a field that supports null values. Visual FoxPro will generate an error if you attempt to enter a null or duplicate value into a field used for a candidate index.
- Prevents translation to a different code page for character and memo fields. If the cursor is converted to another code page, the fields for which NOCPTRANS has been specified are not translated. NOCPTRANS can be specified only for character and memo fields.
The following example creates a cursor named
MYCURSORcontaining two character fields and two memo fields. The second character field
CHAR2and the second memo field
MEMO2include NOCPTRANS to prevent translation.
CREATE CURSOR mycursor (char1 C(10), char2 C(10) NOCPTRANS,; memo1 M, memo2 M NOCPTRANS)
- FROM ARRAY ArrayName
- Specifies the name of an existing array whose contents are the name, type, precision, and scale for each field in the temporary table. See AFIELDS( ) for the proper format for the contents of the array.
CREATE CURSOR creates a temporary table that exists only until it is closed. A temporary table created with CREATE CURSOR can be manipulated like any other table — it can be browsed and indexed, and you can append and modify records.
The temporary table is opened in the lowest available work area, and can be accessed by its alias. Each field in the temporary table is defined with a name, type, precision, and scale. These definitions can be obtained from the command itself or from an array. The temporary table is opened exclusively regardless of the setting of SET EXCLUSIVE.
The following example creates a cursor with the alias
employee. A blank record is appended, filled, and displayed with the BROWSE command.
CLOSE DATABASES CLEAR CREATE CURSOR employee ; (EmpID N(5), Name C(20), Address C(30), City C(30), ; PostalCode C(10), OfficeNo C(8) NULL, Specialty M) DISPLAY STRUCTURE WAIT WINDOW "Press a key to add a record." INSERT INTO employee (EmpId, Name, Address, City, PostalCode, ; OfficeNo, Specialty); VALUES (1002, "Dr. Bonnie Doren", "University of Oregon", "Eugene", ; "98403", "", "Secondary Special Education") BROWSE * At this point you could copy this record to a permanent table CLOSE ALL && Once the cursor is closed, all data is flushed && from memory CLEAR