CREATE CURSOR - SQL Command

Article
07/09/2007

Creates a temporary table that exists until closed.

CREATE CURSOR alias_name 
...[CODEPAGE=nCodePage]
   (fname1 cFieldType [(nFieldWidth [, nPrecision])] [NULL | NOT NULL] 
   [CHECK lExpression [ERROR cMessageText]] 
   [AUTOINC [NEXTVALUE NextValue [STEP StepValue]]]
   [DEFAULT eExpression] [UNIQUE [COLLATE cCollateSequence]]
   [NOCPTRANS] [, fname2 ...]) 
   | FROM ARRAY ArrayName

Parameters

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

nCodePage
Specifies the code page to use. For a list of code pages, see Code Pages Supported by Visual FoxPro.

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

cFieldType
Specifies a single letter or a long name indicating the field's data type.

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

nPrecision
Specifies the number of decimal places for the specified data type. Some data types require that you specify a value for nPrecision.

The following table shows the possible values for cFieldType, nFieldWidth, and nPrecision.

cFieldType	nFieldWidth	nPrecision	Description
W, Blob	-	-	Blob
C, Char, Character	n	–	Character field of width n
Y, Currency	–	–	Currency
D, Date	–	–	Date
T, DateTime	–	–	DateTime
B, Double	–	d	Double
G, General	–	–	General
I, Int, Integer	–	–	Integer
L, Logical	–	–	Logical
M, Memo	–	–	Memo
N, Num, Numeric	n	d	Numeric field of width n with d decimal places
F, Float	n	d	Floating Numeric field of width n with d decimal places
Q, Varbinary	n	-	Varbinary field of width n
V, Varchar	n	-	Varchar field of width n

The nFieldWidth and nPrecision parameters are ignored for W, Y, D, T, G, I, L, and M types. If nPrecision is not included for the N, F, or B types, the nPrecision parameter defaults to zero (no decimal places).

NULL | NOT NULL
Specifies whether null values are allowed in the field. NULL permits null values, while NOTNULL does not allow null values.

CHECK lExpression
Specifies a validation rule for the field. The lExpression parameter must evaluate to a logical expression and can be a user-defined function or a stored procedure.

ERROR cMessageText
Specifies an error message. Visual FoxPro displays this message when the validation rule specified in the CHECK clause generates an error. The message displays only when data is changed within a Browse window or Edit window.

AUTOINC [NEXTVALUE NextValue [STEP StepValue]]
Enables autoincrementing for the field.

NextValue specifies the start value and can be a positive or a negative integer value ranging from 2,147,483,647 to -2,147,483,647. The default value is 1. You can set NextValue using the Next Value box on the Fields tab in the Table Designer.

StepValue specifies the increment value for the field and can be a positive, nonzero integer value ranging from 1 to 255. The default value is 1. You can set StepValue using the Step box on the Fields tab in the Table Designer.

Autoincrementing values cannot be null (.NULL.).

Note

Tables containing automatically incrementing field values append table-buffered records approximately 35% slower than tables without automatically incrementing field values, which might affect performance. When using table buffering, the table header is locked when the record is appended.

DEFAULT eExpression
Specifies a default value for the field. The data type of eExpression must be the same as the specified field's data type.

If you use the AUTOINC clause to turn on autoincrementing for a field and specify a default value, Visual FoxPro stores the default value in the table but does not use it. Visual FoxPro uses the default value if you use the ALTER TABLE - SQL command to remove autoincrementing for the field.

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 Visual FoxPro Index Types.

COLLATE cCollateSequence
Specifies a collation sequence other than the default setting, MACHINE. The cCollateSequence parameter must be a valid Visual FoxPro collation sequence. For more information about setting collation sequences, see Optimizing International Applications and SET COLLATE Command.

NOCPTRANS
Prevents translation to a different code page for Character, Memo, and Varchar fields. You can specify NOCPTRANS only for Character, Memo, and Varchar fields.

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. You can use the FROM ARRAY clause instead of specifying individual fields. For the proper format of the contents of the array, see AFIELDS( ) Function.

Autoincrementing is turned on when the step value is greater than 0.

Remarks

You can manipulate a temporary table created with CREATE CURSOR like any other table — you can browse and index it, and you can append and modify records. The temporary table opens in the lowest numbered available (unused) 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 opens exclusively, regardless of the setting of SET EXCLUSIVE.

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, Visual FoxPro disregards the current setting of SET NULL, and the field defaults to NOT NULL.

Null values and duplicate records are not permitted in a field used for a candidate index. However, Visual FoxPro does not generate an error if you create a candidate index for a field that supports null values. Visual FoxPro generates an error if you attempt to enter a null or duplicate value into a field used for a candidate index.

Note

Candidate indexes, created by including the UNIQUE option (provided for ANSI compatibility) in CREATE TABLE – SQL or ALTER TABLE – SQL commands, are not the same as indexes created in the INDEX command with the UNIQUE option. An index created in the INDEX command using the UNIQUE option allows duplicate index keys; candidate indexes do not allow duplicate index keys. For more information about the UNIQUE option in the INDEX command, see INDEX Command.

If the table is converted to another code page, the fields for which NOCPTRANS has been specified are not translated.

CREATE CURSOR...FROM ARRAY recognizes all the fields available in the AFIELDS( ) function and turns on autoincrementing in the cursor when the Step value is greater than zero.

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 Character(20), Address C(30), City C(30), ;
  PostalCode C(10), OfficeNo C(8) NULL, Specialty Memo)
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 can copy this record to a permanent table.
CLOSE ALL   && Once the cursor closes, all data is flushed from memory.
CLEAR

The following example uses NOCPTRANS to prevent translation to a different code page. The example creates a cursor named "mycursor" that contains 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)

CREATE CURSOR - SQL Command

Parameters

Remarks

Example

See Also

Reference

Other Resources

Additional resources