O$ ALTER TABLE OMNIS SQL

[sethpaien]

Thank you for all responses,

Dealing with files and schemas together in the same library or copy tables
to change it.

Thank you again rainers.




2009/4/22 David Wieland <dwieland at approis.com>

> 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
> Subject: O$ ALTER TABLE OMNIS SQL
>
>
> | 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.
> |
> |
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com
>