At 09:11 AM 23/02/2005 -0800, you wrote:

>Heh, A subject might help. Also, I think I meant 4 values not 5 being
>inserted.
>
>-----Original Message-----
>From: Edwin A. Epstein, III [mailto:eepstein@...]
>Sent: Wednesday, February 23, 2005 8:58 AM
>To: firebird-support@yahoogroups.com
>Subject: [firebird-support]
>
>
>
>Arithmatic exception, numeric overflow, or string truncation. One of my
>favorites.
>
>This was working, and still is working in another database. I made a meta
>data backup and copied it to another linux sever. Now the after insert
>trigger which does an insert on a table generates this error.
>
>The 2 servers running side by side with the same code do not act the same
>way. Both are identical hardware, identical operating systems, Firebird
>1.5, and running the same database, just different data pumped in from a
>previous database.
>
>I am inserting 5 values into a table:
>
>INSERT INTO TRACKING(TRACKING_TYPE_ID,T1,T2,T3)
>VALUES(3,NEW.H_ABBREV,NEW.H_DESCRIPTION,NEW.PARENT_ID);
>
>This is the DDL for that table:
>
>RECREATE TABLE TRACKING
>(
> TRID INTEGER NOT NULL,
> TRACKING_DATE TIMESTAMP DEFAULT
>CURRENT_TIMESTAMP,
> TRACKING_NOTES BLOB SUB_TYPE 1 SEGMENT
>SIZE 80 CHARACTER SET ASCII,
> TRACKING_TYPE_ID INTEGER,
> T1 VARCHAR( 255) CHARACTER SET
>ASCII COLLATE ASCII,
> T2 VARCHAR( 255) CHARACTER SET
>ASCII COLLATE ASCII,
> T3 VARCHAR( 255) CHARACTER SET
>ASCII COLLATE ASCII,
> T4 VARCHAR( 255) CHARACTER SET
>[...]
>);
>update rdb$relations set rdb$description = ' ' where rdb$relation_name =
>'TRACKING';
>
>Now inserting the values (3,'XXX','XTest',0) generates an error. I have
>tried different values of course, all integers, all varchars, no success.
>
>Any Ideas?

INSERT INTO TRACKING(TRACKING_TYPE_ID,T1,T2,T3)
VALUES(
3,
NEW.H_ABBREV,
NEW.H_DESCRIPTION,
NEW.PARENT_ID); /* this will throw your error */

Either drop T3 and replace it with an integer, or use a cast in the insert
statement:

INSERT INTO TRACKING(TRACKING_TYPE_ID,T1,T2,T3)
VALUES(
3,
NEW.H_ABBREV,
NEW.H_DESCRIPTION,
CAST(NEW.PARENT_ID AS varchar(255));

For the ad hoc query,

INSERT INTO TRACKING(TRACKING_TYPE_ID,T1,T2,T3)
values (3,'XXX','XTest','0')

As for the apparent discrepancies, are the other databases dialect 1 while
this one is dialect 3? (The dialects differ in the way implicit casts are
handled).

Are you matching the client and database character sets and dialects?

Have you reality checked your assumptions about what happens on the other
machines, by recreating this database (under a different name, of course)
on one of them? I mean - recreating it using the same steps you used on
this machine?

Curiosity questions:
1. Why are you defining those string columns with a COLLATE clause? ASCII
is the default (and only) collation available for ASCII.
2. Why ASCII anyway? And, if ASCII is a requirement, how are you ensuring
that a user can't attempt to store text containing characters higher than
ASC(127)? You will get the same error, plus a transliteration error if a
higher ASCII character is encountered during a SELECT.
3. What is the purpose of the statement updating the rdb$relations
table? This will disappear with a backup and restore.

./heLen

./heLen