Subject Re: [firebird-support] Database versions
Author Paul Vinkenoog
Hello Jesus,

> Hello Paul, thank you for your response a have readed it several times,
> but until now i have not tried the migration. My only problem is with date
> fields, that now may be timestamp or date.I do not use double quotes and
> reserverd words in the database, but the database has a large metadata (1000
> tables, 3000 triggers and 1500 sp) and we have customers that works 24x7
> with 4-15Gb databases. I can't stop them one day to migrate and i need to
> change the dialect to 3.

First, I don't know which InterBase or Firebird version your database is.
If it's pre-IB6, you have to migrate it to IB6 or Firebird using backup and
restore. That will give you a Dialect 1 database with all the DATE columns
now named TIMESTAMP.

After that, extract the metadata from the database. Then set isql to Dialect
2 (this is a client-only dialect) and run the metadata file in isql. You will
get warnings and errors for everything that needs your attention when you
make the migration. With that list, you can start working on the necessary
(or desired) changes. You shouldn't set the database dialect to 3 before
you have solved every potential problem.

However, converting old DATE columns (now TIMESTAMP) to new DATE or TIME
columns, as well as converting big numerals from DOUBLE PRECISION to INT64
will have to wait until you have upgraded to Dialect 3.

> Is there any problem if i change timestamp field to date field?

You lose the time information, but if you only need the date, that's OK.

> Are both stored internal as double precision?

TIMESTAMP (old DATE) is an 8-byte field and (new) DATE and TIME are both
4-byte fields. I don't know the internal representation (once did, but
forgot) but that's not important for the migration.

> The stored procedures and triggers has variables and parameters that will
> be converted by default to timestamp. I think there is not problem if in
> one insert for a date field i use one timestamp variable, is right?

No - again, you lose the time part.

The guide I gave you the URL to contains detailed instructions, both for
in-place migration and for copying the data to a new Dialect 3 database.
Take your time - make sure you understand what's going on and test
everything on _copies_ of the database.

Also think about the client software - it may need changing too, to cope
with Dialect 3. And if it explicitly specifies Dialect 1 when connecting,
it should stop doing that.

Good luck,
Paul Vinkenoog