FIX: Table Can Not Be USED After REPLACE of Memo in TRANSACTION

MORE INFORMATION

Visual FoxPro tables consist of one or more files. Each file has the same name, but with different extensions. These are:

   File      Extension   Comment
   -----------------------------------------------------------------------

   Table     .dbf        Present for all tables.
   Memo      .fpt        Present only if table has Memo or General fields.
   Index     .cdx        Present only if table has index tags.
				

When a table is opened (with a USE command), the .dbf file opens. The .fpt and .cdx files are also opened if they exist, and they must be open for the USE command to succeed.

When a replace is made on a Memo field, at the time the replace is made, the memo file (.fpt) header is locked. Normally, this lock is made and released instantaneously, but the transaction causes the lock to be held until the transaction ends.

Because the USE command can not open the memo file if its header is locked, the scrolling message appears until the lock is removed, for example, when the transaction ends.

Steps to Reproduce Behavior

1. Run the following code from a program (.prg) file:

         SET EXCLUSIVE OFF
         SET MULTILOCKS ON
         SET REPROCESS TO 5
   
         IF NOT FILE('MUTest.DBC')
            WAIT WINDOW "Creating test data..." NOWAIT
            CREATE DATABASE MUTest
            CREATE TABLE MUTable (IntFld I, MemoFld M)
            APPEND BLANK
            CLOSE DATABASE
         ENDIF
   
         WAIT WINDOW "Opening database..." NOWAIT
         OPEN DATA MUTest SHARE
   
         WAIT WINDOW "Opening table..." NOWAIT
         USE MUTable IN 0 SHARE
   
         WAIT CLEAR
         SELECT MUTable
         LOCATE
   
         BEGIN TRANSACTION
            REPLACE MemoFld WITH sys(3)
            =MESSAGEBOX('Start another instance of Visual FoxPro and run ;
            this program again. Observe that the USE statement causes ;
            the "Record not available...please wait." scrolling message    ;
            to appear when attempting to open the table.', 16)
   
         ROLLBACK
         CLOSE DATABASE
   							

2. Start another instance of Visual FoxPro and change to the same default directory that the first instance used.
3. Run the program above in the second instance.

RESULTS:

• The second instance receives a scrolling "Record not available...please wait" message on the USE command.
• If the REPLACE is made to the Integer field rather than the Memo field, the scrolling message will not occur and the table opens.
• Buffering helps if the REPLACE is made before the BEGIN TRANS.
• To clear the scrolling message in the second instance, click OK in the message box in the first instance, then click OK in the message box of the second instance.

NOTES:

• In this case, we have intentionally added a message box to make the transaction last longer, which is what we want to avoid, because the lengthening of the transaction makes the behavior more likely to occur.
• It is much preferable to use buffering and issue your REPLACE before starting the transaction. Use the preceding steps with the following code to demonstrate:

        SET EXCLUSIVE OFF
        SET MULTILOCKS ON
        SET REPROCESS TO 5
  
        IF NOT FILE('MUTest.DBC')
           WAIT WINDOW "Creating test data..." NOWAIT
           CREATE DATABASE MUTest
           CREATE TABLE MUTable (IntFld I, MemoFld M)
           APPEND BLANK
           CLOSE DATABASE
        ENDIF
  
        WAIT WINDOW "Opening database..." NOWAIT
        OPEN DATA MUTest SHARE
  
        WAIT WINDOW "Opening table..." NOWAIT
        USE MUTable IN 0 SHARE
  
        WAIT CLEAR
        SELECT MUTable
        =CURSORSETPROP('Buffering', 5)
        LOCATE
  
        REPLACE MemoFld WITH sys(3)
  
        BEGIN TRANSACTION
           =TABLEUPDATE(.T.)
           =MESSAGEBOX('Start another instance of Visual FoxPro and run ;
           this program again. The second instance will open the table ;
           without the scrolling message', 16)
  
        ROLLBACK
        CLOSE DATABASE
  							

In this case, the scrolling message will not occur and the table opens in the second instance without problem.