[SOLVED] Is there a limit to a SQLite3 Memo/CLOB field?

[1HuntnMan]

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]

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]

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]

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]

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

Note that TS says "CLOB" which gets TEXT-Affinity (Basically the same as VARCHAR)
BLOB is no Datatype at all

[cdbc]

Aaaaha, haven't I read somtin' about, at least, varchars limits at 1024?
I dunno...
Regards Benny

[Khrys]

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

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

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]

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...

[Khrys]

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

1. // +-------------------------------------+
2. // |          TAccessConnection          |
3. // +-------------------------------------+
4.  
5. /// `TODBCConnection` descendant providing patches for the hopeless mess known as the Microsoft Access driver.
6.  
7. /// Fixes an issue whereby the length of long text (aka `SQL_LONGVARCHAR`) fields is inflated by a factor of two
8. /// when the contained data is longer than 2 KiB (e.g. possible length values: 2047, 2048, 4098, 4100, 4102, etc.).
9. ///
10. /// There is no rational explanation for this (we're talking about Microsoft Access, after all).
11. procedure TAccessConnection.LoadBlobIntoBuffer(FieldDef: TFieldDef; FieldBuf: PBufBlobField;
12.                                                Cursor: TSQLCursor; Trans: TSQLTransaction);
13. begin
14.  
15.   inherited LoadBlobIntoBuffer(FieldDef, FieldBuf, Cursor, Trans);
16.  
17.   with FieldBuf^.BlobBuffer^ do if (FieldDef.DataType = ftMemo) and (Size > $800) then begin
18.     Size := Size div 2;
19.     ReAllocMem(Buffer, Size);
20.   end;
21. end;

[Thaddy]

Although I first answered relating terminators we saw, it is a documented known bug in Microsoft's Access odbc driver for lengths over ~2k.

[Khrys]

Although 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]

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.