Share via

CREATE CURSOR – SQL Command

  • Article

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

Parameters

  • 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 Index Information Display and Setting a Primary or Candidate Index.

    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.

Example

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

See Also

AFIELDS( ) | CREATE | CREATE QUERY | CREATE TABLE - SQL | INSERT - SQL | MODIFY QUERY | SELECT - SQL