TSQLite3DataSet - trouble saving Blobs

[victorhelsing]

Can anyone offer me a brief segment of code to store a Buffer of 1234 bytes of binary data into a blob field?  I have tried the approaches listed below without success.  I am using the TSQLite3DataSet component included with Lazarus 9.27 (think it is from the SQLitePass package, might be labeled sqlitelaz0.4 in the packages)

Thanks so much for your help!  Victor

____________________________
My failed attempts are listed below as an example: 

{For what it is worth, SQLiteBlobField is defined as a TStringField by the compiler when I add fields using the field editor on the form.  Don't know if this makes a difference.  In fact, most or all of the fields appear to be defined as TStringField...}

Able to modify regular fields e.g.
   SQLiteName.AsString := 'ABCD';   { works fine }

However, when trying to write a blob field, I can't get it to work:

  Stream := SQLite.CreateBlobStream (SQLiteBlobField, bmWrite);
  nBytes := Stream.Write (Buffer^, 1234);  { nBytes returns 1234 }
  Stream.Free;  { nothing is written to the blob field in the file }

  Stream := SQLite.CreateBlobStream (SQLiteBlobField, bmREAD);
  nBytes := Stream.Read (Buffer^, 1234);  { nbytes returns ZERO, nothing is there }
  Stream.Free;

Strangely, I tried this approach:

  SetLength (LocalStr, 1234);
  Move (Buffer^, LocalStr[1], 1234);
  SQLiteBlobField.AsString := LocalStr;     { does not store anything, although ... }
         { note that my buffer does NOT contain actual "string" data, but rather lots
            of binary data, including bytes = 0 }

  SQLiteBlobField.AsString := 'ABCD'; { surprisingly, works fine }

[Loesje]

I don't know TSQLite3Dataset very well, but I see some strange things...

{For what it is worth, SQLiteBlobField is defined as a TStringField by the compiler when I add fields using the field editor on the form.  Don't know if this makes a difference.  In fact, most or all of the fields appear to be defined as TStringField...}

So you aren't using a blob-field at all, but a string-field. Well, in principle I can stop here because that's can't ever work as a blob-field

However, when trying to write a blob field, I can't get it to work:
  Stream := SQLite.CreateBlobStream (SQLiteBlobField, bmWrite);

Didn't you find it strange that you use the CreateBlobStream procedure on a TStringField? (You may name it SQLiteBlobField, but if it's a TStringField, that doesn't matter)

  nBytes := Stream.Write (Buffer^, 1234);  { nBytes returns 1234 }
  Stream.Free;  { nothing is written to the blob field in the file }

This is also wrong I think. (Not completely sure for TSQLite3DataSet). You should call .post first before you free the stream. But I think you also have to write the changes to disk (.ApplyUpdates?) before you free the stream.

Strangely, I tried this approach:

  SetLength (LocalStr, 1234);
  Move (Buffer^, LocalStr[1], 1234);
  SQLiteBlobField.AsString := LocalStr;     { does not store anything, although ... }
         { note that my buffer does NOT contain actual "string" data, but rather lots
            of binary data, including bytes = 0 }

Should work. But maybe that TSQLite3DataSet can't handle it. (The #0 values, most probably)

  SQLiteBlobField.AsString := 'ABCD'; { surprisingly, works fine }

Perfectly normal, especially when you realize that SQLiteBlobFiels is defined as a TStringField.

[victorhelsing]

Well, your first thoughts are QUITE helpful.  Here is the definition of my table as exported by SqLiteMan:

CREATE TABLE DEFS (
    ID             INTEGER,
    NAME           VARCHAR(23),
    ABBREVIATION   VARCHAR(15),
    DEFTYPE        VARCHAR(15),
    DEFCLASS       VARCHAR(15),
    PREFIX         BLOB,
    MEMO           BLOB  ....

I was using PREFIX field in this example.  In contrast, here is the definition in my program, created by the field editor used when right click on the SQLite3Dataset component:

    SQL3Defs: TSqlite3Dataset;
    SQL3DefsABBREVFIELD: TStringField;
    ...
    SQL3DefsDATATYPE: TLongintField;
    SQL3DefsDEFAULTID: TLongintField;
    SQL3DefsDEFAULTNUMBER: TLongintField;
    SQL3DefsDEFAULTSTRING: TStringField;
    SQL3DefsDEFCLASS: TStringField;
    SQL3DefsDEFTYPE: TStringField;
    ...
    SQL3DefsOFFSETBLOCK: TStringField;         <------------This should be BLOB
    SQL3DefsPACKBYTES: TStringField;
    SQL3DefsPIXELWIDTH: TStringField;
    SQL3DefsPREFIX: TStringField;                  <-------------This should be BLOB
    ...

I wonder why the field editor assigns these fields with a TStringfield type...Incidentally, here is the schema (exported by IBExpert) from which I created the SQLite file.  It clearly designates this field as a Blob.

Can you recommend another SQLite component to use as an alternative?  I really like the idea of trying out SQLite rather than Interbase, since I have been experiencing some deployment issues, but this episode has spooked me a little bit.

Incidentally, on reading through the FAQ from SQLite.org, it states:

"(2) What datatypes does SQLite support?

    SQLite uses dynamic typing. Content can be stored as INTEGER, REAL, TEXT, BLOB, or as NULL.

(3) SQLite lets me insert a string into a database column of type integer!

    This is a feature, not a bug. SQLite uses dynamic typing. It does not enforce data type constraints. Any data can be inserted into any column. You can put arbitrary length strings into integer columns, floating point numbers in boolean columns, or dates in character columns. The datatype you assign to a column in the CREATE TABLE command does not restrict what data can be put into that column. Every column is able to hold an arbitrary length string. (There is one exception: Columns of type INTEGER PRIMARY KEY may only hold a 64-bit signed integer. An error will result if you try to put anything other than an integer into an INTEGER PRIMARY KEY column.)

    But SQLite does use the declared type of a column as a hint that you prefer values in that format. So, for example, if a column is of type INTEGER and you try to insert a string into that column, SQLite will attempt to convert the string into an integer. If it can, it inserts the integer instead. If not, it inserts the string. This feature is called type affinity. "

Anyway, thanks again very much for the help.  I knew there was a simple answer.  By the way, is there a good basic book on using SQLite, preferably with Pascal/Delphi?

[LuizAmérico]

The TSqlite3Dataset that comes with fpc/lazarus does not support yet blob fields. Sqlite itself supports blob fields, just is not implemented in that component.

You can use http://source.online.free.fr/ that supports blob