Adding columns to a table programmatically...

[brigmar]

As there is no builtin command in FPW2.6 that allows additions of columns to a table programmatically, I decided to write something to do the job... (call it a training exercise)

I'm doing a COPY STRUCTURE EXTENDED TO... to a SYS(3) file.
I'm then appending records for the new columns into that table.

I then CREATE ... FROM ... into another SYS(3) file.

Which gives me:
My original table.
A Uniquely Named new table that describes a structure.
A Uniquely Named new table of that structure.

So, now I want to fill the new table with the data from the old table, and I've been given two conflicting stories of how to do this. Please bear in mind that I want to also transfer deleted records to the new table (but marked as deleted).

I was told first that all I need to do is:
SET DELETED OFF
APPEND FROM oldtable
SET DELETED ON

...but later told that this would NOT transfer deleted records...

So I was then given something along the lines of:
SET DELETED OFF
SELECT oldtable
SCAN
SCATTER BLANK MEMVAR
SELECT newtable
APPEND BLANK
GATHER MEMVAR
IF DELETED(oldtable)
DELETE
ENDIF
SELECT oldtable
ENDSCAN
SET DELETED ON

What's the story, and is there a 'cleaner' way to do it if the latter is the 'solution' ???

 

[Stella740pl]

APPEND FROM do transfer all records, including deleted, even with SET DELETED OFF - unless you specify APPEND FROM ... FOR !DELETED().

But why you allow to be 'given stories' or 'told' what it would or would not do? What happend to looking up help? And you can always test it on a small sample table, can't you?

 

[Stella740pl]

Sorry, I wanted to say that APPEND FROM appends even deleted records when SET DELETED is OFF - my mistake. Still, it does the job.

 

[brigmar]

APPEND FROM :
I gave an erroneous statement. APPEND FROM does obey the "SET DELETED" value allowing the copy as you say. However, the state of the DELETE flag is reset. We need to keep the DELETED () status in the new table.

As for the why, I don't currently have access to FPW from home (which is where I am posting from). I'm not a FP guy at all yet, and have been given these two conflicting 'solutions' by two of the 'experts' here. I'm asking here on my time, not my company's.

 

[dbMark]

Be very careful when appending deleted records from another table. Unless I'm mistaken, and correct me if I'm wrong, but some 6-7 years ago I noticed that the DELETED() flag or status does not copy over with those appended records with FoxPro for DOS/Windows. (I have Visual FoxPro 9 and it does copy over the deleted status correctly.)

It's easy to verify. Create a test table where half the records are deleted. Be sure to set deleted off so that deleted records can be seen to be appended. Append the records. Check the deleted status of the appended records. Are half deleted or are they all undeleted?

That is one behavior of APPEND and DELETED flag that I never liked in that earlier version.

 

[DSummZZZ]

Maybe something like this would do the trick?

SELECT *, ;
   Space(10) AS newfield1, ;
   0 AS newfield2;
   FROM oldtable;
   INTO TABLE newtable;
   WHERE !Deleted()

-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[Stella740pl]

Dave Summers,

It's an exact opposite of what brigmar needs. He wants all the deleted records to be copied over with the DELETED() flag intact.

brigmar,

One of the ways to do it, besides what is shown in your original post is with SELECT-SQL, but an extra field is required.

SELECT *, ;
   Something AS newfield, ;
   [COLOR=red]IIF(DELETED(),1,0) AS Del_Flag[/color] ;
   FROM oldtable;
   INTO TABLE newtable

SELECT newtable
DELETE ALL FOR Del_Flag=1

Or, you may do the following:

SET DELETED OFF
SELECT newtable
APPEND FROM oldtable
* And, if DELETED() flags are not transferred 
* AND you have a unique key, then:
SELECT YourUniqueKey ;
   FROM oldtable ;
   WHERE DELETED() ;
   INTO ARRAY arDel

SELECT newtable
DELETE ALL FOR ASCAN(arDel,YourUniqueKey)>0

You may use RECNO() instead of unique key, but be very careful - switch indexes off with SET ORDER TO before doing this, as the records are copied in the order of the current index.

 

[NasibKalsi]

Brigmar:

You have tons of solutions, presented.
I tried on Foxpro 2.6 with 29995 records as follow:

* Upper case is FieldName
delete all for at('A',DESC) > 0.
* About 20,000 records got deleted

copy to t_1 && It copied all 29995 records

zap && Now delete all records from the source file

append from t_1 && Append from the temp file. All records

were added to the source file. Exactly what it should do.

If this is not working in your case because you have FPW2.6,
may be following is a tiny help.

use main && Your main File
copy to t_1 for deleted() && copy all deleted records
copy to t_2 for .not. deleted() && Copy Other records

use t_1 && Deleted records
recall all


use newfile && Use modified file with updated structure

* Append all the deleted records first, and delete them
appe from t_1
dele all

* Append now the regular records
appe from t_2

try it.

Nasib Kalsi

 

[DSummZZZ]

Stella,

Long day. I misunderstood.
I would go with your first example after you set me straight.


-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[Stella740pl]

Dave,
There is also very valid way the OP shown - I would time-test all of them, then pick.


I also want to add that in VFP6 (the only version I currently have) APPEND FROM respects SET DELETED setting and also transfers the DELETED() flags - but I vaguely remember that older version probably didn't. But that's not the catch - the catch in the VFP6 Help file. It erroneously states that

If you are appending from a Visual FoxPro table, records in the table that are marked for deletion are appended, regardless of the SET DELETED setting.

Got me at first - even though I understood from experience that something is wrong with this statement and checked with a couple of tests.
Same statement is made in VFP7 Help - see here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/fox7help/html/lngappend_from.asp

The Help was corrected in vfp8, though:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_foxhelp/html/lngappend_from.asp

 

[brigmar]

Thanks for the suggestions.

dbMark: That's exactly the behaviour I'm referring to and my reaction is the same too.

It seems that both my experts were right, but for differing versions.

 

[dbMark]

If you need to preserve the DELETED() status you can always SET DELETED OFF and COPY TO (destinationTable).

A variation of that is you could copy off the deleted records to a separate table then append in from the two tables separately (one to get the recalled or undeleted records and the other to get the deleted records) and reset the deleted flag for the deleted group.

dbMark

 

[Stella740pl]

dbMark,

Of course, COPY TO preserves DELETED() status - but it doesn't help to add a column/change structure.

You variation, though, could be a good idea. Something like this:

COPY STRUCTURE EXTENDED TO... 
* Now add the new column
CREATE ... FROM ... 

SET DELETED OFF
SELECT newtable

* Append deleted records first and delete them all 
*     in the new table.
APPEND FROM oldtable FOR DELETED()
DELETE ALL   

* Now, append all other (not deleted) records.
APPEND FROM oldtable FOR !DELETED()

 

[dbMark]

Another possibility is to rethink the logic for determining which records are deleted. Lots of times we think, out of sight, out of the way, so we delete records just to get them out of sight. If we don't really want them gone forever, then maybe it's better to add another field to record the record's status. (Actually, the deleted flag is just a one character table-level field before field #1 that holds either a space or an asterisk.)

If a record is bad or invalid, yes, delete it and forget about it forever. But if it is only completed and you want it saved for archiving or historical tracking, then create a field named something appropriate and meaningful like completed, done, arc, printed, etc. Then set a filter on the table to hide those records as approriate for the situation.

That way you don't accidently wipe or recall those records. And you don't have to worry about them accidently getting recalled when appended. You just have to remember to use the filters.

 

[Stella740pl]

Mike Yearwood,

This is similar to what I already suggested above, on 5-Apr-06 19:48.

 

[Stella740pl]

Hi Mike!

The difference is ... not worrying about dropping off the delflag field.
Oh. The part about not worrying looks more like a similarity to me. ;-)

 

[alvechurchdata]

I'm doing a COPY STRUCTURE EXTENDED TO... to a SYS(3) file.

Just a word of warning from the battlefield. For some years weve been using SYS(3) to generate names for the cursors driving grids on the pages of a page frame. We now find that some users' PCs are sometimes fast enough to beat SYS(3) and generate the same "random" number twice. I know it's always been a theoretical possibility but this is the first time I've seen it happen in real life.

It won't be a problem if yours is a one-shot process but it might be safer to use SYS(2015) instead if you are going to do this more than once.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[brigmar]

Thanks for the ideas.

dbMark:
I'd love to redo the logic of the whole system, which seems very haphazard, but I have a limited remit. Plus I've been at the job here the figurative '10 minutes'. And yes, I have been asking "WHY are you delete-ing records that you should be archiving?".

Geoff:
There would only be the one process accessing the directory, so I think SYS(3) is safe in this regard. Thanks for the heads-up though.

 

[TamarGranor]

RE: SYS(3)

One machine isn't necessarily safe. If the process in question is fast enough, you can generate duplicate values on a single machine. Try this code:

CREATE CURSOR HoldResults (cSys3 C(8))

FOR n=1 TO 1000
	INSERT INTO HoldResults VALUES (SYS(3))
ENDFOR

SELECT cSys3, CNT(*) ;
	FROM HoldResults ;
	GROUP BY 1 ;
	INTO CURSOR CountResults

I get only 7 different values from 1000 calls.

Tamar

 

[brigmar]

SYS(3) is easily worked around, no ?

FUNCTION cSys3
PRIVATE cName
  DO WHILE FILE(cName)
    cName=SYS(3)
  ENDDO
RETURN cName