×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

vfp 9 Memo Field oddness

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

RE: vfp 9 Memo Field oddness

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. sad

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

RE: vfp 9 Memo Field oddness

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

RE: vfp 9 Memo Field oddness

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
 

RE: vfp 9 Memo Field oddness

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
 

RE: vfp 9 Memo Field oddness

(OP)
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.


RE: vfp 9 Memo Field oddness

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close