Knowledge Base

PRB: Cannot Modify Table Structure After Using SQL SELECT

Article ID: 133470

Article Last Modified on 2/15/2000

APPLIES TO

This article was previously published under Q133470

SYMPTOMS

After performing an SQL SELECT statement on a table, the table structure becomes read-only, so you can't modify it.

CAUSE

Initially the SELECT statement is processed to see if the result set can be obtained from the original table with the application of a filter. If it can, a temporary cursor is not created on disk; instead, a filtered view of the original table is created. You will see a cursor object in the View window with the name QUERY, this is an alias of the source table.

Because you are looking at a filtered view of the original table, the MODIFY STRUCTURE command won't work; the underlying table structure of the result set has been temporarily frozen.

RESOLUTION

To execute an immediate MODIFY STRUCTURE command on the source table, you must first close the QUERY cursor. Alternatively, you could avoid this by editing the query to involve complexities that would force a temporary, physically distinct cursor to be created.

STATUS

This behavior is by design.

MORE INFORMATION

Code to Reproduce Behavior

The following test code demonstrates this principle with a very simple single-table query: 
     CREATE TABLE TEST (Name C(20))         && Create temporary table
   CLOSE ALL                              && Close all tables
   USE TEST EXCLUSIVE                     && Re-use the table
   INSERT INTO TEST VALUES ("Bob")        && Populate with some data
   SELECT * FROM TEST INTO CURSOR QUERY   && Execute query
   SELECT TEST                            && Re-select the table
   MODIFY STRUCTURE                       && Command fails.
The following example shows a more complex query. Consequently, Visual FoxPro creates a cursor object named QUERY on the hard disk instead of creating a filtered alias. 
   USE SYS(2004)+"\SAMPLES\DATA\CUSTOMER" IN 0
   SELECT * FROM CUSTOMER WHERE REGION="OR" INTO CURSOR QUERY
   SELECT CUSTOMER
   MODIFY STRUCTURE

Additional query words: VFoxWin

Keywords: KB133470


Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.