Subject Re: Issues with String Truncation error on insert (FB 2.1 64 bits windows server)
Author fabianchocron
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.


--- In, 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?