vfp 9 Memo Field oddness
vfp 9 Memo Field oddness
(OP)
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
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
RE: vfp 9 Memo Field oddness
You can work around this limitation by adding your column in your UNION query:
CODE
FROM test1;
UNION ALL SELECT test2.*, SPACE(10) as STATUS ;
FROM test2;
INTO TABLE test3.DBF
RE: vfp 9 Memo Field oddness
Tamar
RE: vfp 9 Memo Field oddness
CODE
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)
I ran it both ways:
CODE
*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
RE: vfp 9 Memo Field oddness
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
RE: vfp 9 Memo Field oddness
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.
RE: vfp 9 Memo Field oddness
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.