SQLite fields truncated to a maximum of 1020 characters lazarus / 16-Databases ---------------------------------------------------------------------- joxeankoret · 2023-01-02 00:49 Hi! I'm trying to display some large texts (decompiled C++ and assembler source codes) that I have stored in SQLite databases (using TSQLite3Connection and some data aware components), however, they are getting truncated to a maximum of 1020 characters no matter what I do. The data types for the fields that are getting truncated to 1020 characters are set to TEXT in the SQLite database. So, I have tried, for example, to put a TDBMemo, set the field name and then put the MaxSize to a real big value instead of 0: it doesn't matter, the text is truncated at exactly 1020 characters. I have also tried with a TDBGrid and other data aware components. I have tried also with one TSQLQuery component defining the fields and setting the size to big values as well as changing the DataType to ftMemo instead of ftString for the 2 fields I need. Still, the text is truncated at 1020 characters. Am I doing something wrong or is it a bug or a limitation? Thanks in advance and happy new year! ---------------------------------------------------------------------- lainz · 2023-01-02 02:15 Here another related post: Why Do I Get Only 1016 Chacters on a Long Text From SQLite Database? https://forum.lazarus.freepascal.org/index.php/topic,61677.msg464695.h tml#msg464695 ---------------------------------------------------------------------- joxeankoret · 2023-01-02 10:21 I see, so it looks like a bug to me If you want, I can make a small testing application that shows the bug and/or report it wherever it's appropriate so it can get fixed for future versions. As for my problem, using Zeos DBO components I can properly get the whole text. Which means I have to replace all standard DB components to Zeos DBO dropping replacements in my project... but at least I have a way to get the whole text. Thank you all! ---------------------------------------------------------------------- avra · 2023-01-02 10:56 Quote from: joxeankoret on January 02, 2023, 11:21:27 amI see, so it looks like a bug to me If you want, I can make a small testing application that shows the bug and/or report it wherever it's appropriate so it can get fixed for future versions.Have you tested with trunk FPC and trunk Lazarus? If not then how would you know it wasn't already fixed? You can easily get these by using fpcupdeluxe tool. On your left you can see Bugtracker link which you can use for reporting bugs. Many developers do not follow forum. ---------------------------------------------------------------------- joxeankoret · 2023-01-02 11:31 Sure, I will try to reproduce with the code in trunk whenever I have some time and report it. ---------------------------------------------------------------------- LacaK · 2023-01-05 07:34 Can you provide SQL CREATE TABLE statement ? If table is created like: CREATE TABLE tab1 (col1 TEXT); then TEXT datatype is mapped to ftMemo and there is no limitation to 1024 characters. Can you check data type of the "truncated" field in opened TSQLQuery? is it ftMemo or ftString? ---------------------------------------------------------------------- totya · 2023-02-15 20:55 I tested with TSqlite3Dataset, and I don't see any limit. Tested UTF8 length is 270000 char (540000 byte), and passed with TEXT field. Lazarus 2.3.0, fpc 3.2.3 fixes branch. Code (draft) (recommended minimum memory is 8GB): Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure TMainForm.Button1Click(Sender: TObject);const  cIndex = 'ID';  cValue = 'Value';  cTestString = 'öüóőúéáűíÖÜÓŐÚÉÁŰÍ';  cTestCount = 15000;var  SD: TSqlite3Dataset;  mSQL: string;  s, sSQL: string;  i: integer;  LengthTestError: boolean;const  cSQLTestFileName = 'sql_test.db';  cSQLTestMainTableName = 'Main';begin  Memo.Lines.Add('SQL test...');   SD := TSqlite3Dataset.Create(Self);  try    if FileExistsUTF8(cSQLTestFileName) then      if not (DeleteFileUTF8(cSQLTestFileName)) then        raise Exception.Create('Cannot delete SQL file!');     // Create table...  with SD do    begin      FileName := cSQLTestFileName;      TableName := cSQLTestMainTableName;       if not TableExists then      begin      FieldDefs.Clear;         FieldDefs.Add(cIndex, ftInteger);        FieldDefs.Add(cValue, ftMemo);         PrimaryKey := cIndex;        CreateTable;         Application.ProcessMessages;      end;    end;   with SD do    begin      if Active then Close;       with SQLList do      begin        Clear;        Add('BEGIN;');      end;    end;     mSQL := 'INSERT INTO %s(%s) ' + 'VALUES(%s);';     s := '';    for i := 0 to cTestCount - 1 do    begin      s := s + cTestString;      SD.SQLList.Add(        Format(mSQL, [cSQLTestMainTableName, cValue, AnsiQuotedStr(s, '"')]));    end;     Memo.Lines.Add('SQL write to file...');    Application.ProcessMessages;     with SD do    begin    SQLList.Add('COMMIT;');      try        ExecSQLList;      finally      SQLList.Clear;        Close;      end;    end;     Memo.Lines.Add('SQL write to file... OK');    Application.ProcessMessages;     Memo.Lines.Add('SQL text compare...');    Application.ProcessMessages;     with SD do    begin      Open;      LengthTestError := False;      s := '';      SD.First;      for i := 0 to cTestCount - 1 do      begin        s := s + cTestString;         sSQL := SD.FieldByName(cValue).AsString;        if UTF8CompareStr(s, sSQL) <> 0 then        begin          LengthTestError := True;          break;        end        else        SD.Next;      end;      Close;    end;   finally    SD.Free;  end;   if not (LengthTestError) then    Memo.Lines.Add(Format('SQL text compare... OK! Tested string length: %d, from SQL: %d (byte length: %d)',      [UTF8Length(cTestString) * cTestCount, UTF8Length(s), Length(s)]))  else    Memo.Lines.Add(Format('SQL text compare...FAILED! Different Length! Original: %d, from SQL: %d', [UTF8Length(s), UTF8Length(sSQL)]));  Result: QuoteMemo SQL test... SQL write to file... SQL write to file... OK SQL text compare... SQL text compare... OK! Tested string length: 270000, from SQL: 270000 (byte length: 540000)