[Lazarus] Help reading SQLite field with more than 255

[mulcamd]

Date: Tue, 19 Nov 2013 09:27:23 +0100 (CET)
From: Michael Van Canneyt <michael at freepascal.org>
Subject: Re: [Lazarus] Help reading SQLite field with more than 255
	chars
To: Lazarus mailing list <lazarus at lists.lazarus.freepascal.org>
Message-ID: <alpine.DEB.2.02.1311190922270.5927 at home.telenet.be>
Content-Type: text/plain; charset="iso-8859-7"; Format="flowed"

On Mon, 18 Nov 2013, mulcamd wrote:

> 
> I?m reading the SQLite database of Adobe Lightroom (SQLite).
> 
> ?
> 
> I want to process the AdditionalMetadata table, see below, the XMP field,
which may be 3000 chars long.
> 
> When retrieving the contents of this field I only get 255.
>

I cannot create the table with the SQL you posted.
I get a
Error: duplicate column name: 
from sqlite.

Meanwhile you can try the following:

First of all, make sure you are using an ansistring variable instead of a
shortstring variable.
If that is OK, and you still get only 255, try using a cast expression:

Select id_local, cast (xmp as text)? from Adobe_AdditionalMetadata

or

Select id_local, cast (xmp as BLOB)? from Adobe_AdditionalMetadata

======== Answer
As you suggested I did a cast to text, yet the same result. So code below.
Based on the ShowMessage () debug messages I added I get:
  i := SQLQuery1.FieldByName('id_local').AsInteger;
  ShowMessage(IntToStr(i)); 
==> answer 205
  myText := SQLQuery1.FieldByName('type').asString;
  ShowMessage(myText);
==> text. So I get the right type
  myText := SQLQuery1.FieldByName('xmp').asString;
  ShowMessage(IntToStr(Length(myText)));
==> 255

My code is:
==== Code start
var
		  myText : String;
		  myLine : String;
          List: TStrings;
          i: Integer;
begin
  SQLite3Connection1.DatabaseName:='Test_v5.lrcat';
  SQLite3Connection1.Transaction:=SQLTransaction1;

  SQLTransaction1.Database:=SQLite3Connection1;

  SQLQuery1.Database:=SQLite3Connection1;
  SQLQuery1.Transaction:=SQLTransaction1;
  SQLQuery1.SQL.Text:='Select id_local, cast(xmp as text) as xmp,
typeof(cast(xmp as text)) as type from Adobe_AdditionalMetadata';
  SQLQuery1.Open;
  SQLQuery1.Next;

  i := SQLQuery1.FieldByName('id_local').AsInteger;
  ShowMessage(IntToStr(i));
  myText := SQLQuery1.FieldByName('type').asString;
  ShowMessage(myText);
  myText := SQLQuery1.FieldByName('xmp').asString;
  ShowMessage(IntToStr(Length(myText)));
  ShowMessage(myText);
==== Code end