||Re: Modify database structure in a program
Mervyn Bick <firstname.lastname@example.org>
||Tue, 9 Apr 2019 10:07:36 +0200
On 2019-04-08 10:02 PM, Dave Cornelson wrote:
> Is there a code I can put in a program to change the type and width in a database file with out using the command Modify Structure and do each one at a time. I have many tables to modify and like to create a program to run to do this...
The short answer is Yes. Actually doing it may present some challenges
but it isn't rocket science. :-)
Changing the width of a field, especially if increasing it, shouldn't
present a problem. Changing the type of the field could well result in
the loss of data depending on the change made. This can be overcome but
it would need a different approach with quite a bit more work
The scheme suggested by Ken is certainly "do-able" and is probably the
least complicated way of doing this. It does, however, change the order
of the fields in the table. After the work is complete the changed
field will be the last one in the record. This could be a problem if
you display data in a grid unless you have used the grid's columns
property to select columns.
If you need to keep the fields in the original order a possible solution
is to use COPY TABLE to move the data to a temporary table. Use
cretabl7.prg from the dUFLP which will create a text file containing the
code to re-create the table. Use a file object to read the text file
line by line and write it out to a new file. Change the line for the
field in question to reflect the new field specification before writing
it out to the new file. DROP the original table and execute the new
text file to create a new table. Append from the temporary table.
Needless to say, make sure you have a complete up-to-date back-up of all
your tables before you try to make any changes.
If you give us more details of exactly what you need to do you may get
further suggestions. For instance, do you need to make the same change
in all tables or only in some tables? What change do you need to make
to the field specification? Do you need to change one field or several
fields in a table? Is the fieldname of the field you need to change the
same in all the tables? Does a field with the same fieldname exist in
tables which don't need to be changed. Would it cause problems if such
a field were to be changed?