CREATE CURSOR – SQL Command

Example   See Also

Creates a temporary table.

Syntax

CREATE CURSOR alias_name
  (fname1 type [(precision [, scale])
     [NULL | NOT NULL]
     [CHECK lExpression [ERROR cMessageText]]
     [DEFAULT eExpression]
     [UNIQUE]
     [NOCPTRANS]]
  [, fname2 ...])
  | FROM ARRAY ArrayName

Arguments

alias_name

Specifies the name of the temporary table to create. alias_name can be a name expression.

fname

Specifies the name of a field in the temporary table. Each fname can be a name expression.

type

Specifies a single letter indicating the data type for the field.

precision

Specifies the width of the field specified with fname. Some data types require that you specify a precision.

scale

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.

NULL

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.

UNIQUE

Creates a candidate index for the field. The candidate index tag has the same name as the field.

For more information about candidate indexes, see "Using Indexes" in Chapter 7, Working with Tables, in the Programmer's Guide.

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.

NOCPTRANS

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 MYCURSOR containing two character fields and two memo fields. The second character field CHAR2 and the second memo field MEMO2 include 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.

Remarks

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.