samedi 28 novembre 2015

SQLite - How to insert BMP image from XML file (using Delphi 2009)

I need to read a BMP image stored in a XML file and insert it into a blob field in a SQLite database.

I can do parts of this already as shown in the code below but need help with putting it together. Can sombody please show me how to read the textual bitmap in the XML file and insert it into the blob field in the database?

This is what I can already do.

I can read the XML file using a delphi XML Data Binding unit and store textual stuff from it in the database using the following (simplified) code:

procedure TForm1.BtnReadXML(Sender: TObject);
 var
   sql : string;
   Data : XML_Binding_Photos.IXMLSuperStarReportType;  
   RecNum : integer;

begin
Data := XML_Binding_Photos.LoadSuperStarReport ('Photos.xml'); 
Database1.Open;
try
    RecNum := 2;
    SQL := 'INSERT INTO main.Photos ( id , firstname) VALUES ( Data.record_[RecNum].ID,  Data.record_[RecNum].Fname ) ;' ;
    Query1.Close;
    Query1.selectSQL := sql; //insert the data
    Query1.Open ;
finally
    Database1.Close;
end;
end;

I can also insert a JPG image from disc into the database using streams like this:

procedure TForm1.Insert_JPG_PhotoFromFile(Sender: TObject);
var 
    strm : TmemoryStream ;
begin
Database1.Open;
strm := TmemoryStream.Create;
try
    strm.LoadFromFile('C:\...\testpic2.jpg');
    Query1.Close;
    Query1.selectSQL := ('INSERT INTO main.Photos (id, photo) VALUES (''999'', :photo)');
    Query1.Params.ParamByName('photo').LoadFromStream(Strm,ftGraphic);
    Query1.Open ;
finally
    strm.Free ;
    DISQLite3Database1.close;
end;
end; 

But my XML file has a BITMAP image in it, encoded as follows:

 <Record>
    <ID>14046</ID>
    <Fname>Fred</Fname>
    <Photo>/9j/4AAQSkZJRgABAQEASABIAAD/2wBDAAE  - lots more of the same - txNxvd8dc44Htz/Ktx3Oe3T/AB96KK6DA//Z </Photo> 
  </Record>  

I can read the characters making up the bitmap into a string or a StringStream using the XML Data Binding unit but can't seem to get it saved correctly as a blob in the database.

(I don't want to save each XML image to disc as jpg first, partly as its already on the disc in a different form and partly as there are thousands of these images in the xml file. Also, for other reasons, I do want to put the image in the database, rather than put it on disc and just store the filename)

Aucun commentaire:

Enregistrer un commentaire