Modifying Customizer tables via SQL
It is possible to modify Customizer table definitions through SQL and have
iMIS use them correctly. Mind some caveats, and this can be a much faster way to make mass changes, or to copy settings from a test database to live.
What iMIS does when you save changes to a table
(Example table: Demog_All)
- Delete all rows from UD_Table and UD_Field for Demog_All
- Insert UD_Table with new values
- Insert each field definition in UD_Field in order by SEQ
- Ask user "Create/Update Server Table?" If no, stop here, otherwise...
- Rename Demog_All as Demog_All_old (if it exists)
- Create Demog_All with new specs
- Insert Demog_All from corresponding fields in Demog_All_old (if it exists)
- Drop Demog_All_old (if it exists)
What iMIS does when you save changes to a window
(Example window: "Demographics" based on table "Demog_All")
- Delete UD_WindowHeader, UD_WindowFields for Demographics
- Insert UD_WindowHeader
- Insert each field in UD_WindowFields
What iMIS does when you click Utilities -> Drop Table Specs
This is a hunch, based on what the item is called and what seems to happen when you forget to do it. It's clearly an all-Omnis thing.
- Updates IMIS4.LBR, IMISMEM.LBR, IMISLOC.DF1 with (possibly cached) data from all four UD_* fields
- Frees cached data from UD_* tables
How to successfully modify UD_* tables and not make iMIS crash
The essence of this technique is to use SQL to make changes you could have made by hand through the Customizer windows, and then make some minor, unimportant change so
iMIS realizes something has happened and it needs to update the Omnis stuff.
- Any changes made to UD_Field must have corresponding changes in UD_WindowFields. Likewise, UD_Table and UD_WindowHeader.
- Not every change you can think of can actually be performed by iMIS. For example, if you change a long character field to a Checkbox, iMIS will have trouble trying to fit the character data into a bit field. At the same time, iMIS would assign a different Width on the window to a Text field.
- You should be able to make the following kinds of changes through SQL and let iMIS update it without any extra fancy work:
- Make a character field longer or shorter (data will be truncated if needed)
- Add/remove/change a validation table
- Change the default prompt
- Move from smaller to larger numeric datatypes (checkbox -> int/money/numeric, int -> money/numeric, money -> numeric, numeric -> money)
- Move from smaller to larger date/time datatypes (date -> datetime, time -> datetime)
- Change any numeric or date/time type to character
- Changing the Precision field (no effect anyway)
- Changing Format (as typed/upper case/upper-lower)
- Changing the order of fields within the table
- Changing the number of columns or "show on profile" attribute of a window
- Changing the order of fields in a window
- Changing the prompt, read-only or new-row attribute of a field on a window
- When you have changed the data as you want it to be, you then go into iMIS to make the changes take effect:
- Log in as MANAGER!sa
- Define Tables -> bump a field -> save -> answer Yes to "Create/Update Server Table"
- Define Windows -> for each window based on a changed table: bump a field -> save
- Utilities -> Drop Table Specs
- IFC -> User Defined Tables -> Build All -> Build All
- Restart iMIS
Making more drastic changes
If you have to make any of the following kinds of changes,
iMIS won't be able to copy the data from the old table to the new one.
- Renaming a field
- Making datatype changes not listed above
- Renaming the table
In this case, add the following steps to the above procedure.
- Make the changes to the UD_* tables through SQL
- Rename the existing table through SQL (e.g. Demog_All -> Demog_All_hold)
- Go into iMIS and bump the fields as before. "Create/Update Server Table" will create a new table.
- Manually insert the data from your old table into the new table. SQL is pretty much the only option here.
There are some good shortcuts in Query Analyzer and Management Studio to save you a bunch of typing on this part.