[SOLVED] Is there a limit to a SQLite3 Memo/CLOB field? lazarus / 16-Databases ---------------------------------------------------------------------- 1HuntnMan · 2026-04-30 20:38 I just was testing an application that I'm working on at the moment, in the Contacts table I have a CLOB or tried changing it to a MEMO field for the Notes/Profile field.  After about 150-200 characters, it's truncating or cutting off the rest when I save the record. It's supposed to have a limit of 1GB I read somewhere.  200 characters is no where that large. Changing it from CLOB to MEMO didn't cure the issue. ---------------------------------------------------------------------- paweld · 2026-05-01 04:34 Whether using the SQLdb or Zeos components, I have no trouble saving long text (I tested it with over 1,000 characters) or a file (an image of ~100 KB) to the database. Perhaps when defining the dataset, you set a limit on that column yourself (the Size property for the field), and in that case, the dataset ensures that you do not exceed that size. ---------------------------------------------------------------------- Zvoni · 2026-05-04 06:33 Is this happening only from your Frontend, or is it the same, when you use a different client (e.g. DB Browser for SQlite)? Maybe the Limit is in your frontend code and not on the backend ---------------------------------------------------------------------- cdbc · 2026-05-04 06:43 Hi I have no trouble saving several megabytes of pictures or text into SQLite3's BLOB-fields  Mind you -- I don't use "DBAware-gui-stuff"  Regards Benny ---------------------------------------------------------------------- Zvoni · 2026-05-04 07:18 Quote from: cdbc on May 04, 2026, 08:43:27 amHi I have no trouble saving several megabytes of pictures or text into SQLite3's BLOB-fields  Mind you -- I don't use "DBAware-gui-stuff"  Regards Benny Note that TS says "CLOB" which gets TEXT-Affinity (Basically the same as VARCHAR) BLOB is no Datatype at all ---------------------------------------------------------------------- cdbc · 2026-05-04 07:33 Aaaaha, haven't I read somtin' about, at least, varchars limits at 1024? I dunno... Regards Benny ---------------------------------------------------------------------- H₂SO₄ · 2026-05-04 07:36 Quote from: 1HuntnMan on April 30, 2026, 10:38:14 pm[...] when I save the record. Are you using  TParam.AsString  or  TParam.AsMemo? The former is intended for short strings only (another devious footgun, if you ask me). ---------------------------------------------------------------------- Zvoni · 2026-05-04 07:52 Quote from: Khrys on May 04, 2026, 09:36:53 amQuote from: 1HuntnMan on April 30, 2026, 10:38:14 pm[...] when I save the record. Are you using  TParam.AsString  or  TParam.AsMemo? The former is intended for short strings only (another devious footgun, if you ask me). That's why i asked if it just happens from his Frontend-code, or also with a different client ---------------------------------------------------------------------- 1HuntnMan · 2026-05-07 21:30 I'll do some testing with DB Browser just to cross-check. I just have a TDBMemo component on the forms for edit/adding. Thanks... ---------------------------------------------------------------------- 1HuntnMan · 2026-05-07 21:50 There's no real limit for text or memo according what I'm reading online. So, did some testing with copy/paste of a large amount of text into a Memo field in one of my tables via DB Browser. Saved just fine. Then open my app. and it's all there so. When testing I was copying test data from a website but when I saved it was cutting off the test data.  So, probably hidden characters or whatever.  Not an issue, thanks... ---------------------------------------------------------------------- H₂SO₄ · 2026-05-08 05:12 Somewhat off-topic, but has anyone else encountered issues when querying long text fields when using the ODBC  Microsoft Access Driver (*.mdb, *.accdb)? I've consistently gotten strings longer than 2 KiB being padded with large amounts of garbage data at the end. I've had to override a method in  TODBCConnection  to resolve this (but as far as I can remember this bug isn't caused by Lazarus's ODBC code itself, so I didn't report it). 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 // +-------------------------------------+// |          TAccessConnection          |// +-------------------------------------+ /// `TODBCConnection` descendant providing patches for the hopeless mess known as the Microsoft Access driver. /// Fixes an issue whereby the length of long text (aka `SQL_LONGVARCHAR`) fields is inflated by a factor of two/// when the contained data is longer than 2 KiB (e.g. possible length values: 2047, 2048, 4098, 4100, 4102, etc.).////// There is no rational explanation for this (we're talking about Microsoft Access, after all).procedure TAccessConnection.LoadBlobIntoBuffer(FieldDef: TFieldDef; FieldBuf: PBufBlobField;                                               Cursor: TSQLCursor; Trans: TSQLTransaction);begin   inherited LoadBlobIntoBuffer(FieldDef, FieldBuf, Cursor, Trans);   with FieldBuf^.BlobBuffer^ do if (FieldDef.DataType = ftMemo) and (Size > $800) then begin    Size := Size div 2;    ReAllocMem(Buffer, Size);  end;end; ---------------------------------------------------------------------- Thaddy · 2026-05-08 05:51 Although I first answered relating terminators we saw, it is a documented known bug in Microsoft's Access odbc driver for lengths over ~2k. ---------------------------------------------------------------------- H₂SO₄ · 2026-05-08 06:14 Quote from: Thaddy on May 08, 2026, 07:51:33 amAlthough I first answered relating terminators we saw, it is a documented known bug in Microsoft's Access odbc driver for lengths over ~2k. It's documented? Where? ---------------------------------------------------------------------- Thaddy · 2026-05-08 10:30 The only official mention is KB293653 in which you should ignore the unicode focus. But it is a known issue across languages and your solution is the correct one.