[Firedac] Truncation error on Firebird select query

[Jacek Laskowski 57]

I have simple table:

 

CREATE TABLE WORKSTATION
(
  IDWORKSTATION        INTEGER,
  MACHINEID            VARCHAR(32)
);

 

I create TFDQuery with simple select SQL:

 

SELECT * FROM WORKSTATION w
WHERE W.MACHINEID = :PARAM

 

When I set as param string longer than 32 chars then I get error:

 

"arithmetic exception, numeric overflow, or string truncation"

 

I set StrsTrim2Len option to True:

 

http://docwiki.embarcadero.com/Libraries/Rio/en/FireDAC.Stan.Option.TFDFormatOptions.StrsTrim2Len

 

...but the error still occurs.

 

How to properly config Firedac to eliminate this problem?

Edited April 8, 2019 by Jacek Laskowski

[Lars Fosdal 1793]

I assume the problem does not happen if you trim :PARAM manually?

Could it be that StrsTrim2Len only deals with insert/update parameterization and not selects?

[Jacek Laskowski 57]

10 minutes ago, Lars Fosdal said:

I assume the problem does not happen if you trim :PARAM manually?

Yes

 

10 minutes ago, Lars Fosdal said:

Could it be that StrsTrim2Len only deals with insert/update parameterization and not selects? 

Yes

[Jacek Laskowski 57]

Correction, Insert does not work either 😕

 

Before query execution (param value is longer than column size):

 

After F9:

 

 

 

Delphi Tokyo, FB 2.5.8.

 

Does the StrsTrim2Len option work at all?

 

[Lars Fosdal 1793]

That looks like a bug.  Create a QP entry, perhaps?

[Jacek Laskowski 57]

Maybe this is corrected in Rio? Can anyone check it out in Rio?

[Jacek Laskowski 57]

I debug FD and I found suspected place:

 

 

and DataSize method code:
 

function TIBVariable.GetDataSize: LongWord;
begin
  if FDataSize = 0 then
    Result := sqllen
  else
    Result := FDataSize;
end;

But why result (sqllen) is  128??

 

 

Edited April 9, 2019 by Jacek Laskowski

[PeterBelow 239]

21 hours ago, Jacek Laskowski said:

I have simple table:

 

CREATE TABLE WORKSTATION
(
  IDWORKSTATION        INTEGER,
  MACHINEID            VARCHAR(32)
);

I create TFDQuery with simple select SQL:

 

SELECT * FROM WORKSTATION w
WHERE W.MACHINEID = :PARAM

 

When I set as param string longer than 32 chars then I get error:

 

"arithmetic exception, numeric overflow, or string truncation"

 

I set StrsTrim2Len option to True:

 

http://docwiki.embarcadero.com/Libraries/Rio/en/FireDAC.Stan.Option.TFDFormatOptions.StrsTrim2Len

 

...but the error still occurs.

 

How to properly config Firedac to eliminate this problem?

This is, in my opinion, a programmer error, not a problem with the framework. You are using data that does does not fit the declaration of the database table. In such an occasion I would expect the framework to throw an error, since bad input should definitely not be swept under the carpet but brought to the user's attention. So, if the value you use for PARAM comes from an edit control filled by the user, the MaxLength property of that control needs to be set to the length of the database field the input is destined to be used with, so bad length input is simply not possible. If that is impractical you have add your own checks for the input before it is used, and produce a sensibel error message, e.g. "A machine ID cannot be longer than 32 characters.".

 

Just my 2 Euro-cents...

[Jacek Laskowski 57]

54 minutes ago, PeterBelow said:

This is, in my opinion, a programmer error, not a problem with the framework. You are using data that does does not fit the declaration of the database table.

[...]

Just my 2 Euro-cents...

See:

http://docwiki.embarcadero.com/Libraries/Rio/en/FireDAC.Stan.Option.TFDFormatOptions.StrsTrim2Len

 

citation from doc:

Quote

 

Controls the trimming of string values to the declared length.

Use the StrsTrim2Len property to specify whether FireDAC should trim too long string values to the maximum declared length (True) or not (False). The default value is False. 

For example, if the application is trying to assign '12345' to the field declared in the database as VARCHAR(3), then FireDAC raises an exception 'value too long'. If StrsTrim2Len = True, FireDAC trims the value to '123'.

 

 

Edited April 9, 2019 by Jacek Laskowski

[Jacek Laskowski 57]

4 hours ago, Jacek Laskowski said:

I debug FD and I found suspected place:

 

 

But why result (sqllen) is  128??

 

 

 

128 = 32 * 4

 

where 32 is column size in chars and 4 comes from hardcoded method:

 

function TIBLib.GetBytesPerChar(ACSID: Integer): Integer;
var
  iID: Integer;
begin
  iID := ACSID mod 256;
  if iID = csIDUnicodeFSS then
    Result := 3
  else if iID = IDUTF8 then
    Result := 4    <<<------------------ for UTF8 columns
  else if (FBrand = ibInterbase) and ((iID = csIDUnicodeBE) or (iID = csIDUnicodeLE)) then
    Result := 2
  else
    Result := 1;
end;

 

[Lars Fosdal 1793]

@Dmitry Arefiev - Can you shine some light on this?  Is it a bug or "as designed"?

[Jacek Laskowski 57]

23 hours ago, Lars Fosdal said:

@Dmitry Arefiev - Can you shine some light on this?  Is it a bug or "as designed"?

I checked the Rio, here the problem also exists. But I also found it:

 

https://quality.embarcadero.com/browse/RSP-16057

 

And now I don't understand anything.

[Jacek Laskowski 57]

On 4/10/2019 at 9:03 AM, Lars Fosdal said:

@Dmitry Arefiev - Can you shine some light on this?  Is it a bug or "as designed"?

@Dmitry Arefiev Dmitry, take the floor on this, please. Is it working or not?

[Guest]

On 4/9/2019 at 2:54 PM, Jacek Laskowski said:

Controls the trimming of string values to the declared length.

Use the StrsTrim2Len property to specify whether FireDAC should trim too long string values to the maximum declared length (True) or not (False). The default value is False. 

For example, if the application is trying to assign '12345' to the field declared in the database as VARCHAR(3), then FireDAC raises an exception 'value too long'. If StrsTrim2Len = True, FireDAC trims the value to '123'.

The docs apparently states that FireDAC will throw an exception. But the exception reported above,

"arithmetic exception, numeric overflow, or string truncation"

, is from Firebird, *not* from FireDAC.

 

HTH.

[Jacek Laskowski 57]

8 hours ago, Dany Marmur said:

If StrsTrim2Len = True, FireDAC trims the value to '123'.

And I have this flag set to True! Why, then, don't truncate the data to the length of the field?

[romariomelo 0]

Any solution? I have the same problem.

[Jacek Laskowski 57]

No, @Dmitry Arefiev is still silent. I don't know why...

[Jacek Laskowski 57]

@Dmitry Arefiev

 

  Maybe now you can reply to this issue? I still fight with it.

[Jeff Overcash 2]

This looks like a difference between IB and Fb.  InterBase does not actually check the character length, but instead only checks that the # of bytes sent fit into the max # of bytes of the column declaration.  Normally that is 1:1@charset size bytes, but with variable byte character sets (like SJIS or UTF8) you could actually store more characters than defined.  So for instance anything in ANSI <= 127 all map to a 1-byte code point in UTF8.  So if you have a VarChar(5) UTF8, InterBase (and therefore early versions of Fb) would allow you to store '1234567890' into that column.  Reading the FireDac code section in this thread that is the type of check FD is doing.  Seems Fb at some point started enforcing strict character counting verification and is now throwing the exception seen here. 

 

I had complaints from people (ab)using this for SJIS when IBX went to Unicode because I enforced strict character counting on the client-side.

 

I tested this against IB and no exception is thrown (as expected) so at some point (I could not find in the FB documentation this change in behavior) strict character counting for multi-byte character sets must have been implemented in Fb. I am also able to insert 35 characters into a UTF8 VarCahr(32) in IB.

 

you can try this fix.  Untested but reads right to me -

{code}     

     if not lIsNull then begin
        pUTF8 := nil;
        if (lib.FBrand = ibFirebird) then begin
          if ALen > DataSize div 4 then
            if FVars.Statement.StrsTrim2Len then
              ALen := DataSize div 4
            else
              ErrorDataTooLarge(DataSize div 4, ALen);
          iByteLen := FVars.Statement.Database.Encoder.Encode(ApData, ALen,
            pUTF8, ecUTF16);
        end
        else begin
          iByteLen := FVars.Statement.Database.Encoder.Encode(ApData, ALen,
            pUTF8, ecUTF16);
          if iByteLen > DataSize then
            if FVars.Statement.StrsTrim2Len then
              iByteLen := DataSize
            else
              ErrorDataTooLarge(DataSize, iByteLen);
        end;
        if SQLDataType = SQL_VARYING then begin
          PVary(pData)^.vary_length := Smallint(iByteLen);
          pData := @PVary(pData)^.vary_string[0];
        end
        else
          pLen^ := Smallint(iByteLen);
        Move(PFDAnsiString(pUTF8)^, PFDAnsiString(pData)^, iByteLen * SizeOf(TFDAnsiChar));
      end;

{code}

Edited November 2, 2020 by Jeff Overcash
/ to div

[Guest]

19 hours ago, Jeff Overcash said:

This looks like a difference between IB and Fb.

Yes, finally, something on point! The exception came from Firebird (albeit *via* FireDAC).

 

On 4/9/2019 at 2:02 PM, PeterBelow said:

I would expect the framework

No, the framework did not raise anything. It just transpiles the FB exceptions into an Delphi one. Otherwise i agree, there's no reason to push too much data into any API.

[c0d3r 17]

I think you were using UTF8 character set while connecting a Firebird database which created with NONE/ANSI/... character set, which caused Firebird raised this exception.  Its NOT a FireDAC issue.   In order to make it works, either you use NONE character set while connecting to your database, or convert your Firebird database to UTF8.

Edited November 4, 2020 by c0d3r

[Jacek Laskowski 57]

@c0d3r

I guess you write to me.
Well, you're wrong. The database has declared UTF8 type for text fields. The connection is also made with the parameter UTF8.

[c0d3r 17]

3 hours ago, Jacek Laskowski said:

@c0d3r

I guess you write to me.
Well, you're wrong. The database has declared UTF8 type for text fields. The connection is also made with the parameter UTF8.

Ah, OK.  Sorry about that.  I don't use FireDAC,  I'm always using UIB to work with Firebird databases with UTF8/Unicode,  I got the same error when I was using UTF8 to connect to a NONE set of Firebird database.