How to Force SELECT-SQL to Create Temp File for Cursor

ID: Q109584

2.50 2.50a 2.50b 3.00| 2.50 2.50a 2.50b

WINDOWS              | MS-DOS

The information in this article applies to:

Microsoft Visual FoxPro for Windows, version 3.0
Microsoft FoxPro for Windows, versions 2.5, 2.5a, 2.5b
Microsoft FoxPro for MS-DOS, versions 2.5, 2.5a, 2.5b

SUMMARY

When the INTO CURSOR clause of the SELECT-SQL command is used to direct output to a cursor, this cursor can take one of two forms. It can be a cursor that has its own temporary file on the disk, or it can be a filtered subset of the table that the SELECT-SQL statement was based on.

A cursor can be created as a filtered view of the original table if:

There was only one table involved in the SELECT statement.
-and-
There were no calculated fields in the field list.

You can force FoxPro to create the cursor with a temporary file, instead of the filtered one.

The existence of a cursor on the hard disk provides the following benefits:

You can create a single index.
NOTE: You cannot create multiple indexes on the same temp file.
- You can use the SELECT statement to select from the cursor.

MORE INFORMATION

You can force the creation of a cursor within an SELECT-SQL statement in FoxPro. To do this, use the SELECT statement to select column expressions in the field list that do not exist in the database.

In a multiple-table SELECT statement, this is accomplished when fields from both tables are selected. Since the field list does not match a table directly, FoxPro will not create a mask of the database but instead will create a temporary file on disk. If only one table exists, or if you do not want to select fields from more than one .DBF file, use the following code.

   * The following lines create tables for use in the SELECT-SQL below

   CREATE TABLE customer ;
      ( cno C(5), company C(35), contact C(20), ;
      address C(30), city C(15), state C(2), zip C(5), ;
      phone C(12), ono C(1), ytdpurch N(8,2), lat N(7,4), ;
      long N(8,4) )

   INSERT INTO customer (cno, company, contact, address, ;
      city, state, zip, ;
      phone, ono, ytdpurch, lat, long) ;
      VALUES ('a123', '1st Company', 'No Name', 'One Microsoft Way', ;
      'Redmond', 'WA', '98052', '1206123456', '1', ;
      1000.99, 100.999, 100.999)

   * The .T. in the field list causes FoxPro to create a cursor.

   SELECT Customer.cno, Customer.ytdpurch, .T. ;
    FROM Customer INTO CURSOR Query

To verify the existence of a temporary file for a cursor, issue the following command:

   ".TMP"$DBF('<alias>')

Additional reference words: VFoxWin 3.00 FoxDos FoxWin 2.50 2.50a 2.50b index on cursor KBCategory: KBSubcategory: FxprgSql

Keywords          : kbcode FxprgSql 
Version           : 2.50 2.50a 2.50b 3.00| 2.50 2.50
Platform          : MS-DOS WINDOWS

Last Reviewed: May 22, 1998