[Solved] Firebird and binary BLOB's
lazarus / 16-Databases

----------------------------------------------------------------------
SlowCoder · 2012-05-22 10:36

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:=ExtractFi
leName(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

----------------------------------------------------------------------
BigChimp · 2012-05-22 10:55

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

----------------------------------------------------------------------
ludob · 2012-05-22 11:30

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.

----------------------------------------------------------------------
SlowCoder · 2012-05-22 12:00

Hi Ludob, BigChimp,

Thanks for the swift reply.

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

Have a nice day
Slowcoder