Recent

Author Topic: [Solved] Firebird and binary BLOB's  (Read 7302 times)

SlowCoder

  • New member
  • *
  • Posts: 8
[Solved] Firebird and binary BLOB's
« on: May 22, 2012, 12:36:13 pm »
Hi all,

I cannot find a clear example of using binary blob's with Firebird.

Using the following method does not work, I get SIGSEGV error on marked line:

Code: [Select]
procedure TDataModule1.ImportFiles(const FileNames: TStringList);
type
  TBuffer = array of byte;
var
  i,count: integer;
  Buffer: TBuffer;
  ImportFile: File of byte;
  b: byte;
begin
  if FileNames.Count>0 then begin
    SQLQuery2.Close;
    SQLQuery2.SQL.Text:=
      'insert into DOCUMENTS (DOC_BINARY,DOC_FILENAME) values (:DOC_BINARY,:DOC_FILENAME)';
    for i:=0 to FileNames.Count-1 do begin
      AssignFile(ImportFile,FileNames.Items[i]);
      ReSet(ImportFile,1);
      count:=0;
      while not Eof(ImportFile) do begin
        Count+=1;
        BlockRead(ImportFile,b,1);
        SetLength(Buffer,count);
        Buffer[count-1]:=b;
      end;

      { Next line gets SIGSEGV error }
      SQLQuery2.Params.ParamByName('DOC_BINARY').SetBlobData(@Buffer, count);
      SQLQuery2.Params.ParamByName('DOC_FILENAME').AsString:=ExtractFileName(FileNames.Items[i]);     
      SQLQuery2.ExecSQL;
      SQLTransaction1.Commit;
    end;
  end;
end;                   

Am I on the right track here? or is loading Blob's into Firebird done differently? Some suggestions or examples would be welcome.

Using: Laz 0.9.30.4, FPC 2.6.0, WinXP


Have a nice day
« Last Edit: May 22, 2012, 02:01:21 pm by SlowCoder »

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: Firebird and binary BLOB's
« Reply #1 on: May 22, 2012, 12:55:28 pm »
Don't know about SetBlobData (actually, it's more like I can't remember.. ;) )...

See e.g. firebirdsegment.pas in this bug report http://bugs.freepascal.org/view.php?id=20719 for a technique that loads a blob from a string, something like
Code: [Select]
  RecordText:='some string that obviously will always be the same in this example';
  for RecordCounter := 0 to 20 do
  begin
    FQuery.Open;
    FQuery.Insert;
    FQuery.FieldByName('ID').AsInteger:=RecordCounter;
    FQuery.FieldByName('BLOBCOL').Clear;
    FQuery.FieldByName('BLOBCOL').AsString := RecordText;
    FQuery.Post;
    FQuery.ApplyUpdates;
  end;

Let's hope ludob chimes in - he usually has answers to this kind of questions ;)
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

ludob

  • Hero Member
  • *****
  • Posts: 1173
Re: Firebird and binary BLOB's
« Reply #2 on: May 22, 2012, 01:30:10 pm »
Passing it as a string is indeed the easiest. The backend knows how to deal with blobs. Example to load a binary file in a blob:

Code: [Select]
var
  fs:TFileStream;
  buf:string;
begin
  SQLQuery1.SQL.Text:='RECREATE TABLE blobs ( id Integer DEFAULT ''0'' NOT NULL,'+
       'picture blob sub_type binary, constraint pk_people primary key (id))';
  SQLquery1.ExecSQL;
  SQLTransaction1.Commit;
  fs:=TFileStream.Create('project1.ico',fmOpenRead);
  setlength(buf,fs.Size);
  fs.ReadBuffer(buf[1],fs.Size);
  SQLQuery1.SQL.Text:='insert into blobs values(:ID,:blob)';
  SQLQuery1.Params[0].AsInteger:=1;
  SQLQuery1.Params[1].AsString:=buf;
  SQLquery1.ExecSQL;
  SQLTransaction1.Commit;
  fs.Free;
end;

Edit: didn't reply why the SIGSEGV: in SetBlobData(@Buffer, count), @Buffer is the address of the variable that holds the pointer to the TBuffer array. You should pass a pointer to the first byte of the array:
Code: [Select]
SetBlobData(@Buffer[0], count) or
Code: [Select]
SetBlobData(pointer(Buffer), count) 
The problem with dynamic arrays and blockread is that there is a lot of memory re-allocation going on which causes a lot of data being moved around. The above string solution is much easier and faster.
« Last Edit: May 22, 2012, 01:47:41 pm by ludob »

SlowCoder

  • New member
  • *
  • Posts: 8
Re: Firebird and binary BLOB's
« Reply #3 on: May 22, 2012, 02:00:17 pm »
Hi Ludob, BigChimp,

Thanks for the swift reply.

Adapted my code to Ludob's example, works like a charm.

Have a nice day
Slowcoder