Adding new fields to existing tables when executable is upgraded 4

[jworley]

Hi,

I have written an app that is installed in 30 or so locations. User input has resulted in a new version which has required changes in several tables i.e. addition of new fields. Is there an easy way of inserting the new fields into the existing tables when the upgraded executable runs for the first time, so that the user does'nt have to do a complex migration/backup & restore ?

Any ideas would be much appreciated.

Jim Worley
jim@aits-uk.net

http://www.aits-uk.net

 

[RandyDeWitt]

Yes,

It is a relatively simple operation. I save the data base structures to a table named dbfstruc and the index keys to another table named cdxstruc.

I then change the start up routine to look for the existence of a file named chkfiles.txt, if found, I launch a routine that opens the dbf and cdx structure tables, reads the information about each table, opens the original table and compares the structure to the structure in my dbf table. If it is different, I make a new table from my structure and append the records from the original table. Once complete, I copy the original file to an "olddata" directory under my source path.

I would be happy to share the code snippets.

 

[jimstarr]

You can check each table in question for the existence of the new field(s) using something like FCOUNT() or AFIELDS(). When necessary, add the new fields with ALTER TABLE.

Jim

 

[jimoo]

Jim,

That question comes up from time to time so I wrote an FAQ on it. Check it out and see if it helps you.

faq184-4104


Jim Osieczonek
Delta Business Group, LLC

http://www.deltabg.com

 

[IRABYY]

Colleagues:

Using ALTER TABLE is fine and dandy, but I found out that it can only APPEND a column to a table (or drop an existing column). More often than not, we need to INSERT a column, not just "append" (that is add a new field as a last field in a table).

Therefore, I wrote a function that does exactly that: it inserts a new field with the given characteristics into existing table. This function is quite big (200+ lines), considering the necessity to confirm writeability of the files on each and every step (we know how Sys. Admins love to restrict end users' rights), so it seems inappropriate to post it here. But here's the function's header, below, and if anybody is interested, I could post the entire code in the FAQ section.

******************************************************************************************************************************
FUNCTION InsertField(tcAlias, tnFldPos, tcFldName, tcFldType, tnFldLen, tnDecimals, tlVerbose)
******************************************************************************************************************************
** Function Name : Insert Field
** Purpose : Inserts a new field of the given data type and size at the given place into an existing table.
** Description : After runninig all the checks and balances, dumps the table structure into array with AFIELDS() and copies
** this array into another array with extra row; builds a row with the new field attributes and inserts it
** with AINS() into that new array; creates new table files in the SysTemp dir, appends data from the old
** table, indexes it if needed; renames old table files to <FileName>_Old, moves new table files into the old
** files' place.
** Parameter(s) : Table's alias name as String, position of the new filed as Integer, new field's name as String, new field's
** data type as Char, new field length as Integer, number of decimal places as Integer, function's verbosity
** as Boolean.
** Return : Error code as Integer.
** Side Effect(s): Might leave the files in WinTemp dir in case of abnormal termination.
** Notes: : 1. Table in subject must be opened.
** 2. Function does not do the indexing.
** 3. Error codes denomination:
** -1 - Field with the same parameters already exists == success
** 0 - Success
** 1 - Parameter Alias is not a string
** 2 - Parameter Alias is empty
** 4 - Parameter Alias representing an alias of an open table not found
** 8 - Parameter Field Name is not a string
** 16 - Parameter Field Name is empty
** 32 - Parameter Field Type is not a string
** 64 - Parameter Field Type is empty
** 128 - Parameter Field Type value is illegal as a field data type
** 256 - Parameter Field Length is not of numeric type
** 512 - Field with this name already exists, but of different type, or length, or precision
** 1024 - New field's name is part of existing field's name i.e. &quot;Inv&quot; and &quot;InvDate&quot;
** 2048 - New field's name contains then existing field's name, i.e. &quot;InvDate&quot; and &quot;Inv&quot;
** 4096 - Cannot copy array to array due to a system problem
** 8192 - Cannot insert a row into array due to a system problem
** 16384 - Insufficient User Rights
** 32768 - Cannot open table file
******************************************************************************************************************************


Regards,

Ilya

 

[csr]

If you are not using databases, I have routines in my personal library which address the exact problem you are describing. I would be willing to provide you with that code if it fits your situation.


Don

 

[baltman]

Why not just build it with a SELECT?

Select existing1,existing2,000.00 as newnumber,replicate(&quot; &quot;,10) as newchar,existing3 from old_table into table revised_table