David Wieland dwieland at approis.com
Wed Apr 22 01:27:17 EDT 2009

I also use a DF1 datafile with Omnis SQL and have found, as you say, that the only ALTER TABLE statement that actually works is one that adds a column.  In fact, I also learned (in a test database, fortunately) that you can run such a command repeatedly, with Omnis adding another column with the same name each time!  I doubt there's another SQL engine on the planet that allows such a thing, but Omnis is only faking relational technology.  That became painfully obvious to me when I learned I had to do my own query optimization.

But to answer your main question, when I needed to alter a column definition in a populated table, e.g., to increase the number of decimal places, I used an apparently undocumented feature of the SQL Browser.  Using the right-click menu, I copied the table to be altered and then "pasted" it back to a new name.  This operation first presented an editable grid for mapping from the original table to the new one.  This allowed me to add the decimal precision I wanted, while preserving the data.  

During the "paste", I also created the associated indexes with altered names. After verifying the copy, I then deleted the original and copied/pasted the copy back to the original name.  It's a lot of steps for an "ALTER", but at least it works.

After completing the table alteration, I did what other list respondents suggested -- I created a new, matching schema by deleting the old one and dragging the table from the SQL Browser folder to the application library folder.

David Wieland
Appro Information Systems
Kars (Ottawa), Ontario, Canada
dwieland at approis.com

----- Original Message ----- 
From: "sethpaien" <sethpaien at gmail.com>
To: "OmnisDev List - English" <omnisdev-en at lists.omnis-dev.com>
Sent: Tuesday, April 21, 2009 9:47 AM

| Hi all,
| We would like to do an ALTER TABLE using OMNIS SQL DAM connecting to a DF1
| File, but without success.
| Dealing with Omnis documentation, ALTER TABLE can not do anything but ADD
| field at the end of table, and with SQL Browser it's the same problem...
| Does any one use OMNIS SQL DAM ? and if so, how do you alter table when
| needed ? (Change data type, len of a VARCHAR or the name of a field).
| Thank you for any help.

More information about the omnisdev-en mailing list