Help reading SQLite field with more than 255

mulcamd11-19-2013 16:42 UTC

Date: Tue, 19 Nov 2013 09:27:23 +0100 (CET)
From: Michael Van Canneyt
Subject: Re: [Lazarus] Help reading SQLite field with more than 255 chars
To: Lazarus mailing list
Message-ID:
Content-Type: text/plain; charset="iso-8859-7"; Format="flowed"

mulcamd wrote 6 lines

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

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

mulcamd11-19-2013 16:50 UTC

Message-ID:

Hi,

The solution was:
  SQLQuery1.SQL.Text:='Select id_local, cast(xmp as blob) as xmp from
Adobe_AdditionalMetadata';

Thank you Michael Van Canneyt for the tip!

-----Oorspronkelijk bericht-----
Van: mulcamd [mailto:]
Verzonden: dinsdag 19 november 2013 17:43
Aan: '
Onderwerp: Re: [Lazarus] Help reading SQLite field with more than 255