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.