vfp 9 Memo Field oddness

Question

vfp 9 Memo Field oddness

BobInVB (Programmer)

(OP)

14 Mar 11 15:16

VFP 9 SP2 on a Win7 platform

When two tables are combined into one using a SQL SELECT UNION command the memo field becomes corrupted (FPT missing or invalid message when accessing the table) if more than 16 records with data in one of the memo fields when one the the tables is empty.  Has anyone seen anything like this?

I suspect it has something to do with how the SQL-SELECT UNION operates to combine the tables.

Here is some code to reproduce:

CREATE TABLE test1 FREE ;
( nPk I, ;
  M1  M, ;
  M2  M, ;
  M3  M, ;
  M4  M, ;
  M5  M, ;
  M6  M, ;
  M7  M, ;
  M8  M, ;
  M9  M, ;
  M10 M, ;
  M11 M ;
)

FOR x = 1 TO 20
    APPEND BLANK
ENDFOR

REPLACE ALL nPK WITH RECNO(), M1 WITH ALLTRIM(STR(RECNO()))

* Create an empty table
SELECT * FROM test1 WHERE nPk = -8765 INTO TABLE test2

SELECT test1.*;
    FROM test1;
    UNION ALL SELECT test2.*;
    FROM test2;
INTO TABLE test3.DBF

* Aborts here with the FPT is missing or invalid message
ALTER TABLE test3 ;
  ADD COLUMN cStatus C(10)

Repeat the process but load M1 in first 16 records of test1 and all works.

Any ideas are appreciated...thanks

Dan Freeman (Programmer) · Answer 1 · 2011-03-14 15:41:18

Looks like a bug to me! You should call it in, although be prepared to be told that nobody works on the product any more.

You can work around this limitation by adding your column in your UNION query:

CODE

SELECT test1.*, SPACE(10) as STATUS ;
    FROM test1;
    UNION ALL SELECT test2.*, SPACE(10) as STATUS ;
    FROM test2;
INTO TABLE test3.DBF

TamarGranor (Programmer) · Answer 2 · 2011-03-14 16:21:22

That is ugly. If you don't need this table permanently, you could SELECT ... INTO CURSOR Test3 READWRITE. That doesn't seem to have the same bug.

Tamar

JRB-Bldr (Programmer) · Answer 3 · 2011-03-14 16:25:30

I just ran the code:

CODE

cDBF = "C:\temp\temp1.dbf"
CREATE TABLE (cDBF) FREE ;
( nPk I, ;
  M1  M, ;
  M2  M, ;
  M3  M, ;
  M4  M, ;
  M5  M, ;
  M6  M, ;
  M7  M, ;
  M8  M, ;
  M9  M, ;
  M10 M, ;
  M11 M ;
)
USE

USE (cDBF) IN 0
SELECT temp1
FOR x = 1 TO 20
    APPEND BLANK
ENDFOR

REPLACE ALL nPK WITH RECNO()
*REPLACE ALL M1 WITH ALLTRIM(STR(RECNO()))

* Create an empty table
SELECT * ;
   FROM temp1 ;
   WHERE nPk = -8765 ;
   INTO TABLE C:\Temp\temp2

SELECT temp1.*;
    FROM temp1;
    UNION ALL SELECT temp2.*;
    FROM temp2;
INTO TABLE c:\temp\temp3.DBF

ALTER TABLE temp3 ;
  ADD COLUMN cStatus C(10)

in VFP7 in XP.

I ran it both ways:

CODE

REPLACE ALL nPK WITH RECNO()
*REPLACE ALL M1 WITH ALLTRIM(STR(RECNO()))
and then
REPLACE ALL nPK WITH RECNO()
REPLACE ALL M1 WITH ALLTRIM(STR(RECNO()))

And it ran fine both ways.

Good Luck,
JRB-Bldr

JRB-Bldr (Programmer) · Answer 4 · 2011-03-14 16:43:57

As a side note - I agree with Tamar's comment above - "That is ugly."

And any table that is comprised of that many Memo fields should be seriously questioned as to the appropriateness of the data architecture.

Good Luck,
JRB-Bldr

BobInVB (Programmer) · Answer 5 · 2011-03-15 13:45:14

Thanks for the input...

The bottom line is that it appears there is a flaw in VFP 9 as pertains to the UNION clause when one source table is empty and outputting to a table. Do I expect this to be fixed by MS? No.

Tamar's suggestion of using a cursor may be the way to go.

To be sure it is ugly but it is not reality. I could have just as effectively used just three memo fields and some other fields for the demonstration of the problem.

Dan Freeman (Programmer) · Answer 6 · 2011-03-15 13:56:35

I don't have time to experiment, but if the meme is "any use of multiple memo fields" then I'd have to say "don't do that!" <g>

There is only one memo *file* associated with any table, and if you have multiple memo *fields* they're all jumbled together in that single file.

VFP has enough trouble keeping pointers into memo fields without corruption with a single memo field, much less when adding multiple memo fields AND then joining to another table also having multiple memo fields.

It's just sounds like bad mojo before writing a single line of code.

Come Join Us!

Posting Guidelines

vfp 9 Memo Field oddness