SQLite fields truncated to a maximum of 1020 characters

[joxeankoret]

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]

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.html#msg464695

[joxeankoret]

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]

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.

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]

Sure, I will try to reproduce with the code in trunk whenever I have some time and report it.

[LacaK]

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]

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][+][-]

1. procedure TMainForm.Button1Click(Sender: TObject);
2. const
3.   cIndex = 'ID';
4.   cValue = 'Value';
5.   cTestString = 'öüóőúéáűíÖÜÓŐÚÉÁŰÍ';
6.   cTestCount = 15000;
7. var
8.   SD: TSqlite3Dataset;
9.   mSQL: string;
10.   s, sSQL: string;
11.   i: integer;
12.   LengthTestError: boolean;
13. const
14.   cSQLTestFileName = 'sql_test.db';
15.   cSQLTestMainTableName = 'Main';
16. begin
17.   Memo.Lines.Add('SQL test...');
18.  
19.   SD := TSqlite3Dataset.Create(Self);
20.   try
21.     if FileExistsUTF8(cSQLTestFileName) then
22.       if not (DeleteFileUTF8(cSQLTestFileName)) then
23.         raise Exception.Create('Cannot delete SQL file!');
24.  
25.     // Create table...
26.     with SD do
27.     begin
28.       FileName := cSQLTestFileName;
29.       TableName := cSQLTestMainTableName;
30.  
31.       if not TableExists then
32.       begin
33.         FieldDefs.Clear;
34.  
35.         FieldDefs.Add(cIndex, ftInteger);
36.         FieldDefs.Add(cValue, ftMemo);
37.  
38.         PrimaryKey := cIndex;
39.         CreateTable;
40.  
41.         Application.ProcessMessages;
42.       end;
43.     end;
44.  
45.     with SD do
46.     begin
47.       if Active then Close;
48.  
49.       with SQLList do
50.       begin
51.         Clear;
52.         Add('BEGIN;');
53.       end;
54.     end;
55.  
56.     mSQL := 'INSERT INTO %s(%s) ' + 'VALUES(%s);';
57.  
58.     s := '';
59.     for i := 0 to cTestCount - 1 do
60.     begin
61.       s := s + cTestString;
62.       SD.SQLList.Add(
63.         Format(mSQL, [cSQLTestMainTableName, cValue, AnsiQuotedStr(s, '"')]));
64.     end;
65.  
66.     Memo.Lines.Add('SQL write to file...');
67.     Application.ProcessMessages;
68.  
69.     with SD do
70.     begin
71.       SQLList.Add('COMMIT;');
72.       try
73.         ExecSQLList;
74.       finally
75.         SQLList.Clear;
76.         Close;
77.       end;
78.     end;
79.  
80.     Memo.Lines.Add('SQL write to file... OK');
81.     Application.ProcessMessages;
82.  
83.     Memo.Lines.Add('SQL text compare...');
84.     Application.ProcessMessages;
85.  
86.     with SD do
87.     begin
88.       Open;
89.       LengthTestError := False;
90.       s := '';
91.       SD.First;
92.       for i := 0 to cTestCount - 1 do
93.       begin
94.         s := s + cTestString;
95.  
96.         sSQL := SD.FieldByName(cValue).AsString;
97.         if UTF8CompareStr(s, sSQL) <> 0 then
98.         begin
99.           LengthTestError := True;
100.           break;
101.         end
102.         else
103.           SD.Next;
104.       end;
105.       Close;
106.     end;
107.  
108.   finally
109.     SD.Free;
110.   end;
111.  
112.   if not (LengthTestError) then
113.     Memo.Lines.Add(Format('SQL text compare... OK! Tested string length: %d, from SQL: %d (byte length: %d)',
114.       [UTF8Length(cTestString) * cTestCount, UTF8Length(s), Length(s)]))
115.   else
116.     Memo.Lines.Add(Format('SQL text compare...FAILED! Different Length! Original: %d, from SQL: %d', [UTF8Length(s), UTF8Length(sSQL)]));
117.  

Result:

Memo
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)