Jump to content
Jacek Laskowski

[Firedac] Truncation error on Firebird select query

Recommended Posts

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 by Jacek Laskowski

Share this post


Link to post

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?

Share this post


Link to post
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

Share this post


Link to post

Correction, Insert does not work either 😕

 

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

 

vps_2019_04.09_01.thumb.png.d0df363a17b74dc2087134bf9af84be9.png

After F9:

 

vps_2019_04.09_02.thumb.png.1ad9632917b19b6c7ddf76d85b1dd3eb.png

 

 

Delphi Tokyo, FB 2.5.8.

 

Does the StrsTrim2Len option work at all?

 

Share this post


Link to post

I debug FD and I found suspected place:

 

vps_2019_04.09_03.thumb.png.16c1c72b893fa27c4548b635d1511f1c.png

 

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 by Jacek Laskowski

Share this post


Link to post
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...

  • Like 3

Share this post


Link to post
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 by Jacek Laskowski

Share this post


Link to post
4 hours ago, Jacek Laskowski said:

I debug FD and I found suspected place:

 

vps_2019_04.09_03.thumb.png.16c1c72b893fa27c4548b635d1511f1c.png

 

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;

 

Share this post


Link to post
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.

Share this post


Link to post
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?

Share this post


Link to post

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 by Jeff Overcash
/ to div

Share this post


Link to post
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.

Share this post


Link to post

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 by c0d3r

Share this post


Link to post
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.

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×