[firebird-support] Re: Issues with String Truncation error on insert (FB 2.1 64 bits windows server)

[Dmitry Yemanov]

16.01.2013 8:43, fabianchocron wrote:
>
> 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
> add a trigger before insert as follows:
> new.string = substring(new.string from 1 for 100);

This is not going to work.

> 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
> we may need to upgrade to resolve this issue?

OLD/NEW pseudo-records have the same format as regular table records, 
inheriting the same length limits. And this is not a bug.


Dmitry

[firebird-support] Re: Issues with String Truncation error on insert (FB 2.1 64 bits windows server)

[fabianchocron]

> OLD/NEW pseudo-records have the same format as regular table
> records, inheriting the same length limits. And this is not a bug.

Hi Dmitry

mmm, it does not make sanse to me, think it this way:

The field on table is say char 32
The insert is trying to insert a string of 40 characters, say "AAA..." repeated 
40 times
What you are saying is the old and new records are char (32), and that is fine, 
as the trigger is assigning a string of say 31 characters into a char 32. Why 
is it failing?

the instruction on the trigger is:

new.mychar = substring(new.mychar from 1 for 31)

I don't see where the code is breaching the rules?

Unless Firebird is comparing the size of the original string posted in the 
insert instruction against the database, and that to me sounds like a bug.

Regards
Fabian




--- In firebird-support@yahoogroups.com, Dmitry Yemanov  wrote:
>
> 16.01.2013 8:43, fabianchocron wrote:
> >
> > 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
> > add a trigger before insert as follows:
> > new.string = substring(new.string from 1 for 100);
> 
> This is not going to work.
> 
> > 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
> > we may need to upgrade to resolve this issue?
> 
> OLD/NEW pseudo-records have the same format as regular table records, 
> inheriting the same length limits. And this is not a bug.
> 
> 
> Dmitry
>

[firebird-support] Re: Issues with String Truncation error on insert (FB 2.1 64 bits windows server)

[fabianchocron]

I have trouble understanding the following:

Are you saying that when the application posts the "insert statement" into 
firebird's dll the size of the string contained in the insert statment is 
measured by firebird's engine and that size is what is being compared against 
the size of the field on the table? If that is the case then I am cooked/fried.

The application is written in visual basic 6 and the insert in executed 
multiple times without any issues until the contents of the text inserted is 
larger than the field definition in Firebird, so what I am trying to do is to 
"catch" the string before saved on the DB and trim it to (field size - 1).

Do you think it could be any way to sort out the issue without modifying and 
recompiling firebird engine?

Regards,
Fabian





--- In firebird-support@yahoogroups.com, Ann Harrison  wrote:
>
> On Wed, Jan 16, 2013 at 6:08 AM, fabianchocron  wrote:
> 
> >
> >
> > 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 add
> > a trigger before insert as follows:
> >  new.string = substring(new.string from 1 for 100);
> >
> 
> Unfortunately, the check for the length of new.string happens when the
> value is inserted into the record buffer, not when it's stored in the
> database, so your trigger isn't going to work.   The buffer is based on the
> table definition.  Have you considered increasing the size of the field?
> 
> Good luck,
> 
> Ann
> 
> 
> [Non-text portions of this message have been removed]
>

[firebird-support] Re: Issues with String Truncation error on insert (FB 2.1 64 bits windows server)

[fabianchocron]

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.


Regards
Fabian




--- In firebird-support@yahoogroups.com, Robert martin  wrote:
>
> Hi
> 
> What about Helens suggestion to increase the field size.  If you must 
> have a max of 100 characters in the field, you could still increase the 
> field size and then your trigger should work. Otherwise it sounds like 
> you are cooked/deep fried !
> 
> Cheers
> Rob
> 
> On 17/01/2013 2:21 p.m., fabianchocron wrote:
> >
> > I have trouble understanding the following:
> >
> > Are you saying that when the application posts the "insert statement" 
> > into firebird's dll the size of the string contained in the insert 
> > statment is measured by firebird's engine and that size is what is 
> > being compared against the size of the field on the table? If that is 
> > the case then I am cooked/fried.
> >
> > The application is written in visual basic 6 and the insert in 
> > executed multiple times without any issues until the contents of the 
> > text inserted is larger than the field definition in Firebird, so what 
> > I am trying to do is to "catch" the string before saved on the DB and 
> > trim it to (field size - 1).
> >
> > Do you think it could be any way to sort out the issue without 
> > modifying and recompiling firebird engine?
> >
> > Regards,
> > Fabian
> >
>

[firebird-support] Re: Issues with String Truncation error on insert (FB 2.1 64 bits windows server)

[fabianchocron]

Helen

Thank you, the problem is our plan was to have an App that DOES NOT need to 
know the DB schema to work, so the insert statements were planned to just post 
data to the DB and let the DB handle the trimming. That way the App is generic 
and works regardless of DB changes. 

My idea of recompiling the FB client was to "capture" any insert statement on 
the customers side, and replace it with a trimmed insert statement. I don't see 
an issue with that. I am not sure why you think it is not possible. 
Alternatively we could capture the insert statements on the server side, and 
replace the original insert with a trimmed sentence using the DB schema to know 
the size to use for each field. I was hopping to be able to resolve it without 
a re-compile of the DB engine, but it seems I was wrong.

Regarding the DB doing it's job in checking and protecting against arithmetic 
overflow, I still think the way it is done is a Bug as it is actually checking 
the size of a field that is NOT what is being inserted because the trigger has 
actually changed already the size of the field to be inserted, so the DB is 
actually checking something that is irrelevant. I don't know if it was designed 
to be that way for a reason, or if it was something that could be "re-designed" 
to compare the field actually being inserted after all triggers have being 
applied. Under the current model, what would happened if we have a trigger 
before insert to update the field in a way that it does not feet any-more into 
the target table? The checking and validation was done before the trigger, so I 
guess it did not pick up the issue and who know what was actually inserted?

Regards,
Fabian







--- In firebird-support@yahoogroups.com, Helen Borrie  wrote:
>
> At 02:28 p.m. 17/01/2013, fabianchocron wrote:
> >> OLD/NEW pseudo-records have the same format as regular table
> >> records, inheriting the same length limits. And this is not a bug.
> >
> >Hi Dmitry
> >
> >mmm, it does not make sanse to me, think it this way:
> >
> >The field on table is say char 32
> >The insert is trying to insert a string of 40 characters, say "AAA..." 
> >repeated 40 times
> >What you are saying is the old and new records are char (32), and that is 
> >fine, as the trigger is assigning a string of say 31 characters into a char 
> >32. Why is it failing?
> 
> It is failing for the reason explained by Dmitry and Ann:  the NEW variable 
> has the same size as the column definition - in your example, char(32).  
> Therefore, there is no way to store a string of 40 characters in the NEW 
> variable.  The exception occurs *before* the context variables are created, 
> do you see?
> 
> 
> >the instruction on the trigger is:
> >
> >new.mychar = substring(new.mychar from 1 for 31)
> >
> >I don't see where the code is breaching the rules?
> 
> OK, one more time.  NEW.MYCHAR is defined by the size of the column named 
> MYCHAR in your table.  Your substring expression cannot work because the 
> exception happens in the parsing of the dynamic value passed in the INSERT 
> statement.
> 
> >Unless Firebird is comparing the size of the original string posted in the 
> >insert instruction against the database, and that to me sounds like a bug.
> 
> 
> You think it is a bug if a database engine protects databases from string 
> overflow? Oh dear, dear, you need to take some deep breaths, methinks.
> 
> Triggers work in database operations, not in the client. 
> 
> ./heLen
>

[firebird-support] Re: Issues with String Truncation error on insert (FB 2.1 64 bits windows server)

[karolbieniaszewski]

> Regarding the DB doing it's job in checking and protecting against arithmetic 
> overflow, I still think the way it is done is a Bug as it is actually 
> checking the size of a field that is NOT what is being inserted because the 
> trigger has actually changed already the size of the field to be inserted, so 
> the DB is actually checking something that is irrelevant. I don't know if it 
> was designed to be that way for a reason, or if it was something that could 
> be "re-designed" to compare the field actually being inserted after all 
> triggers have being applied. Under the current model, what would happened if 
> we have a trigger before insert to update the field in a way that it does not 
> feet any-more into the target table? The checking and validation was done 
> before the trigger, so I guess it did not pick up the issue and who know what 
> was actually inserted?
> 
> Regards,
> Fabian


Hi,

You are wrong 
think about float field not varchar
and you client put in insert statement data 'blah blah blah'
do you really think that this should be transferred to db context trigger? How 
you then declare vars in trigger - maximum possible varchar size, blob...

This is client side context to check this if not then db return error.
The problem is that you not tell your customers what is accepted format for 
string data in your system.

If they create wrong statement they should get error response and finish.



regards,
Karol Bieniaszewski

[firebird-support] Re: Issues with String Truncation error on insert (FB 2.1 64 bits windows server)

[Svein Erling Tysvær]

>Thank you, the problem is our plan was to have an App that DOES NOT need to 
>know the DB schema to work,
>so the insert statements were planned to just post data to the DB and let the 
>DB handle the trimming. 
>That way the App is generic and works regardless of DB changes. 
>
>My idea of recompiling the FB client was to "capture" any insert statement on 
>the customers side, and 
>replace it with a trimmed insert statement. I don't see an issue with that. I 
>am not sure why you think
>it is not possible. Alternatively we could capture the insert statements on 
>the server side, and 
>replace the original insert with a trimmed sentence using the DB schema to 
>know the size to use for 
>each field. I was hopping to be able to resolve it without a re-compile of the 
>DB engine, but it seems 
>I was wrong.
>
>Regarding the DB doing it's job in checking and protecting against arithmetic 
>overflow, I still think
>the way it is done is a Bug as it is actually checking the size of a field 
>that is NOT what is being 
>inserted because the trigger has actually changed already the size of the 
>field to be inserted, so the
>DB is actually checking something that is irrelevant. I don't know if it was 
>designed to be that way
>for a reason, or if it was something that could be "re-designed" to compare 
>the field actually being
>inserted after all triggers have being applied. Under the current model, what 
>would happened if we have
>a trigger before insert to update the field in a way that it does not feet 
>any-more into the target 
>table? The checking and validation was done before the trigger, so I guess it 
>did not pick up the issue
>and who know what was actually inserted?

Hi Fabian!

I don't think telling the user that the field is too long to handle can be 
considered a bug. Sure, it would be nice if Firebird had infinitely sized 
buffers and didn't check the size until after the BEFORE INSERT trigger had 
finished, but it doesn't. First, Firebird receives data, then BEFORE triggers 
are executed, then the data is stored in the database. The error that you see 
occurs before the BEFORE INSERT trigger.

Your problem is still solvable. Simply add another table. Let one table have 
huge field lengths (CHAR/VARCHAR or BLOB SUBTYPE TEXT) and use this only for 
imports, then use AFTER INSERT triggers to copy the first few characters of 
field(s) to the other table (which you in turn use for querying). Variations of 
this solution includes adding another field rather than table or even another 
database ('another database' would probably be an overkill and more difficult 
to implement in triggers).

HTH,
Set

[firebird-support] Re: Issues with String Truncation error on insert (FB 2.1 64 bits windows server)

[karolbieniaszewski]

> 
> Your problem is still solvable. Simply add another table. Let one table have 
> huge field lengths (CHAR/VARCHAR or BLOB SUBTYPE TEXT) and use this only for 
> imports, then use AFTER INSERT triggers to copy the first few characters of 
> field(s) to the other table (which you in turn use for querying). Variations 
> of this solution includes adding another field rather than table or even 
> another database ('another database' would probably be an overkill and more 
> difficult to implement in triggers).
> 
> HTH,
> Set
>

Do you really think that he should do this?
i do not know what do that system
but why you need to truncate data provided by your customer?

Think abut this system:
1. Customer can put an order for product 
and write in order info text:
"I need 200 red boots .. but only if you can send it to me in two days"
2. your system store truncated data
"I need 200 red boots"
3. and you send a request by week
4. what do customer - say "i do not need this now, get it back"

regards,
Karol Bieniaszewski

[firebird-support] Re: Issues with String Truncation error on insert (FB 2.1 64 bits windows server)

[Svein Erling Tysvær]

>> Your problem is still solvable. Simply add another table. Let one table have 
>> huge field lengths 
>>(CHAR/VARCHAR or BLOB SUBTYPE TEXT) and use this only for imports, then use 
>>AFTER INSERT triggers 
>>to copy the first few characters of field(s) to the other table (which you in 
>>turn use for querying). 
>>Variations of this solution includes adding another field rather than table 
>>or even another database
>> ('another database' would probably be an overkill and more difficult to 
>> implement in triggers).

>Do you really think that he should do this?
>i do not know what do that system
>but why you need to truncate data provided by your customer?
>
>Think abut this system:
>1. Customer can put an order for product and write in order info text:
>"I need 200 red boots .. but only if you can send it to me in two days"
>2. your system store truncated data
>"I need 200 red boots"
>3. and you send a request by week
>4. what do customer - say "i do not need this now, get it back"

Hi Karol!

I know nothing about his system, of course it would be ridiculous to truncate 
in the example you mention. Though there are other situations where it could 
make sense: Imagine a medical system using ICD-10 codes. Typically they would 
be 4 or 6 characters each. Now, suppose you received such codes in a long 
string from various hospitals. You know that each patient has one line and that 
the codes are ordered by decreasing importance. A patient can have lots of 
simultaneous diagnosis and they may all be important for the doctors treating 
the patient. However, for the statistical report that you are supposed to 
create from the files they send you, it is OK to only care about the five or 
six most important diagnoses.

Whether Fabian should truncate data or not, all depends on what kind of data he 
receives and what they are to be used for. The question I tried to answer was 
whether it was possible to truncate, not whether it was sensible in his case.

Another way that I think I've "truncated" before, is to use a trigger populated 
field that I can index (as you know, Firebird indexes have limited length). 
Though that is only to speed up searches and not a field the users ever see.

Set

[firebird-support] Re: Issues with String Truncation error on insert (FB 2.1 64 bits windows server)

[fabianchocron]

Hi Set,

The system we have is a "protocol" to connect and transfer data across multiple 
different systems, and as each system has a different DB schema we planed the 
solution reading from source DB and writing into the other DB without 
restrictions, and left the trimming for the DB on each target system to handle. 
It appears the idea was BAD, but using the "intermediary" table with triggers 
after insert could end up being the solution we are after, so THANK YOU for 
thinking outside the box and sharing it. I would have preferred to see a 
trigger before insert handling the issue, but I understand why the DB currently 
allocates the new and old fields based on the DB schema, and why it would be 
too hard to try to write a smarted method to sort out this issue. 

Regards
Fabian


--- In firebird-support@yahoogroups.com, Svein Erling Tysvær  wrote:
>
> >Thank you, the problem is our plan was to have an App that DOES NOT need to 
> >know the DB schema to work,
> >so the insert statements were planned to just post data to the DB and let 
> >the DB handle the trimming. 
> >That way the App is generic and works regardless of DB changes. 
> >
> >My idea of recompiling the FB client was to "capture" any insert statement 
> >on the customers side, and 
> >replace it with a trimmed insert statement. I don't see an issue with that. 
> >I am not sure why you think
> >it is not possible. Alternatively we could capture the insert statements on 
> >the server side, and 
> >replace the original insert with a trimmed sentence using the DB schema to 
> >know the size to use for 
> >each field. I was hopping to be able to resolve it without a re-compile of 
> >the DB engine, but it seems 
> >I was wrong.
> >
> >Regarding the DB doing it's job in checking and protecting against 
> >arithmetic overflow, I still think
> >the way it is done is a Bug as it is actually checking the size of a field 
> >that is NOT what is being 
> >inserted because the trigger has actually changed already the size of the 
> >field to be inserted, so the
> >DB is actually checking something that is irrelevant. I don't know if it was 
> >designed to be that way
> >for a reason, or if it was something that could be "re-designed" to compare 
> >the field actually being
> >inserted after all triggers have being applied. Under the current model, 
> >what would happened if we have
> >a trigger before insert to update the field in a way that it does not feet 
> >any-more into the target 
> >table? The checking and validation was done before the trigger, so I guess 
> >it did not pick up the issue
> >and who know what was actually inserted?
> 
> Hi Fabian!
> 
> I don't think telling the user that the field is too long to handle can be 
> considered a bug. Sure, it would be nice if Firebird had infinitely sized 
> buffers and didn't check the size until after the BEFORE INSERT trigger had 
> finished, but it doesn't. First, Firebird receives data, then BEFORE triggers 
> are executed, then the data is stored in the database. The error that you see 
> occurs before the BEFORE INSERT trigger.
> 
> Your problem is still solvable. Simply add another table. Let one table have 
> huge field lengths (CHAR/VARCHAR or BLOB SUBTYPE TEXT) and use this only for 
> imports, then use AFTER INSERT triggers to copy the first few characters of 
> field(s) to the other table (which you in turn use for querying). Variations 
> of this solution includes adding another field rather than table or even 
> another database ('another database' would probably be an overkill and more 
> difficult to implement in triggers).
> 
> HTH,
> Set
>

[firebird-support] Re: Issues with String Truncation error on insert (FB 2.1 64 bits windows server)

[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.

Cheers,
Fabian







--- In firebird-support@yahoogroups.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?
>

[firebird-support] Re: Issues with String Truncation error on insert (FB 2.1 64 bits windows server)

[fabianchocron]

Mark

Thank you, I understand the need for the DB to return to the requester the 
exception but you have to admit from the high level perspective it sounds 
ridiculous to report an error on data that has not yet being "prepared" by the 
triggers. After all what's the logic in validating raw data when we know there 
are triggers waiting to massage the data before posting it to the engine? Even 
if the raw data is valid, the triggers could then modify it and make it 
"non-compliant" so from the high level point of view it seems the validation 
process is not efficient, it is effective but is checks things before there are 
ready. Now from the Firebird's developer perspective, I do understand 100% they 
need to allocate the memory for a variable and that variable needs to be 
inherited from somewhere.
Perhaps in the future this issue will come to a head in some developers meeting 
and a solution would be found.

If you think it breaking the problem by level, it is easier to see the issue, 
let's think it this way:
1) Level Requester, where the insert statement is created, or App.
2) Level Massaging where the triggers occur before being ready to pass to the 
data saving process.
3) Level Archiving, where the DB validates and either saves or returns error to 
requester.

The key is understanding that in 1) and 2) there is still "change occurring" 
while 3) is a black or white outcome, where either the request can be inserted 
or it is "illegal" and fails. At the moment the DB is returning an error on a 
request that has being posted by 1) but not being processed by 2), so it seems 
inefficient to report on an issue before allowing 2) to take care of it.

Cheers
Fabian

--- In firebird-support@yahoogroups.com, Mark Rotteveel  wrote:
>
> On Wed, 16 Jan 2013 04:36:36 -, "fabianchocron" 
> wrote:
> > 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
> add
> > 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
> the
> > 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
> we
> > may need to upgrade to resolve this issue? Or are we doing something
> wrong?
> 
> 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.
> 
> Mark
>

[firebird-support] Re: Issues with String Truncation error on insert (FB 2.1 64 bits windows server)

[karolbieniaszewski]

--- In firebird-support@yahoogroups.com, "fabianchocron"  wrote:
>
> Mark
> 
> Thank you, I understand the need for the DB to return to the requester the 
> exception but you have to admit from the high level perspective it sounds 
> ridiculous to report an error on data that has not yet being "prepared" by 
> the triggers. After all what's the logic in validating raw data when we know 
> there are triggers waiting to massage the data before posting it to the 
> engine? Even if the raw data is valid, the triggers could then modify it and 
> make it "non-compliant" so from the high level point of view it seems the 
> validation process is not efficient, it is effective but is checks things 
> before there are ready. Now from the Firebird's developer perspective, I do 
> understand 100% they need to allocate the memory for a variable and that 
> variable needs to be inherited from somewhere.
> Perhaps in the future this issue will come to a head in some developers 
> meeting and a solution would be found.
> 
> If you think it breaking the problem by level, it is easier to see the issue, 
> let's think it this way:
> 1) Level Requester, where the insert statement is created, or App.
> 2) Level Massaging where the triggers occur before being ready to pass to the 
> data saving process.
> 3) Level Archiving, where the DB validates and either saves or returns error 
> to requester.
> 
> The key is understanding that in 1) and 2) there is still "change occurring" 
> while 3) is a black or white outcome, where either the request can be 
> inserted or it is "illegal" and fails. At the moment the DB is returning an 
> error on a request that has being posted by 1) but not being processed by 2), 
> so it seems inefficient to report on an issue before allowing 2) to take care 
> of it.
> 
> Cheers
> Fabian
> 


I have a good mood today so here is a solution to your problem ;-)

CREATE TABLE REAL_TABLE
(
REAL_FIELD VARCHAR(60)
);

CREATE TABLE AAA
(
XXX BLOB SUB_TYPE TEXT
);

CREATE TRIGGER TRIG_BI_AAA FOR AAA 
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  INSERT INTO REAL_TABLE(REAL_FIELD) VALUES(SUBSTRING(NEW.XXX FROM 1 FOR 60));
  NEW.BLOB = NULL;
  DELETE FROM AAA;
END;


Regards,
Karol Bieniaszewski

[firebird-support] Re: Issues with String Truncation error on insert (FB 2.1 64 bits windows server)

[karolbieniaszewski]

--- In firebird-support@yahoogroups.com, "Bogdan"  wrote:
>
> >Mark
> 
> >Thank you, I understand the need for the DB to return to the requester the
> exception but you have to admit from the high level perspective it sounds
> ridiculous to report an error on >data that has not yet being "prepared" by
> the triggers. After all what's the logic in validating raw data when we know
> there are triggers waiting to massage the data before posting it >to the
> engine? Even if the raw data is valid, the triggers could then modify it and
> make it "non-compliant" so from the high level point of view it seems the
> validation process is not >efficient, it is effective but is checks things
> before there are ready. Now from the Firebird's developer perspective, I do
> understand 100% they need to allocate the memory for a >variable and that
> variable needs to be inherited from somewhere.
> >Perhaps in the future this issue will come to a head in some developers
> meeting and a solution would be found.
> 
> >If you think it breaking the problem by level, it is easier to see the
> issue, let's think it this way:
> >1) Level Requester, where the insert statement is created, or App.
> >2) Level Massaging where the triggers occur before being ready to pass to
> the data saving process.
> >3) Level Archiving, where the DB validates and either saves or returns
> error to requester.
> 
> >The key is understanding that in 1) and 2) there is still "change
> occurring" while 3) is a black or white outcome, where either the request
> can be inserted or it is "illegal" and fails. At >the moment the DB is
> returning an error on a request that has being posted by 1) but not being
> processed by 2), so it seems inefficient to report on an issue before
> allowing 2) to take >care of it.
> 
> >Cheers
> >Fabian
> 
> Hi Fabian
> 
>  
> 
> I would go with stored procedure with input parameter 32K. Inside stored
> procedure, you can do whatever you want.
> 
> Or am i missing something ?
> 
>   
> 
> Regards Bogdan
> 
>  

Hi,

If he can use stored procedure then i believe better is to change client side 
logic

i understand him previously that customer provide him whole insert statement 
and changing something now in application logic is problematic 

with helper table and trigger (as i previously posted) this is simple to fix on 
db side

Regards,
Karol Bieniaszewski

[firebird-support] Re: Issues with String Truncation error on insert (FB 2.1 64 bits windows server)

[fabianchocron]

Hi Karol

THANK YOU VERY VERY MUCH - Muchas Gracias  Obrigado!

We are going to apply this solution you suggested, we need to do so for every 
field in the protocol so it will be a bit more generic and complex, but the 
concept is what matters, and this also shows that thinking outside the box is 
the key.

Cheers,
Fabian



--- In firebird-support@yahoogroups.com, "karolbieniaszewski"  wrote:
>
> 
> 
> --- In firebird-support@yahoogroups.com, "fabianchocron"  wrote:
> >
> > Mark
> > 
> > Thank you, I understand the need for the DB to return to the requester the 
> > exception but you have to admit from the high level perspective it sounds 
> > ridiculous to report an error on data that has not yet being "prepared" by 
> > the triggers. After all what's the logic in validating raw data when we 
> > know there are triggers waiting to massage the data before posting it to 
> > the engine? Even if the raw data is valid, the triggers could then modify 
> > it and make it "non-compliant" so from the high level point of view it 
> > seems the validation process is not efficient, it is effective but is 
> > checks things before there are ready. Now from the Firebird's developer 
> > perspective, I do understand 100% they need to allocate the memory for a 
> > variable and that variable needs to be inherited from somewhere.
> > Perhaps in the future this issue will come to a head in some developers 
> > meeting and a solution would be found.
> > 
> > If you think it breaking the problem by level, it is easier to see the 
> > issue, let's think it this way:
> > 1) Level Requester, where the insert statement is created, or App.
> > 2) Level Massaging where the triggers occur before being ready to pass to 
> > the data saving process.
> > 3) Level Archiving, where the DB validates and either saves or returns 
> > error to requester.
> > 
> > The key is understanding that in 1) and 2) there is still "change 
> > occurring" while 3) is a black or white outcome, where either the request 
> > can be inserted or it is "illegal" and fails. At the moment the DB is 
> > returning an error on a request that has being posted by 1) but not being 
> > processed by 2), so it seems inefficient to report on an issue before 
> > allowing 2) to take care of it.
> > 
> > Cheers
> > Fabian
> > 
> 
> 
> I have a good mood today so here is a solution to your problem ;-)
> 
> CREATE TABLE REAL_TABLE
> (
> REAL_FIELD VARCHAR(60)
> );
> 
> CREATE TABLE AAA
> (
> XXX BLOB SUB_TYPE TEXT
> );
> 
> CREATE TRIGGER TRIG_BI_AAA FOR AAA 
> ACTIVE BEFORE INSERT POSITION 0
> AS
> BEGIN
>   INSERT INTO REAL_TABLE(REAL_FIELD) VALUES(SUBSTRING(NEW.XXX FROM 1 FOR 60));
>   NEW.BLOB = NULL;
>   DELETE FROM AAA;
> END;
> 
> 
> Regards,
> Karol Bieniaszewski
>

Re: [firebird-support] Re: Issues with String Truncation error on insert (FB 2.1 64 bits windows server)

[Helen Borrie]

At 02:28 p.m. 17/01/2013, fabianchocron wrote:
>> OLD/NEW pseudo-records have the same format as regular table
>> records, inheriting the same length limits. And this is not a bug.
>
>Hi Dmitry
>
>mmm, it does not make sanse to me, think it this way:
>
>The field on table is say char 32
>The insert is trying to insert a string of 40 characters, say "AAA..." 
>repeated 40 times
>What you are saying is the old and new records are char (32), and that is 
>fine, as the trigger is assigning a string of say 31 characters into a char 
>32. Why is it failing?

It is failing for the reason explained by Dmitry and Ann:  the NEW variable has 
the same size as the column definition - in your example, char(32).  Therefore, 
there is no way to store a string of 40 characters in the NEW variable.  The 
exception occurs *before* the context variables are created, do you see?


>the instruction on the trigger is:
>
>new.mychar = substring(new.mychar from 1 for 31)
>
>I don't see where the code is breaching the rules?

OK, one more time.  NEW.MYCHAR is defined by the size of the column named 
MYCHAR in your table.  Your substring expression cannot work because the 
exception happens in the parsing of the dynamic value passed in the INSERT 
statement.

>Unless Firebird is comparing the size of the original string posted in the 
>insert instruction against the database, and that to me sounds like a bug.


You think it is a bug if a database engine protects databases from string 
overflow? Oh dear, dear, you need to take some deep breaths, methinks.

Triggers work in database operations, not in the client. 

./heLen

Re: [firebird-support] Re: Issues with String Truncation error on insert (FB 2.1 64 bits windows server)

[Robert martin]

Hi

What about Helens suggestion to increase the field size.  If you must 
have a max of 100 characters in the field, you could still increase the 
field size and then your trigger should work. Otherwise it sounds like 
you are cooked/deep fried !

Cheers
Rob

On 17/01/2013 2:21 p.m., fabianchocron wrote:
>
> I have trouble understanding the following:
>
> Are you saying that when the application posts the "insert statement" 
> into firebird's dll the size of the string contained in the insert 
> statment is measured by firebird's engine and that size is what is 
> being compared against the size of the field on the table? If that is 
> the case then I am cooked/fried.
>
> The application is written in visual basic 6 and the insert in 
> executed multiple times without any issues until the contents of the 
> text inserted is larger than the field definition in Firebird, so what 
> I am trying to do is to "catch" the string before saved on the DB and 
> trim it to (field size - 1).
>
> Do you think it could be any way to sort out the issue without 
> modifying and recompiling firebird engine?
>
> Regards,
> Fabian
>

Re: [firebird-support] Re: Issues with String Truncation error on insert (FB 2.1 64 bits windows server)

[Reinier Olislagers]

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?

Re: [firebird-support] Re: Issues with String Truncation error on insert (FB 2.1 64 bits windows server)

[Reinier Olislagers]

On 17-1-2013 9:14, karolbieniaszewski wrote:
> i do not know what do that system
> but why you need to truncate data provided by your customer?
> 
> Think abut this system:
> 1. Customer can put an order for product
> and write in order info text:
> "I need 200 red boots .. but only if you can send it to me in two days"
> 2. your system store truncated data
> "I need 200 red boots"
> 3. and you send a request by week
> 4. what do customer - say "i do not need this now, get it back"

Based on the thread so far I suspect this could indeed be a problem.

However, in theory, the OP could have some error feedback/exception
reporting mechanism in his application that rolls back the transaction
on errors and reports back to the originating application if truncation
is unacceptable...
Going by the OP's posts, he thinks truncating data actually makes sense
given his business needs - no idea if all data may be truncated or only
some, etc.

RE: [firebird-support] Re: Issues with String Truncation error on insert (FB 2.1 64 bits windows server)

[Bogdan]

>Mark

>Thank you, I understand the need for the DB to return to the requester the
exception but you have to admit from the high level perspective it sounds
ridiculous to report an error on >data that has not yet being "prepared" by
the triggers. After all what's the logic in validating raw data when we know
there are triggers waiting to massage the data before posting it >to the
engine? Even if the raw data is valid, the triggers could then modify it and
make it "non-compliant" so from the high level point of view it seems the
validation process is not >efficient, it is effective but is checks things
before there are ready. Now from the Firebird's developer perspective, I do
understand 100% they need to allocate the memory for a >variable and that
variable needs to be inherited from somewhere.
>Perhaps in the future this issue will come to a head in some developers
meeting and a solution would be found.

>If you think it breaking the problem by level, it is easier to see the
issue, let's think it this way:
>1) Level Requester, where the insert statement is created, or App.
>2) Level Massaging where the triggers occur before being ready to pass to
the data saving process.
>3) Level Archiving, where the DB validates and either saves or returns
error to requester.

>The key is understanding that in 1) and 2) there is still "change
occurring" while 3) is a black or white outcome, where either the request
can be inserted or it is "illegal" and fails. At >the moment the DB is
returning an error on a request that has being posted by 1) but not being
processed by 2), so it seems inefficient to report on an issue before
allowing 2) to take >care of it.

>Cheers
>Fabian

Hi Fabian

 

I would go with stored procedure with input parameter 32K. Inside stored
procedure, you can do whatever you want.

Or am i missing something ?

  

Regards Bogdan

 



[Non-text portions of this message have been removed]

Re: [firebird-support] Re: Issues with String Truncation error on insert (FB 2.1 64 bits windows server)

[Reinier Olislagers]

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





++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/