Your additional explanation helps clarify things, thanks.

Regardless, my suggestion still stands: *if* you/your application are
inserting data into a Firebird DB, *the inserting application* can read
the schema and trim accordingly. No idea if you have to search and
replace raw SQL strings etc but it should be possible.

I really have trouble seeing why using a temp table with relaxed size
limits/BLOBs would work and the solution I proposed wouldn't - unless
you have no control over the application that inserts data into the
Firebird db.... but apparently you do have control over the Firebird
database because you can modify stored procs etc?!?!

Also, I have trouble envisaging situations where:
1. truncating data without error message rather than sending at least a
warning back to the originator would make sense - apparently the
integrity of the data is irrelevant?!?!
2. other database systems wouldn't face similar problems

However if you're happy with your solution, fine by me ;)

On 17-1-2013 12:57, fabianchocron wrote:
> The application is actually a protocol and it does not have permission
> to read the db schema. Think it as an object, the DB schema is private,
> not accessible to the App. And the App should not need to know the DB
> Schema as it is a generic protocol, it transfers data across from sender
> to receiver, without knowing the size of each field on the recipient's
> DB. Furthermore, the App does not even know if there is a DB on the
> other side, all it knows is that it is "agreed" between sender and
> receiver to use SQL language, insert sentence, to send data to the
> receiver inside the insert statement. I know everybody assumed there are
> a lot of "normal things" that should apply, such as the developer
> knowing the DB schema, but unfortunately this is a very generic
> application, used across the world by thousands of different companies,
> with different languages, platforms, DB engines, etc. Some recipient
> would just have a plain text file to save the data, but use the SQL
> format to communicate, even when there is not SQL engine there, just a
> program parsing the insert statement and getting the strings and other
> data types into a disk file. The sad thing is that we could achieve the
> results if we bypass Firebird and just save the data into txt files on
> disk, but we are not going to go that way thanks to the brilliant idea
> of using a temporary table with BLOBS and transmitting back to the real
> table via trigger after insert.
>
> Cheers,
> Fabian
>
> --- In firebird-support@yahoogroups.com
> <mailto:firebird-support%40yahoogroups.com>, Reinier Olislagers wrote:
>>
>> On 17-1-2013 4:35, fabianchocron wrote:
>> > What we are trying to achieve is to have a trigger that reads the size
>> > of each field and trims all insert sentences to the size of the field
>> > being inserted, and the reason is because the application receives the
>> > strings to insert on database from many different systems across the
>> > world and it is not aware of the current size of each field, the
>> > application's job is to receive data via internet and post it to the DB
>> > via insert command. So the App is doing what it was designed to do. The
>> > DB's fields size cannot be increased every time a new system across the
>> > world is connected to the application, and we cannot just increase field
>> > sizes to the maximum because it will slowdown the system's response. So
>> > we planned to use triggers to trim the input but it seems impossible.
>> > The alternative would be to let the App know the DB schema upfront, and
>> > deal with the issue at the App level, but we were under the impression
>> > the problem could be sorted via triggers, it appears it is not possible.
>>
>> Why do you need to "let the app know the DB schema"? Can't the
>> application query the DB schema tables (RDB$*) itself to find out field
>> lengths - e.g. once when the application starts?