Using Delphi XE7 and Tokyo with Firebird 2.5 I've come to the conclusion that StrsTrim2Len has no impact whatsoever when doing updates/inserts with TFDQuery and ParamByName, which makes oversized strings raise an exception.
Are there any other approaches than to truncate all strings in code like:
ParamByName('Field1').AsString := SomeVar.SubString(0, 50);
With the added need to keep track of the field length also?
The source and form is:
unit Unit3;
interface
uses
Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
System.StrUtils,
Vcl.Controls, Vcl.Forms, Vcl.Dialogs, FireDAC.Stan.Intf, FireDAC.Stan.Option, FireDAC.Stan.Error, FireDAC.UI.Intf,
FireDAC.Phys.Intf, FireDAC.Stan.Def, FireDAC.Stan.Pool, FireDAC.Stan.Async, FireDAC.Phys, FireDAC.Phys.FB,
FireDAC.Phys.FBDef, FireDAC.VCLUI.Wait, FireDAC.Stan.Param, FireDAC.DatS, FireDAC.DApt.Intf, FireDAC.DApt,
Vcl.StdCtrls, Data.DB, FireDAC.Comp.DataSet, FireDAC.Comp.Client, FireDAC.Comp.UI, FireDAC.Phys.IBBase;
type
TForm3 = class(TForm)
FDConnection1: TFDConnection;
FDPhysFBDriverLink1: TFDPhysFBDriverLink;
FDGUIxWaitCursor1: TFDGUIxWaitCursor;
FDQuery1: TFDQuery;
Button1: TButton;
procedure Button1Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;
var
Form3: TForm3;
implementation
{$R *.dfm}
procedure TForm3.Button1Click(Sender: TObject);
begin
FDConnection1.Open;
FDQuery1.FormatOptions.StrsTrim2Len := True;
FDQuery1.SQL.Text := 'INSERT INTO MyTable (ID, MyField) VALUES (:ID, :MyField)';
FDQuery1.ParamByName('ID').AsInteger := 1;
FDQuery1.ParamByName('MyField').AsString := DupeString('0', 21); { ← field is 20 chars }
FDQuery1.ExecSQL;
FDQuery1.SQL.Text := 'SELECT MyField FROM MyTable WHERE ID = 1';
FDQuery1.Open;
Assert(Length(FDQuery1.FieldByName('MyField').AsString) = 20); { ← trimmed to 20 chars? }
FDConnection1.Close;
end;
end.
The corresponding .dfm file:
object Form3: TForm3
Left = 0
Top = 0
Caption = 'Form3'
ClientHeight = 294
ClientWidth = 161
Color = clBtnFace
Font.Charset = DEFAULT_CHARSET
Font.Color = clWindowText
Font.Height = -11
Font.Name = 'Tahoma'
Font.Style = []
OldCreateOrder = False
PixelsPerInch = 96
TextHeight = 13
object Button1: TButton
Left = 40
Top = 16
Width = 75
Height = 25
Caption = 'Button1'
TabOrder = 0
OnClick = Button1Click
end
object FDConnection1: TFDConnection
Params.Strings = (
'Database=MyUTF8Db'
'User_Name=Sysdba'
'Password='
'Server=127.0.0.1'
'CharacterSet=UTF8'
'DriverID=FB')
FormatOptions.AssignedValues = [fvStrsTrim2Len]
FormatOptions.StrsTrim2Len = True
Left = 48
Top = 48
end
object FDPhysFBDriverLink1: TFDPhysFBDriverLink
Left = 48
Top = 104
end
object FDGUIxWaitCursor1: TFDGUIxWaitCursor
Provider = 'Forms'
ScreenCursor = gcrHourGlass
Left = 48
Top = 160
end
object FDQuery1: TFDQuery
Connection = FDConnection1
Left = 48
Top = 216
end
end
No, when you enable StrsTrim2Len option, even assigned parameter values should be trimmed to the limit of the binded field. So, direct string assignment should trim the value in length for a string field parameter, IMHO:
ParamByName('MyFieldUTF8').AsWideString := 'Value to be encoded by FireDAC';
Why IMHO? Because in this particular case (Delphi Tokyo, UTF-8 field in Firebird), implementation of this option works like DataTrim2Size rather than how it reads, and I am not sure if there is some reason for it left behind. String fields in Firebird are constrained by string length limit not by storage data size limit when defined (as far as I know).
FireDAC, as implemented now for Firebird driver, trims parameter data buffer for a UTF-8 field only when data size of the encoded parameter value exceeds storage data size of the parameter field (when the StrsTrim2Len option is enabled). So it's based on data size rather than string length.
Parameter data size (if not specified) seems to be obtained from RDB$FIELD_LENGTH meta field of the RDB$FIELDS system table (cannot confirm that, but it seems the sqllen member is filled by that field value, from a quick Firebird code browse). But it's not that important for now.
The problem is this code and analogy of what the StrsTrim2Len option should actually do here (it's the TIBVariable.SetData method implementation, UTF-8 branch; comments added by me):
{ this encodes the value to UTF-8 and returns number of bytes written to the buffer }
iByteLen := FVars.Statement.Database.Encoder.Encode(ApData, ALen, pUTF8, ecUTF16);
{ DataSize here equals to the RDB$FIELD_LENGTH, so let's try a calculation for field
let's say VARCHAR(20), and to the parameter binded to it assign e.g. 21 chars long
string consisting from ANSI chars:
iByteLen → 21 ANSI chars occupies 21 bytes
DataSize → XSQLVAR.sqllen → RDB$FIELD_LENGTH → 80 (20 chars * max UTF-8 char size?)
Now, is 21 > 80? No? No trimming then. }
if iByteLen > DataSize then
if FVars.Statement.StrsTrim2Len then
iByteLen := DataSize
else
ErrorDataTooLarge(DataSize, iByteLen);
As you may guess, trimming of the parameter value buffer would actually happen, but the encoded string was too small in size, so the trimming was skipped. And you can also predict that if you used such parameter e.g. for inserting a value, you would end up with the engine exception because the engine works with inserted string value length rather than data size when validating constraints.
I cannot find practical meaning of working with data size here. Well, you will never overflow storage data size, but on the other hand you can easily exceed length of the field. And the discussed option is about length, not about size.
I'll open a bug report and try to wait for some comment around this topic as I don't believe this code was an accident. Will report back..