Extreme problem adding fields to a table.

[preston2]

I am using a function to add fields to a table and I have been doing it this way for years without any problems but now I am trying to add fields to a table and it is blowing the table up deleting at least one field and changing field types on others.

I will post the function code here but first I am trying to get a list of the fields in the table using A5_GET_FIELDNAMES() and then write it somewhere like a txt file so I can post the results here and also use it to check for problems.

So if I use fld_list = a5_get_fieldnames( "wo_header.dbf" ) it puts the fields in a list.

I think if I tried

fld_list = a5_get_fieldnames( "wo_header.dbf" )
f=file.create("Filename",file_rw_exclusive)
f.write_line(fld_list)
f.flush()
f.close()

It would write all the field names to one line. I am going to try that and see.

Here the function I am using to add the fields to the table.

FUNCTION wo_header AS L ( )
DIM datapath as C
IF a5.Get_Master_Path() = ""
    datapath = a5.Get_Path()+chr(92)
ELSE
    datapath = a5.Get_Master_Path()+chr(92)
END IF
 
'Check table and LAST field updated first. If it has already been 
'done, skip the rest. Don't need to use "datapath" to get field names.
fld_list = a5_get_fieldnames( "wo_header.dbf" )
IF "IS_Exported"$fld_list '2013-12-13
    GOTO Updates_done
END if

fld_list = a5_get_fieldnames( "wo_header.dbf" )
IF .not. ("Is_Exported"$fld_list)
    new_flds = <<%list%
Is_Exported,L,1,0         
event_id,N,16,0
Details,C,72,0
tmstart,T,17,0
tmend,T,17,0
Alarm,C,20,0
How,C,20,0
Equip,C,20,0
Export_It,L,1,0
Add_Date,D,8,0
Notes,M,10,0
Priority,N,4,0
Class_type,C,16,0
Rev_Date,D,8,0
Sch_datec,C,16,0
Sch_Date,D,8,0
Cust_email,C,60,0
%list%
    a5_add_fields_to_table( datapath + "wo_header.dbf", new_flds )
 end if   
Updates_done:
END FUNCTION

[DaveM]

Re: Extreme problem adding fields to a table.

How about
<Tbl>.FIELD_ADD()

???
expanded:

table.create_begin("firstname","c",30)
table.field_add("lastname","c",30)
table.field_add("price","n",5,2)
table.field_add("quantity","n",2)
table.field_add("birthday","d")
'The create_end method creates the table and assign an object pointer to the table
tbl = table.create_end("sample.dbf")

or
TABLE.FIELD_ADD()

[johnkoh]

Re: Extreme problem adding fields to a table.

Try this sample.

Example

This script adds several new fields to an existing table.

fields = <<%str%
Product,c,20
Price,n,10,2
Notes,m
%str%

table.add_fields("table1", fields)

[preston2]

Re: Extreme problem adding fields to a table.

Thanks guys. I will test both methods out.

[preston2]

Re: Extreme problem adding fields to a table.

Ok,

Table.Field_Add() is only used between TABLE.CREATE_BEGIN() and TABLE.CREATE_END() so it looks like it is for creating new tables and not adding fields to an existing table.

Using the example John post yields the same result I was having with my code above. In other words it trashes the table.

I have something else I am about to try and see if that may be the problem.

[preston2]

Re: Extreme problem adding fields to a table.

I give up. I even tried only adding one field and it still trashed the table. Beyond me that a function code I have used for years now stops working right. It may be something with the table, who knows.

I think I am just going to add a child table and then redo all my forms and scripts to work off the child table data.

[CraigSchumacker]

Re: Extreme problem adding fields to a table.

Can you, as an alternate solution, create the new table with an additional linking field, then use create_set() to link one to one?

[preston2]

Re: Extreme problem adding fields to a table.

If I do it as an update the new child table will already be linked one to one in the set.

If I am understanding you correctly.

[CraigSchumacker]

Re: Extreme problem adding fields to a table.

I'm not sure of your goal, but:

1) You have a table with, say, 10 fields.

2) You need to add 3 new fields "on the fly".

3) You can use tbl.field_add() to create a new table with the 3 new fields, and one additional field for linking back to the original table with 10 fields.

4) You use create_set() to link those two tables, with a one to one relationship giving the set a new, unique name.

[Stan Mathews]

Re: Extreme problem adding fields to a table.

You haven't told us much about the base table. There are limits on the number of fields and the total of the field width. Can you add one field with your code?

Contrary to the song, the road does not go ever on.

[CraigSchumacker]

Re: Extreme problem adding fields to a table.

table_restructure() will work without overwriting the data, but YOU MUST keep the existing order of existing fields the same.

Example....

If you were adding height to a table with two existing fields, FIRST_NAME and LAST_NAME

PTR = table.open("customers",FILE_RW_EXCLUSIVE)
PTR.table_restructure(<<%str%
FIRST_NAME,Character,25,0
LAST_NAME,Character,25,0
HEIGHT, Numeric,5,2
%str%)
PTR.close()

I stumbled on this looking to see if you could enumerate field structure info...

[preston2]

Re: Extreme problem adding fields to a table.

You haven't told us much about the base table. There are limits on the number of fields and the total of the field width. Can you add one field with your code?

Contrary to the song, the road does not go ever on.

Stan. I have all the fields added in the development, created an installer, installed it and everything works.

It is when the updates are installed to a previous version that everything goes south. This has never happened before.

In the beginning of this thread I was also looking for a way to either write the tables field list to a file or even print it out so I could post it here but have yet to find anything to do that either.

And yes, I tried having it add just one field as I stated above and it trashed the table.

I can add them all manually, just not with code.

Looking at the field properties it is 154 fields counting the ones I added manually.

[DaveM]

Re: Extreme problem adding fields to a table.

try this. it may bge easier

a5_add_fields_to_table("thedbf", "newfield,C,0")

http://wiki.alphasoftware.com/A5_ADD...TABLE+Function

[preston2]

Re: Extreme problem adding fields to a table.

From my code above,

   a5_add_fields_to_table( datapath + "wo_header.dbf", new_flds )

The "new_flds" is a list and it has always worked before.

But I guess I could try a a5_add_fields_to_table( ) line for each field and see if that still trashes the table.

Too tired tonight, will try in the morn.

[DaveM]

Re: Extreme problem adding fields to a table.

I just tested it and it works fine. Test it on a junk table somewhere first with a bunch of fields. If memory serves right, the newest field become the last in the table.

I have used it in the past with a dummy adb file on install. It has just been a while since I needed it.