I had completely forgotten what dialect 2 is for!

Anyway, should it not be possible to drop a field named TIMESTAMP
directly in dialect 1?

Best regards,
Bengt


Helen Borrie wrote:

> At 01:26 PM 2/08/2005 +0000, you wrote:
> >I have restored a Database on FB 1.5.2 which was originally on IB 6.
> >
> >Working with the data, everyting works ok. However, the database is
> >still dialect 1. I want to convert to dialect 3 and in order to do so,
> >I have to change some of the column names.
> >
> >My problem is with a column named TIMESTAMP. FB refuses to drop that
> >column saying "Token unknown"
> >
> >What can I do?
>
> Start up isql without connecting to the database.
>
> SQL> SET SQL DIALECT 2;
> SQL> connect 'd:\path\to\database.fdb'
> CON> user 'ownername' password 'potplant';
>
> zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz (stuff about connected to database)
>
> SQL> set auto on;
> SQL> alter table yourtable add column TEMP timestamp;
> SQL> update yourtable set TEMP = TIMESTAMP;
> SQL> select TEMP from yourtable ; -- reality check
> SQL> commit;
> SQL> alter table yourtable add "TIMESTAMP" timestamp;
> SQL> update yourtable set "TIMESTAMP" = TEMP;
> SQL> select "TIMESTAMP" from yourtable;
> SQL> commit;
> SQL> alter table yourtable drop temp;
>
> Even simpler, if you want to avoid the quoted identifier and apply a new
> name directly to the column:
> SQL> alter table yourtable add column TSTAMP timestamp;
> SQL> update yourtable set TSTAMP = TIMESTAMP;
> SQL> commit;
> SQL> alter table yourtable drop TIMESTAMP;
>
> Once you have added one quoted identifier to something in the
> database, you
> have a database that is not consistent with either Dialect 1 or 3. You
> must continue to use the dialect 2 client until the whole database is
> made
> dialect-3-friendly. Then you can run gfix -sql 3 to change the
> dialect. Note, though, that changing the dialect does not alter any
> data. Watch out for legacy DATE types. Any which you are going to keep
> should be altered to TIMESTAMP. There may be other anomalies in your
> data,
> too, esp. with number columns. Watch out for "computed by" columns and
> check constraints that perform integer/integer division!
>
> The better third-party tools support the dialect 2 client, so you are not
> stuck with using isql. Some migration tasks - like recreating
> triggers and
> SPs - will be easier to do in a posh tool like FbWorkbench or IBExpert.
>
> Myself, I don't do conversions this way. I treat D2 as a useful thing to
> have around for the odd job during preparation. A person can go mad in a
> day using it with a big database. I extract and edit the metadata using
> IB_SQL's tools, create a dialect 3 database, run the metadata script,
> then
> use IB_SQL's datapump to pump the data across. There are more
> user-friendly datapumping tools around, too.
>
> ./heLen
>
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://firebird.sourceforge.net and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>
>
> ------------------------------------------------------------------------
> YAHOO! GROUPS LINKS
>
> * Visit your group "firebird-support
> <http://groups.yahoo.com/group/firebird-support>" on the web.
>
> * To unsubscribe from this group, send an email to:
> firebird-support-unsubscribe@yahoogroups.com
> <mailto:firebird-support-unsubscribe@yahoogroups.com?subject=Unsubscribe>
>
> * Your use of Yahoo! Groups is subject to the Yahoo! Terms of
> Service <http://docs.yahoo.com/info/terms/>.
>
>
> ------------------------------------------------------------------------
>

[Non-text portions of this message have been removed]