SQLite FireDAC trailing spaces

Question

I'm using Delphi XE7 with FireDAC to access SQLite.

When I put data into a TEXT field, any trailing spaces or #0 characters get truncated.

Is there something I can change in either SQLite or FireDAC to have it preserve the trailing white space?

// The trailing spaces after Command don't come back from SQLite.
fFireDACQuery.ParamByName(kSQLFieldScriptCommands).AsString := 'Command          ';

P.S. don't be confused from the initial description of the StrsTrim property. There is a note saying "For SQLite, this property is applied to all string columns, including ftMemo, ftWideMemo, ftString, ftWideString, ftFixedChar, and ftFixedWideChar." which is just your case. — TLama
Sounds like a good answer, then why post it as a comment, not under "Your Answer" below? — Stijn Sanders
I've set FormatOptions.StrsTrim to False and it fixed some of the cases but not all of them. The ones missed are those with #0 at the end of the string (as a character). I'm using ShortStrings because C-style strings have their own problems with an actual #0 inside the string. — Mike at Bookup

Victoria Victoria · Accepted Answer · 2017-07-14T00:30:30

Disable the StrsTrim property. This property is described as:

TFDFormatOptions.StrsTrim

Controls the removing of trailing spaces from string values and zero bytes from binary values.

And it seems that you want to store binary data rather than text. If that is correct, better define your field data type e.g. as BINARY[255] for fixed length binary string of 255 bytes (255 is the maximum length of ShortString that you use).

Parameter value for such field you would then access this way:

var
  Data: RawByteString;
begin
  ReadByteDataSomehow(Data);

  FDQuery.FormatOptions.StrsTrim := False;
  FDQuery.SQL.Text := 'INSERT INTO MyTable (MyBinaryField) VALUES (:MyBinaryData)';
  FDQuery.ParamByName('MyBinaryData').AsByteStr := Data;
  FDQuery.ExecSQL;
end;

SQLite FireDAC trailing spaces

1 Answers