×
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!
  • Students Click Here

*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

Jobs

Add field to Existing DB in FoxPro 2.6

Add field to Existing DB in FoxPro 2.6

Add field to Existing DB in FoxPro 2.6

(OP)
I am trying to add a field to an existing table in an existing program on our system...  The table is written for an extract program and can change in size and the fields may change.  Unfortunately the program is very convoluted so I cannot just create a new table based on the information provided in the pgm...  Any suggestions as to how to add a field to a table in an existing program...  I can copy the structure but how do I add the new field?  Can send any suggestions to dtaylor@minolta.com...  Thanks

RE: Add field to Existing DB in FoxPro 2.6

use the table exclusively
type modi struc in the command box
the rest should be obvious

RE: Add field to Existing DB in FoxPro 2.6

I've used two techniques, the first works for all field types except memo fields. Basically it uses the SQL Select capability to create new fields. Some sample code follows:

lcdbf = "customer"
SELECT 0
USE (lcdbf)
IF ! chkfldx("fax")
   SELECT *, SPACE(13) AS fax, SPACE(3) AS country, ;
     SPACE(7) AS user_id, {} AS LastUpdate, ;
     SPACE(25) as email, 0000.00 as CustCred, ;
     .F. as NewUser ;
   FROM (lcdbf) ;
   INTO TABLE xtemp.dbf
   USE IN xtemp
   USE IN (lcdbf)
   ERASE (lcdbf+".DBF")
   RENAME xtemp.dbf TO (lcdbf+".DBF")
ELSE
   USE IN (m.lcdbf)
ENDIF
.....

FUNCTION chkfldx  && Check for field already eXisting in DBF
PARAMETERS zcfield
PRIVATE lnii
zcfield = UPPER(ALLTRIM(zcfield))
FOR lnii = 1 to FCOUNT()
   IF ALLTRIM(FIELD(lnii)) == zcfield
      RETURN .T.
   ENDIF
ENDFOR
RETURN .F.

Adding fields to Customer.DBF
fax C(13)
country C(3)
user_id C(7)
LastUpdate D
email C(25)
CustCred N(7,2)
NewUser L

Another way is to use AFIELDS() to get the field information, re-DIMENSION the array for the number of additional fields. Then fill in the additional field information, and use this with CREATE TABLE ... FROM ARRAY to create an empty table with the new structure. Finally use APPEND to transfer the data from the "old" table to the new one.

A variant of this technique is to use COPY STRUCTURE EXTENDED to create a table with the field information, add new records for the new fields and use CREATE <newtable> FROM <structTable> to make the new table. Finish again by transfering the data.

All techniques will likely require you to recreate the indexes (.CDXs) - especially if the new fields are indexed.

Rick

RE: Add field to Existing DB in FoxPro 2.6

Dtaylor,
   If I am understanding your delima correctly, you need the code to add the field for you, because it is created dynamically?  But this is difficult, due to convolution...
   Couple of questions:
   1) What are you planning to "Do" with the field once it is added.
   2) Does the tables structure get "Re-created" every time the program is run?
   3) Can you break the "Convoluted" code down into smaller chunks.  (That may require some work, but I would think will benifit you in the long run, as you may need to make other changes in the future.)

          Thanks,
           -Scott

Please let me know if this has helped!

RE: Add field to Existing DB in FoxPro 2.6

Hiya Visitor,

It all depends on what you're trying to accomplish.

If you are simply adding a field to a database using your FoxPro development environment, select the database and modify its structure using the <Setup> functionality from either the menu, or the view window (or, indeed, the command window by typing MODIFY STRUCTURE).

If you're looking for a way to 'upgrade' a database from within a program, here's what I do:
- I create an empty database with the extra field ; and put that in an 'upgrade' directory i send along witht he updated exe file.
- From an upgrade procedure, I first PACK the old database (otherwise the append will copy the DELETED records too, and happily mark them as not deleted *boggle*) and then APPEND ALL the records from the old database to the new empty database (handy if you need to do some REPLACE ALL instruction to set things up roight after setting up the new datafile).
- From the same procedure, I then close and rename the original, delete it, and rename the new database.
- In order to check if everything went okay, and to have a backup of the original data, you can of course always copy the original file before deleting.
- Don't forget to toss out the index files and make new ones.

A practical, and less convoluted way I think..

Best regards,

Jan Schenkel.

"As we grow older, we grow both wiser and more foolish at the same time." (De Rochefoucald)

RE: Add field to Existing DB in FoxPro 2.6

(OP)
Hello all, I am still working on this...  My problem is the table is created in this program based on a criteria from the screen selection...  This is an extract program...  I have found the location in the program where I need to make the change but I am not sure how to create a new table based on the fields from the temporary table from the program since based on the selection criteria the actual contents and structure of the database can change...
If I use AFIELDS() to get the total of fields and AFIELDS(tablestructure) to also get the structure, Use a COPY STRUCTURE EXTENDED command (Or re DIMENSION), How do I utilize this with the ability of adding two additional fields and how do I code the addition of the two fields...
I will be performing two other selects to fill these fields after I have APPENDED the contents of the temporary table to it...  

RE: Add field to Existing DB in FoxPro 2.6

Assume we want to add 4 new fields:
newchar C(6)
newnum N(7,2)
newlog L
newdate D
....
SELECT 0
USE oldtable
lnFields = AFIELDS(laFields)
USE && close oldtable

DIMENSION laFields[(lnFields+4), 4]
laFields[lnFields+1, 1] = "NEWCHAR"
laFields[lnFields+1, 2] = "C"
laFields[lnFields+1, 3] = 6
laFields[lnFields+1, 4] = 0 && doesn't really matter
laFields[lnFields+2, 1] = "NEWNUM"
laFields[lnFields+2, 2] = "N"
laFields[lnFields+2, 3] = 7
laFields[lnFields+2, 4] = 2
laFields[lnFields+3, 1] = "NEWLOG"
laFields[lnFields+3, 2] = "L"
laFields[lnFields+3, 3] = 1 && doesn't really matter
laFields[lnFields+3, 4] = 0 && doesn't really matter
laFields[lnFields+4, 1] = "NEWDATE"
laFields[lnFields+4, 2] = "D"
laFields[lnFields+4, 3] = 8 && doesn't really matter
laFields[lnFields+4, 4] = 0 && doesn't really matter

CREATE TABLE newtable FROM ARRAY laFields

APPEND FROM oldtable

** Whatever you what to add data to old records' new fields (default values) and add on any the "new" records.

** You'll probably want to delete the old table and rename the new one

Note: I haven't added the code to check if the "new" field already exists in the table, but using ASCAN() that shouldn't be hard.

Rick

RE: Add field to Existing DB in FoxPro 2.6

(OP)
I figured it out...  Thanks
I used the Copy Structure Extended/Append
I didn't know that it actually dumps it into a table and you just add a row...  I was trying to add a column...Once I started playing with it and viewed the tables it all came together...

Thanks a bunch...

RE: Add field to Existing DB in FoxPro 2.6

(OP)
hey Rick,

I used your last suggestion:

use table
copy structure extended newtable
use newtable
append blank
replace newtable.field1 with "contents"
replace newtable.field2 with "contents"
replace newtable.field3 with "contents"
replace newtable.field4 with "contents"
create temptbl from newtable
append from table
delete file table.dbf
rename temptbl.dbf to table.dbf
delete file newtable.dbf
close all
quit

I still have to go thru the db and fill the new fields...
Thanks Rick...

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