Subject Re: [firebird-support] Issues with String Truncation error on insert (FB 2.1 64 bits windows server)
Author Mark Rotteveel
On Wed, 16 Jan 2013 04:36:36 -0000, "fabianchocron" <fabianch@...>
> Hi All,
> We are trying to resolve an issue caused by the Application inserting a
> string larger than the database field size. The planned solution was to
> a trigger before insert as follows:
> new.string = substring(new.string from 1 for 100);
> We did not modify the application because we don't have at this stage
> ability to recompile it without a massive effort.
> For some reason that we do not understand the trigger is not working as
> desired, and the insert fails with an arithmetic overflow or string
> truncation error. Is is possible that Firebird 2.1 64 bits has a bug and
> may need to upgrade to resolve this issue? Or are we doing something

This is intentional behaviour (I believe it is even specified in the SQL
specification, but not 100% sure on that). If you submit data longer than
can be stored, it will result in an exception because otherwise it would
result in loss of data. Only the client program would know how to deal with
this. A workaround would be to use an updatable view and handle the
transformation in the view trigger, however you can still in run into
problems when you exceed the maximum CHAR or VARCHAR lengths.