Recent

Author Topic: [SOLVED] SQLite String Limit of 255 characters  (Read 16878 times)

Gizmo

  • Hero Member
  • *****
  • Posts: 831
[SOLVED] SQLite String Limit of 255 characters
« on: February 27, 2018, 05:52:13 pm »
Hi all

A user has filled a bug report regarding the length of values that appear in a DBGrid. I recently migrated my program to use SQLite, whichg was a lot of work, so I and my users keep spotting some issues.

Regarding this problem, the first thing was that I'd forgot to set the compiler to {$H+} to ensure long strings are accepted throughout the unit that I made for SQLite processing. But having applied that, still no joy. Values in the DBGrid were still being truncated on my main form.

So then I realised I was setting table fields to a longth of only 128 characters! Something I'd entirely missed! So my CREATE TABLE statement referred to Char(128) SomeColumn, which obviously restricted the length of what could be held. So I changed that from Char(128) to Text, which I read was unlimited. But then I got (MEMO) displayed in my DBGrid. So then I switched Text to VARCHAR which I read was similar but perhaps less memory intensive. That ensured DBGrid could display the result again, but they are truncated at 255 characters.

So somewhere in my code there is a restriction on strings in the SQLIte database of 255 characters.

I have narrowed the problem down, I think, to the TSQLQuery.Params.ParamsByName(StringValue).AsString := MyString, which I use.  I read somewhere that there may be a restriction on Params to 255 chars. Is that right? If so, how do I fix the issue? I need DBGrid to be able to show strings of any length and I need SQLite to be able to store strings of any length. 

Code: Pascal  [Select][+][-]
  1. unit dbases_sqlite;
  2. {$mode objfpc}{$H+} // {$H+} ensures strings are of unlimited size
  3.  
  4. interface
  5.  
  6. uses
  7. ...db, sqldb, sqldblib,...
  8.  
  9. // Create database
  10. procedure TfrmSQLiteDBases.CreateDatabase(DBaseName : string);
  11. begin
  12.   SQLite3Connection1.Close; // Ensure the connection is closed when we start
  13.   try
  14.     // Make a new database and add the tables
  15.     try
  16.       SQLite3Connection1.Open;
  17.       SQLTransaction1.Active := true;
  18.  
  19.       // Periodically sort the database out to ensure it stays in tip top shape
  20.       // during heavy usage
  21.       SQLite3Connection1.ExecuteDirect('PRAGMA auto_vacuum = FULL;');
  22.  
  23.       // The user-version is not used internally by SQLite. It may be used by applications for any purpose.
  24.       // http://www.sqlite.org/pragma.html#pragma_schema_version
  25.       SQLite3Connection1.ExecuteDirect('PRAGMA user_version = ' + IntToStr(user_version) + ';');
  26.       SQLite3Connection1.ExecuteDirect('PRAGMA application_id = ' + IntToStr(application_id) + ';');
  27.  
  28.       // Here we're setting up a table named "TBL_COPY" in the new database
  29.       SQLite3Connection1.ExecuteDirect('CREATE TABLE "TBL_COPY"('+
  30.                   ' "id" Integer NOT NULL PRIMARY KEY,'+
  31.                   ' "SourceFilename" VARCHAR NOT NULL');
  32.       // Creating an index based upon id in the TBL_COPY Table
  33.       SQLite3Connection1.ExecuteDirect('CREATE UNIQUE INDEX "COPIED_FILES_id_idx" ON "TBL_COPY"( "id" );');
  34.  
  35.       // Now write to the new database
  36.       SQLTransaction1.CommitRetaining;
  37.     except
  38.       ShowMessage('SQLite detected but unable to create a new SQLite Database');
  39.     end;
  40.   except
  41.     ShowMessage('SQLite detected but could not check if a database file exists');
  42.   end;
  43. end;
  44.  
  45. // Write computed values from the COPY tab to the database table TBL_COPY
  46. procedure TfrmSQLiteDBases.WriteCOPYValuesToDatabase(Col1 : string);
  47. {Col1 : Source Filename}
  48. begin
  49.   try
  50.     // Insert the values into the database. We're using ParamByName which prevents SQL Injection
  51.     // http://wiki.freepascal.org/Working_With_TSQLQuery#Parameters_in_TSQLQuery.SQL
  52.     sqlCOPY.Close;
  53.     sqlCOPY.SQL.Text := 'INSERT into TBL_COPY (Filename) values (:SourceFilename)';
  54.     SQLTransaction1.Active := True;
  55.     sqlCOPY.Params.ParamByName('SourceFilename').AsString := Col1;  // IF COL1 IS 500 chars, I need it to accept this but the result gets truncated to 255. Note Col1 is OK and can be of any length, but only first 255 chars are assigned to AsString
  56.     sqlCOPY.ExecSQL;
  57.   except
  58.     on E: EDatabaseError do
  59.     begin
  60.       MessageDlg('Error','A database error has occurred. Technical error message: ' + E.Message,mtError,[mbOK],0);
  61.     end;
  62.   end;
  63. end;
  64.  
  65.  

Thanks

« Last Edit: March 01, 2018, 11:25:17 am by Gizmo »

rvk

  • Hero Member
  • *****
  • Posts: 6109
Re: SQLite String Limit of 255 characters
« Reply #1 on: February 27, 2018, 06:14:00 pm »
Officially you should use TEXT for text-fields in SQLite3.

For VARCHAR(length) the (length) is even ignored and is stored in TEXT anyway.

Quote
Note that numeric arguments in parentheses that following the type name (ex: "VARCHAR(255)") are ignored by SQLite - SQLite does not impose any length restrictions (other than the large global SQLITE_MAX_LENGTH limit) on the length of strings, BLOBs or numeric values.

The problem is that FPC (still) thinks it's dealing with a MEMO.

Can you try putting the data in with TEXT (via param) and when selecting for a DBGrid use SELECT CAST(text_field as VARCHAR) from table. In that case FPC also thinks it's dealing with a varchar and show the text correctly (even though it is a TEXT field).

Does it work then?

rvk

  • Hero Member
  • *****
  • Posts: 6109
Re: SQLite String Limit of 255 characters
« Reply #2 on: February 27, 2018, 06:44:29 pm »
And if you absolutely don't want to use TEXT try using VARCHAR(1000) (or max value).

It could be that VARCHAR without length just defaults to 255 in fpc when reading the field definitions.

Gizmo

  • Hero Member
  • *****
  • Posts: 831
Re: SQLite String Limit of 255 characters
« Reply #3 on: February 28, 2018, 12:11:11 pm »
Thanks RVK, as always.

I couldn't get the first suggestion to work. In my CREATE TABLES statement I declared the values as Text and then in my SELECT statements I used CASE, e.g.

SELECT CASE(* as TEXT) from MY_TABLE but I got invalid syntax error, but I couldn't see where it was wrong.

So I reverted those changes and then tried your second suggestion, which did fix the issue, in that it does now display what it should. However the problem I have is that the max value I need to set for these values is 32K! So I have had to decalre VARCHAR(32000) for every row in my database. And whilst 95% of the time, these values won't be anywhere near 32K in length, and will rarely even be over about 500 characters, but any one of them could be at some stage and I don't want the program to crash out because I've been to reserved and set only, say 5K. I have a feeling this 32K VARCHAR declaration may impact performance? Memory usage doesn't seem too bad on the computer while the program is running and doing its thing until the data has to be exported from the DBDataSet to CSV using laddbexport unit and the TCSVExport library, where, prior to export, memory usage sits at about 20Mb, and then after the export it jumps to 600Mb, and stays like that even though I have free'd the TSCVExport creations!

Anyway, it has worked for now RVK so thank you. I can work on a more robust solution in due course.
« Last Edit: February 28, 2018, 12:14:03 pm by Gizmo »

rvk

  • Hero Member
  • *****
  • Posts: 6109
Re: SQLite String Limit of 255 characters
« Reply #4 on: February 28, 2018, 12:32:58 pm »
It doesn't matter if you make it VARCHAR(999999) or something (like VARCHAR(32767)).

I'll explain the 255 first. sqlite3connection reads the DLL for the table. Although SQLite does use TEXT even if you use VARCHAR, it does save that VARCHAR to it's DLL. And sqlite3connection reads that DLL to determine what field it is. There you can also see that TEXT is mapped to a ftMemo and VARCHAR to a ftString.

sqlite3conn.pp:
Code: Pascal  [Select][+][-]
  1.   FieldMap : Array [1..FieldMapCount] of TFieldMap = (
  2.    (n:'INT'; t: ftInteger),
  3.    (n:'LARGEINT'; t:ftLargeInt),
  4. //...
  5.    (n:'MONEY'; t: ftCurrency),
  6.    (n:'VARCHAR'; t: ftString),
  7.    (n:'CHAR'; t: ftFixedChar),
  8. //...
  9.    (n:'NUMERIC'; t: ftBCD),
  10.    (n:'DECIMAL'; t: ftBCD),
  11.    (n:'TEXT'; t: ftMemo),
  12.    (n:'CLOB'; t: ftMemo),
  13.  

In case of ftString (i.e. VARCHAR) it reads the length which should follow it. That's done in this piece of code:   
Code: Pascal  [Select][+][-]
  1.    case FT of
  2.       ftString,
  3.       ftFixedChar,
  4.       ftFixedWideChar,
  5.       ftWideString,
  6.       ftBytes,
  7.       ftVarBytes:
  8.                begin
  9.                  size1 := 255; //sql: if length is omitted then length is 1
  10.                  size2 := 0;
  11.                  ExtractPrecisionAndScale(FD, size1, size2);
  12.                  if size1 > MaxSmallint then size1 := MaxSmallint;
  13.                end;
And there you see the default 255 when there is no size given.

But FPC doesn't "reserve" 999999 in length when you supply it. Even SQLite3 doesn't use 999999 for every string. SQLite3 just maps it internally to TEXT (which is variable size). It's just like a String and Memo. It's dynamically sized in both FPC and SQLite3. MaxSmallInt is 32767 so giving a larger size won't even matter because the maximum is 32767. But for clarity you could use VARCHAR(32767).

For the error you got when doing the CAST(). You can't CAST(*) with a wildcard. You would need to supply every field you want as string separately. Like this:
Code: SQL  [Select][+][-]
  1. SELECT CASE(FIELD1 AS TEXT), CASE(FIELD2 AS TEXT), CASE(FIELD3 AS TEXT) FROM MY_TABLE

There was still another option when using TEXT, and that was using OnGetText for the fields (Column.Field.OnGetText).
Code: Pascal  [Select][+][-]
  1. procedure TForm1.DBGridOnGetText(Sender: TField; var aText: string; DisplayText: Boolean);
  2. begin
  3.   if (DisplayText) then aText := Sender.AsString;
  4. end;  
You could automatically assigned this when opening the table.

But it is easier to use VARCHAR(). (it's unfortunate that FPC choose to map TEXT to ftMemo)
« Last Edit: February 28, 2018, 12:34:41 pm by rvk »

Gizmo

  • Hero Member
  • *****
  • Posts: 831
Re: [SOLVED] SQLite String Limit of 255 characters
« Reply #5 on: March 01, 2018, 11:26:10 am »
Thanks RVK for the suggestion and the detailed explanation, which is helpful to me and I am sure others.

I have implemented the VARCHAR(32000) option which seems to be working well.

 

TinyPortal © 2005-2018