How Do I Alter a Database Table Definition? (Magic xpa 3.x)

Once you have a database table defined in Magic xpa, you can change it when you need to. How you do the changes will depend somewhat on whether or not the table is also being used by another application, and whether or not there is “live” data in a production environment. We will discuss the following cases:

• Magic xpa only: The data is only defined by Magic xpa.

• External data: The data is defined in the DBMS, or by a 3rd party. This is often the case, for instance, when accessing data from an accounting application or purchased database.

Altering database tables defined only in Magic xpa

If a database table is defined only in Magic xpa, changing it is easy. Your program references are automatically kept in sync if at all possible:

• If you add a column, the programs in the repository will be automatically updated

• If you delete a column, you need to make certain that no programs are accessing that column. Use the Edit->Find and Replace->Find Reference (Ctrl+F) utility on the column to check if it is used. If it isn’t used, just delete it.

• If you change a column, the references will be changed too, if possible. If there are overrides on the display of the field, then the field display will not be changed. Also, if you change the attribute of a field from, say, numeric to alpha, then operations that move data to that field will no longer be correct.

Now, what about existing data? If in your database repository, Properties->Options->Change Tables in Studio is Yes, then Magic xpa will automatically reformat your data for you, and even make a backup copy just in case.

This is not something you would want to use with production data, however. When you deliver your finished application, you should also deliver a utility to reformat the existing data. The easiest way to do this is to keep the old database table entry in the repository, as well as the new one, and create a Magic xpa program to move data from the old one to the new one.

Altering the Magic xpa data source to match an external table

Now, if you have a data source that is defined elsewhere, your approach should be different. Here, you need to bring in the definition from another source. Note that you need to be sure these tables have a database definition where Properties->Options->Change Tables in Studio is No, so that Magic xpa doesn’t try to change the existing data.

You can do the changes manually by typing in the changes onto your existing data source definition, as you would for a Magic xpa-only table.

Or, you can use the Options->Get Definition (F9) utility to bring the definition in to Magic xpa automatically. Here is how you do it:

1. Use the Get Definition utility to bring in the table definition, as discussed in How Do I Access an Existing Database Table?.

2. Print out the definition, or take a screen shot of it, so you can view it while you do the next step.

3. Edit your existing table definition (not the one you imported). If columns were added in the new definition, add blank entries to your existing definition. If columns were moved, move them in your old definition. The idea is to make the entries match in position and approximately in content. For instance, if Customer Name was the 5th line down in the new definition and was 20 characters long, and the new definition has it as the 6th line down and 30 characters, add a “dummy” column above it, but don’t worry about the number of characters.

4. Do the same for the indexes.

5. Once the two definitions match positionally, use the Overwrite function to overlay the new definition onto the old one.

This works because Magic xpa references the database columns by position, using an internal ID number. Usually, existing tables, especially those from commercial applications, do not change much, and when they do it is to add fields, so the process does not take long.