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 2

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

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

×