Thank your all for your replies,

But the exception exists and I suppose that it broke many parts of my
applications after the migration from 2.0 to 2.1. Please let me know if
the automatic type conversions will not take place anymore in such
queries. Or it could be fixed in some further Firebird builds?

By the way, I was not able to migrate any of my databases from 2.0 to
2.1. As I had read in "Release Notes" before the migration, the metadata
upgrade operations should be performed after the ODS upgrade to 2.1. I
know only one way to upgrade the ODS version - backup/restore. But each
backup/restore cycle of any of my databases failed, because of error
during the restore operation. The error messages were about text blob
conversions from UNICODE_FSS to UTF8. It were data fields, not metadata.
Now the problem is in the past, because I have done the following:

1) New databases were created by scripts. The databases were populated
with bare tables, without indices and constraints.
2) I wrote a console utility for copying database table by table. (I had
tried fbcopy 1.52 and some other utilities before, but all of them had
failed with the same problem of text blobs character transcoding)
3) All the data were copied using the utility.
4) The rest database objects were created by scripts.

The utility source code:
http://developer.anuko.com/img/wrcms/developer.anuko.com/files/Main.java
and the compiled version:
http://developer.anuko.com/img/wrcms/developer.anuko.com/files/dbcopy.zip

Best regards,

Andrew.

Helen Borrie wrote:

>
> At 07:54 AM 25/01/2009, you wrote:
> >> where h.log_tsc > coalesce(z.upd_tsc, z.create_tsc, '1.1.2006')
> >>
> >> It causes the error: "GDS Exception. 335544321. arithmetic exception,
> >> numeric overflow, or string truncation"
> >>
> >> Please clarify the situation.
> >
> > I would guess that Firebird is trying to cast all arguments of
> >coalesce function to CHAR(8), which is type of '1.1.2008'. And because
> >fields are timestamps, they don't fit 8 chars.
>
> I don't think so. The expression coalesce(z.upd_tsc, z.create_tsc,
> '1.1.2006') doesn't cause an exception (for me) in either v.2.0 or
> 2.1, even with timestamps in the first two arguments.
>
> Another guess: h.log_tsc might be null for some rows and at runtime
> the parser barfs on testing it against any of the arguments.
>
> >PS: You can use specification "_timestamp" instead of CAST. I.e.
> >coalesce(z.upd_tsc, z.create_tsc, _timestamp '1.1.2006')
>
> Actually, not quite right. The "_XXXXXXX" (known as 'introducer')
> syntax is for coercing a literal to character set _XXXXXXX. If you try
> to use it for coercing a data type, you get this exception:
>
> ISC ERROR CODE:335544569
>
> ISC ERROR MESSAGE:
> Dynamic SQL Error
> SQL error code = -504
> CHARACTER SET TIMESTAMP is not defined
>
> For coercing the data type:
>
> coalesce(z.upd_tsc, z.create_tsc, timestamp '1.1.2006')
>
> ./heLen
>
>