At 01:12 PM 30/01/2004 +0000, you wrote:

>Hi.
>I think that I have found a bug in Firebird 1.5. RC8 (also tested in
>post RC8 built from anonymous CVS (using MSVC++ 6 with dinkumware
>patches)). BTW, how old is CVS snapshot on anonymous CVS? I would
>like to build Firebird 2, but cannot using VC++ 6. I have tried with
>MSVC7.1 (VS 2003 NET) and it's OK, but very unstable (expected).
>
>TestCase: Create simple database and one table with one field of type
>TIMESTAMP default 'Now'. Fill it with some data, then change type
>from TIMESTAMP to DATE and try to make backup.
>It fails with error: "Arithmetic overflow or division by zero has
>occured. arithmetic exception, numeric overflow or string truncation."
>
>I hope that you will provide some fix, I need it ASAP.
>I am planing to use Firebird in one very big project. I am migrating
>db from MSSQL2000 to Firebird 1.5, and noticed that cannot make
>backup after we changed some field types from TIMESTAMP to DATE. I
>have pumped db from MSSQL2000 to FB database and datapump tool make
>those fields TIMESTAMP. We need them as date and during backup we
>have error:
>"Unsuccessfull execution caused by system error that does not
>preclude successful execution of subsequent statements.
>message length error (encountered 756, expected 744).
>gds_$receive failed"
>
>Validation result is that everything is OK.
>After changing from DATE back to TIMESTAMP everything appears OK...
>Right now DB is more than 5 Gb in size.
>
>Any help? Or I should make new Db and pump data from old to new?

I am suspicious that you changed the data type by altering the system
tables. Using ALTER TABLE XY ALTER COLUMN ATIMESTAMP TYPE DATE should have
thrown an exception, because you can't change the type if the new type
would cause data to be lost.

You certain don't need to make a new DB. The first thing to try will be to
convert the timestamps into date format, using an UPDATE statement:

update atable
set atimestamp = cast(atimestamp as date)
where atimestamp is not null;

If it won't let you do that, then try a double-cast to get rid of any
significant sub-
day data:
...
set atimestamp = cast (cast (atimestamp as date) as timestamp);
...
(Of course, this is going to get you into a stew if you're using dialect 1
so I hope that's not where you're at - assume dialect 3)

Now, if you can't get that to backup and restore, you *still* don't have to
create a new DB, but it's a much longer job and you'd need to do it in a
script to avoid going crazy.

Alter table atable add tempcol DATE, commit, update atable set tempcol =
cast(atimestamp as date), commit, alter atable drop atimestamp, commit,
alter atable add asubstitute DATE, commit, update atable set asubstitute =
tempcol, commit, alter table drop tempcol, commit.
(sorry that's gabbled)

I'm curious how you managed to get the timestamps if you didn't want
them. The datapump should have given you the ability to cast the
timestamps to date and store them how you needed them...erm, unless you ARE
in dialect 1, and DID ask for DATE - 'cause the DATE type in dialect 1 is a
timestamp.

/hb