To answer your questions:

1) Both are Dialect 3 databases.
2) As for the rdb$relations statement, I dunno. That was created by IB
Workbench when extracting the DDL for the table. I found it interesting
myself, but thought it was probably harmless. I have a pretty good amount
of trust in IB Workbench and Database Workbench.
3) I specified the collation just to do it. I was not aware it was not
required for ASCII
4) I was using NONE as a characterset before and was continually
encountering transliteration errors for ASCII characters in the upper
ranges.
5) I did NOT KNOW that the ASCII character set only stores up to 127. I
thought that would solve my transliteration error quite nicely. Apparently
I am mistaken. As for users, that is not a problem since users never change
or add data. Data is added with scripts and SP's. Since I was getting
transliteration errors using NONE for the characterset, I developed a
cleanup tool to strip 36 specific characters from any data field that would
be imported. 0-9 and A-Z. UCASE() is performed on it beforehand. I
beleived that with the ASCII charater set I could stop doing that... Oh
well. If you can recommend a character set that will encompass all of the
extended characters, I would appreciate it.
6) The NEW.Parent_ID does not cause an error. I have noticed that it
automatically cast the statements for me. It also worked in the other DB
fine for weeks now. I assume that it becuase of Dialect 3?
****************************************************************************
***
7) I actually found the error by tracing every action, insert to trigger to
trigger etc. The database did NOT transfer over intact at all. The meta
data backup set the Generators all to 134,853,152. Now I don't understand
why that would not work, but after changing them back to their original
values (the highest being 500K approx), everything started to work again.
Isn't an integer (signed) supposed to be able to store up to 2.4 billion?

-----Original Message-----
From: Helen Borrie [mailto:helebor@...]
Sent: Wednesday, February 23, 2005 3:58 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] RE: INSERT ERROR



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





Yahoo! Groups Links