Storing BLOBs in Postgresql
lazarus / 16-Databases

----------------------------------------------------------------------
wimpie · 2009-03-30 18:22

Hi All

I use TPQConnection, TSQLTransaction and TSQLQuery to work with a
postgres db. I tried to store data in a 'bytea' (BLOB) field using the
following code:

Query.Params.ParamByName('Signal').AsBlob := string(Data);

At runtime I get the following error:
:Parameters of the BLOB are not (yet) supported.

Is there any other way to store blobs in a PostgreSQL db at the
moment?

----------------------------------------------------------------------
Loesje · 2009-03-31 10:47

No, as PostgreSQL doesn't support blob fields afaik.

You can store blob-data and make a field that contains a link the the
stored blob-data, but I don't know any method to determine if a field
contains a reference to a blob field or not.

So if you can help me with that...

----------------------------------------------------------------------
wimpie · 2009-03-31 13:08

Postgres does support BLOBs. It is called 'bytea' (Byte Array).

I don't quite follow you...where do you store the blobs? In a disk
file?

Anyway, I managed to get a workaround method. It works well, but it
increases the amount of stored data by 30%. The fields in the DB are
of type 'text'. The binary data is first converted to Base64 and then
stored as a string.

Code: [Select]procedure StoreBlobField()
begin
  //Calc buffer length
  ByteCount := length(Data) * sizeof(Data[0]);

  MemStream    := TMemoryStream.Create;
  EncodeStream := TBase64EncodingStream.Create(MemStream);

  //Perform encoding
  EncodeStream.Write(Data[0], ByteCount);

  //Prepare SQL
  Connection.Query.SQL.Text := 'INSERT INTO table (ZoneKey, Time,
Signal) VALUES (1,''NOW'', :Signal)';

  //Set parameter
  Connection.Query.Params.ParamByName('Signal').LoadFromStream(MemStre
am, ftBlob);

  //The blob data is now base64 text. Set the field correctly
  Connection.Query.Params.ParamByName('Signal').DataType := ftString;

  //Execute the query
  Connection.Query.ExecSQL;
  Connection.Commit;
end

Code: [Select]procedure ReadBlobField()
var
  ByteCount: integer;
  EncodedStr: string;
  MemStream: TMemoryStream;
  DecodeStream: TBase64DecodingStream;
  Signal: array of byte;
begin
  //Fetch data from DB
  SQLQuery.SQL.Text:= Format(SqlText, [aDBIncKey]);
  SQLQuery.Active := true;
  SQLQuery.First;
  EncodedStr := SQLQuery.FieldByName('signal').AsString;
  SQLQuery.Active := false;

  //Create streams for decoding
  MemStream    := TMemoryStream.Create;
  DecodeStream := TBase64DecodingStream.Create(MemStream);

  //Find max data length
  ByteCount := length(EncodedStr);

  //Put encoded data in memory stream. String[0] is the size.
  MemStream.Write((EncodedStr[1]), ByteCount);
  MemStream.Seek(0, soFromBeginning); //We want to read the data just
written

  //Make space in target buffer
  Setlength(Signal, ByteCount);

  //Decode the data into the target
  DecodeStream.Reset;
  DecodeStream.Read(Signal[0], ByteCount);
end;

----------------------------------------------------------------------
Loesje · 2009-04-05 21:04

bytea fields are not Postgres' blob fields. The real postgres-blobs
are described here.

Bytea fields are just strings which are stored encoded. Just like you
implement manually using base64-encoding. Only the bytea encoding uses
less space. But it's a slow solution since you always have to parse
your data. Storing large files in a database that way is not really
what I would choose for.

But as the real blob's aren't implementable as the other blob-fields
in sqldb are, I can use the bytea fields as an alternative. I'll look
at it.