mORMot Open Source

sh17

Member

Registered: 2011-08-09

Posts: 26

Can't obtain Blob from SQLite

Can't obtain Blob direct from SQLite

Which DBName is correct?

var
  sql : TSQLDatabase;
  blob : TSQLBlobStream;
  memory : TMemoryStream;
begin
  sql := TSQLDatabase.Create('d:\temp\test.db');
  try               
     sql.Execute('CREATE TABLE IF NOT EXISTS "files" ("UID"    INTEGER PRIMARY KEY AUTOINCREMENT, "Filename"    TEXT NOT NULL, "File"    BLOB);');

    memory := TMemoryStream.Create;
    memory.LoadFromFile('d:\temp\v\DOCUMENT_2013-20280_2013_05_14_11_14_31.XML');
    memory.Position := 0;
 
    sql.Execute('INSERT INTO files (Filename) VALUES ("'+UTF8Encode('DOCUMENT_2013-20280_2013_05_14_11_14_31.XML')+'")');
    blob := sql.Blob('test.db','files','File',0,true);     <------------ no such table test.db.files extended_errcode=1
    blob.CopyFrom(memory,memory.Size);
    memory.Free;
    blob.Free;
  finally
    sql.Destroy;
  end;

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,207

Website

Re: Can't obtain Blob from SQLite

Your path is not correct.
Try 'd:\temp\test.db' instead of 'test.db'.

But why do you use sql.Blob() ?
Use a regular SELECT statement with ColumnBlob() on the TSQLDatabase.
It would be much faster and safer.

sh17

Member

Registered: 2011-08-09

Posts: 26

Re: Can't obtain Blob from SQLite

Your path is not correct.
Try 'd:\temp\test.db' instead of 'test.db'.

dosn't work.

https://www.sqlite.org/c3ref/blob_open.html say the dbname is called 'main', but it also does not work

But why do you use sql.Blob() ?
Use a regular SELECT statement with ColumnBlob() on the TSQLDatabase.
It would be much faster and safer.

sorry, i can't find some example or documentation to read / write binary blobs

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,207

Website

Re: Can't obtain Blob from SQLite

Create a SELECT statement then read the BLOB column with ColumnBlob().

sh17

Member

Registered: 2011-08-09

Posts: 26

Re: Can't obtain Blob from SQLite

according to my first post,

This code should work, but it does not.
Can someone take a look on this, please.
thanks

var
  sql : TSQLDatabase;
  blob : TSQLBlobStream;
  memory : TMemoryStream;
begin
  sql := TSQLDatabase.Create('d:\temp\test.db');
  try                
     sql.Execute('CREATE TABLE IF NOT EXISTS "files" ("UID"    INTEGER PRIMARY KEY AUTOINCREMENT, "Filename"    TEXT NOT NULL, "File"    BLOB);');
    memory := TMemoryStream.Create;
    memory.LoadFromFile('d:\temp\v\DOCUMENT_2013-20280_2013_05_14_11_14_31.XML');
    memory.Position := 0;
  
    sql.Execute('INSERT INTO files (Filename) VALUES ("'+StringToUTF8('DOCUMENT_2013-20280_2013_05_14_11_14_31.XML')+'")');
    blob := sql.Blob('main','files','File',0,true);     <------------ cannot open value of type null extended_errcode=1
    blob.CopyFrom(memory,memory.Size);
    memory.Free;
    blob.Free;
  finally
    sql.Destroy;
  end;

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,207

Website

Re: Can't obtain Blob from SQLite

Why just not use create a TSQLRequest with

 INSERT into files(filename,file) values (?,?)

Then

 aStmt.Bind(1,aFileName');
 aStmt.BindBlob(2,StringFromFile('d:\temp\v\DOCUMENT_2013-20280_2013_05_14_11_14_31.XML')); // or aStmt.Bind(2,memory);
 aStmt.Execute;

sh17

Member

Registered: 2011-08-09

Posts: 26

Re: Can't obtain Blob from SQLite

Thanks