Trouble saving blob to file
lazarus / 16-Databases

----------------------------------------------------------------------
wizbowes · 2013-04-18 22:07

I have a SQLite database that I've successfully written some .xml
files to.  Selecting the data from the command line shows the data is
fine in the database.  I'm now trying to get the data back out and
save it.  Using the procedure below, the first 8 characters are
corrupted, and then the rest is fine. 

I'm sure it's something obvious - but any ideas where I've gone wrong?

Code: [Select]procedure TForm1.SaveFromDatabase();
var
 blob : TStream;
begin
    SQLTransaction.StartTransaction;
    SQLQuery.SQL.Text:='select FileData from Presets where PresetID =
1';
    SQLQuery.Open;
    blob := SQLQuery.CreateBlobStream(SQLQuery.Fields[0],bmread);
    SQLQuery.Close;
    SQLTransaction.EndTransaction;
    blob.Seek(0, soFromBeginning);
        with TFileStream.Create('C:\temp\aaaa.xml', fmCreate) do
        try
          CopyFrom(blob, blob.Size)
        finally
          Free
        end;
    blob.Free;
end;

----------------------------------------------------------------------
BigChimp · 2013-04-19 05:43

Probably on Windows then? What Laz/FPC version are you using?

The blob upload part is also part of your program? I assume that the
blob appears fine when using a 3rd party sqlite tool?

The only thing I'm not sure about is
Code: [Select]blob :=
SQLQuery.CreateBlobStream(SQLQuery.Fields[0],bmread);
the fields[0] part: perhap that should be cast to bytes or something
.AsBlob or something (sorry, don't know th various .As... properties).
Rationale: perhaps sqldb thinks that field is a text field or
something. Still wierd that only the first 8 bytes get corrupted.

----------------------------------------------------------------------
goodname · 2013-04-19 13:24

Following post was specifically for PostgreSQL and images but it looks
much the same as your trying to do. Can't see anything wrong with the
code you have written.
http://www.lazarus.freepascal.org/index.php/topic,16205.0.html

----------------------------------------------------------------------
wizbowes · 2013-04-19 23:49

Thanks for the help everybody.  I actually solved it another way as I
also had another problem after I solved the corruption issue in that
the file was also saved with an additional carriage return which I
couldn't have.  For the record - here's my solution:

Code: [Select]procedure TForm1.SaveFromDatabase();
var
 fs: TFileStream;
 FileData : TStringList;
begin
    SQLTransaction.StartTransaction;
    SQLQuery.SQL.Text:='select FileData from Presets where PresetID =
1'
    SQLQuery.Open;
    FileData.Add(SQLQuery.Fields[0].AsWideString);
    SQLQuery.Close;
    SQLTransaction.EndTransaction;
    fs := TFileStream.Create('C:\temp\aaaa.xml', fmCreate);
    FileData.SaveToStream(fs);
    fs.Size := fs.Size - Length(System.sLineBreak);
    fs.free;
    FileData.Free;
end;

----------------------------------------------------------------------
BigChimp · 2013-04-20 07:03

Ah ok, widestrings so it is an UTF16 XML file I suppose.

Thanks for posting the working solution!