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