Omnis Technical Note TNSQ0005
Altering datatypes for the columns in your Omnis SQL table
For Omnis Studio
By Omnis Technical Support
It is possible to add new columns to your existing
tables using Omnis SQL but how does one alter datatypes for columns already
defined?
The ALTER TABLE statement lets you add a column to an already existing
table using the same syntax as in CREATE TABLE.
Example:
ALTER TABLE table ADD
{ column_data | ( column_data_column_list ) }
The ALTER TABLE statement does not exist in the 1989 ANSI standard.
In order to alter a datatype for an existing
table one could perform
the following simple steps:
1. Open your library then open the library/class browser and create a
file class with the same name
as the table you wish to amend. Be sure to use the
same case.
2. Start the session and open the SQL Object browser to view the columns
in the table already defined.
Duplicate these definitions to the file class fields
and amend the appropriate field to the required
datatype.
3. Close the session and open the Omnis Data File via the Data File browser.
4. Drill down to the Data Slot that represents the Omnis SQL table previously
defined.
5. Notice that it has a small red pencil symbol attached to it. This indicates
that the data file needs
reorganizing due to the datatype amendment. Right-click
on the slot and select 'Reorganize' from
the context menu.
6. Close the data file and open the session again. Drill down to the amended
table and note the
datatypes changed.